Why does DBNull.Value require a proper SqlDbType?

Question

I've written a bunch of generic C# helper functions as a base for my SQL queries. All null object parameters are converted to a DBNull.Value. But when the target column is a varbinary, it tells me that string type is not compatible with the binary one and I must convert.

Why do I need to SqlDbType a DBNull.Value in C#? And is there any generic way to go around this?

// works for nvarchar but throws for varbinary columns
// notice the lack of SqlDbType as I don't know it for a null object
var param1 = new SqlParameter();
param1.Name = "@Param1";
param1.Value = DBNull.Value;

It's a null... it needs no type. SQL server knows the null's type. In SQL you say [Column] IS NULL not [Column] IS TYPE NULL. So null is untyped. And the target column is resolved from the query not the SqlDbType.

I just find this very unintuitive (explicitly typing nulls). I'm wondering if I'm missing something...

Code is like this:

SqlCommand CreateCommand(string query, params object[] arguments) { ... }
// in here I walk the parameters and build the proper SqlParameter for them.
// this saves me from setting up the SqlParameters by hand.

PS: String = NVarChar and Binary means VarBinary. Seriously... PPS: Not interested in EF or anything of the like.

Answer 1

If you are not setting DBType of your SqlParameter explicitly - it has default value SqlDBType.NVarchar (see MDSN for reference).

That's why you're getting exception - your parameter type is NVarchar but target column is VarBinary.

Note that you have to set DBType explicitly just because obviously ADO.NET can't deduce datatype from DBNull.Value.

null is not a type - it just absense of value, so if I'll give you null you can't deduce if it absence of varchar or, for example, absence of int.

UPDATE

I've made some experiments. Let's run simple code:

SqlCommand cmd = new SqlCommand("select * from sometable where somecolumn = @Param1");
cmd.Connection = _MyConnection;

var param = new SqlParameter();
param.ParameterName = "@Param1";
param.Value = DBNull.Value;
cmd.Parameters.Add(param);

cmd.Connection.Open();
try
{
    cmd.ExecuteNonQuery();
}
finally
{
    cmd.Connection.Close();
}

As we can see from SQL Profiler, this will be translated to actual SQL query as:

exec sp_executesql 
    N'select * from sometable where somecolumn = @Param1',
    N'@Param1 nvarchar(4000)',
    @Param1=NULL

So here we definitely will get exception if somecolumn datatype can't be implicitly converted from varchar just because @Param1 datatype is explicitly set to varchar.

At the first look - this can be good enough explanation, right?

But there is one issue confusing me. If we will execute not a straight query, but "dummy" stored procedure having varbinary as parameter, let's say:

create procedure [dbo].[usp_Test]
(
    @Param1 varbinary(max)
)
as
begin
    set nocount on

    select null
end

Now let's try to call it like:

SqlCommand cmd = new SqlCommand("dbo.usp_Test");
cmd.CommandType = CommandType.StoredProcedure;
... and so on (the rest of code dealing with parameter)

Now in SQL Profiler we'll see just this:

exec dbo.usp_Test @Param1=NULL

And this is pretty strange since it is absolutely legal call without explicit specification of parameter type. Actually, if we call this in SQL Management Studio - we will not get any exception.

This part of ADO.NET behaviour is quite strange to me. Probably SQL Profiler doesn't shows complete process (despite the fact I turned on all events tracking), I don't know.

Anyway - recommendation is still the same - always specify DBType of your SqlParameter explicitly.