Dynamically adding Sql type to a parameter

  • Thread starter Thread starter tshad
  • Start date Start date
T

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
 
tshad said:
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?
Click to expand...

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.
 
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?
Click to expand...

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.
 
The only requirement is that your source table will have to contain the
exact enum string (I don't think case matters)
Click to expand...

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".
 
Jeff Johnson said:
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:
Click to expand...
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
 
Back
Top