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?

Answer 1

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 

Answer 2

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