SqlParameter(string, object) can't handle constant value

Question

I got a function in which I send two requests to a SQL Server database; on the second request however, I get a SqlException and the parameter @mpe is missing. If I try to set the constant value 0 in the constructor of SqlParameter.

    protected static string GetX(int mpe, string xsection, string xkey)
    {
        var xSetup = App.Current.Db.GetType<Data.CachedTypes.XSetup>(
            "where mpehotel=@mpe and xsection=@xsection and xkey=@xkey",
            new System.Data.SqlClient.SqlParameter("@mpe", mpe),
            new System.Data.SqlClient.SqlParameter("@xsection", xsection),
            new System.Data.SqlClient.SqlParameter("@xkey", xkey));

        if (mpe > 0 && xSetup == null)
        {
            // Fallback mechanism. Always tries to get the default for all MPEs.
            xSetup = App.Current.Db.GetType<Data.CachedTypes.XSetup>(
                "where mpehotel=@mpe and xsection=@xsection and xkey=@xkey",
                new System.Data.SqlClient.SqlParameter("@mpe", 0), <-- THIS FAILES!!
                new System.Data.SqlClient.SqlParameter("@xsection", xsection),
                new System.Data.SqlClient.SqlParameter("@xkey", xkey));

However, if I extract the constant value 0 to a local value int xmpe = 0 and pass this to the constructor, the SQL query executes as expected and a result is retrieved. Can someone explain why this happens?

Answer 1

That's because when you do this:

new System.Data.SqlClient.SqlParameter("@mpe", 0)

The following constructor is used:

public SqlParameter(string parameterName, SqlDbType dbType)

Which is equivalent to:

new System.Data.SqlClient.SqlParameter("@mpe", SqlDbType.BigInt);

That's because 0 is implicitly convertable to any enum type, so this overload is preferred over this one:

public SqlParameter(string parameterName, object value)

However when you do

int someInt = 0; // or anything else
new System.Data.SqlClient.SqlParameter("@mpe", someInt)

Arbitrary int is not implicitly convertible to enum (only constant 0 is), so correct overload is chosen (with object value). The fact that arbitrary int can also be 0 doesn't matter, because overload resolution is performed at compile time. However if you do this:

const int mpe = 0;
new System.Data.SqlClient.SqlParameter("@mpe", mpe);

Then again wrong constructor is chosen, because mpe is known to be 0 at compile time. If you do this:

const int mpe = 1;
new System.Data.SqlClient.SqlParameter("@mpe", mpe);

Then again object value is chosen, for the reasons above.

To force correct overload all the time, cast your 0 to object:

new System.Data.SqlClient.SqlParameter("@mpe", (object) 0);