SqlCommand, passing something that could be null

Question

I have a SQL command that I've been asked to modify, and I'm having some troubles with the fact that what I'm passing to the SQL can now be null. If I'm passing a value, I can rely on the columnName = @parameterName in the SQL, but with NULL, I can't pass null or DBNull and have it correctly resolve.

Here's the SQL pseudocode:

SELECT
  Columns
FROM
  ClientSetup
WHERE
  Client_Code = @ClientCode AND
  Package_Code = @PackageCode AND
  Report_Code = @ReportCode

The problem is that now @ReportCode can validly be NULL. In my C# code where I set up the SqlCommand, I can put in:

cmd.Parameters.Add("@ReportCode", SqlDBType.VarChar, 5).Value = reportType;
  //reportType is a string, which can be null

But, if reportType is null, I need to use Report_Code IS NULL in the SQL, rather than Report_Code = @reportCode.

The solution I've found is to change the last where clause to the following:

((@ReportCode IS NULL AND Report_Code IS NULL) OR Report_Code = @ReportCode)

and the parameter phrase to

cmd.Parameters.Add("@ReportCode", SqlDBType.VarChar, 5).Value = string.IsNullOrEmpty(reportType) ? System.DBNull : reportType;

What this does works, but I was wondering if anyone knew of a cleaner or better way to handle nullable parameters when passing things to SQL from .NET code.

Answer 1

The short answer is that no, the SqlClient API requires you to pass in a DbNull.Value for NULL parameter values.

But I have some doubts about how you treat NULLs. For one you use string.IsNullOrEmpty which means that you treat the emtpy string as a NULL. This is questionable, there may be legitimate empty string values in the database.

My second concern is the logic of matching NULLs in the database. More often than not passing in a NULL parameter means that the request is interested in any value, not specifically in NULL values. I'm not saying your logic of matching NULL parameters to NULL values is flawed, I just want to make sure you know what you're doing.