ASP.NET2.0数据操作之创建数据访问层

　　添加其他的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 ployeeID, LastName, FirstName,
    Title, HireDate, ReportsTo, Country
    FROM Employees
    WHERE EmployeeID = @EmployeeID
    

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