Data Access Layer 2, SQL Query Structures

To Reorient from the conclusion of my last post, the current topic is:

1. Creating a DataTable in a Typed DataSet using the TableAdapter Configuration Wizard
2. Supplying a SQL Query string to build a useful DataAdapter class within the DataTable 
3. Working with the methods of the DataAdapter class to access and modify DB lists

I provided an example of a basic SQL Query resulting in a method, GetProducts(), that lists all the products in the database when called. To make it more useful, I need the ability to retrieve information based on specific properties. For this, we can add parameterized methods to the TableAdapter.

Creating parameterized methods for the Table Adapter

The first method created in the DAL is GetProducts(). Now I am going to add to that a GetProductsByCategoryID(categoryID) method. 

Right click the TableAdapter interface and select Add Query from the list. The SQL SELECT statement is similar to the last with one important difference:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued

FROM Products

WHERE CategoryID = @CategoryID

Color highligting added to emphasize the filtering string of the previous query. That is the only difference from the original method that gets the entire table.  The obvious question this begs is how to pass the scalar variable to the SQL query in an actual data event.  Two answers to this.

For quick gratification, you can supply a test parameter to a Preview Data test. Right click on the method in the TableAdapter to do this.

To actually use the TableAdapter method in a .net page, I'll provide a summary overview of the complete framework:

named instance object:

NorthwindTableAdapters.ProductsTableAdapter productAdapter = new NorthwindTableAdapters.ProductsTableAdapter();

dataTable object:

Northwind.ProductsDataTable products;

invoke method (returns a Typed DataSet) and assign to the instance object

products = productsAdapter.GetProductsByByCategoryID(1);

and return it row by row as html

foreach (Northwind.ProductsRow productsRow in products)

Response.Write("Product: " + productRow.ProductName + "
");

As regards an implementation in an .aspx and accompanying .cs, the deviations from a blank page are as follows:

the .aspx page

1. Create GridView .NET component and take note of assigned ID

the .cs page

1. on Page_Load, declare a new instance of ProductsTableAdapter as above
2. assign instance.GetProductsByCategoryID(value) to IDofGridView.DataSource
3. instance.DataBind();

That's it!  No data access-specific code required.

Next up, Inserting, Updating and Deleting Data