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 string
s 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 string
s 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
).
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