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 :
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 .
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 =)
Read More...
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
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 =)
.bmp)
.bmp)
.bmp)
