您的位置:知识库 » .NET技术

ASP.NET 2.0数据教程之一:创建一个数据访问层

作者: Scott Mitchell  来源: 博客堂  发布时间: 2008-09-26 17:42  阅读: 19662 次  推荐: 0   原文链接   [收藏]  

系列文章导航:

ASP.NET 2.0数据教程之一:创建一个数据访问层

ASP.NET 2.0数据教程之二:创建一个业务逻辑层

ASP.NET 2.0数据教程之三:母板页和站点导航

ASP.NET 2.0数据教程之四:使用ObjectDataSource展现数据

ASP.NET 2.0数据教程之五:声明参数

ASP.NET 2.0数据教程之六:编程设置ObjectDataSource的参数值

ASP.NET 2.0数据教程之七:使用DropDownList过滤的主/从报表

ASP.NET 2.0数据教程之八:使用两个DropDownList过滤的主/从报表

ASP.NET 2.0数据教程之九:跨页面的主/从报表

ASP.NET 2.0数据教程之十:使用 GridView 和DetailView实现的主/从报表

ASP.NET 2.0数据教程之十一:基于数据的自定义格式化

ASP.NET 2.0数据教程之十二:在GridView控件中使用TemplateField


添加其他的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设计器

0
0
标签:ASP.NET DAL Data

.NET技术热门文章

    .NET技术最新文章

      最新新闻

        热门新闻