Help with Parameterizing SQL Query for C# with possible null values

Question

Hello, people.

I need help with parameterizing this query.

SELECT * 
FROM greatTable 
WHERE field1 = @field1
AND field2 = @field2

The user should be able to search for any of the 2 fields, and the user also should be able to search if the field2 has null values.

var query = "theQuery";
var cm = new SqlCommand(cn, query);

cm.AddParameter("@field1", "352515");
cm.AddParameter("@field2", DBNull.Value);

// my DataTable here is having 0 records
var dt = GetTable(cm);

[Edit]

What is the best alternative?

1. Keep CommandText constant so the plan in Sql be reused

   WHERE (field2 = @field2 OR @field2 IS NULL)

2. Change CommandText dinamically based on the values introduced by the user.

   WHERE field2 IS NULL

I'm not just thinking in one field, it could be various.

Answer 1

A dirty method: isnull(field2,0) = isnull(@param2,0)

have the isnull something that would never be in the field or param

Answer 2

You can use AND (@field2 IS NULL OR field2 = @field2) to make the query return all rows without checking field2 (to allow you to pass DbNull from your code. The complete query would be something like this:

SELECT * 
FROM greatTable 
WHERE field1 = @field1
AND (@field2 IS NULL OR field2 = @field2)

Note that when using this method there might be a performance-hit because of indexing. Take a look at this article for details.

Answer 3

A good question, but in practice I had no time such problem. If I use AddParameter then I also construct the string for the query in the same code fragment. So if I know, that now I should search for "@field2" equal to NULL I decide whether to construct

string query = "SELECT * " +
    "FROM greatTable " +
    "WHERE field1 = @field1";

or

string query = "SELECT * " +
    "FROM greatTable " +
    "WHERE field1 = @field1 AND field2 IS NULL";

and add only one parameter

cm.AddParameter("@field1", "352515");

So I have no time the problem which you describe.

UPDATED: What is the best (or the only correct) way is users input is NULL you should decide based on the context. In the most cases "AND field2 IS NULL" is not needed, but I read your question so, that in your special case the user can explicitly choose not "" (empty string), but a NULL value.