ASP. NET2.0 data operations-data access layer creation (4)

Step 5: complete the data access layer

Note that the ProductsTableAdapters class returns the values of CategoryID and SupplierID from the Products table, but does not include the CategoryName field of the Categories table and the CompanyName field of the Suppliers table, although when we display product information, these are probably the fields we want to display. We can expand the start method GetProducts () of TableAdapter to include the values of the CategoryName and CompanyName fields. This method will then update the strong-type able to include these new fields.

However, this may cause a problem because the TableAdapter insertion, update, and data deletion methods are based on this starting method. Fortunately, the automatically generated insert and update methods are used, the deletion method is not affected by the subquery in the select clause. If we add the queries for Categories and Suppliers into subqueries instead of using JOIN clauses, we can avoid redoing these data modification methods. Right-click the GetProducts () method in ProductsTableAdapter and SELECT "configuration". Then, change the SELECT clause:

SQL
1 2 3 4 5 6 7	SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) as CategoryName, (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName FROM Products

Figure 29: update the SELECT statement of the GetProducts () method

After the GetProducts () method is updated to use this new query statement, the corresponding DataTable will contain two new fields, CategoryName and SupplierName.

Figure 30: Products DataTable has two more fields

Take some time to put GetProductsByCategoryID (CategoryID) The SELECT clause in the method is also updated.

If you use the JOIN syntax to update the SELECT statement in GetProducts (), the DataSet Designer cannot use the DB direct mode to automatically generate insert, update, and delete database records. You must manually generate these methods, just as we did with the InsertProduct method earlier in this tutorial. In addition, you must manually provide the InsertCommand, UpdateCommand, and DeleteCommand attribute values, if you want to use the batch update mode.

　　Add other TableAdapter

So far, we have only discussed a single TableAdapter for a single data table. However, the Northwind database contains several related tables that we need to use in our web applications. A strongly-typed DataSet can contain multiple related datatables. Therefore, in order to complete our DAL, we need to add the corresponding able for the data tables we will use in the future. Follow these steps to open the DataSet Designer, right-click the designer, and select "Add/TableAdapter ". This will generate a new able and TableAdapter, And the Configuration Wizard we discussed earlier will guide you through the configuration.

It takes several minutes to create the TableAdapter corresponding to the following query and its method. Note that the query of ProductsTableAdapter contains a subquery used to obtain the category and supplier name of each product. In addition, if you are following the tutorial, you have added the GetProducts () and GetProductsByCategoryID (CategoryID) Method.

• 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 ployeeID, LastName, FirstName,
    Title, HireDate, ReportsTo, Country
    FROM Employees
    WHERE EmployeeID = @ EmployeeID
    

Figure 31: DataSet Designer after four tableadapters are added

　　Add custom encoding to DAL

TableAdapter and able added to a strongly typed DataSet are defined in an XML Schema definition file (Northwind. xsd. In Solution Explorer, you can right-click the Northwind. xsd file and select View Code to open the Schema file.

Figure 32: XML Schema definition file of the Northwinds strong-type DataSet

This schema information will be translated into C # Or Visual Basic Encoding after compilation at design, or translated at runtime if necessary, then you can traverse and execute in a single step in the debugger. To view the automatically generated codes, expand the TableAdapter class or strong DataSet class in the Class View. If you cannot see the Class View on the screen, select Class View From the View menu or press Ctrl + Shift + C. In the class view, you can see the properties, methods, and events of the strong DataSet class and TableAdapter class. To see the encoding of a specific method, double-click the name of the corresponding method in the Class View or right-click the method and select "Go To Definition )".

Figure 33: Select "Go To Definition" in the Class View To view the automatically generated Encoding

Although the automatically generated encoding saves time and effort, such encoding is often very generic and needs to be customized to meet the unique needs of an application. But the risk of automatically generated extended encoding is that if the tool that generates these encodings decides that it is time to re-generate these encodings, it will overwrite your custom encoding. With the concept of a new part (partial) class in. NET 2.0, it is easy to write the definition of a class in several files. This allows us to add our own methods, attributes, and events to automatically generated classes without worrying that Visual Studio will rush out our custom encoding.

To demonstrate how to customize the DAL, let's add a GetProducts () method to SuppliersRow. This SuppliersRow class represents the individual records of the Suppliers table. Each supplier (supplier) can provide 0 to multiple products, so GetProducts () will return the products of the specified supplier. For example, add a new class file in the App_Code folder, name it SuppliersRow. cs, and then add the following encoding:

C #
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17	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 part (partial) Class instructs the compiler to include the GetProducts () method we just defined when compiling the Northwind. SuppliersRow class. If you compile your project and return to the Class View, you will see that GetProducts () has been listed as a method of Northwind. SuppliersRow.

Figure 34: The GetProducts () method becomes a part of the Northwind. SuppliersRow class.

The GetProducts () method can now be used to enumerate a list of products from a specified supplier, as shown in the following code:

C #

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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＞ ＜/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: data can also be stored in any ASP.. NET Web control. The following webpage uses the GridView control with two fields:

• A BoundField is used to display the name of each supplier,
• Another TemplateField contains a BulletedList control to bind the results returned by the GetProducts () method called for each vendor

We will discuss how to display such a master/Slave report in a later tutorial. Here, the purpose of this example is to demonstrate how to use the custom method added to the Northwind. SuppliersRow class.

SuppliersAndProducts. aspx

ASP. NET

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

＜%@ 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＞  ＜/p＞ ＜/div＞ ＜/form＞ ＜/body＞ ＜/html＞

SuppliersAndProducts. aspx. cs

C #

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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: supplier company names are listed on the left and their products are listed on the right

　　Summary

When constructing a web application, creating the DAL should be one of the first steps you should do before you start to create the presentation layer. When Visual Studio is used, creating a DAL based on a strong DataSet is a task that can be completed within 10 to 15 minutes without writing a line of encoding. The subsequent tutorials will be based on this DAL. In the next tutorial, we will define a bunch of business rules and then see how to implement these rules in a separate business logic layer.