Turn String Parameters to VARCHAR from NVARCHAR

Introduction

When writing the old style (hand coded dirty SQL queries), it is common to add parameters to your command using the Parameters.AddWithValue method.  However as .NET strings are unicode, this will make the parameter be passed as an NVARCHAR. 

If you are running against a database that has all of its columns specified as ANSI strings using the data type VARCHAR, then this will result in a significant performance penalty as the data access layer will need to perform a data type translation as part of the query.

To prevent this, you can explicitly set all your string parameters to ANSI.

Background

Ideally, you should have all your database using NVARCHAR fields if you want to do business in a global marketplace, but often legacy systems are not built that way.

Using the Code

The following static method "cleans up" the command so that all string parameters are passed as ANSI (VARCHAR). 

/// <summary>
/// For every command parameter in the command, if its type is NVARCHAR turn it to VARCHAR instead
/// so that indexes can be used
/// </summary>
/// <param name="sourceCommand">
/// The source command with parameters
/// </param>
public static void CleanCommandStringParameters(SqlCommand sourceCommand)
{
    if (null != sourceCommand)
    {
        if (null != sourceCommand.Parameters)
        {
            foreach (SqlParameter  param in sourceCommand.Parameters )
            {
                if (param.SqlDbType == SqlDbType.NVarChar)
                {
                    param.SqlDbType = SqlDbType.VarChar;
                }
                if (param.SqlDbType == SqlDbType.NChar)
                {
                    param.SqlDbType = SqlDbType.Char;
                }
            }
        }
    }
}

History

• 2015-10-28: Created