Pass "IS NULL" via parameter

Question

I have 5 textboxes:

<TextBox Name="txbFirstName" />
<TextBox Name="txbLastName" />
<TextBox Name="txbCity" />        
<TextBox Name="txbAddress" />
<TextBox Name="txbPhone" />

I want to generate simple SELECT statements using the textbox inputs. To do this I am using parameters and AddWithValue:

database.SetQuery("SELECT * FROM tblCustomer WHERE FirstName = @FirstName AND LastName = @LastName AND City = @City AND Address = @Address AND Phone = @Phone;");
database.sqlCommand.Parameters.AddWithValue("@FirstName", txbFirstName.Text);
database.sqlCommand.Parameters.AddWithValue("@LastName", txbLastName.Text);
database.sqlCommand.Parameters.AddWithValue("@City", txbCity.Text);
database.sqlCommand.Parameters.AddWithValue("@Address", txbAddress.Text);
database.sqlCommand.Parameters.AddWithValue("@Phone", txbPhone.Text);

Now this works just fine, but what I want to do is that if textbox input is empty to handle it with NULL. But as far as I know one can't use "= NULL" in query, instead "IS NULL" should be used, meaning I can't write something like this:

if (txbCity.Text == "")
    database.sqlCommand.Parameters.AddWithValue("@City", null);
else
    database.sqlCommand.Parameters.AddWithValue("@City", txbCity.Text);

Is it possible to pass "IS NULL" to a parameter with code? So if txbCity and txbAddress were null, for example:

• First Name = John
• Last Name = Doe
• City = ""
• Address = ""
• Phone = 812-393-8144

I would want my query to look like this:

SELECT * FROM tblCustomer WHERE FirstName = "John" AND LastName = "Doe" AND City IS NULL AND Address IS NULL AND Phone = "812-393-8144";

Answer 1

Basically: no.

Unless you have disabled ANSI NULL syntax (please don't do that), you need different SQL to test for NULL. A common trick (easy to write, but not very efficient) is to cheat with:

WHERE (FirstName = @FirstName OR (@FirstName IS NULL AND FirstName IS NULL))
AND (LastName = @LastName OR (@LastName IS NULL AND LastName IS NULL))
-- etc x5

(or whatever test you want to perform for nulls) - and use something like:

database.sqlCommand.Parameters.AddWithValue("@FirstName", txbFirstName.Text.DBNullIfEmpty());
database.sqlCommand.Parameters.AddWithValue("@LastName", txbLastName.Text.DBNullIfEmpty());
// etc x5

where DBNullIfEmpty would look something like:

internal static class MyExtensionMethods
{
    public static object DBNullIfEmpty(this string value)
    {
        if(string.IsNullOrWhiteSpace(value)) return DBNull.Value;
        return value;
    }
}