CODEWITHSUNDEEP
mysqldapper
SirG
SirG

Reputation: 389

Dapper - Null values are being inserted into MySql table

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.

Upvotes: 1

Views: 1444

Answers (1)

scgough
scgough

Reputation: 5252

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.

Upvotes: 1

Related Questions