Dynamic WHERE clauses in SqlDataSource

Question

I use SqlDataSource in a very simple application. I allow the user to set several search parameters for the SDS select command via TextBoxes, one TextBox for each parameter (think txtFirstName, txtLastName, etc.). I plan to use a button click event handler to set the SqlDataSource SelectCommand property, which by default will return all records (for my purposes here). I want to refine this select command in order to add one or more WHERE clauses depending on whether the user enters search criteria in any of my text fields.

An example in case I do not understand:

By default, the SqlDataSource SelectCommand property will look something like this:

SELECT * FROM MyTable 

If the user enters "Bob" in txtFirstName, I want the SelectCommand property to look like this:

 SELECT * FROM MyTable WHERE [FirstName]='Bob' 

If the user enters "Jones" in txtLastName, I want the SelectCommand property to look like this:

 SELECT * FROM MyTable WHERE [FirstName]='Bob' AND [LastName]='Jones' 

My question is:
Is there a way to dynamically create these WHERE clauses without me, to check for empty text fields and construct the WHERE clause manually?

My small application has only three parameters, so the rough way to get me through this will not be painful, but I wondered if there is an easier way to do this plus, maybe I will need to add additional parameters to the future. In addition, I can add wildcard search.

Answer 1

As you said, creating this query is not so difficult, since you are always ANDing fields in the where clause.

Know if you do this, do not format the string. Use SqlParameters to avoid SQL injection: http://en.wikipedia.org/wiki/SQL_injection

So, you can start with WHERE and for each text field that matters, add [(fieldName)] = @ (field name) and bind this sql parameter.

See: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx

If you want to use Linq for SQL or an entity framework, see this predicate builder: http://www.albahari.com/nutshell/predicatebuilder.aspx

Answer 2

If you use the SqlDataSource control, and your parameter values ​​come from page controls, you can provide ControlParameters and use the static where clause where short-circuited parameters are encoded. It could just be a ticket for quick code retrieval.

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureworksConnectionString %>" SelectCommand=" SELECT FirstName, LastName, Age FROM Contacts WHERE ( FirstName=@FirstName or @FirstName = '') AND (LastName = @LastName or @LastName = '' AND (Age = @Age or @Age = 0" > <SelectParameters> <asp:ControlParameter Name="FirstName" ControlID="TBFirstName" Type="String" /> <asp:ControlParameter Name="LastName" ControlID="TBLastName" Type="String" /> <asp:ControlParameter Name="Age" ControlID="TBAge" Type="Int16" /> </SelectParameters> </asp:SqlDataSource> 

Answer 3

You can set ConvertEmptyStringToNull="false" and do:

 SELECT * FROM MyTable WHERE [FirstName] = CASE @firstname WHEN '' THEN [FirstName] END AND [LastName] = CASE @lastname WHEN '' THEN [LastName] END 

Or you can set ConvertEmptyStringToNull = "true" and do:

 SELECT * FROM MyTable WHERE [FirstName] = ISNULL(@firstname, [FirstName]) AND [LastName] = ISNULL(@lastname,[LastName]) 

In any of them, if the user leaves the text field empty, the CASE or ISNULL forces each part of the where statement to compare itself, returning TRUE and creating the same records as this part of the sentence somewhere was not there at all. This is a good and simple solution that supports static queries and parameters and pushes logic to the SQL side.

However, it will have a (small) performance metric compared to just saying "SELECT * FROM MyTable" . These CASE and ISNULL operations are not free;) If this is a problem, Bryanmac's solution is perfectly acceptable.

Answer 4

You can use CancelSelectOnNullParameter=false , as shown in this example