Asp.net| Create | access | data
Step Fifth: Complete the data access layer
Note that the Productstableadapters class returns the values of CategoryID and SupplierID from the Products table, but does not include the Categories table CategoryName Fields and Suppliers table CompanyName fields, although when we display product information, these are probably the fields we want to display. We can extend the TableAdapter starting method GetProducts () to contain the values of the CategoryName and CompanyName fields, which in turn will update the strongly typed DataTable to include the new fields.
But this creates a problem because TableAdapter inserts, updates, and deletes data based on this starting method, and fortunately, automatically generated inserts, updates, and deletions are not affected by subqueries in the SELECT clause. If we pay attention to adding queries to categories and suppliers to subqueries rather than join statements, we can avoid the need to redo the methods of modifying the data. On the GetProducts () method in ProductsTableAdapter, press the right mouse, select Configure, and then change the SELECT clause to:
sql |
1 2 3 4 5 6 7 |
SELECT ProductID, ProductName, Suppli Erid, 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 |
Figure 29: Update the GetProducts () method's SELECT statement
After the update getproducts () method uses the new query statement, the corresponding DataTable will contain 2 new fields. CategoryName and SupplierName.
Figure 30:products DataTable 2 new fields
Take a moment to Getproductsbycategoryid ( CategoryID ) The SELECT clause in the method is also updated.
If you update the SELECT statement in GetProducts () using the join syntax, the DataSet Designer cannot automatically generate inserts, updates, and methods for deleting database records using the DB Direct mode. You have to build these methods by hand, just as we did with the insertproduct approach earlier in this tutorial. In addition, you must manually provide Insertcommand,updatecommand and DeleteCommand attribute values, if you want to use batch update mode.
Add additional TableAdapter
So far, we've only discussed a single TableAdapter for a single datasheet. However, the Northwind database contains several related tables that we need to use in our web applications. A strongly typed dataset can contain multiple related DataTable. Therefore, in order to complete our DAL, we need to add the corresponding DataTable for these data tables that we will use in the future. To do this, open the DataSet Designer, press the right mouse on the designer, and select Add/tableadapter. This will generate a new DataTable and TableAdapter, and then the Configuration Wizard we discussed earlier will guide you through the configuration.
Spend a few minutes creating the TableAdapter and its methods that correspond to the following queries. Note that the ProductsTableAdapter query contains subqueries to get the category and vendor name of each product. Also, if you are doing the tutorial, you have added the GetProducts () and Getproductsbycategoryid (CategoryID) Methods of the ProductsTableAdapter class.
- 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
Figure 31: The DataSet Designer with four TableAdapter added
Add custom code to DAL
The TableAdapter and DataTable added to the strongly typed dataset are defined in an XML Schema definition file (northwind.xsd). You can click the right mouse on the northwind.xsd file in Solution Explorer and select "View Code" to open the schema file to view the contents.
Figure 32:northwinds XML Schema definition file for strongly typed datasets
This schema information is translated into C # or Visual Basic encoding at design time, or, if necessary, translated at run time, and then you can step through it in the debugger. To view these auto-generated encodings, in Class View, expand the TableAdapter class or the strongly typed dataset class. If you don't see Class View on the screen, choose Class View from the View menu, or press the key combination ctrl+shift+c. In Class View, you can see the properties, methods, and events of the strongly typed dataset class and the TableAdapter class. To see the encoding of a particular method, double-click the name of the corresponding method in Class View or press the right mouse on the method, select Move to Definition.
Figure 33: Select "Move To Definition" in Class View to view automatically generated encodings
While automatically generated coding saves time and effort, such encodings are often very generic (generic) and require customization to meet an application-specific requirement. But the risk of expanding auto-generated encodings is that if the tools that generate these encodings decide it's time to regenerate those encodings, you'll flush out your custom code. Using the concept of a new partial (partial) class in. NET 2.0, it is easy to write down the definition of a class in several files. This allows us to add our own methods, properties, and events to the auto-generated classes without worrying that Visual Studio will flush out our custom code.
To demonstrate how to customize the Dal, let's add a getproducts () method to the Suppliersrow. This Suppliersrow class represents an individual record of the Suppliers table, each vendor (supplier) can provide 0 to multiple products, so getproducts () will return these products for the specified vendor. as follows, add a new class file in the App_Code folder, name it SuppliersRow.cs, and add the following encoding to it:
C# |
|
Using System; Using System.Data; Using Northwindtableadapters; public partial class Northwind {public partial class Suppliersrow { public Northwind.productsdatatable getproducts () { ProductsTableAdapter productsadapter = New ProductsTableAdapter (); Return Productsadapter.getproductsbysupplierid (this. SupplierID); } |
The
Section (partial) class instructs the compiler to include the GetProducts () method that we have just defined when compiling the Northwind.suppliersrow class. If you compile your project and then return to Class View, you will see that getproducts () has been listed as a Northwind.suppliersrow method. The
Diagram 34:getproducts () method becomes part of the Northwind.suppliersrow class the
GetProducts () method can now be used to enumerate a product list for a specified vendor , as shown in the following encoding:
c# |
< Pre>1 2 3 4 5 6 7 8 9 A |
Northwindtablead Apters. Supplierstableadapter suppliersadapter = new Northwindtableadapters.supplierstableadapter (); Get all of the suppliers northwind.suppliersdatatable suppliers = Suppliersadapter.getsuppliers (); Enumerate the Suppliers foreach (Northwind.suppliersrow supplier in suppliers) {Response.Write ("Supplier:" + sup Plier. CompanyName); Response.Write ("
"); The List the products is supplier northwind.productsdatatable products = supplier. GetProducts (); foreach (Northwind.productsrow product in products) Response.Write ("
- " + product. ProductName + "
"); Response.Write ("
"); } |
This data can also is displayed in any of the ASP. NET ' s data Web controls. The following page uses a GridView control with two fields: Data can also be displayed in any one of the ASP.net Web controls. The following page uses the GridView control with 2 fields:
- A BoundField is used to display each supplier's name,
- Another TemplateField, containing a bulletedlist control that binds the results returned by the GetProducts () method that is invoked for each vendor
We'll discuss how to display such a master/Master-detail report in a future tutorial. Here, the purpose of this example is to demonstrate how to use the custom method added to the Northwind.suppliersrow class.
Suppliersandproducts.aspx
asp.net |
|
<%@ Page language= "C #" autoeventwireup= true "codefile=" SuppliersAndProducts.aspx.cs "inherits=" Suppliersandproducts "%>"! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd "> |
SuppliersAndProducts.aspx.cs
C# |
|
Using System; Using System.Data; Using System.Configuration; Using System.Collections; Using System.Web; Using System.Web.Security; Using System.Web.UI; Using System.Web.UI.WebControls; Using System.Web.UI.WebControls.WebParts; Using System.Web.UI.HtmlControls; Using Northwindtableadapters; public partial class SuppliersAndProducts:System.Web.UI.Page { protected void Page_Load (object sender, EventArgs e) { Supplierstableadapter suppliersadapter = new supplierstableadapter (); Gridview1.datasource = Suppliersadapter.getsuppliers (); Gridview1.databind (); |
Figure 35: The vendor's company name is listed in the left column, and their products are listed in the right column
Summary
When constructing a Web application, creating a DAL should be one of the first steps you should take before you start creating the presentation layer. With Visual Studio, creating a DAL based on a strongly typed DataSet is a task that can be completed in 10-15 minutes without writing a single line of code. Future tutorials will be built on this DAL base. In the next tutorial, we'll define a bunch of business rules and then look at how to implement them in a separate business logic layer.