How to resolve System.Type to System.Data.DbType?

Question

What is the best way to find System.Data.DbType enumeration value for Base Class Library types in System namespace?

score 81 · Accepted Answer · edited Jun 01 '20 at 15:11

A common way is to have a type map, with all supported types (different connectors/providers supports different types) explicitly mapped. Here is the type map for Dapper:

typeMap = new Dictionary<Type, DbType>();
typeMap[typeof(byte)] = DbType.Byte;
typeMap[typeof(sbyte)] = DbType.SByte;
typeMap[typeof(short)] = DbType.Int16;
typeMap[typeof(ushort)] = DbType.UInt16;
typeMap[typeof(int)] = DbType.Int32;
typeMap[typeof(uint)] = DbType.UInt32;
typeMap[typeof(long)] = DbType.Int64;
typeMap[typeof(ulong)] = DbType.UInt64;
typeMap[typeof(float)] = DbType.Single;
typeMap[typeof(double)] = DbType.Double;
typeMap[typeof(decimal)] = DbType.Decimal;
typeMap[typeof(bool)] = DbType.Boolean;
typeMap[typeof(string)] = DbType.String;
typeMap[typeof(char)] = DbType.StringFixedLength;
typeMap[typeof(Guid)] = DbType.Guid;
typeMap[typeof(DateTime)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
typeMap[typeof(byte[])] = DbType.Binary;
typeMap[typeof(byte?)] = DbType.Byte;
typeMap[typeof(sbyte?)] = DbType.SByte;
typeMap[typeof(short?)] = DbType.Int16;
typeMap[typeof(ushort?)] = DbType.UInt16;
typeMap[typeof(int?)] = DbType.Int32;
typeMap[typeof(uint?)] = DbType.UInt32;
typeMap[typeof(long?)] = DbType.Int64;
typeMap[typeof(ulong?)] = DbType.UInt64;
typeMap[typeof(float?)] = DbType.Single;
typeMap[typeof(double?)] = DbType.Double;
typeMap[typeof(decimal?)] = DbType.Decimal;
typeMap[typeof(bool?)] = DbType.Boolean;
typeMap[typeof(char?)] = DbType.StringFixedLength;
typeMap[typeof(Guid?)] = DbType.Guid;
typeMap[typeof(DateTime?)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;
typeMap[typeof(System.Data.Linq.Binary)] = DbType.Binary;

To get a relevant DbType, all you need to do is:

var type = typeMap[typeof(string)]; // returns DbType.String

This looks good for me. I think there's no real need for me in Nullable versions, since `Type.GetGenericTypeDefinition` and `Type.GetGenericArguments` would help resolving Nullable value and underlying type. — Mike, Oct 31 '11 at 10:13

realsonic · Answer 2 · 2020-10-02T20:20:27.363

18

You can convert TypeCode to DbType using method ConvertTypeCodeToDbType in System.Web.UI.WebControls.Parameter class: Parameter.ConvertTypeCodeToDbType Method. To get TypeCode you can use method Type.GetTypeCode(Type type).

edited Oct 02 '20 at 20:20

answered Feb 17 '15 at 12:51

realsonic

318
3
9

35

Why on earth this is on `System.Web`? – talles Jun 14 '16 at 21:53

score 11 · Answer 3 · answered Oct 31 '11 at 09:59

11

You look at the documentation - SQL Server Data Type Mappings (ADO.NET).

The mappings for other providers are also documented.

These give you enough information to write a converter.

answered Oct 31 '11 at 09:59

Oded

489,969
99
883
1,009

But is there provider-agnostic way? – Mike Oct 31 '11 at 10:04
@Mike - I can't imagine there would be. The different databases and providers are implemented in different ways. The closest you can get it to find the most common mappings as documented and use them in a common class. – Oded Oct 31 '11 at 10:06
I understand. I just wonder why is there DbType in Framework? Its values closely resemble BCL types so I thought there should be a default mapping. – Mike Oct 31 '11 at 10:11
@Mike - No default mappings. `DbType` is there to represent all databases, as much as possible. – Oded Oct 31 '11 at 10:40

vasil oreshenski · Answer 4 · 2019-10-09T17:52:12.130

I know it is old question which is already answered, but there is easier way by using SqlParameter, which already has this logic implemented. This is specific for SqlServer, but the providers for Postgre, MySql .. etc have corresponding implementations.

Here is a complete function which handles non-nullable, nullable primitive types, decimal and string

public static DbType GetDbType(Type runtimeType)
{
    var nonNullableType = Nullable.GetUnderlyingType(runtimeType);
    if (nonNullableType != null)
    {
        runtimeType = nonNullableType;
    }

    var templateValue = (Object)null;
    if (runtimeType.IsClass == false)
    {
        templateValue = Activator.CreateInstance(runtimeType);
    }

    var sqlParamter = new SqlParameter(parameterName: String.Empty, value: templateValue);

    return sqlParamter.DbType;
}

How to get SqlParameter:

For SqlServer, depending on your .netframework version you can find the SqlParamter type in System.Data, System.Data.SqlClient nuget and Microsoft.Data.SqlClient nuget

Source code for SqlParameter:

The implementation of SqlParameter is using this piece of code which is pretty close to what the accepted answer is proposing.

Very dirty and inefficient, but very clever. – Ian Kemp Jun 01 '20 at 15:32 — Ian Kemp, Jun 01 '20 at 15:32

score 1 · Answer 5 · answered Oct 31 '11 at 10:01

1

I am not aware of any automated logic, you should do the mapping yourself because those are different types and the .NET Framework cannot do this for you alone.

see here the whole mapping table: SQL Server Data Type Mappings (ADO.NET) you can imagine that for Oracle, MySQL, sqLite and other engines there could be similar tables also depending on the .NET data provider / connect

answered Oct 31 '11 at 10:01

Davide Piras

43,984
10
98
147

I made this gist which contains the mapping table represented as a JavaScript array. It's handy for building your own C# lookup tables. https://gist.github.com/wstabosz/7f59ca2518e0384c73f27902a7830040 – Walter Stabosz Oct 13 '21 at 18:08

Walter Stabosz · Answer 6 · 2021-10-13T17:23:41.387

I'm leaving this here in case anyone else should happen to need the inverse of the Dapper lookup-table mentioned in alexn's answer. (it won't handle nullables)

Dictionary<DbType, Type> dbTypeMap = new Dictionary<DbType, Type>()
{
    { DbType.Binary, typeof(byte[])},
    { DbType.Boolean, typeof(bool)},
    { DbType.Byte, typeof(byte)},
    { DbType.DateTime, typeof(DateTime)},
    { DbType.DateTimeOffset, typeof(DateTimeOffset)},
    { DbType.Decimal, typeof(decimal)},
    { DbType.Double, typeof(double)},
    { DbType.Guid, typeof(Guid)},
    { DbType.Int16, typeof(short)},
    { DbType.Int32, typeof(int)},
    { DbType.Int64, typeof(long)},
    { DbType.SByte, typeof(sbyte)},
    { DbType.Single, typeof(float)},
    { DbType.String, typeof(string)},
    { DbType.StringFixedLength, typeof(char)},
    { DbType.UInt16, typeof(ushort)},
    { DbType.UInt32, typeof(uint)},
    { DbType.UInt64, typeof(ulong)}
};

How to resolve System.Type to System.Data.DbType?

6 Answers6

Linked