Convert Type to SqlDataType and SqlDataType to Type C#

Type conversion basically involve casting and convert from one type to another. You can try the Type conversion by following this web site.

Today i want to share code to convert from Type to SqlDataType. This method can be used for example to add parameters to SqlCommand based on DataTable column DataType, see example below :

SqlCommand dbComm = new SqlCommand("<sql statement>",<sql connection>);

dbComm.Parameters.Add("<parameter name>", < sql Data Type>]).Value  = "test";

what about if you have DataTable and you want to automatically loop into column of datatable and and automatically assign Sql Data Type based on Column DataType of data table ..?There is nothing method or casting that available to do this, but after i googling about converting / casting DataType to SqlDataType, the result seem like impossible to done in .net .

• C# Parse SqlDbType Convertion
• C# data types to SQL Server data types 
• Convert DataColumn.DataType to SqlDbType 

Based on the above link. I have do my workaround  method to convert from Type to SqlDataType and SqlDataType to Type

Type to SqlDataType

        private static Dictionary<Type, SqlDbType> typeReference; 

        public static void loadDictionary()
        {
            typeReference = new Dictionary<Type, SqlDbType>();
            typeReference.Add(typeof(string), SqlDbType.NVarChar);
            typeReference.Add(typeof(Guid), SqlDbType.UniqueIdentifier);
            typeReference.Add(typeof(long), SqlDbType.BigInt);
            typeReference.Add(typeof(byte[]), SqlDbType.Binary);
            typeReference.Add(typeof(bool), SqlDbType.Bit);
            typeReference.Add(typeof(DateTime), SqlDbType.DateTime);
            typeReference.Add(typeof(decimal), SqlDbType.Decimal);
            typeReference.Add(typeof(double), SqlDbType.Float);
            typeReference.Add(typeof(int), SqlDbType.Int);
            typeReference.Add(typeof(float), SqlDbType.Real);
            typeReference.Add(typeof(short), SqlDbType.SmallInt);
            typeReference.Add(typeof(byte), SqlDbType.TinyInt);
            typeReference.Add(typeof(object), SqlDbType.Udt);
            typeReference.Add(typeof(DataTable), SqlDbType.Structured);
            typeReference.Add(typeof(DateTimeOffset), SqlDbType.DateTimeOffset);           
           
        }

Call method

SqlCommand dbCommand = new SqlCommand("Select * from <tableName> where id=@id",<sql connection>);

dbCommand.Parameters.Add("@id", typeReference[<DataType to pass>]).Value  = "12345";

SqlDataType to Type

 public static Type GetClrType(SqlDbType sqlDataType)
        {
            switch (sqlDataType)
            {
                case SqlDbType.BigInt:
                    return typeof(long?);

                case SqlDbType.Binary:
                case SqlDbType.Image:
                case SqlDbType.Timestamp:
                case SqlDbType.VarBinary:
                    return typeof(byte[]);

                case SqlDbType.Bit:
                    return typeof(bool?);

                case SqlDbType.Char:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.VarChar:
                case SqlDbType.Xml:
                    return typeof(string);

                case SqlDbType.DateTime:
                case SqlDbType.SmallDateTime:
                case SqlDbType.Date:
                case SqlDbType.Time:
                case SqlDbType.DateTime2:
                    return typeof(DateTime?);

                case SqlDbType.Decimal:
                case SqlDbType.Money:
                case SqlDbType.SmallMoney:
                    return typeof(decimal?);

                case SqlDbType.Float:
                    return typeof(double?);

                case SqlDbType.Int:
                    return typeof(int?);

                case SqlDbType.Real:
                    return typeof(float?);

                case SqlDbType.UniqueIdentifier:
                    return typeof(Guid?);

                case SqlDbType.SmallInt:
                    return typeof(short?);

                case SqlDbType.TinyInt:
                    return typeof(byte?);

                case SqlDbType.Variant:
                case SqlDbType.Udt:
                    return typeof(object);

                case SqlDbType.Structured:
                    return typeof(DataTable);

                case SqlDbType.DateTimeOffset:
                    return typeof(DateTimeOffset?);

                default:
                    throw new ArgumentOutOfRangeException("sqlDataType");
            }
        }

Have a try, happy coding ..


By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)