Tweaking Dapper Parameters on the Fly

Tweaking Dapper Parameters on the Fly

So I’ve been working with Dapper lately (love that micro-ORM!), and ran into an interesting challenge around parameter manipulation. If you’ve used Dapper, you know it’s blazingly fast and dead simple to work with. Here’s a typical example:

public async Task<List<T>> GetSomeDataAsync<T>(
    DateTime startDate, DateTime endDate,
    CancellationTokenSource sourceToken = null)
{
    if (sourceToken == null) sourceToken = new CancellationTokenSource();
    using (var con = CreateConnection())
    {
        var sql = @"SELECT (your field list) FROM (your table/view/function))(
                    @StartDate, @EndDate)";
        var command = CreateCommand(sql, new
        {
            @StartDate = startDate,
            @EndDate = endDate
        }, cancellationToken: sourceToken.Token);
        return await con.QueryCmdAsync<T>(command);
    }
}

Pretty straightforward stuff - I’m hitting a table-valued function with date parameters. The cool thing here is how we can just throw anonymous objects at Dapper and it figures everything out. Those startDate and endDate parameters get turned into a CommandDefinition automagically.

The CreateCommand helper is where the magic happens - it’s basically our central place to mess with commands before they hit the database:

public static CommandDefinition CreateCommand(
    string commandText, object parameters = null,
    IDbTransaction transaction = null,
    int? commandTimeout = null,
    CommandType? commandType = null,
    CommandFlags flags = CommandFlags.Buffered,
    CancellationToken cancellationToken = default)
{
    //Here you can apply some custom code before the creation of the command definition
    return new CommandDefinition(
        commandText, parameters, transaction,
        commandTimeout ?? SqlHelper.GetCommandTimeout(),
        commandType, flags, cancellationToken);
}

The Problem: DateTime Hell

Now here’s where things get spicy. Imagine you need to validate parameters before they hit the database - maybe prevent some wonky values that’ll make SQL Server throw a tantrum. A classic example? The age-old DateTime mismatch between .NET and SQL Server:

The SQL Server DateTime type range is from January 1, 1753, through December 31, 9999
NET Framework DateTime struct range is from January 1, 0001, through December 31, 9999

See the problem? If you accidentally send a DateTime earlier than 1753 to SQL Server, you’re gonna have a bad time. Wouldn’t it be sweet if we could catch these edge cases before they blow up?

Digging into Anonymous Objects

Alright, so here’s the thing - if you want to peek inside an anonymous object and mess with its properties, reflection is pretty much your only option.

With reflection, we can loop through the properties and sniff out those DateTime values that might cause trouble:

var paramsProperties = parameters.GetType().GetProperties();
foreach (var p in paramsProperties)
{
    if (p.PropertyType == typeof(DateTime))
    {
        DateTime dateValue = Convert.ToDateTime(
            parameters.GetType().GetProperty(p.Name).GetValue(parameters));
        //do something
    }
}

The Immutability Problem

However, in c# anonymous types are immutable and we cannot change their value via reflection.

Sadly, this code won’t work:

parameters.GetType().GetProperty(p.Name).SetValue(parameters, newValue));

There are different ways to work around this. You could use an ExpandoObject, but I’m going with Dapper’s DynamicParameters since CommandDefinition happily accepts either regular objects or DynamicParameters.

The Solution

Here’s the game plan: loop through all the parameter properties with reflection, check any DateTime values, and if they’re earlier than SQL Server’s minimum date, bump them up. Then stuff everything into a DynamicParameters collection:

private static DynamicParameters GetDynamicParameters(object parameters)
{
    var sqlMinDate = System.Data.SqlTypes.SqlDateTime.MinValue;
    var newParameters = new DynamicParameters();
    var paramsProperties = parameters.GetType().GetProperties();
    foreach (var p in paramsProperties)
    {
        if (p.PropertyType == typeof(DateTime))
        {
            DateTime dateValue = Convert.ToDateTime(
                parameters.GetType().GetProperty(p.Name).GetValue(parameters));
            if (dateValue <= sqlMinDate.Value) dateValue = sqlMinDate.Value;
            newParameters.Add(p.Name, dateValue);
        }
        else
            newParameters.Add(p.Name,
                parameters.GetType().GetProperty(p.Name).GetValue(parameters));
    }
    return newParameters;
}

Now we can use the previous method in the CreateCommand method:

public static CommandDefinition CreateCommand(
    string commandText, object parameters = null,
    IDbTransaction transaction = null,
    int? commandTimeout = null,
    CommandType? commandType = null,
    CommandFlags flags = CommandFlags.Buffered,
    CancellationToken cancellationToken = default)
{
    if (parameters != null)
        parameters = GetDynamicParameters(parameters);
    return new CommandDefinition(
        commandText, parameters, transaction,
        commandTimeout ?? SqlHelper.GetCommandTimeout(),
        commandType, flags, cancellationToken);
}

This post is just a note to my future self :)

Hope you enjoy it, and feel free to send me your thoughts about this solution.