Converting null to DBNull not required when using context connection

Question

Trying to understand why i don't have to convert null to DBNull when using a context connection in a Sql server 2008 Clr assembly.

I use the following clr procedure:

    [SqlProcedure]
    public static void ParamTest(SqlBoolean useNormalConnection)
    {
        bool mode = useNormalConnection.Value;

        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "INSERT INTO TestNote (UserId, [Text]) VALUES (@UserId, @Text)";

            cmd.Parameters.Add("UserId", SqlDbType.Int).Value = null;
            cmd.Parameters.Add("Text", SqlDbType.NVarChar).Value = "hello!";

            if (mode)
            {
                using (SqlConnection con = new SqlConnection(@"Data Source=localhost\SQL2008;Initial Catalog=TestDb;Integrated Security=True"))
                {
                    con.Open();
                    cmd.Connection = con;
                    cmd.ExecuteNonQuery();
                }
            }
            else
            {
                using (SqlConnection con = new SqlConnection("context connection=true;"))
                {
                    con.Open();
                    cmd.Connection = con;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }

exec ParamTest 0 will work

exec ParamTest 1 wont work giving the following exception message:

The parameterized query '(@UserId int,@Text nvarchar(6))INSERT INTO TestNote (UserId' expects the parameter '@UserId', which was not supplied.

Why does passing null work when using a context connection?

Answer 1

I don't know the answer for sure but I would guess that it is using a different provider behind the scenes for a context connection and is simply an implementation difference. I wouldn't count on setting the value to null to work. The behavior also appears to be different for varying versions of the .NET Framework -- e.g. for .NET 3.5 according to this you need to use DBNull, but if you switch to .NET 4.5 docs it doesn't mention DBNull at all.