当前位置导航:炫浪网>>网络学院>>网页制作>>ASP.NET教程

完成数据访问层

 完成数据访问层

  注意,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 Categories.CategoryID = Products.CategoryID) as CategoryName,

  (SELECT CompanyName

  FROM Suppliers

  WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName

  FROM Products

  图29: 更新GetProducts()方法的SELECT语句

  在更新GetProducts()方法使用这个新查询语句之后,对应的DataTable将包含2个新字段,CategoryName和SupplierName。

 

  图30: Products DataTable多了2个新字段

  花点时间把GetProductsByCategoryID(categoryID)方法中的SELECT 子句也更新一下。

  如果你使用JOIN句法更新GetProducts()中的SELECT语句的话 ,DataSet设计器不能使用DB直接模式自动生成插入,更新,以及删除数据库记录的方法。你必须手工生成这 些方法,就象本教程早先时候我们对InsertProduct方法的做法一样。此外,你必须手工提供InsertCommand,UpdateCommand和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 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设计器

相关内容
赞助商链接