How to enable System.Type in System.Data.DbType?

Question

What is the best way to find the System.Data.DbType enumeration value for base class library types in the System namespace?

Answer 1

A common way is to have a type map, and all supported types (different connectors / providers support different types) are clearly displayed. 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 the appropriate DbType, all you have to do is:

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

Answer 2

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

Answer 3

You are looking at documentation - SQL Server Data Type Mapping (ADO.NET) .

Mappings for other vendors are also documented .

This gives you enough information to write the converter.

Answer 4

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

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