Tuesday, July 14, 2009

System.Type to SqlDbType Conversion

I've been looking for something to do this that was simple, and I found this:
private SqlDbType GetDBType(System.Type theType)
{
  SqlParameter param;
  System.ComponentModel.TypeConverter tc;
  param = new SqlParameter();
  tc = System.ComponentModel.TypeDescriptor.GetConverter(param.DbType);
  if (tc.CanConvertFrom(theType))
  {
      param.DbType = (DbType)tc.ConvertFrom(theType.Name);
  }
  else
  {
      // try to forcefully convert
      try
      {
          param.DbType = (DbType)tc.ConvertFrom(theType.Name);
      }
      catch(Exception e)
      {
          // ignore the exception
      }
  }
  return param.SqlDbType;
}
There are a number of problems here, but it occurred to me that the SqlParameter object does type conversion when you create a new parameter. So, I did this:
      private SqlDbType GetDBType(object o)
      {
          SqlParameter p = new SqlParameter("x", o);
          return p.SqlDbType;
      }