views:

920

answers:

1

How do I determine which SQLDBType to assign to a parameter depending on the input variable to store in the DB? Is there a GetType equivelant to test with?

If IsNumeric(ParameterValue) Then
    Parameter.SqlDbType = SqlDbType.Float
ElseIf IsDate(ParameterValue) Then
    Parameter.SqlDbType = SqlDbType.DateTime
ElseIf IsArray(ParameterValue) Then
    Parameter.SqlDbType = SqlDbType.VarBinary
Else
    Parameter.SqlDbType = SqlDbType.VarChar
End If
+1  A: 

There are too many SqlDbTypes (across too many versions) to do it the safely the way you want to, especially if you need to use more precise data types (e.g. decimal as opposed to float, or even bigint).

I would consider creating a simple mapping system between .NET data types and SqlDbTypes, using SMO. Then you simply map your parameters to the DB type as you require for storing the data.

The nice thing is that if you go to this effort now, you can reuse the library, or create an interface and extend it for each version of SQL.


Try this (C# code):

// Get the DataType from the DataRow in your result set
public void GetDataType(DataRow dr)
{
    DataType dt = new DataType((SqlDataType)Enum.Parse(typeof(SqlDataType), dr["DataTypeName"].ToString(), true))
    {
        MaximumLength = Convert.ToInt32(dr["ColumnSize"]),
        NumericPrecision = Convert.ToInt32(dr["NumericPrecision"])
    };
    // TODO: Map DataType to .NET datatype
}
Randolph Potter
That's a good idea. I like it but because the items are bassed in one at a time and are in an array I can't quite do it that way. Instead I'm actually checking for known types and where I have to be more specific I've added some extra elements to the array to track any specific data types.
Middletone