How to retrieve .NET type of given StoredProcedure's Parameter in SQL?

Question

I'm creating 'generic' wrapper above SQL procedures, and I can resolve all required parameters' names and sqltypes, but is there any way how to get it's 'underlying' .NET type?

My goal is to do something like:

SqlParameter param;
object value;
object correctParam = param.GetNETType().GetMethod("Parse", 
    new Type[] { typeof(string) }).Invoke(value.ToString());
param.Value = correctParam;

Where GetNETType is the thing I need. I know that it can be written as switch inside of param.SqlDbType, but this is shorter way, and shorter commented code means lower mainteance :)

Answer 1

Unfortunately, as far as I know this mapping is not exposed in code inside the .NET Framework. I've looked through the .NET Framework reference source before for this, and found that inside the .NET code there's a lot of long per-type switch statements, just like the ones you're trying to avoid, but none of them seem to be exposed externally.

If you really just want to map from SqlTypes to the most likley .NET type, I think your best bet is to simply turn the mapping table in the MSDN docs into code. Note that the table on MSDN has (at least) two errors: #1: there is no .NET type called "DateTime2" (I used DateTime) and there is also no type called "Xml" (I used SqlXml).

Anyway, here's the mapping I've been using-- using a Dictionary instead of a switch for ease of access without a separate method.

public static Dictionary<SqlDbType, Type> TypeMap = new Dictionary<SqlDbType, Type>
{
    { SqlDbType.BigInt, typeof(Int64) },
    { SqlDbType.Binary, typeof(Byte[]) },
    { SqlDbType.Bit, typeof(Boolean) },
    { SqlDbType.Char, typeof(String) },
    { SqlDbType.Date, typeof(DateTime) },
    { SqlDbType.DateTime, typeof(DateTime) },
    { SqlDbType.DateTime2, typeof(DateTime) },
    { SqlDbType.DateTimeOffset, typeof(DateTimeOffset) },
    { SqlDbType.Decimal, typeof(Decimal) },
    { SqlDbType.Float, typeof(Double) },
    { SqlDbType.Int, typeof(Int32) },
    { SqlDbType.Money, typeof(Decimal) },
    { SqlDbType.NChar, typeof(String) },
    { SqlDbType.NText, typeof(String) },
    { SqlDbType.NVarChar, typeof(String) },
    { SqlDbType.Real, typeof(Single) },
    { SqlDbType.SmallInt, typeof(Int16) },
    { SqlDbType.SmallMoney, typeof(Decimal) },
    { SqlDbType.Structured, typeof(Object) }, // might not be best mapping...
    { SqlDbType.Text, typeof(String) },
    { SqlDbType.Time, typeof(TimeSpan) },
    { SqlDbType.Timestamp, typeof(Byte[]) },
    { SqlDbType.TinyInt, typeof(Byte) },
    { SqlDbType.Udt, typeof(Object) },  // might not be best mapping...
    { SqlDbType.UniqueIdentifier, typeof(Guid) },
    { SqlDbType.VarBinary, typeof(Byte[]) },
    { SqlDbType.VarChar, typeof(String) },
    { SqlDbType.Variant, typeof(Object) },
    { SqlDbType.Xml, typeof(SqlXml) }, 
};

Note that one thing you'll need to watch out for is size/precision-- some SQL types (e.g. varchar) have size limits, while .NET types (e.g. string) don't. So being able to know the most-likely .NET type is not really enough... if you're using this to, for example, drive validation rules, you also need to be able to prevent users from entering invalid (e.g. too large) values by knowing more about the parameter, like the precision. Note that, if you look inside the SqlClient source, they use special code to handle cases like setting the precision of a Decimal type from the corresponding SQL precision.

Note that if the only reason you need the .NET type is to be able to stuff data into a stored proc parameter, you might want to try simply using ToString() on all your .NET values, stuffing a string into the Value property of the SqlParameter, and seeing if the framework will do the conversion/parsing for you. For example, for an XML or Date parameter you might be able to get away with sending a string instead.

Also, instead of using reflection to find a Parse() method on each type, since there's a known (and small) list of types, you can get better performance by using strongly-typed parsing code for each, like the code below. (Note that several types (e.g. SqlDbType.Udt) don't necessarily have an obvious parser method-- you'll need to figure out how you want to handle those.)

