.net types to sqldbtypes

[Roy]

The SqlCommand.Parameters.Add(..) function takes an paramter for SqlDBType.
Is there a generic way in .NET framework to convert .net types to SqlDBTypes?
Or I have to mapping manually with a big switch statement?

[Mark Harris]

On Fri, 03 Mar 2006 13:11:28 +0800, Roy <Ro*@discussions.microsoft.com>
wrote:

The SqlCommand.Parameters.Add(..) function takes an paramter for
SqlDBType.
Is there a generic way in .NET framework to convert .net types to
SqlDBTypes?
Or I have to mapping manually with a big switch statement?

Conversion is handled automatically for you, for example:
cmd.Parameters.Add("control_id", SqlDbType.Int);
cmd.Parameters.Add("style_id", SqlDbType.Int);
cmd.Parameters.Add("name", SqlDbType.VarChar);
cmd.Parameters.Add("value", SqlDbType.VarChar);
cmd.Parameters.Add("enabled", SqlDbType.Bit);
cmd.Parameters.Add("isCollection", SqlDbType.Bit);

cmd.Parameters["control_id"].Value = controlId;
cmd.Parameters["style_id"].Value = _styleId;
cmd.Parameters["name"].Value = property.Name;
cmd.Parameters["value"].Value = tc.ConvertToString(value);
cmd.Parameters["enabled"].Value = true;
cmd.Parameters["isCollection"].Value = false;

No casting between types is required

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,
"Roy" <Ro*@discussions.microsoft.com> wrote in message
news:45**********************************@microsof t.com...

The SqlCommand.Parameters.Add(..) function takes an paramter for
SqlDBType.
Is there a generic way in .NET framework to convert .net types to
SqlDBTypes?
Or I have to mapping manually with a big switch statement?

It does it for you , SqlParameter.Value is of type object so it will do the
conversion internally.

One very important clarification though. if you are passing a null you have
to use DBNull , not null.
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Roy]

My situation is I have parameters as object array. When I walk through the
array, I can get .NET data types for each parameter. However,
SqlCommand.Parameters.Add(..) function
takes SqlDBType as its parameter. I can do a mapping with a switch
statement. but I want to know if there is a generic way or existing class
provided by .NET to do the mapping.

"Mark Harris" wrote:

On Fri, 03 Mar 2006 13:11:28 +0800, Roy <Ro*@discussions.microsoft.com>
wrote:

The SqlCommand.Parameters.Add(..) function takes an paramter for
SqlDBType.
Is there a generic way in .NET framework to convert .net types to
SqlDBTypes?
Or I have to mapping manually with a big switch statement?

Conversion is handled automatically for you, for example:
cmd.Parameters.Add("control_id", SqlDbType.Int);
cmd.Parameters.Add("style_id", SqlDbType.Int);
cmd.Parameters.Add("name", SqlDbType.VarChar);
cmd.Parameters.Add("value", SqlDbType.VarChar);
cmd.Parameters.Add("enabled", SqlDbType.Bit);
cmd.Parameters.Add("isCollection", SqlDbType.Bit);

cmd.Parameters["control_id"].Value = controlId;
cmd.Parameters["style_id"].Value = _styleId;
cmd.Parameters["name"].Value = property.Name;
cmd.Parameters["value"].Value = tc.ConvertToString(value);
cmd.Parameters["enabled"].Value = true;
cmd.Parameters["isCollection"].Value = false;

No casting between types is required

[Kevin Spencer]

> My situation is I have parameters as object array. When I walk through the

array, I can get .NET data types for each parameter. However,
SqlCommand.Parameters.Add(..) function
takes SqlDBType as its parameter. I can do a mapping with a switch
statement. but I want to know if there is a generic way or existing class
provided by .NET to do the mapping.
You've got a big problem there. There are far more SqlDBTypes than there are
matching .Net data types. The same .Net data types are used for multiple
SqlDBTypes, so it's not possible to determine the SqlDBType for a given .Net
data type. You can work it the other way round, but not that way.

As an example, take the .Net data type String. It is mapped to the following
SqlDBTypes:

Char
NChar
NText
NVarChar
Text
VarChar

Now, if you know the SqlDBType is one of these, you know that it is mapped
to System.String. But if you know the data type of a piece of data is a
string, which of these SqlDBTypes should it be matched to?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer

Presuming that God is "only an idea" -
Ideas exist.
Therefore, God exists.

"Roy" <Ro*@discussions.microsoft.com> wrote in message
news:D2**********************************@microsof t.com... My situation is I have parameters as object array. When I walk through the
array, I can get .NET data types for each parameter. However,
SqlCommand.Parameters.Add(..) function
takes SqlDBType as its parameter. I can do a mapping with a switch
statement. but I want to know if there is a generic way or existing class
provided by .NET to do the mapping.

"Mark Harris" wrote:

On Fri, 03 Mar 2006 13:11:28 +0800, Roy <Ro*@discussions.microsoft.com>
wrote:

> The SqlCommand.Parameters.Add(..) function takes an paramter for
> SqlDBType.
> Is there a generic way in .NET framework to convert .net types to
> SqlDBTypes?
> Or I have to mapping manually with a big switch statement?

Conversion is handled automatically for you, for example:
cmd.Parameters.Add("control_id", SqlDbType.Int);
cmd.Parameters.Add("style_id", SqlDbType.Int);
cmd.Parameters.Add("name", SqlDbType.VarChar);
cmd.Parameters.Add("value", SqlDbType.VarChar);
cmd.Parameters.Add("enabled", SqlDbType.Bit);
cmd.Parameters.Add("isCollection", SqlDbType.Bit);

cmd.Parameters["control_id"].Value = controlId;
cmd.Parameters["style_id"].Value = _styleId;
cmd.Parameters["name"].Value = property.Name;
cmd.Parameters["value"].Value = tc.ConvertToString(value);
cmd.Parameters["enabled"].Value = true;
cmd.Parameters["isCollection"].Value = false;

No casting between types is required

[Gabriel Magaña]

Agreed... The data type argument to Parameters.Add() is governed by the
database field data type, not the .NET data type used to contain the data.