建站
咨詢
售后
建站咨詢
新聞中心
第五步：完成數(shù)據(jù)訪問層
創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),若羌企業(yè)網(wǎng)站建設(shè),若羌品牌網(wǎng)站建設(shè),網(wǎng)站定制,若羌網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,若羌網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
注意，ProductsTableAdapters類從Products表中返回的是CategoryID和SupplierID的值，但并不包括Categories表 的CategoryName字段和Suppliers表的CompanyName字段，盡管當(dāng)我們顯示產(chǎn)品信息時(shí)，這些很可能是我們想要顯示的字段。我們可以擴(kuò)充TableAdapter的起始方法GetProducts()來包含CategoryName和CompanyName字段的值，這方法進(jìn)而會(huì)更新強(qiáng)類型的DataTable來包括這些新的字段。
但這會(huì)造成一個(gè)問題，因?yàn)門ableAdapter的插入，更新，刪除數(shù)據(jù)的方法是基于這個(gè)起始方法的，幸運(yùn)的是，自動(dòng)生成的插入，更新，刪除方法并不會(huì)受SELECT子句中的子查詢的影響。如果我們注意把對(duì)Categories和Suppliers的查詢添加成子查詢，而不是用JOIN語 句的話，我們可以避免重做這些修改數(shù)據(jù)的方法。在ProductsTableAdapter中的GetProducts()方法上按右鼠標(biāo)，選擇“配置”，然后，把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()方法使用這個(gè)新查詢語句之后，對(duì)應(yīng)的DataTable將包含2個(gè)新字段，CategoryName和SupplierName。
圖30: Products DataTable多了2個(gè)新字段
花點(diǎn)時(shí)間把GetProductsByCategoryID(categoryID)方法中的SELECT 子句也更新一下。
如果你使用JOIN句法更新GetProducts()中的SELECT語句的話 ，DataSet設(shè)計(jì)器不能使用DB直接模式自動(dòng)生成插入，更新，以及刪除數(shù)據(jù)庫記錄的方法。你必須手工生成這 些方法，就象本教程早先時(shí)候我們對(duì)InsertProduct方法的做法一樣。此外，你必須手工提供InsertCommand，UpdateCommand和DeleteCommand屬性值，假如你想使用批更新模式的話。
完成數(shù)據(jù)訪問層：添加其他的TableAdapter
到目前為止，我們只討論了針對(duì)單個(gè)數(shù)據(jù)表的單個(gè)TableAdapter。但是，Northwind數(shù)據(jù)庫里含有我們需要在我們的web應(yīng)用中使用的幾個(gè)相關(guān)的表。一個(gè)強(qiáng)類型的DataSet可以包含多個(gè)相關(guān)的DataTable。因此，為了完成我們的DAL，我們需要為這些我們將來要用到的數(shù)據(jù)表添加相應(yīng)的DataTable。步驟如下，打開 DataSet設(shè)計(jì) 器，在設(shè)計(jì)器上按右鼠標(biāo)，選擇“添加/TableAdapter”。這會(huì)生成一個(gè)新的DataTable和TableAdapter，然后我們?cè)缦扔懻撨^的配置向?qū)?huì)指引你完成配置。
花上幾分鐘，創(chuàng)建對(duì)應(yīng)于下列查詢的TableAdapter及其方法。注意，ProductsTableAdapter的查詢中包含了用以獲取每個(gè)產(chǎn)品的分類和供應(yīng)商名字的子查詢。另外，如果你是隨著教程在做的話，你已經(jīng)添加過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:完成數(shù)據(jù)訪問層：添加了四個(gè)TableAdapter后的DataSet設(shè)計(jì)器
【編輯推薦】
- 如何在IIS6.0中部署asp.net mvc程序
- 用Winform傻瓜式搭建asp.net mvc框架
- ASP.NET Session失效的編程思路
- ASP.NET Session 狀態(tài)的存儲(chǔ)
- 了解ASP.NET Web應(yīng)用程序模型
網(wǎng)站欄目：ASP.NET 2.0數(shù)據(jù)教程:完成數(shù)據(jù)訪問層
轉(zhuǎn)載注明：http://fisionsoft.com.cn/article/cdjiedi.html