public static Dictionary<SqlDbType, Func<string, object>>  TypeMapper = new Dictionary<SqlDbType, Func<string, object>>
{
    { SqlDbType.BigInt, s => Int64.Parse(s)},
    { SqlDbType.Binary, s => null },  // TODO: what parser?
    { SqlDbType.Bit, s => Boolean.Parse(s) },
    { SqlDbType.Char, s => s },
    { SqlDbType.Date, s => DateTime.Parse(s) },
    { SqlDbType.DateTime, s => DateTime.Parse(s) },
    { SqlDbType.DateTime2, s => DateTime.Parse(s) },
    { SqlDbType.DateTimeOffset, s => DateTimeOffset.Parse(s) },
    { SqlDbType.Decimal, s => Decimal.Parse(s) },
    { SqlDbType.Float, s => Double.Parse(s) },
    { SqlDbType.Int, s => Int32.Parse(s) },
    { SqlDbType.Money, s => Decimal.Parse(s) },
    { SqlDbType.NChar, s => s },
    { SqlDbType.NText, s => s },
    { SqlDbType.NVarChar, s => s },
    { SqlDbType.Real, s => Single.Parse(s) },
    { SqlDbType.SmallInt, s => Int16.Parse(s) },
    { SqlDbType.SmallMoney, s => Decimal.Parse(s) },
    { SqlDbType.Structured, s => null }, // TODO: what parser?
    { SqlDbType.Text, s => s },
    { SqlDbType.Time, s => TimeSpan.Parse(s) },
    { SqlDbType.Timestamp, s => null },  // TODO: what parser?
    { SqlDbType.TinyInt, s => Byte.Parse(s) },
    { SqlDbType.Udt, s => null },  // consider exception instead
    { SqlDbType.UniqueIdentifier, s => new Guid(s) },
    { SqlDbType.VarBinary, s => null },  // TODO: what parser?
    { SqlDbType.VarChar, s => s },
    { SqlDbType.Variant, s => null }, // TODO: what parser?
    { SqlDbType.Xml, s => s }, 
};

The code to use above is pretty easy, e.g. :

        string valueToSet = "1234";
        SqlParameter p = new SqlParameter();
        p.SqlDbType = System.Data.SqlDbType.Int;
        p.Value = TypeMapper[p.SqlDbType](valueToSet);

Answer 2

No one else seems to want to tell you, but what you're doing is probably not the best way to do it.

object correctParam = param.GetNETType().GetMethod("Parse", 
    new Type[] { typeof(string) }).Invoke(value.ToString());
param.Value = correctParam;

You're saying that you're given a string value, which you know has to be assigned to a parameter, and you want to stuff that value in there any way that it can fit?

Please consider why you are doing this. You are making the assumption that the following code is right:

param.Value = NetType.Parse(value.toString())

There's no clear reason why this is better than:

param.Value = value;

But since you want to do it, it seems safe to assume that you have tried this and found that your real problem is that value isn't the right type for the parameter. Thus you want a magical fix that you can run which will always make sure that value is the right type. What you really want is likely:

SetParam(param, value);

Where this function stuffs the value into the parameter. This actually makes things a bit easier if value is not simply of type object as you say, but has a real type (like int or string). This is because you can use method overloading like SetParam(SqlParam param, int value) or generics to infer the value type SetParam<T>(SqlParam param, T value).

So we know the function you want, what we don't know is why. In most reasonable scenarios you have an idea of the types of the values, and you also have an idea of the type of the parameter. You are asking for a way to cram a value that doesn't match a parameter into a parameter that you don't understand.

There are two main reasons I can think of for this request:

1. You in reality know that the types are compatible, and are looking for a general way to do this to avoid writing a lot of code. So you know that you are trying to assign a long to a parameter that is a SqlInt, and are relying on string conversions to get you past the type safety issues.

2. You don't really understand the code that you are using and are trying to patch in a fix to get something working.

It's really important to be honest with yourself about which case you are in. If you are in the first case, then you can write a method like SetParam that I described above fairly easily. You will have to write a switch statement (or like the best answer above, a Dictionary lookup). You are going to have to lose precision (casting a long to an int doesn't work for large numbers, but neither will your Parse) but it will work.

If you're in the second case, stop for a minute. Recognize that you are setting yourself up for more bugs in the future (because converting to and from string will not solve the problems you have of not understanding the types). You know that you need help, which is why you are on Stack Overflow and offering a bounty for help, and you are dealing with a codebase that you don't understand. I can tell right now from your question that you are going to dig yourself a deeper hole than you realize if this is your situation, because you have already refused the best answer (to do a switch statement based on parameter type) for no strong reason.

So, if you are in the second case, the thing that is going to help you most is not an answer from Stack Overflow, unless you are willing to describe your real problem more completely. What will help you is understanding where the values are coming from (is it UI? Is it a different subsystem, which rules do they follow? Is there a reason the types don't match?) and where they are going (what is the definition of the stored procedure you are calling? What are the parameter types defined as?). I imagine you probably don't even need to go into SQL to find this, as whoever gave you the SqlParam probably already has defined it properly for you. If you defined it, you do indeed need to go to the SQL to figure it out, immediately.