Programming: .NET types vs. DB Types

Programming kvetch, with code, because I don't want to do anymore work, avert eyes.

This drives me nuts. In .NET, there is no built in way to translate from System.Type to System.Data.DbType, in any direction. This annoyance is compounded by the fact that none of the venders that implement the System.Data.Common abstraction layer offer a direct path to step down from their types to DbType. Curiously, if you abstract such implementations, e.g. ((DbCommand)OleDbCommand).Parameters.Add(..., you can stuff them with DbType. This can be quite handy.

So, types. You can write a giant case statement. I'm sure I have more than one lurking around. With the info found here, any halfway competent programmer can roll their own. Still, it's annoying. So, here's some code snips to cover most cases. I had to write these today because I couldn't find them and thought I'd share (i.e. put it somewhere I can find it again.)


DbType DbTypeFromSystemType(Type type) {
   try {
      return (DbType)Enum.Parse(typeof(DbType), type.Name);
   } catch {
      //return DbType.String;
      return DbType.AnsiString;
   }
}


Note the AnsiString. My program didn't seem to deal gracefully with a return value from Oracle with the unicode "DbType.String", so there you are. Also note that rather than failing, I'll take a string. It usually works.

If you want to go back the other way, you can do this:

Type SystemTypeFromDbType(System.Data.DbType dbType) {
   try {
      return Type.GetType("System." + dbType.ToString());
   } catch {
      return typeof(string);
   }
}


This was actually spawned from another annoyance, null and DBNull are constantly in disagreement for data binding. Here's some generic code that helps bound nulls play nice.


DbParameter AddParam(DbCommand cmd, string paramName, DbType dbType) {
   DbParameter p = cmd.CreateParameter();
   p.ParameterName = paramName;
   p.DbType = dbType;
   p.Value = System.DBNull.Value;
   cmd.Parameters.Add(p);
   return p;
}

DbParameter AddParam(DbCommand cmd, string paramName, Type type) {
   return AddParam(cmd, paramName, DbTypeFromSystemType(type));
}

DbParameter AddParam(DbCommand cmd, string paramName, object value, Type type) {
   DbParameter p = AddParam(cmd, paramName, type);
   if (value != null) {
      p.Value = value;
   }
   return p;
}

DbParameter AddParam(DbCommand cmd, string paramName, object value) {
   if (value == null) {
      return AddParam(cmd, paramName, typeof(string));
   } else {
      return AddParam(cmd, paramName, value, value.GetType());
   }
}

DbParameter AddParam(DbCommand cmd, string paramName, object obj, string fieldName) {
   FieldInfo fi = obj.GetType().GetField(fieldName);
   return AddParam(cmd, paramName, fi.GetValue(obj), fi.FieldType);
}


Yes, overloading is fun. That last one is a convenience method that uses reflection for those times you're binding to object values.

For completeness, sample code looks something like this:
class Person { public string FullName; public bool IsSingle; public int EmpId; }
void AddPerson(DbConnection conn, Person person) {
   DbCommand cmd = conn.CreateCommand();
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = "add_person";
   AddParam(cmd, "p_employee_id", person, "EmpId");
   AddParam(cmd, "p_full_name", person, "FullName");
   AddParam(cmd, "p_marriage_status", (person.IsSingle ? "S" : "M"));
   try {
      cmd.Connection.Open();
      cmd.ExecuteNonQuery();
   } finally {
      cmd.Connection.Close();
   }
}


Yeah, I should start a tech blog, but I'm too lazy. If this ever helps anyone, drop me a line.