C#: Convert String to DBType.AnsiStringFixedLength

Question

Hi,

I have a stored procedure. One of its input parameters is expecting a char(8). I try to convert a string "AAA" to this particular parameter type, which is a DBType.AnsiStringFixedLength.

object v = Convert.ChangeType("AAA", param.DbType.GetTypeCode()); 
// param is AnsiStringFixedLength

However, all I get is an exception: Input string was not in a correct format. And the stack trace says: at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) [...]

Why is System.Convert trying to convert a string into a number, even though the prodecure's parameter is expecting a char(8)? How do I solve this? I don't want to use one huge switch case mapping all SQL types to CLR types...

EDIT: This is the code in question: (A generic method to call any MS SQL stored procedure)

using (SqlConnection conn = new SqlConnection(this.config.ConnectionString))
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = this.config.StoredProcedureName;

        conn.Open();
        SqlCommandBuilder.DeriveParameters(cmd);

        foreach (SqlParameter param in cmd.Parameters)
        {
            if (param.Direction == ParameterDirection.Input ||
                 param.Direction == ParameterDirection.InputOutput)
            {
                try
                {
                    string rawParam = param.ParameterName.Replace("@", "");
                    if (this.config.Parameters.ContainsKey(rawParam))
                    {
                        try
                        {
                            param.Value = Convert.ChangeType(this.config.Parameters[rawParam],
                                param.DbType.GetTypeCode());
                        }
                        catch(Exception oops)
                        {
                            throw new Exception(string.Format("Could not convert to '{0}'.", param.DbType), oops);
                        }
                    }
                    else
                        throw new ArgumentException("parameter's not available");
                }
                catch (Exception e)
                {
                    throw;
                }
            }
        }  
        cmd.ExecuteNonQuery();
    }
}

The actual parameter values are provided by this.config.Parameters - all of them are strings. I iterate through SqlCommand's parameter list and set them accordingly. Converting the string values to the parameter's Sql type is necessary here, and as far as I can see, the Sql type is provided by param.DBType.

Answer 1

You seem to mix up some things here, or I don't get what you try to do. The DbType (an enumeration) inherits Enum and that implements IConvertible -> You can call GetTypeCode(). But - you are now calling Enum.GetTypeCode(), which returns the underlying type. If you didn't specify it (and DbType didn't) any Enum is backed by an int.

What are you trying to solve with the code anyway? Why would you want to change the type of a string if the parameter is a string (although with a fixed length)?

---

Looking at the question some more it seems even more odd. You have an object v (probably for value?) - what do you care about the type?

object v1 = "Foo";

object v1 = 42;

What is the difference for you? I guess you want to pass the values to something else, but - if you only reference the value as object you might still need to cast it.

Please update your question and explain what you really want to do, what you expect to gain.

---

Regarding the comment:

I'm using Convert.ChangeType(object value, TypeCode typeCode), so it's not really converting into an Enum/int. At least that's what I thought...

See above: DbType.GetTypeCode() is not what you want. Try it, give me the benefit of the doubt: What do you expect to get from DbType.AnsiStringFixedLength.GetTypeCode()? What is the actual result, if you try it?

Now to your code: You try to set the SqlParameter.Value property to the "correct" type. Two things: According to the documentation you probably want to set the SqlParameter.SqlValue, which is the value using SQL types according to the docs. SqlParameter.Value, on the other hand, is the value using CLR types and allows to infer both DbType and SqlValue. Sidenote, implementation detail: The SqlParameter.SqlValue setter just calls the setter of SqlParameter.Value again...

I would expect that the ADO.NET stuff converts the value on its own, if at all possible. What error are you getting without jumping through this hoops?