Dynamic WHERE clauses in a SqlDataSource

Question

I'm using a SqlDataSource in a very simple application. I'm allowing the user to set several search parameters for the SDS's select command via TextBoxes, one TextBox per parameter (think txtFirstName, txtLastName, etc). I'm planning on using a button click event handler to set the SqlDataSource's SelectCommand property which by default will return all records (for my purposes here). I want to refine this select command to possibly add one or more WHERE clauses depending on if the user enters search criteria in any of my TextBoxes.

Example in case I'm not being clear:

By default, my SqlDataSource's SelectCommand property will be something like this:

SELECT * FROM MyTable

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

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

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

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

My question:
Is there a way to dynamically create these WHERE clauses without me having to test for empty TextBoxes and constructing a WHERE clause by hand?

My little application only has three parameters so brute forcing my way through this wouldn't be painful, but I've wondered if there was an easier way to do this plus it's possible I'll need to add more parameters in the future. Plus I may want to add wildcard searching.

Answer 1

If you are using a SqlDataSource control, and your parameter values are coming from page controls, you can supply ControlParameters and use a static where clause with short-circuited parameters. This might be just the ticket for quickly banging out some code.

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