Trim String with Dapper.NET

c# dapper sql

Question

I have been using Dapper.NET for a while now. I was just wondering if it's possible to get Dapper to trim strings as it assigns them to the properties of the object.

I currently use LTRIM(RTRIM(fieldname)) in the SQL, and / or value.Trim() in the property setter.

I am however working with a legacy database that uses chars instead of varchar, and I was wondering if there was a way to reduce my time of having to trim everything.

I had a go myself by editing the source code of dapper but ended up breaking other mappings etc so gave in.

Just wondered if anyone had any suggestions that could reduce this overhead. (I may be missing something very simple!)

I am working with C# 3.5 by the way.

Accepted Answer

I didn't like the idea of modifying Dapper directly. I decided to solve the problem by creating an extension method to wrap Dapper's and just reflect over the result and trim all string properties.

public static class DapperExtensions {
    public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) {
        var dapperResult = SqlMapper.Query<T>(cnn, sql, param, transaction, buffered, commandTimeout, commandType);
        var result = TrimStrings(dapperResult.ToList());
        return result;
    }

    static IEnumerable<T> TrimStrings<T>(IList<T> objects) {
        //todo: create an Attribute that can designate that a property shouldn't be trimmed if we need it
        var publicInstanceStringProperties = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => x.PropertyType == typeof (string) && x.CanRead &&  x.CanWrite);
        foreach (var prop in publicInstanceStringProperties) {
            foreach (var obj in objects) {
                var value = (string) prop.GetValue(obj);
                var trimmedValue = value.SafeTrim();
                prop.SetValue(obj, trimmedValue);
            }
        }
        return objects;
    }

    static string SafeTrim(this string source) {
        if (source == null) {
            return null;
        }
        return source.Trim();
    }
}

Crucial to my solution (since I wanted to use the same name as Dapper) is how extension method resolution works, which you can read about here.


Popular Answer

Matt,

This can be done quite easy. I made this change to clean the SQL char spaces. I have tested it and my code shows no signs if slowness due to the change.

First make sure that you back up your exiting source code so it will be easier to revert back should you need to.

Next Create the following method:

public static string ReadString(object value) /*** CUSTOM CODE ***/
{
    if (value == null || value is DBNull) return null; 
    return value.ToString().Trim();
}

I always mark all my code changes with /* CUSTOM CODE */ so that I can later find my changes easily

Next find the following method:

public static void SetTypeMap(Type type, ITypeMap map)

now in that method locate the following lines:

if (memberType == typeof (char) || memberType == typeof (char?))
{
    il.EmitCall(OpCodes.Call, typeof (SqlMapper).GetMethod(
        memberType == typeof (char) ? "ReadChar" : "ReadNullableChar",
        BindingFlags.Static | BindingFlags.Public), null);
        // stack is now [target][target][typed-value]
}
else

and modify as follow:

if (memberType == typeof (char) || memberType == typeof (char?))
{
    il.EmitCall(OpCodes.Call, typeof (SqlMapper).GetMethod(
        memberType == typeof (char) ? "ReadChar" : "ReadNullableChar",
        BindingFlags.Static | BindingFlags.Public), null);
        // stack is now [target][target][typed-value]
}
else if (memberType == typeof(string)) /*** CUSTOM CODE START ***/
{
    il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod("ReadString", BindingFlags.Static | BindingFlags.Public), null);
    // stack is now [target][target][typed-value]
}    /*** CUSTOM CODE END ***/
else

Compile and you are ready to go




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why