asp.net中数据链路层的创建（11）

添加其他的TableAdapter

到目前为止，我们只讨论了针对单个数据表的单个TableAdapter。但是，Northwind数据库里含有我们需要在 我们的web应用中使用的几个相关的表。一个强类型的DataSet可以包含多个相关的DataTable。因此，为了完 成我们的DAL，我们需要为这些我们将来要用到的数据表添加相应的DataTable。步骤如下，打开 DataSet设计 器，在设计器上按右鼠标，选择“添加/TableAdapter”。这会生成一个新的DataTable和TableAdapter，然后我 们早先讨论过的配置向导会指引你完成配置。

花上几分钟，创建对应于下列查询的TableAdapter及其方法。注意，ProductsTableAdapter的查询中包含了用以获取每个产品的分类和供应商名字的子查询。另外，如果你是随着教程在做的话，你已经添加过ProductsTableAdapter类 的GetProducts()和GetProductsByCategoryID(categoryID)方法了。

• 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
    

图31:添加了四个TableAdapter后的DataSet设计器