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?

Elm

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

Gershon

+1. Why would anyone NOE use AnsiString of it IS an ansi non unicode string in the database? Eludes me as logic. – Baily

Is DbType.AnsiString a sub-set of DbType.String? – Elm

Short: multilingual support for Chinese, Japanese, Korean and so on – Gershon

Unicode may be represented by different encodings - what's the one in question? – Khartoum

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 in MSSQL 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)

Ratoon

Answer 3

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

Short answer: When your SqlParameter is pointing to a varchar (or char) database column you would use DbType.AnsiString. When you point to a nvarchar column, use DbType.String because its Unicode.

Longer answer: If these are not mapped accurately (e.g. pointing DbType.String to a varchar(nn) column, you will see CONVERT_IMPLICIT warnings in your query plans as described by Pinal Dave here: https://blog.sqlauthority.com/2018/06/11/sql-server-how-to-fix-convert_implicit-warnings/

In my case the SqlParameter.Size property also seemed to be important: to completely eliminate CONVERT_IMPLICIT warnings I had to map DbType and Size correctly. The default Size if omitted "is inferred from the actual size of the specified parameter value"(1) which will usually mismatch the real column size definition and that raises a warning as well.

1. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter.size?view=netframework-4.7.2

Janitajanith

I don't get CONVERT_IMPLICIT warning when I omit Size for varchar column. Though I didn't try to pass a string longer than max database column length. – Thaddeusthaddus

Vasily, thanks for the feedback. I made a slight edit to note that size could be a factor (it was in my case) but maybe not in all cases. – Janitajanith

Answer 4

See how to solve the problem in C# with Dapper:

SqlMapper.AddTypeMap(typeof(string), DbType.AnsiString);
SqlMapper.AddTypeMap(typeof(String), DbType.AnsiString);

Warp