Build Dapper SQL Builder query param using Reflection

Question

I'm working on the project that use Dapper as our DAL layer and because of we're still using inline queries. To construct our query, we're using dapper sqlBuilder template.

Below is one of our method code:

SqlBuilder builder = new SqlBuilder();
        var update = builder.AddTemplate("DECLARE @transactionID bigint; " +
                                         "EXEC @transactionID = dbo.sp_getAndIncrementTransactionCounter @accountID; " +
                                         "UPDATE TimeEntryIndex /**set**/ OUTPUT inserted.id /**where**/");

        builder.Set("userID         = @userID", new { field.UserId });
        builder.Set("deviceID       = @deviceID", new { field.DeviceId });
        builder.Set("deviceName     = @deviceName", new { field.DeviceName });
        builder.Set("transactionID  = @transactionID");
        builder.Set("state          = @state", new { ttContent.state });
        builder.Set("lastUpdated    = GETUTCDATE()");

        // Determine which fields to update
        if (!string.IsNullOrEmpty(ttContent.title))
        {
            builder.Set("title = @title", new { ttContent.title });
        }
        if (!string.IsNullOrEmpty(ttContent.description))
        {
            builder.Set("description = @description", new { ttContent.description });
        }
        if (tEntryIndex.finish.HasValue)
        {
            builder.Set("finish = @finish", new { tEntryIndex.finish });
        }
        if (ttContent.created.HasValue)
        {
            builder.Set("created = @created", new { ttContent.created });
        }
        if (tEntryIndex.duration.HasValue)
        {
            builder.Set("duration = @duration", new { tEntryIndex.duration });
        }

        builder.Where("accountid = @accountid", new { accountid = field.AccountId });
        builder.Where("id = @id", new { id = timeEntryId });

        var result = new Result<long?>(sqlConn.ExecuteScalar<long?>(update.RawSql, update.Parameters));

In the above code, it looks right for a small amount of field in the database. But when we have a lot field in the table let's say 25 column it takes a lot of works just for constructing that builder.

I thought to wrap it in a function using reflection. I try it first with something like this:

foreach (PropertyInfo prop in typeof(Account).GetProperties())
{
   // Will do checking on every type possible
   if (typeof(String).IsAssignableFrom(prop.PropertyType) && prop.GetValue(aContent) != null && !string.IsNullOrEmpty(prop.GetValue(aContent).ToString()))
   {
       builder.Set(string.Format("{0} = @{0}", prop.Name.ToLowerInvariant()), new { <what should I put in here> });
   }
 }

The problem is I can't find a way to make it works with a dynamic parameter that Dapper needs on its builder class.

Every suggestion and help would be very appreciated.

Answer 1

You can use the DynamicParamters Class for add dynamic name-value pair:

var prms = new DynamicParameters();
foreach (PropertyInfo prop in typeof(Account).GetProperties())
{
    // Will do checking on every type possible
    if (typeof(String).IsAssignableFrom(prop.PropertyType) && prop.GetValue(aContent) != null && !string.IsNullOrEmpty(prop.GetValue(aContent).ToString()))
    {
        builder.Set(string.Format("{0} = @{0}", prop.Name.ToLowerInvariant())ף
        prms.Add(prop.Name.ToLowerInvariant(), prop.GetValue(aContent));
    }
}

prms.AddDynamicParams(update.Parameters);
var result = new Result<long?>(sqlConn.ExecuteScalar<long?>(update.RawSql, prms));