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 potential to return any sql-type-- int, smallint, datetime, binary, etc. I need to take this data type and convert it to a .Net Type object.

Matthew's comment below does provide all of the necessary mapping information, straight from Microsoft's documentation but, again, I was wondering if there was something integrated in either the System.Data or System.Data.SqlClient namespaces.

Answer 1

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;
}