Convert a object variable to a DBType before setting object as value of a parameter?

Question

i am using a SqlCommand to execute a stored procedure. The parameters are created automatically based on the definition of the stored procedure by using the DeriveParameters method of class SqlCommandBuilder. This automatically sets the DbType for me. Next I loop through the dictionary with <string, object> key-value pairs where the string is the name of the parameter and the object contains its value to set.

Simplied example source:

public DataTable FetchProducts(SqlConnection sqlConn, IDictionary<string, object> paramvalues)
{
    using (SqlCommand cmd = new SqlCommand("ProcFetchProducts", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlCommandBuilder.DeriveParameters(cmd);

        foreach (KeyValuePair<string, object> pair in paramvalues)
        {
            var index = cmd.Parameters.IndexOf(pair.Key);
            cmd.Parameters[index].Value = pair.Value;
        }

        using (var dr = cmd.ExecuteReader())
        {
            var dt = new DataTable("Result");
            dt.Load(dr);
            return dt;
        }
    }
}

Sometimes the object contains a value which does not match the parameter's DBType. For instance, a parameter is of type smallint and the object contains a string. Now when I execute the datareader, I get a "input string is not in a correct format" FormatException, which does not tell me which parameter is causing this problem.

So my main question is: is there a way to cast the object from the dictionary to the DBType defined in the parameter, so I can check if it is of the right type before executing the datareader?

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

Updated

======Updated the answer after clarification from OP(see comments)=======

You need to maintain the list of CLR type map w.r.t. SqlDbType then check if it's a string type and parse/convert the string type to respective clr type and return it as object. This will change the underlying type from string to mapped clr type of SqlDbType.

SqlDbType to CLR Type: (referred from this source)

public static Type GetClrType(SqlDbType sqlType)
{
    switch (sqlType)
    {
        case SqlDbType.BigInt:
            return typeof(long?);

        case SqlDbType.Binary:
        case SqlDbType.Image:
        case SqlDbType.Timestamp:
        case SqlDbType.VarBinary:
            return typeof(byte[]);

        case SqlDbType.Bit:
            return typeof(bool?);

        case SqlDbType.Char:
        case SqlDbType.NChar:
        case SqlDbType.NText:
        case SqlDbType.NVarChar:
        case SqlDbType.Text:
        case SqlDbType.VarChar:
        case SqlDbType.Xml:
            return typeof(string);

        case SqlDbType.DateTime:
        case SqlDbType.SmallDateTime:
        case SqlDbType.Date:
        case SqlDbType.Time:
        case SqlDbType.DateTime2:
            return typeof(DateTime?);

        case SqlDbType.Decimal:
        case SqlDbType.Money:
        case SqlDbType.SmallMoney:
            return typeof(decimal?);

        case SqlDbType.Float:
            return typeof(double?);

        case SqlDbType.Int:
            return typeof(int?);

        case SqlDbType.Real:
            return typeof(float?);

        case SqlDbType.UniqueIdentifier:
            return typeof(Guid?);

        case SqlDbType.SmallInt:
            return typeof(short?);

        case SqlDbType.TinyInt:
            return typeof(byte?);

        case SqlDbType.Variant:
        case SqlDbType.Udt:
            return typeof(object);

        case SqlDbType.Structured:
            return typeof(DataTable);

        case SqlDbType.DateTimeOffset:
            return typeof(DateTimeOffset?);

        default:
            throw new ArgumentOutOfRangeException("sqlType");
    }
}

String to type converter:

private static object Convert(string value, Type type)
    {
        object result;

        if (string.IsNullOrWhiteSpace(value))
        {
            return null;
        }

        try
        {
            var converter = TypeDescriptor.GetConverter(type);
            result = converter.ConvertFromString(value);
            return result;
        }
        catch (Exception exception)
        {
            // Log this exception if required.
            throw new InvalidCastException(string.Format("Unable to cast the {0} to type {1}", value, newType, exception));
        }
    }

Usage:

foreach (KeyValuePair<string, object> pair in paramvalues)
{
    var index = cmd.Parameters.IndexOf(pair.Key);

    var value = pair.Value;

    if (pair.Value == typeof(string))
    {
        value = Convert((string)pair.Value, GetClrType(cmd.Parameters[index].SqlDbType));
    }

    cmd.Parameters[index].Value = value;
}

Correct me if I am wrong, but in the example I gave, my parameters have been derived from the stored procedure definition. A parameter in the proc is of type smallint, but my object (the value) is of type string. in your case, the dbtype would be DbType.String, which will result in an error from the datareader because of type mismatch. — TedOnTheNet, Dec 17 '15 at 21:53
I got your point.. You would need a map for SqlDbType and .Net types. Then you need a string parser which will convert provided value according to .NetType w.r.t. SqlDbType in your parameter. I'll update my answer. — vendettamit, Dec 17 '15 at 22:04
nice.. almost the same as my answer :) I just used Convert.ChangeType and see if conversion is possible. if not, the types don't match. I'll accept your answer for all your work. thanks. — TedOnTheNet, Dec 17 '15 at 22:31
@vendettamit How can I convert the SqlDbType.Structured into a string, as captured in the MSSQL Profiler. — Thrainder, Oct 07 '22 at 12:35

