Given a T-SQL type as a string, what is the easiest way to evaluate it to a .Net Type?

Question

If given a string that contains a SQL Server/T-SQL datatype, what is the easiest way to evaluate the the string to a .Net Type? For instance, if you have a string containing "nvarchar", the result returned by the conversion method should be a the `System.String` Type. If I have a string containing "int", the result should be a `System.Int32` Type object. I could easily write a function that takes a SQL datatype string and sends the string through a switch/case statement that returns a .Net Type object. However, I wasn't sure if there was a function buried in the .Net framework that I overlooked that already does this. What is the easiest/correct way to resolve a SQL Server datatype to a .Net datatype? ADDITIONAL CONTEXT In my case, I actually have a stored procedure that returns some meta-information about data. Specifically, a string field is returned, containing a sql-type value which could be any sql-type that was available within SQL Server 2005. My stored procedure has the potent…

Accepted Answer

There is nothing exposed that I know of. Deep in the System.Data.SqlClient code there is this function that is used to determine the type mapping:

```
internal Type GetTypeFromStorageType(bool isSqlType)
{
    if (isSqlType)
    {
        switch (this._type)
        {
            case StorageType.Empty:
                return null;

case StorageType.Boolean:
                return typeof(SqlBoolean);

case StorageType.Byte:
                return typeof(SqlByte);

case StorageType.DateTime:
                return typeof(SqlDateTime);

case StorageType.Decimal:
                return typeof(SqlDecimal);

case StorageType.Double:
                return typeof(SqlDouble);

case StorageType.Int16:
                return typeof(SqlInt16);

case StorageType.Int32:
                return typeof(SqlInt32);

case StorageType.Int64:
                return typeof(SqlInt64);

case StorageType.Money:
                return typeof(SqlMoney);

case StorageType.Single:
                return typeof(SqlSingle);

case StorageType.String:
                return typeof(SqlString);

case StorageType.SqlBinary:
                return typeof(object);

case StorageType.SqlCachedBuffer:
                return typeof(SqlString);

case StorageType.SqlGuid:
                return typeof(object);

case StorageType.SqlXml:
                return typeof(SqlXml);
        }
    }
    else
    {
        switch (this._type)
        {
            case StorageType.Empty:
                return null;

case StorageType.Boolean:
                return typeof(bool);

case StorageType.Byte:
                return typeof(byte);

case StorageType.DateTime:
                return typeof(DateTime);

case StorageType.Decimal:
                return typeof(decimal);

case StorageType.Double:
                return typeof(double);

case StorageType.Int16:
                return typeof(short);

case StorageType.Int32:
                return typeof(int);

case StorageType.Int64:
                return typeof(long);

case StorageType.Money:
                return typeof(decimal);

case StorageType.Single:
                return typeof(float);

case StorageType.String:
                return typeof(string);

case StorageType.SqlBinary:
                return typeof(byte[]);

case StorageType.SqlCachedBuffer:
                return typeof(string);

case StorageType.SqlGuid:
                return typeof(Guid);

case StorageType.SqlXml:
                return typeof(string);
        }
    }
    return null;
}
```

Given a T-SQL type as a string, what is the easiest way to evaluate it to a .Net Type?

Solution

Problem