Method Overriding in SqlParameter

Question

I am using parameterized query in C# with following piece of code:

int i = 5;
string query = "select col1 from table where col2 = @prm1 and col3 = @prm2";

SqlCommand cmd = new SqlCommand(query,connection);
cmd.Parameters.Add(new SqlParameter("@prm1", (int)MyEnum.val1));
cmd.Parameters.Add(new SqlParameter("@prm2", i);

public enum MyEnum
{
    val1 = 0,
    val2 = 100,
    val3 = 150
}

Now, the built-in class SqlParameter has 2 overloaded method with 2 params as follows :

public SqlParameter(string parameterName, SqlDbType dbType);
   
public SqlParameter(string parameterName, object value);

Now, for @prm2, where I am passing int variable, it's correctly initializing parameter with proper value. But for @prm1, where I am passing enum & converting it as int as value, it's taking it as 1st method of SqlParameter.

Built-in SqlDbType enum is as follows:

public enum SqlDbType
{
   BigInt = 0,
   Binary = 1,
   ....
   ....
}

Since the value of enum I am passing is 0, it is taking that 0 and using first method and creating a parameter with BigInt DATATYPE instead of creating param with value 0.

What could be the reason behind this? Is it some sort of bug or am I missing some silly point here?

Update: Although this provides answer to my original question, which says '0 is implicitly convertable to enum, but non-zero value isn't' hence the difference in choosing method for both statements. I've tried following:

     cmd.Parameters.Add(new SqlParameter("@prm1", (int)MyEnum.val1));
     cmd.Parameters.Add(new SqlParameter("@prm1", Convert.ToInt32(MyEnum.val1)));

1st statement calling method with SqlDbType arg & 2nd is calling method with object value, even though I'm basically passing both values as int. Any explaination for that?

You should always use the first overload anyway, so that you are in control of specifying the datatype and to ensure its consistent every time the query is called - else you can end up with different query plans. — Dale K, Sep 14 '20 at 20:35
@DaleK But in order to invoke first method, i supposed to pass 2nd argument of `SqlDbType`, but here in both cases, I am passing `int` & yet diff methods gets invoked for both statements. — Harshil Doshi, Sep 14 '20 at 20:39
`SqlDbType` is also an enum, which is an int... so I guess the compiler is getting confused around which enum is being used. However if you use the first method correct, by actually specifying a type you will never have an issue. — Dale K, Sep 14 '20 at 20:40
@DaleK hmmm...that makes some sense actually. Also, I tried with `Convert.ToInt32(MyEnum.val1)` and it took it as value (method-2) instead of `DbType`. So I guess, there is some sort of precedence is in play here. — Harshil Doshi, Sep 14 '20 at 20:47
So this only seems to be an issue when the underlying value is 0. So for instance if you try `(int)DayOfWeek.Monday` it uses the object overload, but for `(int)DayOfWeek.Sunday` it goes with `SqlDbType`. In fact if you just do `new SqlParameter("name", 0)` that also goes with `SqlDbType`, but any other `int` does not. — juharr, Sep 14 '20 at 21:15
@juharr exactly. Try with `new SqlParameter("name", (int)0)` and `new SqlParameter("name", Convert.ToInt32(0))` and it will interpret both statements in a different way. — Harshil Doshi, Sep 16 '20 at 15:15
Yeah, it has to specifically be a constant value of 0. It technically doesn't know the result of `Convert.ToInt32(0)` until runtime. — juharr, Sep 16 '20 at 16:09

eocron · Answer 1 · 2020-09-14T21:08:40.647

A little bit clumsy API in my opinion. Just specify it explicitly:

cmd.Parameters.Add(new SqlParameter("@prm1", SqlDbType.Int){ Value = (int)MyEnum.val1}));

I usually replace it with extension if not using any ORM or frameworks to build SQL:

public static class SqlExtensions
{
    public static SqlParameterCollection Add(this SqlParameterCollection pars, string name, SqlDbType type, object value)
    {
        var res = new SqlParameter(name, type){Value = (value == null ? DbNull.Value)};
        pars.Add(res);
        return pars;
    }
}

Then:

cmd.Parameters.Add("@a", SqlDbType.Int, 123)
              .Add("@b", SqlDbType.BigInt, 234)
              .Add("@c", SqlDbType.Binary, null);

oh yes...offcourse it'll work if you specifically define the value & datatype. I am just confused with choice of method on runtime by compiler here. — Harshil Doshi, Sep 14 '20 at 21:13

Method Overriding in SqlParameter

1 Answers1