ASP. NET 2.0 data Tutorial: completes the data access layer

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

 
 

  
  
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 update the SELECT clause in the GetProductsByCategoryID (categoryID) method.

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.

Complete data access layer: 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, 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) 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 EmployeeID, LastName, FirstName,  
  
  
Title, HireDate, ReportsTo, Country  
  
  
FROM Employees  
  
  
WHERE EmployeeID = @EmployeeID  
 
 

 

Figure 31: data access layer completed: DataSet Designer added after four tableadapters

1. How to deploy the asp.net mvc program in IIS6.0
2. Use Winform to build the asp.net mvc Framework
3. Programming idea of ASP. NET Session failure
4. ASP. NET Session state storage
5. Understand ASP. NET Web Application Models