1
public void InsertUser(string firstname, string lastname, string email, string password, int age)
{
    using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("Database")))
    {
        List<user> users = new List<user>();
        users.Add(new user { Firstname = firstname, Lastname = lastname, Email = email, Password = password, Age = age });
        connection.Execute("InsertUserProcedure", @Firstname,@Lastname,@Email,@Password,@Age,users);
    }
}
Filburt
  • 17,626
  • 12
  • 64
  • 115
Mily
  • 11
  • 1

1 Answers1

1

The following is general guidance that applies to most ADO.NET providers; it does not take into account any MySQL peculiarities or nuances (because I'm not a MySQL user)

If the stored procedure parameters match exactly the properties on user, then you should be able to do this simply as;

connection.Execute(
    "InsertUserProcedure", users, commandType: CommandType.StoredProcedure);

If there are more properties on user than the stored procedure wants, then one approach is to invoke the stored procedure indirectly via EXEC / CALL (as a CommandText, which is the default):

connection.Execute(
    "EXEC InsertUserProcedure @Firstname,@Lastname,@Email,@Password,@Age", users);

(or the same with CALL instead of EXEC, on MySQL apparently - see comments)

Another approach is to use Select as a projection, allowing you to use the stored procedure directly, but limiting the properties that are passed in.:

connection.Execute("InsertUserProcedure", users.Select(u => new {
        u.Firstname, u.Lastname, u.Email, u.Password, u.Age
    }, commandType: CommandType.StoredProcedure);
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Hi Marcthe stored procedure parameters match exactly the properties on user – Mily Jun 17 '20 at 11:53
  • but I will get error of lastname column cant be null – Mily Jun 17 '20 at 11:54
  • @Mily did you specify `commandType: CommandType.StoredProcedure` ? – Marc Gravell Jun 17 '20 at 11:55
  • if I use your sec script I get Syntax error and ask to check MariaDb server version – Mily Jun 17 '20 at 11:56
  • @Mily maybe try `:Firstname,:Lastname,:Email,...` etc? I'm not a MySQL person, but I know Dapper "pretty well" – Marc Gravell Jun 17 '20 at 11:58
  • yes I did connection.Execute("InsertUserProcedure", users, commandType: CommandType.StoredProcedure ); – Mily Jun 17 '20 at 12:00
  • no doesn't work still, the third script also gives an error under Commandtype which saying select does not have a parameter named 'commandtype – Mily Jun 17 '20 at 12:06
  • I have the code for SQL server and works fine but MySQL syntax is a bit different connection.Execute("dbo.users_insert @Firstname,@Lastname,@Email,@Password,@Age",users); – Mily Jun 17 '20 at 12:09
  • Marc's code works perfectly for me (using MySqlConnector or MySql.Data). The only change necessary is `connection.Execute("CALL InsertUserProcedure(@Firstname,@Lastname,@Email,@Password,@Age)", users);` for the non-`CommandType.StoredProcedure` approach, but using `CommandType.StoredProcedure` will work fine (even if there are more properties on `users` than the stored procedure accepts). – Bradley Grainger Jun 18 '20 at 02:47
  • 1
    @Bradley that's useful, thanks; have clarified in an edit – Marc Gravell Jun 18 '20 at 05:21