Adding Additional Queries to a Dataset

by Admin 6. November 2009 22:31

In our first post on 2-tier architecure Using Strongly Typed Datasets, we created a very simple dataset based on the Customers table in the Northwind database. We are now going to expand upon this simple dataset and add the abilty to return records based on parameters, such as CustomerID.

Opne the Customers.xsd Dataset we created in the previous post and right mouse click on the CustomersTableAdapter heading to open the popup menu. Select Add Query to launsh the TableAdapter Query Configuration Wizard.

  1. Choose Use SQL statements
  2. Choose the SELECT with returns rows option
  3. Type the following SQL statement and click next

SELECT CustomerID, CompanyName, Address, City, PostalCode
FROM Customers
WHERE CustomerID=@ID

Unselect the Fill a DataTable option and rename the Method GetCustomersByID, click next and finish.

 

Build the web site to cofirm it compiles and then open the CustomerList.aspx file we created in the first post. To utilise our new query that expects a CustomerID, we need to add some extra fields to the page. Add a textbox called txtCustomerID and a button called btnSearch.

Next we need to bind the GridView to the new datatable, so select Configure Data Source from the ObjectDataSource Tasks popup. click through until you get the the Define Data Methods page of the wizard. In the Choose a Method dropdown list you will see that we now have two methods available. Select the GetCustmersByID() method.

 

Next we need to define where the data for the parameter @ID will come from. In the Define Parameters screen there are a number of source, including QueryString and Session objects, as well as an option to select a control. We will be getting our information from the textbox called txtCustomerID, so go ahead and define the parameter as shown below. Although it's not manditory, it will be easier to assign a default customerID so that when the page loads the GridView will display one record. If we did not want to select a default value, we would have to handle binding via code, so that we did not attempt to bind the GridView to an empty datatable.

Run the page and check the results. You should see the GridView populated with the record for customer Alfreds Futterkiste.

 

If you enter a different CustomerID, for instance BOTTM and click the button, the page will pstback and display the record for Bottom-Dollar Markets.

Obviously this is a very limited search option, as we need to know the exact customerID to search for. With a little more tweaking, it would be easy to add wildcards to our query or to even add the ability to search on more than one field, so have fun.

Tags: ,

C# | ASP.NET | C#