How to add a "select all" value to a parametrized query in C# and MS SQL

Question

I have the following code:

As you can see, i need to pass a parameter to Field2, but i also need that parameter to be ablo to handle the ""all values" option, for example, if i assign "foo" to the parameter, the query will return every record where Field2 = "foo"... but i also want to be able to pass a wildcard, or something to tell that parameter to give all values as result.

MyDataset dataset = new MyDataset();
SqlConnection objConnection = new SqlConnection(_connectionstring);
SqlDataAdapter objDataAdapter = new SqlDataAdapter();
objDataAdapter.SelectCommand = new SqlCommand();
objDataAdapter.SelectCommand.Connection = objConnection;
objDataAdapter.SelectCommand.CommandText =
"SELECT Field1, Field2, Field3 FROM Table WHERE (Field2 = @Field2)";
objDataAdapter.SelectCommand.CommandType = CommandType.Text;
objDataAdapter.SelectCommand.Parameters.AddWithValue("@Field2", txtBoxField2.Text);
objDataAdapter.Fill(dataset.Table);
this.DataContext = dataset.Table.DefaultView;

Thank you in advance.

Answer 1

One way to do this is to use nullable parameters:

SELECT Field1, Field2, Field3 
FROM Table 
WHERE (@Field2 IS NULL OR Field2 = @Field2)

but you need to be aware that this can lead to incorrectly cached query plans in some circumstances where there are many parameters. If you are using SQL Server 2005+, this can be mitigated to a large ectent using OPTIMIZE FOR.

Also, make sure your statistics are up to date.

Answer 2

If you're building up the SQL like that within your C# code, then just put an if condition in your C# logic to not include the WHERE clause in the instance you want to return all records. This would create a different SQL statement, so would be OK for performance/execution plan-wise.

If you're going to be using a sproc, you could try this approach:

IF (@Field2 = 'SomeWildcardValue')
    SELECT Field1, Field2, Field3 FROM SomeTable
ELSE
    SELECT Field1, Field2, Field3 FROM SomeTable WHERE Field2 = @Field2

Or even, keep them completely separate, create one sproc to return ALL, and one to return based on Field2 search. Although if you have a larger number of parameters, this can soon mount up!

Answer 3

In general I would use the nullable parameter method in an SP. Based on your code though, why don't you build the SQL commandtext based on what the user has entered? For example, only add a where clause to the SQL if the user has entered something into the text box, otherwise, just a simple select.