Dapper - Null values are being inserted into MySql table

Question

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.

Using @ and : instead of ? in the query.
Using the Expando directly in QueryAsync instead of converting it to DynamicParameters.
Using an IDictionary as the input instead of the ExpandoObject and passing it directly to QueryAsync.
Using QueryAsync instead of ExecuteAsync.

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.

The MySql version I'm using is 5.7.13
My Dapper version is 1.60.6
The MySql.Data nuget package that I'm using is 8.0.17

Can you confirm that the values in `param.Key` match exactly the params in your sql: `salutation`, `firstName`, `firstName`. Also, I always prefix mine with `@`, not `?`. You also don't need to use the `@/?` in your `param.Key` value. — scgough, Oct 04 '19 at 15:29
@scgough, I confirmed that each param.key matches the params in the SQL. And yes, in the query, I did prefix the params with @. In fact, I tried using ? after using @ didn't work. — SirG, Oct 04 '19 at 18:30
Hmm. Ok, try bypassing the expando with hard-codes values instead and see if that works. Also, silly question I know but has the param.Value got a value. — scgough, Oct 04 '19 at 18:38
Yes, it works with hard-coded values, and I also confirmed that param.Value is assigned correctly. — SirG, Oct 04 '19 at 18:56
It works when I use a MySqlCommand object directly and set its CommandText to the sql value. — SirG, Oct 05 '19 at 12:16
Hmm. Ok. Maybe it’s because Dapper can’t correctly determine the value types from the DynamicParameters? — scgough, Oct 06 '19 at 09:02

score 1 · Answer 1 · answered Oct 08 '19 at 08:16

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.

Dapper - Null values are being inserted into MySql table

1 Answers1