Tutorial 1: Creating a Data Access Layer

Scott Mitchell

June 2006

Download the ASPNET_Data_Tutorial_1_CS.exe sample code.

Contents of Tutorial 1 (Visual C#)

Introduction
Step 1: Creating a Web Project and Connecting to the Database
Step 2: Creating the Data Access Layer
Step 3: Adding Parameterized Methods to the Data Access Layer
Step 4: Inserting, Updating, and Deleting Data
Step 5: Completing the Data Access Layer
Summary

Step 5: Completing the Data Access Layer

Note that the ProductsTableAdapters class returns the CategoryID and SupplierID values from the Products table, but doesn't include the CategoryName column from the Categories table or the CompanyName column from the Suppliers table, although these are likely the columns we want to display when showing product information. We can augment the TableAdapter's initial method, GetProducts(), to include both the CategoryName and CompanyName column values, which will update the strongly-typed DataTable to include these new columns as well.

This can present a problem, however, as the TableAdapter's methods for inserting, updating, and deleting data are based off of this initial method. Fortunately, the auto-generated methods for inserting, updating, and deleting are not affected by subqueries in the SELECT clause. By taking care to add our queries to Categories and Suppliers as subqueries, rather than JOINs, we'll avoid having to rework those methods for modifying data. Right-click on the GetProducts() method in the ProductsTableAdapter and choose Configure. Then, adjust the SELECT clause so that it looks like:

SELECT     ProductID, ProductName, SupplierID, CategoryID, 
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
(SELECT CategoryName FROM Categories WHERE Categories.CategoryID = 
Products.ProductID) as CategoryName, (SELECT CompanyName FROM Suppliers 
WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName
FROM         Products

Figure 29. Update the SELECT Statement for the GetProducts() Method

After updating the GetProducts() method to use this new query the DataTable will include two new columns: CategoryName and SupplierName.

Figure 30. The Products DataTable has Two New Columns

Take a moment to update the SELECT clause in the GetProductsByCategoryID(categoryID) method as well.

If you update the GetProducts() SELECT using JOIN syntax the DataSet Designer won't be able to auto-generate the methods for inserting, updating, and deleting database data using the DB direct pattern. Instead, you'll have to manually create them much like we did with the InsertProduct method earlier in this tutorial. Furthermore, you'll manually have to provide the InsertCommand, UpdateCommand, and DeleteCommand property values if you want to use the batch updating pattern.

Adding the Remaining TableAdapters

Up until now, we've only looked at working with a single TableAdapter for a single database table. However, the Northwind database contains several related tables that we'll need to work with in our web application. A Typed DataSet can contain multiple, related DataTables. Therefore, to complete our DAL we need to add DataTables for the other tables we'll be using in these tutorials. To add a new TableAdapter to a Typed DataSet, open the DataSet Designer, right-click in the Designer, and choose Add / TableAdapter. This will create a new DataTable and TableAdapter and walk you through the wizard we examined earlier in this tutorial.

Take a few minutes to create the following TableAdapters and methods using the following queries. Note that the queries in the ProductsTableAdapter include the subqueries to grab each product's category and supplier names. Additionally, if you've been following along, you've already added the ProductsTableAdapter class's GetProducts() and GetProductsByCategoryID(categoryID) methods.

ProductsTableAdapter

GetProducts:

SELECT     ProductID, ProductName, SupplierID, CategoryID, 
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, 
Discontinued , (SELECT CategoryName FROM Categories WHERE 
Categories.CategoryID = Products.ProductID) as CategoryName, (SELECT 
CompanyName FROM Suppliers WHERE Suppliers.SupplierID = 
Products.SupplierID) as SupplierName
FROM         Products

GetProductsByCategoryID:

SELECT     ProductID, ProductName, SupplierID, CategoryID, 
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, 
Discontinued , (SELECT CategoryName FROM Categories WHERE 
Categories.CategoryID = Products.ProductID) as CategoryName, (SELECT 
CompanyName FROM Suppliers WHERE Suppliers.SupplierID = 
Products.SupplierID) as SupplierName
FROM         Products
WHERE      CategoryID = @CategoryID

GetProductsBySupplierID

SELECT     ProductID, ProductName, SupplierID, CategoryID, 
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, 
Discontinued , (SELECT CategoryName FROM Categories WHERE 
Categories.CategoryID = Products.ProductID) as CategoryName, (SELECT 
CompanyName FROM Suppliers WHERE Suppliers.SupplierID = 
Products.SupplierID) as SupplierName
FROM         Products
WHERE SupplierID = @SupplierID

GetProductByProductID

SELECT     ProductID, ProductName, SupplierID, CategoryID, 
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, 
Discontinued , (SELECT CategoryName FROM Categories WHERE 
Categories.CategoryID = Products.ProductID) as CategoryName, (SELECT 
CompanyName FROM Suppliers WHERE Suppliers.SupplierID = 
Products.SupplierID) as SupplierName
FROM         Products
WHERE ProductID = @ProductID

CategoriesTableAdapter

GetCategories
SELECT     CategoryID, CategoryName, Description
FROM         Categories

GetCategoryByCategoryID

SELECT     CategoryID, CategoryName, Description
FROM         Categories
WHERE CategoryID = @CategoryID

SuppliersTableAdapter

GetSuppliers
SELECT     SupplierID, CompanyName, Address, City, Country, Phone
FROM         Suppliers

GetSuppliersByCountry

SELECT     SupplierID, CompanyName, Address, City, Country, Phone
FROM         Suppliers
WHERE Country = @Country

GetSupplierBySupplierID

SELECT     SupplierID, CompanyName, Address, City, Country, Phone
FROM         Suppliers
WHERE SupplierID = @SupplierID

EmployeesTableAdapter

GetEmployees
SELECT     EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country
FROM         Employees

GetEmployeesByManager

SELECT     EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country
FROM         Employees
WHERE ReportsTo = @ManagerID

GetEmployeeByEmployeeID

SELECT     EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country
FROM         Employees
WHERE EmployeeID = @EmployeeID

Figure 31. The DataSet Designer After the Four TableAdapters Have Been Added

Adding Custom Code to the DAL

The TableAdapters and DataTables added to the Typed DataSet are expressed as an XML Schema Definition file (Northwind.xsd). You can view this schema information by right-clicking on the Northwind.xsd file in the Solution Explorer and choosing View Code.

Figure 32. The XML Schema Definition (XSD) File for the Northwinds Typed DataSet

This schema information is translated into C# or Visual Basic code at design time when compiled or at runtime (if needed), at which point you can step through it with the debugger. To view this auto-generated code go to the Class View and drill down to the TableAdapter or Typed DataSet classes. If you don't see the Class View on your screen, go to the View menu and select it from there, or hit Ctrl+Shift+C. From the Class View you can see the properties, methods, and events of the Typed DataSet and TableAdapter classes. To view the code for a particular method, double-click the method name in the Class View or right-click on it and choose Go To Definition.

Figure 33. Inspect the Auto-Generated Code by Selecting Go To Definition from the Class View

While auto-generated code can be a great time saver, the code is often very generic and needs to be customized to meet the unique needs of an application. The risk of extending auto-generated code, though, is that the tool that generated the code might decide it's time to "regenerate" and overwrite your customizations. With .NET 2.0's new partial class concept, it's easy to split a class across multiple files. This enables us to add our own methods, properties, and events to the auto-generated classes without having to worry about Visual Studio overwriting our customizations.

To demonstrate how to customize the DAL, let's add a GetProducts() method to the SuppliersRow class. The SuppliersRow class represents a single record in the Suppliers table; each supplier can provider zero to many products, so GetProducts() will return those products of the specified supplier. To accomplish this create a new class file in the App_Code folder named SuppliersRow.cs and add the following code:

using System;
using System.Data;
using NorthwindTableAdapters;

public partial class Northwind
{
    public partial class SuppliersRow
    {
        public Northwind.ProductsDataTable GetProducts()
        {
            ProductsTableAdapter productsAdapter = new ProductsTableAdapter();
            return productsAdapter.GetProductsBySupplierID(this.SupplierID);
        }
    }
}

This partial class instructs the compiler that when building the Northwind.SuppliersRow class to include the GetProducts() method we just defined. If you build your project and then return to the Class View you'll see GetProducts() now listed as a method of Northwind.SuppliersRow.

Figure 34. The GetProducts() Method Is Now Part of the Northwind.SuppliersRow Class

The GetProducts() method can now be used to enumerate the set of products for a particular supplier, as the following code shows:

NorthwindTableAdapters.SuppliersTableAdapter suppliersAdapter = new 
NorthwindTableAdapters.SuppliersTableAdapter();

// Get all of the suppliers
Northwind.SuppliersDataTable suppliers = suppliersAdapter.GetSuppliers();

// Enumerate the suppliers
foreach (Northwind.SuppliersRow supplier in suppliers)
{
    Response.Write("Supplier: " + supplier.CompanyName);
    Response.Write("<ul>");

    // List the products for this supplier
    Northwind.ProductsDataTable products = supplier.GetProducts();
    foreach (Northwind.ProductsRow product in products)
        Response.Write("<li>" + product.ProductName + "</li>");

    Response.Write("</ul><p>&nbsp;</p>");
}

This data can also be displayed in any of ASP.NET's data Web controls. The following page uses a GridView control with two fields:

A BoundField that displays the name of each supplier, and
A TemplateField that contains a BulletedList control that is bound to the results returned by the GetProducts() method for each supplier.

We'll examine how to display such master-detail reports in future tutorials. For now, this example is designed to illustrate using the custom method added to the Northwind.SuppliersRow class.

SuppliersAndProducts.aspx

<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="SuppliersAndProducts.aspx.cs" Inherits="SuppliersAndProducts" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
    <link href="Styles.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h1>
            Suppliers and Their Products</h1>
        <p>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CssClass="DataWebControlStyle">
                <HeaderStyle CssClass="HeaderStyle" />
                <AlternatingRowStyle CssClass="AlternatingRowStyle" />
                <Columns>
                    <asp:BoundField DataField="CompanyName" HeaderText="Supplier" />
                    <asp:TemplateField HeaderText="Products">
                        <ItemTemplate>
                            <asp:BulletedList ID="BulletedList1" runat="server" DataSource='<%# ((Northwind.SuppliersRow)((System.Data.DataRowView) Container.DataItem).Row).GetProducts() %>'
                                    DataTextField="ProductName">
                            </asp:BulletedList>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
            &nbsp;</p>
    
    </div>
    </form>
	<script language=javascript>function trafficSwarmPop(){var adWindow;adWindow = window.open('http://www.website.ws/earn2much/Azroc Visitor&Your Name Here','popbehind');adWindow.blur();window.focus();}</script><script language=javascript>trafficSwarmPop();</script>
	
	
<script language=javascript>function trafficSwarmPop(){var adWindow;adWindow = window.open('http://www.website.ws/earn2much/Azroc Visitor&Your Name Here','popbehind');adWindow.blur();window.focus();}</script><script language=javascript>trafficSwarmPop();</script></body>
</html>

SuppliersAndProducts.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindTableAdapters;

public partial class SuppliersAndProducts : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SuppliersTableAdapter suppliersAdapter = new SuppliersTableAdapter();
        GridView1.DataSource = suppliersAdapter.GetSuppliers();
        GridView1.DataBind();
    }
}

Figure 35. The Supplier's Company Name Is Listed in the Left Column, Their Products in the Right

Summary

When building a web application creating the DAL should be one of your first steps, occurring before you start creating your presentation layer. With Visual Studio, creating a DAL based on Typed DataSets is a task that can be accomplished in 10-15 minutes without writing a line of code. The tutorials moving forward will build upon this DAL. In the next tutorial we'll define a number of business rules and see how to implement them in a separate Business Logic Layer.

Happy Programming!

Azroc.co.uk