Trim String with Dapper.NET

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.

Answer 1

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.

Answer 2

I am assuming that you want a more natural way to imply the LTRIM(RTRIM()) function through your POCOs such that you don't need to manually type in LTRIM(RTRIM()) every time you want to return that field, giving you the SQL performance without the manual repeated labour.

I have two options for you:

On the Dapper-Dot-Net side:

You could add some code right at the query function stage, performing a replace algorithm on the raw SQL query.

• If you have multiple tables in the query, I would consider this out of scope
• Otherwise I believe this can be done quite easily without disrupting the rest of the dapper engine.

Process:

• First perform an ignorant IndexOf on your table name, ignoring case so you can do a quick pass-through if it's not.
• If the table name was found, then perform some more reliable analysis of the query, making sure the table name you found was in fact a table-name in the query. Here you can also ensure there is only one table and no joins in the query.
• If there is a * on the select, this can be expanded to the field names based on your poco definition
• So now you simply have fields in your select line-up
• For all fields, in the select line-up, find the names in your poco definition, where there is a [Trim] attribute, replace the field name with LTRIM(RTRIM({FieldName})) as [{FieldName}]

On the SQL Side:

If you have authorization to create views on the server, then you can write a stored procedure to create/alter(update) a set of views which expose the varchar interface for char fields.

Batch Update - run this everytime there is a schema change: 1. Loop through all user tables 2. Create or Update the corresponding View

UpdateTableView(tableName): 1. Exit if there are no char fields 2. Automate creation of view with cast(LTRIM(RTRIM(..)) as varchar(CHAR_FIELD_SIZE)

Answer 3

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