Anybody got a C# function that maps the SQL datatype of a column to its CLR equivalent?

Question

I'm sitting down to write a massive switch() statement to turn SQL datatypes into CLR datatypes in order to generate classes from MSSQL stored procedures. I'm using this chart as a reference. Before I get too far into what will probably take all day and be a huge pain to fully test, I'd like to call out to the SO community to see if anyone else has already written or found something in C# to accomplish this seemingly common and assuredly tedious task.

Answer 1

This is the one we use. You may want to tweak it (e.g. nullable/non-nullable types etc.) but it should save you most of the typing.

public static Type GetClrType(SqlDbType sqlType) {     switch (sqlType)     {         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("sqlType");     } }