Converting from DbType to [Sql]DbType is easy
I often see question on how to convert from DbType to SqlDbType (or any other *DbType). This is often required if you do some database independent code with ado.net. One way is certainly a gigantic switch block, such as:
private SqlDbType FromDbType(DbType type)
{
switch (type)
{
case System.Data.DbType.AnsiStringFixedLength:
case System.Data.DbType.StringFixedLength:
return SqlDbType.Char; ;
case System.Data.DbType.AnsiString:
case System.Data.DbType.String:
return SqlDbType.VarChar;
case System.Data.DbType.Binary:
return SqlDbType.Binary;
........
}
However, this is not the best solution as elegant conversion method is already present within .net. Here it is:
DbType type = DbType.Int32;
SqlParameter parm = new SqlParameter();
try
{
parm.DbType = type;
}
catch (Exception ex)
{
// can't map
}
SqlDbType sqlDbType = parm.SqlDbType;
The trick is to use database specific parameter, in this case Sql Server's SqlParameter. And don't forget to wrap DbType assignment in try/catch block as an ArgumentException will be thrown when mapping doesn't exist between given DbType and target database specific type.
The implementation could be nicer (perhaps through a static method) but hey, it works just fine.