How do i determine the SQLDBType of a variable to be added to a parameter?

Question

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

Answer 1

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
}