How to Trim String Values in C# with Dapper

This led to unnecessary whitespace at the beginning and end of string values, which is not ideal for data processing.

Strings Not Trimmed in Stored Procedure Results

The stored procedure was returning strings that contained leading or trailing spaces. While I could dig into the stored procedure and update it, I wanted a quicker solution that didn’t require learning SQL specifics. Thankfully, Dapper provides a built-in way to handle this scenario with a custom TypeHandler that can automatically trim all string values returned from queries.

Creating a Custom TypeHandler in Dapper

To resolve this issue, I implemented a custom TypeHandler in C# that automatically trims the string values when they're returned by Dapper.

using Dapper;
using System.Data;

namespace MyApp
{
    public class TrimStringHandler : SqlMapper.TypeHandler<string>
    {
        public override string Parse(object value)
        {
            return (value as string)?.Trim();  // Trim the string when parsing
        }

        public override void SetValue(IDbDataParameter parameter, string value)
        {
            parameter.Value = value;  // Pass the value as is when setting
        }
    }
}

In this code:

• Parse() trims the string when it’s returned from the database.
• SetValue() is responsible for assigning the value to the database parameter, where no trimming is necessary.

Apply the TypeHandler Globally

Dapper allows you to register custom TypeHandlers globally so that all queries benefit from them without needing to configure each one individually. To do this, simply add the following line in your Program.cs or application startup code:

Dapper.SqlMapper.AddTypeHandler(new TrimStringHandler());

This ensures that the TrimStringHandler is applied to all string fields returned by Dapper queries or stored procedures.

After applying the TypeHandler, all strings returned from the stored procedure will be trimmed of any leading or trailing whitespace. This simple solution prevents having to manually trim strings in every query or stored procedure.

For example, Query Result Before and After

Before:

Strings returned from the stored procedure contained unnecessary spaces.

"  Hello World  "

After:

The strings are now automatically trimmed.

"Hello World"

By implementing a custom TypeHandler and applying it globally in your Dapper configuration, you can easily ensure that string values are returned trimmed without needing to modify the stored procedure or manually handle whitespace in every query.