asp.net2.0 data access layer to create data operations (2)

Asp.net| Create | access | data

This last check box, "Generate DB Direct Method (GenerateDBDirectMethods)", if selected, automatically generates inserts (), Update (), and Delete () methods for TableAdapter. If you do not choose this option, all updates need to be implemented through the TableAdapter unique update () method that accepts a strongly typed DataSet, or a DataTable, or a single DataRow, or a DataRow array. (This check box does not work if you remove the "Generate Add, update, and DELETE statements" option in the advanced properties shown in Figure 9). Let's reserve the option for this check box.


Figure 11: Change the method name from GetData to GetProducts

Press the Finish button to end the wizard. After the wizard closes, we go back to the DataSet Designer, which shows the DataTable we just created. You can see the field lists (ProductID, ProductName, etc.) of the products DataTable, as well as the ProductsTableAdapter fill () and getproducts () methods.


Figure 12:products DataTable and ProductsTableAdapter are added to the strongly typed dataset

At this point, we have generated a strongly typed DataSet containing a single DataTable class (Northwind.products) and a strongly typed DataAdapter class containing the GetProducts () method ( Northwindtableadapters.productstableadapter). These objects allow you to obtain a list of all products using the following encoding:

c#
< Pre>1 2 3 4 5 6 7	Northwindtableadapters.productstableadapter Produ Ctsadapter = new Northwindtableadapters.productstableadapter (); Northwind.productsdatatable Products; Products = Productsadapter.getproducts (); foreach (Northwind.productsrow productrow in products) Response.Write ("Product:" + productrow.productname + " " );

This code does not require us to write a line of code related to data access. We do not need to generate any instances of the Ado.net class, we do not need to indicate any connection strings, any SQL query statements, or any stored procedures. TableAdapter provides us with the underlying data access code!

Each object in this example is strongly typed and allows Visual Studio to provide IntelliSense help and compile-time type checking. Best of all, the DataTable returned from TableAdapter can be directly bound to the ASP.net data Web control, including Gridview,detailsview,dropdownlist,checkboxlist, and several other controls. The following example demonstrates the ability to bind a DataTable returned from the GetProducts () method to a GridView by simply adding a short three-line encoding to the Page_Load event handler.

Allproducts.aspx

asp.net
	<%@ Page language= "C #" autoeventwireup= "true" codefile= "AllProducts.aspx.cs" inherits= "allproducts"%> " ! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "http://www.w3.org/TR/xhtml1/DTD/ Xhtml1-transitional.dtd "> <title> View All Products in a GridView All products /div>

AllProducts.aspx.cs

c#

< Pre>1 2 3 4 5 6 7 8 (9)

using S ystem; 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 AllProducts:System.Web.UI.Page {protected void Page_Load (object sender, EventArgs e) { ProductsTableAdapter productsadapter = new ProductsTableAdapter (); Gridview1.datasource = Productsadapter.getproducts (); Gridview1.databind (); } }

Figure 13: Product List displayed in the GridView

This example requires us to write three lines of code in the Page_Load event handler of the ASP.net Web page. In future tutorials, we'll talk about using ObjectDataSource to get data from the DAL in a declarative way. With ObjectDataSource words, we do not have to write a line of code, but also to get pagination and sorting support it!

　　 Step Three: Add a parameterized method to the data access layer

At this point, ProductsTableAdapter has only one method, GetProducts (), which returns all the products in the database. It's certainly useful to be able to operate all the products, but often we want to get information about a given product, or all products that belong to a particular category. To add such functionality to our data access layer, we can add parameterized methods to TableAdapter.

Let's add a Getproductsbycategoryid (CategoryIDMethod To add a new method to the DAL, let's go back to the DataSet Designer, press the right mouse on the ProductsTableAdapter, and select Add Query.


Figure 14: Press the right mouse on the TableAdapter and select "Add Query"

The wizard will first ask if we want to access the database through a AD-HOC SQL statement, or by building a new stored procedure, or by using an existing stored procedure. Let's still choose to use the SQL statement. The wizard then asks us what type of SQL query to use. Because we want to return all the products that belong to the specified category, we need to write a SELECT statement that returns rows of data.


Figure 15: Selecting a SELECT statement to generate a return data row

The next step is to define the SQL query statement that is used to access the data. Because we only want to return those products that belong to the specified category, I reuse the SELECT statement in GetProducts (), but add a WHERE clause: where CategoryID = @CategoryID. The @categoryid parameter to the TableAdapter Configuration Wizard indicates that the method we are generating will require an input parameter for the corresponding class (that is, an integer that can be null-nullable).


Figure 16: Enter a query that returns only the products of the specified category

In the final step, we can choose which data access mode to use, and we can customize the name of the generated method. corresponding to fill mode, let's change the name to Fillbycategoryid and the method to return the DataTable pattern (getXmethod), let's use the name Getproductsbycategoryid.


Figure 17: Selecting a name for the TableAdapter method

After the wizard finishes, the DataSet Designer includes these new TableAdapter methods.


Figure 18: Querying Products by Category

Take the time to add a getproductbyproductid with the same technique (ProductIDMethod

These parameterized queries can be tested directly in the DataSet Designer. Press the right mouse on the method in TableAdapter and select Preview Data (Preview). Next, enter the value of the corresponding parameter and press preview (Preview).


Figure 19: List of products belonging to the beverage (beverages) Category

Through the Getproductsbycategoryid in our Dal (CategoryIDmethod, we can design a asp.net web page to display those products that belong to the specified category. The following example shows all products belonging to the beverages (beverage) class (categoryid=1).

beverages.aspx

 <%@ Page language= "C #" autoeventwireup= "true" codefile= "Beverages.aspx.cs" inherits= "Beverages"%>! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">    untitled Page  
       
     
    
 

  
      Beverages  
     
  
 
     
  
      
 
    
 
    

" TD style= "BORDER:0;PADDING:0;" >

asp.net

1 2 3 4 5 6 7 8 9" (+)

Beverages.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 Beverages:System.Web.UI.Page { protected void Page_Load (object sender, EventArgs e) c6/>{ ProductsTableAdapter productsadapter = new productstableadapter (); Gridview1.datasource = Productsadapter.getproductsbycategoryid (1); Gridview1.databind ();

Figure 20: Display of all products belonging to the beverages (beverage) class