CODEWITHSUNDEEP
c#.netsql-server
mngeek206
mngeek206

Reputation: 5157

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

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.)

Upvotes: 6

Views: 2703

Answers (6)

Cetin Basoz
Cetin Basoz

Reputation: 23797

SqlCommand myCommand = new SqlCommand(@"SELECT Field1,Field2,Field3 
     FROM MyTable 
     WHERE 
     ( (@F1 IS NULL AND [field1] IS NULL) OR [field1] = @F1 ) AND
     ( (@F2 IS NULL AND [field2] IS NULL) OR [field2] = @F2 );", this.myconn);
    myCommand.Parameters.Add("@F1", System.Data.SqlDbType.VarChar).Value = DBNull.Value;
    myCommand.Parameters.Add("@F2", System.Data.SqlDbType.Int).Value = DBNull.Value;


    if (!string.IsNullOrEmpty(f1))
      myCommand.Parameters["@F1"].Value = f1;
    if (f2 != -1)
      myCommand.Parameters["@F2"].Value = f2;

This would utilize indexes on fields.

Upvotes: 1

Mart&#237;n Misol
Mart&#237;n Misol

Reputation: 333

The way you are dealing with NULLs is the right way. Maybe you could use a helper method like this one:

private string AppendParameter(string query, string parameterName, string parameterValue, SqlParameterCollection parameters)
{
     if (string.IsNullOrEmpty(parameterValue))
         query += "Field1 IS NULL ";
     else
     {
         query += "Field1 = " + parameterName + " ";
         parameters.AddWithValue(parameterName, parameterValue);
     }

     return query;
}

Upvotes: 1

Kabulan0lak
Kabulan0lak

Reputation: 2136

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.

Upvotes: 3

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You could do something like

WHERE ISNULL(Field, '') = @F1

In that case, NULL fields are treated like empty strings. Another way would be:

WHERE Field IS NULL OR Field = @1

Upvotes: 1

Daniel W
Daniel W

Reputation: 1142

If you want to avoid if blocks, you can probably change the query into something like this:

SELECT Field1,Field2,Field3 FROM MyTable 
WHERE COALESCE(Field1,'') = @F1 AND COALESCE(Field2,-1) = @F2

Upvotes: 0

Eduardo Hitek
Eduardo Hitek

Reputation: 613

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

Upvotes: 2

Related Questions