score 1 · Answer 2 · edited May 23 '17 at 12:01

There is no built-in function, but you can create your own simple list and check whether they match:

typeMap = new Dictionary<Type, DbType>();
typeMap[typeof(byte)] = DbType.Byte;
typeMap[typeof(sbyte)] = DbType.SByte;
typeMap[typeof(short)] = DbType.Int16;
typeMap[typeof(ushort)] = DbType.UInt16;
typeMap[typeof(int)] = DbType.Int32;
typeMap[typeof(uint)] = DbType.UInt32;
typeMap[typeof(long)] = DbType.Int64;
typeMap[typeof(ulong)] = DbType.UInt64;
typeMap[typeof(float)] = DbType.Single;
typeMap[typeof(double)] = DbType.Double;
typeMap[typeof(decimal)] = DbType.Decimal;
typeMap[typeof(bool)] = DbType.Boolean;
typeMap[typeof(string)] = DbType.String;
typeMap[typeof(char)] = DbType.StringFixedLength;
typeMap[typeof(Guid)] = DbType.Guid;
typeMap[typeof(DateTime)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
typeMap[typeof(byte[])] = DbType.Binary;
typeMap[typeof(byte?)] = DbType.Byte;
typeMap[typeof(sbyte?)] = DbType.SByte;
typeMap[typeof(short?)] = DbType.Int16;
typeMap[typeof(ushort?)] = DbType.UInt16;
typeMap[typeof(int?)] = DbType.Int32;
typeMap[typeof(uint?)] = DbType.UInt32;
typeMap[typeof(long?)] = DbType.Int64;
typeMap[typeof(ulong?)] = DbType.UInt64;
typeMap[typeof(float?)] = DbType.Single;
typeMap[typeof(double?)] = DbType.Double;
typeMap[typeof(decimal?)] = DbType.Decimal;
typeMap[typeof(bool?)] = DbType.Boolean;
typeMap[typeof(char?)] = DbType.StringFixedLength;
typeMap[typeof(Guid?)] = DbType.Guid;
typeMap[typeof(DateTime?)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;
typeMap[typeof(System.Data.Linq.Binary)] = DbType.Binary;

Just call GetType of your KVP-Value, like this:

foreach (KeyValuePair<string, object> pair in paramvalues)
{
    var index = cmd.Parameters.IndexOf(pair.Key);
    cmd.Parameters[index].Value = pair.Value;

    // If null, you should use DbNull.
    var YOUR_TYPE = typeMap[pair.Value.GetType()];
}

Credits for the list goes to: https://stackoverflow.com/a/7952171/2630261

Just as some hint, i would prefer var over KeyValuePair<string, object>, but this is not important for your question :)

Like I just commented to Vendettamit's post, this will return a stringtype. What I want to do, is try and convert a string into a smallint so that if it's a string, it will fail because the type is wrong. — TedOnTheNet, Dec 17 '15 at 21:57

score 1 · Answer 3 · answered Dec 17 '15 at 22:27

Thanks to @BendEg and @Vendettamit I found a solution. I created a dictionary Dictionary<DbType, Type>() based on their example code and mapped each DbType to a Clr Type. A simple GetClrType method gets the clr type from the dictionary. Next, I try to convert it. If this fails, I catch the exception and report back to the user that conversion failed and the value for the parameter is of the wrong type.

Type clrType = SqlDbTypeResolver.GetClrType(cmd.Parameters[index].DbType);
try
{ 
    Convert.ChangeType(parm.Value, clrType); // no need to store the value, I just need to catch the exception if thrown.
}
catch(SomeException ex)
{
    //report stuff to user about failed conversion
}

Convert a object variable to a DBType before setting object as value of a parameter?

3 Answers3

Updated

SqlDbType to CLR Type: (referred from this source)

String to type converter:

Usage: