ASP. NET 2.0 data Tutorial: how to add parameterization to the data access layer

Step 3: add parameterization method to the data access layer

So far, ProductsTableAdapter has only one method, GetProducts (), which returns all products in the database. Operations on all products are useful, but we often want to obtain information about a specific product or all products under a specific category. To add such a feature to our data access layer, we can add a parameterized method to TableAdapter.

Add parameterization: let's add a GetProductsByCategoryID (categoryID) method. To Add a new method to the DAL, let's go back to the DataSet Designer, right-click the ProductsTableAdapter, and select "Add Query )".

 

Figure 14: Right-click TableAdapter and select "add query"

The Wizard will first ask if we want to use an ad-hoc SQL statement to generate a new stored procedure or use an existing stored procedure to access the database. Let's choose to use SQL statements. Then, the wizard will ask us what type of SQL query we use. Because we want to return all products belonging to the specified category, we need to write a SELECT statement that returns data rows.

 

Figure 15: SELECT to generate a SELECT statement that returns data rows

The next step to adding parameterization is to define the SQL query statements used to access data. Because we only want to return the products that belong to the specified category, I re-use the SELECT statement in GetProducts (), but added a WHERE sub-sentence: WHERE CategoryID = @ CategoryID. The @ CategoryID parameter indicates to the TableAdapter Configuration Wizard that the method we are generating will need an input parameter for the corresponding class (that is, it can be a null-nullable integer.

 

Figure 16: enter a query that only returns the product of the specified category

In the last step of adding parameterization, we can select the data access mode and customize the name of the generated method. Corresponding to the Fill mode, let's change the name to FillByCategoryID. For the method (GetX method) that returns the able mode, let's use the name GetProductsByCategoryID.

 

Figure 17: select a name for the TableAdapter Method

After the Wizard is completed, the DataSet Designer contains the new TableAdapter methods.

 

Figure 18: Query products by category

Take some time to add a GetProductByProductID (productID) method in the same way.

These parameterized queries can be directly tested in the DataSet Designer. Right-click the method in TableAdapter and select "Preview Data )". Then, enter the value of the corresponding parameter and press "Preview )".

 

Figure 19: list of products in the beverage (Beverages) Category

Through the GetProductsByCategoryID (categoryID) method in Our DAL, we can design an asp.net webpage to display products belonging to the specified category. The following example shows all products belonging to the Beverages (beverage) Class (CategoryID = 1.

Beverages. aspx

Asp.net

 
 

  
  
 < %@ 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">  
  
  
 
  
  
< html xmlns="http://www.w3.org/1999/xhtml" >  
  
  
< head runat="server">  
  
  
    < title>Untitled Pagetitle>  
  
  
    < link href="Styles.css"   
  
  
 
  
  
rel="stylesheet"   
  
  
 
  
  
type="text/css"   
  
  
 
  
  
/>  
  
  
< /head>  
  
  
< body>  
  
  
    < form id="form1" runat="server">  
  
  
    < div>  
  
  
        < h1>Beveragesh1>  
  
  
        < p>  
  
  
            < asp:GridView ID="GridView1" runat="server" 
  
  
             CssClass="DataWebControlStyle">  
  
  
               < HeaderStyle CssClass="HeaderStyle" />  
  
  
               < AlternatingRowStyle CssClass="AlternatingRowStyle" />  
  
  
            asp:GridView>  
  
  
             < /p>  
  
  
    < /div>  
  
  
    < /form>  
  
  
< /body>  
  
  
< /html>  
  
  
 
 
 

Beverages. aspx. cs

 
 

  
  
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)  
  
  
    {  
  
  
        ProductsTableAdapter productsAdapter = new 
  
  
         ProductsTableAdapter();  
  
  
        GridView1.DataSource =  
  
  
          productsAdapter.GetProductsByCategoryID(1);  
  
  
        GridView1.DataBind();  
  
  
    }  
  
  
}  
 
 

 

Figure 20: display of all products in the Beverages (beverage) Category

1. How to deploy the asp.net mvc program in IIS6.0
2. Use Winform to build the asp.net mvc Framework
3. Programming idea of ASP. NET Session failure
4. ASP. NET Session state storage
5. Understand ASP. NET Web Application Models