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?

Answer 1

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;
}