.NET: How to deal with possible null values in an SQL query?

Question

Code:

private void DoSomethingWithDatabase(string f1, int f2) 
{
    SqlCommand myCommand = new SqlCommand("SELECT Field1,Field2,Field3 FROM MyTable WHERE Field1 = @F1 AND Field2 = @F2", this.myConn);
    myCommand.Parameters.Add("@F1", System.Data.SqlDbType.VarChar);
    myCommand.Parameters.Add("@F2", System.Data.SqlDbType.Int);

    if (f1 == "") 
        myCommand.Parameters["@F1"].Value = DBNull.Value;
    else
        myCommand.Parameters["@F1"].Value = f1;

    if (f2 < 0)
        myCommand.Parameters["@F2"].Value = DBNull.Value;
    else
        myCommand.Parameters["@F2"].Value = f2;

    // code to do stuff with the results here
}

The server is a Microsoft SQL Server instance.

The database table MyTable contains fields which are nullable. Therefore null is a valid value to search on when performing the query.

From my reading, and also from testing code like this, doing something like what I did here doesn't work properly because apparently you can't do an "equals null" comparison this way - you're supposed to do "IS NULL".

It looks like you can correct this and make it work by setting ANSI_NULL to OFF (as per https://msdn.microsoft.com/en-us/library/ms188048.aspx) but it also indicates this method is deprecated and should not be used.

That article suggests you can use an OR operator to do something like WHERE Field1 = 25 OR Field1 IS NULL. The problem is, with a single call to this function, I want to check for either null and only null, or for the given constant value and nothing else.

So far, it seems like I need to basically build the query piece by piece, string by string, to account for the possibility of NULL values. So I'd need to do something like:

string theQuery = "SELECT Field1,Field2,Field3 FROM MyTable WHERE ";
if (f1 == "")
    theQuery += "Field1 IS NULL ";
else
    theQuery += "Field1 = @F1 ";

// ...

SqlCommand myCommand = new SqlCommand(theQuery, this.myConn);
if (f1 == "")
{
    myCommand.Parameters.Add("@F1", System.Data.SqlDbType.VarChar);
    myCommand.Parameters["@F1"].Value = f1;
}

// ...

Is this really how it needs to be done? Is there a more efficient way to do this without repeating that if block and by using parameters rather than concatenating a query string together?

(Notes: An empty string is converted to a NULL here for the example. In the scenario I'm actually working with, empty strings are never used, but instead NULL is stored. The database is out of my control, so I can't just say "change all your NULLs to empty strings". Same goes for the ints - if we pass, say, -1 into the function it should be testing for a null value. Bad practice? Maybe, but the database itself is not in my control, only the code to access it is.)

Answer 1

Why not using:

string theQuery = "SELECT Field1, Field2, Field3 FROM MyTable WHERE ISNULL(Field1,'') = @F1";

?

That way you get rid of your if block and your null values are interpreted as an empty string, like your f1 variable.

Answer 2

SQL Server has a function called ISNULL(Column,Value) where you can specify one column to check and set a Default Value in case this Column is NULL. You can check here