C#: DbType.String versus DbType.AnsiString

Question

I have taken over some C# code.

The code is hitting a database with some SQL which uses parameters.

All of the string parameters are typed as DbType.AnsiString instead of DbType.String.

Why would you use DbType.AnsiString instead of DbType.String?

Answer 1

AnsiString
A variable-length stream of non-Unicode characters ranging between 1 and 8,000 characters.

String
A type representing Unicode character strings.

In database:

nchar and nvarchar is unicode

char and varchar is non-unicode

Answer 2

You would use ansistring instead of string to avoid implicit conversions in your SQL Server database.

i.e. when you pass a string variable into MSSQL it appears as nvarchar(max). Given the fact a well designed database may use varchars as opposed to nvarchars by default (unless there is a business requirement for non latin character sets).

A string variable in this case will cause an implicit conversion in the database. This can then render the engine unable to use certain indexes and perform full table scans (one of the roots of all evil for db performance)