Here we explore simple yet powerful methods to retrieve and display one or more database records based on a value posted from user input. This is made possible by simply providing the input variable to the SQL query statement that obtains the data set.

dynamic_query_sql.aspx Run Sample | View Source

dynamic_query_oledb.aspx Run Sample | View Source

Basically, the logic in both applications is implemented as follows:

1. A Page_Load event handler runs a simple procedure that
   1. returns a list of all the product categories in the data source, and
   2. binds the result set to the HtmlSelect control named mySelect.

   NOTE: This procedure runs only when the page’s IsPostBack property returns false, which means only the first time the page loads.

   protected void Page_Load ( object src, EventArgs e ) {
      if ( !IsPostBack ) {
         string query = "SELECT DISTINCT Type FROM Products";
         mySelect.DataSource = fetchData ( query, "gear" );
         mySelect.DataBind ( );
      }
   }

   Note use of the DISTINCT keyword in the SQL statement, to display only unique values ( no duplicates ) from the given field.

   string query = "SELECT DISTINCT Type FROM Products";

2. When the user selects a category and posts the form, the submit handler getProducts receives control and runs a simple procedure that
   1. returns a list of all the products that belong to the given category, and
   2. binds the result set to the DataList control named myDataList.

   public void getProducts ( object src, EventArgs e ) {
      string query = "SELECT * FROM Products WHERE Type='" + mySelect.Value + "’";
      myDataList.DataSource = fetchData ( query, "gear" );
      myDataList.DataBind ( );
   }

Note that both the above event handlers call an external method fetchData that does the actual query and obtains the result set from the source. This method is used extensively in this workshop; it is a generic reusable method that returns a DataSet based on two arguments that are passed to it: the query statement, and the database with which to issue the query.

3. When the user selects a product and posts the form ( by clicking on the product link or on the product image ), the product details page receives control and in turn runs a simple procedure that
   1. returns the record of the selected item, and
   2. binds the result set to the DataList control named myData.

As mentioned, retrieving the correct result set is made possible by providing the input variable to the SQL query statement.

When used within the same page, the variable can be passed directly to the SQL Select statement, as shown in the examples when fetching the list of products that belong to a selected category.

string query = "SELECT ... FROM Products WHERE Type='" + mySelect.Value + "’";

Recall that mySelect is bound to the list of categories, and mySelect.Value returns the category selected by the user.

The selected value can also be obtained and passed to the query statement using the Form collection of the page’s Request object.

string query = "SELECT ... FROM Products WHERE Type='" + Request.Form [ "mySelect" ] + "’";

When the variable will be used in another page ( ex. the product details page ), a variable that references the identifier for the record can be appended to the target URL, and passed as a querystring argument to the requested page ( ex. details_gear.aspx?id=123 ).

Because here we are obtaining the record identifiers from a data source bound to a DataList control, the value passed to the id= parameter would depend on the value of the specified record identifier field ( in this case ProductId ) for each record in the list.

IMPORTANT: The field used as the record identifier must be set ( at the database configuration level ) to accept only unique values, to ensure that no two records ever share the same identifier.

The below snippet shows how this argument is added when constructing the hyperlink to the target page.

<a href='details_gear.aspx?id=<%# Eval ( "ProductId" ) %>’>

Then on the product details page, the variable ( containing the specified ProductID parameter ) can be obtained and passed to the Select statement using the QueryString collection of the page’s Request object.

string query = "SELECT * FROM Products WHERE ProductID='" + Request.QueryString [ "id" ] + "’";

IMPORTANT: If the result set bound to the DataList is expected to change with each request, there is no point in having to store state information in the control across requests. In this case, the control’s EnableViewState property must be set to false to improve overall performance.