CODEWITHSUNDEEP
c#.netado.net
Mike
Mike

Reputation: 2567

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

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

Upvotes: 37

Views: 51729

Answers (6)

Walter Stabosz
Walter Stabosz

Reputation: 7735

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)}
};

Upvotes: 0

realsonic
realsonic

Reputation: 328

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

Upvotes: 18

alexn
alexn

Reputation: 59022

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

Upvotes: 82

vasil oreshenski
vasil oreshenski

Reputation: 2836

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.

Upvotes: 6

Davide Piras
Davide Piras

Reputation: 44595

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

Upvotes: 1

Oded
Oded

Reputation: 499382

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.

Upvotes: 11

Related Questions