After hours of trying to figure out the problem, I am still stuck. Here is the problem:
I have a MySql query in my C# code:
var sql = "INSERT INTO MEMBERS(Salutation, FirstName, LastName) VALUES(?salutation, ?firstName, ?lastName)";
This query is in a method which accepts an ExpandoObject:
public async Task AddMember(ExpandoObject expando)
{
var sql = "INSERT INTO MEMBERS(Salutation, FirstName, LastName) VALUES(?salutation, ?firstName, ?lastName)";
// Convert the incoming ExpandoObject to Dapper's DynamicParameters
// https://stackoverflow.com/questions/9481678/how-to-create-arguments-for-a-dapper-query-dynamically
if (expando != null)
{
var dbArgs = new DynamicParameters();
foreach (var param in expando)
{
dbArgs.Add(param.Key, param.Value);
}
await mySqlConnection.ExecuteAsync(sql, dbArgs);
}
}
The query is executing (meaning that I am not getting an error, and a row is created in the Members table, and also the MemberId column value is generated), but Salutation, FirstName and LastName are null.
I checked the contents of the Expando. The input values are all there.
I tried various things.
Nothing works. The 3 columns are null every time. Not sure what is wrong here.
Due to several reasons, I can't use a strong type like Member as the input to this method. I have to use an ExpandoObject.
OK, I'm basing this on the theory that we discussed above. I'd expand on your DynamicParameters
so you are passing in DBType
values as well.
For example: (source: https://dapper-tutorial.net/parameter-dynamic)
parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);
parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
So, in your case it could be something like:
public async Task AddMember(ExpandoObject expando)
{
var sql = "INSERT INTO MEMBERS(Salutation, FirstName, LastName) VALUES(@salutation, @firstName, @lastName)";
// Convert the incoming ExpandoObject to Dapper's DynamicParameters
// https://stackoverflow.com/questions/9481678/how-to-create-arguments-for-a-dapper-query-dynamically
if (expando != null)
{
var dbArgs = new DynamicParameters();
foreach (var param in expando)
{
dbArgs.Add(param.Key, param.Value, DbType.String, ParameterDirection.Input);
}
await mySqlConnection.ExecuteAsync(sql, dbArgs);
}
}
I get this detracts a little from the dynamic way you're trying to do this (by having the type and direction hard-coded) but you could add to your initial Expando to allow for this value to be set and passed in to the AddMember
method.