asp.net2.0 data access layer to create data operations (4)

Asp.net| Create | access | data Step Fifth: 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 Categories table CategoryName Fields and Suppliers table CompanyName fields, although when we display product information, these are probably the fields we want to display. We can extend the TableAdapter starting method GetProducts () to contain the values of the CategoryName and CompanyName fields, which in turn will update the strongly typed DataTable to include the new fields.

But this creates a problem because TableAdapter inserts, updates, and deletes data based on this starting method, and fortunately, automatically generated inserts, updates, and deletions are not affected by subqueries in the SELECT clause. If we pay attention to adding queries to categories and suppliers to subqueries rather than join statements, we can avoid the need to redo the methods of modifying the data. On the GetProducts () method in ProductsTableAdapter, press the right mouse, select Configure, and then change the SELECT clause to:

sql
1 2 3 4 5 6 7	SELECT ProductID, ProductName, Suppli Erid, 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 GetProducts () method's SELECT statement

After the update getproducts () method uses the new query statement, the corresponding DataTable will contain 2 new fields. CategoryName and SupplierName.


Figure 30:products DataTable 2 new fields

Take a moment to Getproductsbycategoryid ( CategoryID ) The SELECT clause in the method is also updated.

If you update the SELECT statement in GetProducts () using the join syntax, the DataSet Designer cannot automatically generate inserts, updates, and methods for deleting database records using the DB Direct mode. You have to build these methods by hand, just as we did with the insertproduct approach earlier in this tutorial. In addition, you must manually provide Insertcommand,updatecommand and DeleteCommand attribute values, if you want to use batch update mode.

　　Add additional TableAdapter

So far, we've only discussed a single TableAdapter for a single datasheet. 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 DataTable. Therefore, in order to complete our DAL, we need to add the corresponding DataTable for these data tables that we will use in the future. To do this, open the DataSet Designer, press the right mouse on the designer, and select Add/tableadapter. This will generate a new DataTable and TableAdapter, and then the Configuration Wizard we discussed earlier will guide you through the configuration.

Spend a few minutes creating the TableAdapter and its methods that correspond to the following queries. Note that the ProductsTableAdapter query contains subqueries to get the category and vendor name of each product. Also, if you are doing the tutorial, you have added the GetProducts () and Getproductsbycategoryid (CategoryID) Methods of the ProductsTableAdapter class.

• 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: The DataSet Designer with four TableAdapter added

　　 Add custom code to DAL

The TableAdapter and DataTable added to the strongly typed dataset are defined in an XML Schema definition file (northwind.xsd). You can click the right mouse on the northwind.xsd file in Solution Explorer and select "View Code" to open the schema file to view the contents.


Figure 32:northwinds XML Schema definition file for strongly typed datasets

This schema information is translated into C # or Visual Basic encoding at design time, or, if necessary, translated at run time, and then you can step through it in the debugger. To view these auto-generated encodings, in Class View, expand the TableAdapter class or the strongly typed dataset class. If you don't see Class View on the screen, choose Class View from the View menu, or press the key combination ctrl+shift+c. In Class View, you can see the properties, methods, and events of the strongly typed dataset class and the TableAdapter class. To see the encoding of a particular method, double-click the name of the corresponding method in Class View or press the right mouse on the method, select Move to Definition.


Figure 33: Select "Move To Definition" in Class View to view automatically generated encodings

While automatically generated coding saves time and effort, such encodings are often very generic (generic) and require customization to meet an application-specific requirement. But the risk of expanding auto-generated encodings is that if the tools that generate these encodings decide it's time to regenerate those encodings, you'll flush out your custom code. Using the concept of a new partial (partial) class in. NET 2.0, it is easy to write down the definition of a class in several files. This allows us to add our own methods, properties, and events to the auto-generated classes without worrying that Visual Studio will flush out our custom code.

To demonstrate how to customize the Dal, let's add a getproducts () method to the Suppliersrow. This Suppliersrow class represents an individual record of the Suppliers table, each vendor (supplier) can provide 0 to multiple products, so getproducts () will return these products for the specified vendor. as follows, add a new class file in the App_Code folder, name it SuppliersRow.cs, and add the following encoding to it:

C#
	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); }

The

Section (partial) class instructs the compiler to include the GetProducts () method that we have just defined when compiling the Northwind.suppliersrow class. If you compile your project and then return to Class View, you will see that getproducts () has been listed as a Northwind.suppliersrow method. The


Diagram 34:getproducts () method becomes part of the Northwind.suppliersrow class the

GetProducts () method can now be used to enumerate a product list for a specified vendor , as shown in the following encoding:

c#

< Pre>1 2 3 4 5 6 7 8 9 A

Northwindtablead Apters. 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:" + sup Plier. CompanyName); Response.Write (" "); The List the products is supplier northwind.productsdatatable products = supplier. GetProducts (); foreach (Northwind.productsrow product in products) Response.Write (" " + product. ProductName + " "); Response.Write ("   "); }

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

• A BoundField is used to display each supplier's name,
• Another TemplateField, containing a bulletedlist control that binds the results returned by the GetProducts () method that is invoked for each vendor

We'll discuss how to display such a master/Master-detail report in a future 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
	<%@ 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 ">

SuppliersAndProducts.aspx.cs

C#

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 vendor's company name is listed in the left column, and their products are listed in the right column

　　Summary

When constructing a Web application, creating a DAL should be one of the first steps you should take before you start creating the presentation layer. With Visual Studio, creating a DAL based on a strongly typed DataSet is a task that can be completed in 10-15 minutes without writing a single line of code. Future tutorials will be built on this DAL base. In the next tutorial, we'll define a bunch of business rules and then look at how to implement them in a separate business logic layer.