Setting a SQL command parameter DbType dynamically

Question

I seem to be gnawing at this issue, and although I can see how it should work, I can't seem to see the wood for the trees..

Basically I have a situation where I have a SQL 2008 stored procedure.. very basic.. simple varchar's and int's etc. Because I am getting the value/pair data from javascript, of course I don't implicitly know the DbType to set.. I know this sounds a bit off perhaps, and I could technically add some sort of tag to the fieldname to indicate the actual type.

So right now I am trying to do the following:

(I am using the Enterprise Library Data Access Block, but db is the database object/connection object) - also the val/pairs are from a hashtable..

db.DiscoverParameters(cmd);
IDictionaryEnumerator de;
de = PDMdata.GetEnumerator();
while (de.MoveNext())
{
    DbType ptype = cmd.Parameters[de.Key.ToString()].DbType;
    db.AddInParameter(cmd, de.Key.ToString(), ptype, (ptype)de.Value.ToString());
}

For reference, the db.AddInParameter takes the Command object, param name, param type and param value..

As you can see I am trying to cast the value as correct SQL DbType.. but of course it is trying to use the normal C# Type..

Any ideas of converting the value to the dynamic SQL DbType??

Thanks in advance for your help..

Answer 1

Ok, silly me.. if I am already checking the format/data quality in the frontend/middleware, then I can (because I am using the Enterprise Library 5.0) .SetParameterValue(cmd, paramname, paramvalue) and the parameter discovery will take care of the rest...

So basically instead of using the Explicit .AddInParameter (which needs the DbType), the above solution works for me.. might for you too in this scenario.

Answer 2

The SqlCommand.Paramaters collection has an AddWithValue(String name, Object value) method... so you can skip the SqlDbType altogether if you're so inclined.