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.

Comments (1) -

  • Pierre

    31.8.2018 17:23:43 | Reply

    If you use the elegant conversion method, be aware that default mapping for
    DbType.Date and DbType.Time will be SqlDbType.DateTime. You will need to adress this case specifically.

    github.com/.../SqlParameter.cs#L277

Loading