Dynamically adding Sql type to a parameter

[tshad]

This is similar to my previous question.

If I have a set of parameters that I won't know until runtime what the
parameter type will be, is there an easy way to do this?

For example if I have:

cmdCleaned.Parameters.Add("@" + "Zip",
SqlDbType.varchar).Value = "something";
or
cmdCleaned.Parameters.Add("@" + "amount",
SqlDbType.decimal).Value = 100.50;

I may know know what the SqlDBType is except as a string I get from the
database.

So I may have a field called dbType with a value of "int" or "datatime" or
"varchar".

Is there some easy way to handle this without a bunch of if statements?

Thanks,

Tom

 

[Anthony Jones]

This is similar to my previous question.

If I have a set of parameters that I won't know until runtime what the
parameter type will be, is there an easy way to do this?

For example if I have:

cmdCleaned.Parameters.Add("@" + "Zip",
SqlDbType.varchar).Value = "something";
or
cmdCleaned.Parameters.Add("@" + "amount",
SqlDbType.decimal).Value = 100.50;

I may know know what the SqlDBType is except as a string I get from the
database.

So I may have a field called dbType with a value of "int" or "datatime" or
"varchar".

Is there some easy way to handle this without a bunch of if statements?

You've got one of two choices (that I know of).

A switch statement (or series of ifs) looking matching the common primitive
types to the SqlDbType equivalent. This isn't as bad as it sounds once
built into a function you can forget about it.

OR

pass everything as strings, SQL Server does a pretty good job of parsing
strings to the desired type.

I use both approaches but I prefer the former.

 

[Jeff Johnson]

This is similar to my previous question.

If I have a set of parameters that I won't know until runtime what the
parameter type will be, is there an easy way to do this?

For example if I have:

cmdCleaned.Parameters.Add("@" + "Zip",
SqlDbType.varchar).Value = "something";
or
cmdCleaned.Parameters.Add("@" + "amount",
SqlDbType.decimal).Value = 100.50;

I may know know what the SqlDBType is except as a string I get from the
database.

So I may have a field called dbType with a value of "int" or "datatime" or
"varchar".

Is there some easy way to handle this without a bunch of if statements?

SqlDbType is simply an enum, and so you can get an instance of it from a
string using Enum.Parse(). The only requirement is that your source table
will have to contain the exact enum string (I don't think case matters), so
if, for example, the SqlDbType member is "Int", your table cannot contain
"Integer". Example code:

SqlDbType dbType = (SqlDbType)Enum.Parse(typeof(SqlDbType),
desirdeDataTypeString);

And then you use dbType in the Parameters.Add() method.

 

[Jeff Johnson]

The only requirement is that your source table will have to contain the
exact enum string (I don't think case matters)

Okay, with the code I posted, case DOES matter, but there's another overload
which allows you to pass a Boolean to indicate whether Parse() should be
case sensitive. Just set it to false if you want to allow your database to
contain "varchar" instead of "VarChar".

 

[tshad]

SqlDbType is simply an enum, and so you can get an instance of it from a
string using Enum.Parse(). The only requirement is that your source table
will have to contain the exact enum string (I don't think case matters),
so if, for example, the SqlDbType member is "Int", your table cannot
contain "Integer". Example code:

Worked fine when I did:

parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType),
"Decimal");

case did matter, btw.

If I did:

parameter.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType),
"decimal");

I got an error.

Thanks,

Tom

 

[Jeff Johnson]

case did matter, btw.

Yeah, see my add-on reply to myself.