完成数据访问层
注意ProductsTableAdapters类从Products表中返回的是CategoryID和SupplierID的值但并不包括Categories表 的CategoryName字段和Suppliers表的CompanyName字段尽管当我们显示产品信息时这些很可能是我们想要显示的字段我们可以扩充TableAdapter的起始方法GetProducts()来包含CategoryName和CompanyName字段的值这方法进而会更新强类型的DataTable来包括这些新的字段
但这会造成一个问题因为TableAdapter的插入更新删除数据的方法是基于这个起始方法的幸运的是自动生成的插入更新删除方法并不会受SELECT子句中的子查询的影响如果我们注意把对Categories和Suppliers的查询添加成子查询而不是用JOIN语 句的话我们可以避免重做这些修改数据的方法在ProductsTableAdapter中的GetProducts()方法上按右鼠标选择配置然后把SELECT子句改成
SQL
SELECT ProductID ProductName SupplierID CategoryID
QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
(SELECT CategoryName
FROM Categories
WHERE CategoriesCategoryID = ProductsCategoryID) as CategoryName
(SELECT CompanyName
FROM Suppliers
WHERE SuppliersSupplierID = ProductsSupplierID) as SupplierName
FROM Products
)thisstylewidth=; border=>
图: 更新GetProducts()方法的SELECT语句
在更新GetProducts()方法使用这个新查询语句之后对应的DataTable将包含个新字段CategoryName和SupplierName
image onmousewheel=javascript:return big(this) height= alt= hspace= src=http://imgeducitycn/img_///jpg width= onload=javascript:if(thiswidth>)thisstylewidth=; border=>
图: Products DataTable多了个新字段
花点时间把GetProductsByCategoryID(categoryID)方法中的SELECT 子句也更新一下
如果你使用JOIN句法更新GetProducts()中的SELECT语句的话 DataSet设计器不能使用DB直接模式自动生成插入更新以及删除数据库记录的方法你必须手工生成这 些方法就象本教程早先时候我们对InsertProduct方法的做法一样此外你必须手工提供InsertCommandUpdateCommand和DeleteCommand属性值假如你想使用批更新模式的话
完成数据访问层添加其他的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 CategoriesCategoryID = ProductsProductID) as CategoryName
(SELECT CompanyName
FROM Suppliers
WHERE SuppliersSupplierID = ProductsSupplierID)
as SupplierName
FROM Products GetProductsByCategoryID:
SELECT ProductID ProductName SupplierID CategoryID
QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder
ReorderLevel Discontinued
(SELECT CategoryName
FROM Categories
WHERE CategoriesCategoryID = ProductsProductID) as CategoryName
(SELECT CompanyName
FROM Suppliers
WHERE SuppliersSupplierID = ProductsSupplierID) as SupplierName
FROM Products
WHERE CategoryID = @CategoryID GetProductsBySupplierID
SELECT ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice
UnitsInStock UnitsOnOrder ReorderLevel Discontinued
(SELECT CategoryName
FROM Categories
WHERE CategoriesCategoryID = ProductsProductID) as CategoryName
(SELECT CompanyName FROM Suppliers
WHERE SuppliersSupplierID = ProductsSupplierID) as SupplierName
FROM Products
WHERE SupplierID = @SupplierID GetProductByProductID
SELECT ProductID ProductName SupplierID CategoryID QuantityPerUnit
UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
(SELECT CategoryName
FROM Categories
WHERE CategoriesCategoryID = ProductsProductID) as CategoryName
(SELECT CompanyName
FROM Suppliers
WHERE SuppliersSupplierID = ProductsSupplierID) 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
image onmousewheel=javascript:return big(this) height= alt= hspace= src=http://imgeducitycn/img_///jpg width= onload=javascript:if(thiswidth>)thisstylewidth=; border=>
图:完成数据访问层添加了四个TableAdapter后的DataSet设计器
