Database table search using text box and dropdown list with ASP.net/C#

Question

I'm building a small website using ASP.net/C# and I wanted to know how to implement a simple search feature using a text box and a dropdown.

The datasource is a products table and in the text box I enter the product's name and in the dropdown are the categories. (I have managed to populate the dropdown with the available categories already)

It must follow these conditions:

• If both text box and dropdown are blank all products must be listed;

• If a category is chosen all products from that category must be listed;

• If only text is filled, all products that match are shown;

If possible, some code snippets would be appreciated.

Thanks.

Answer 1

I will assume that you use standard DataSets and that you are loading all data from Products table you should take a look at one of the following links:

ADO.NET: Filter Data
Filtering and Sorting in Datasets
Web Forms DataGrid and DataSet Programming

Answer 2

Are you familiar with SQL? if so, all you have to do is create a different query for each condition, or one query with parameters that will indicate what condition to add to the select statement, example:

• If both text box and dropdown are blank all products must be listed;

  SELECT * FROM Products

• If a category is chosen all products from that category must be listed;

  SELECT * FROM Products WHERE Category = @Category

• If only text is filled, all products that match are shown

  SELECT * FROM Products WHERE ProductName = @ProductName

This is the simplest way to do this, @Category and @ProductName are the parameters containing the values of the text box and the dropdown list. You still have to add code to decide when to execute each one of the queries and populate a controls with your results... assuming that the first item in the drop down list is not an actual category but maybe some text as "select category", here's an example of how the first condition could be taken care of:

If (string.IsNullOrEmpty(txtProductName.Text && ddlCategory.SelectedIndex == 0)
{
    //execute query#1
    GridViewSearchResults.DataSource = DataSet1; //This dataset is populated with data from the first query "select * from products"
    GridViewSearchResults.DataBind();
}

If the above does not make sense, I suggest you go ahead and do some more reading about this topic. The above example is not the ideal way of programming a production application, it is very simplistic and it is only intended to give you an idea on what to do.