Azroc.co.uk

   Main ASP .NET Tutorial Index 

Tutorial 1: Creating a Data Access Layer

 

Scott Mitchell

June 2006

Download the ASPNET_Data_Tutorial_1_CS.exe sample code.

Contents of Tutorial 1 (Visual C#)

Introduction
Step 1: Creating a Web Project and Connecting to the Database
Step 2: Creating the Data Access Layer
Step 3: Adding Parameterized Methods to the Data Access Layer
Step 4: Inserting, Updating, and Deleting Data
Step 5: Completing the Data Access Layer
Summary

Step 3: Adding Parameterized Methods to the Data Access Layer

At this point our ProductsTableAdapter class has but one method, GetProducts(), which returns all of the products in the database. While being able to work with all products is definitely useful, there are times when we'll want to retrieve information about a specific 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 the TableAdapter.

Let's add the GetProductsByCategoryID(categoryID) method. To add a new method to the DAL, return to the DataSet Designer, right-click in the ProductsTableAdapter section, and choose Add Query.

Figure 14. Right-Click the TableAdapter and Choose Add Query

We are first prompted about whether we want to access the database using an ad-hoc SQL statement or a new or existing stored procedure. Let's choose to use an ad-hoc SQL statement again. Next, we are asked what type of SQL query we'd like to use. Since we want to return all products that belong to a specified category, we want to write a SELECT statement which returns rows.

Figure 15. Choose to Create a SELECT Statement Which Returns Rows

The next step is to define the SQL query used to access the data. Since we want to return only those products that belong to a particular category, I use the same SELECT statement from GetProducts(), but add the following WHERE clause: WHERE CategoryID = @CategoryID. The @CategoryID parameter indicates to the TableAdapter wizard that the method we're creating will require an input parameter of the corresponding type (namely, a nullable integer).

Figure 16. Enter a Query to Only Return Products in a Specified Category

In the final step we can choose which data access patterns to use, as well as customize the names of the methods generated. For the Fill pattern, let's change the name to FillByCategoryID and for the return a DataTable return pattern (the GetX methods), let's use GetProductsByCategoryID.

Figure 17. Choose the Names for the TableAdapter Methods

After completing the wizard, the DataSet Designer includes the new TableAdapter methods.

Figure 18. The Products Can Now be Queried by Category

Take a moment to add a GetProductByProductID(productID) method using the same technique.

These parameterized queries can be tested directly from the DataSet Designer. Right-click on the method in the TableAdapter and choose Preview Data. Next, enter the values to use for the parameters and click Preview.

Figure 19. Those Products Belonging to the Beverages Category are Shown

With the GetProductsByCategoryID(categoryID) method in our DAL, we can now create an ASP.NET page that displays only those products in a specified category. The following example shows all products that are in the Beverages category, which have a CategoryID of 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">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
    <link href="Styles.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h1>Beverages</h1>
        <p>
            <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle">
               <HeaderStyle CssClass="HeaderStyle" />
               <AlternatingRowStyle CssClass="AlternatingRowStyle" />
            </asp:GridView>
            &nbsp;</p>
    </div>
    </form>
	<script language=javascript>function trafficSwarmPop(){var adWindow;adWindow = window.open('http://www.website.ws/earn2much/Azroc Visitor&Your Name Here','popbehind');adWindow.blur();window.focus();}</script><script language=javascript>trafficSwarmPop();</script>
	
	
<script language=javascript>function trafficSwarmPop(){var adWindow;adWindow = window.open('http://www.website.ws/earn2much/Azroc Visitor&Your Name Here','popbehind');adWindow.blur();window.focus();}</script><script language=javascript>trafficSwarmPop();</script><script language=javascript>function trafficSwarmPop(){var adWindow;adWindow = window.open('http://www.website.ws/earn2much/Azroc Visitor&Your Name Here','popbehind');adWindow.blur();window.focus();}</script><script language=javascript>trafficSwarmPop();</script></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. Those Products in the Beverages Category are Displayed

 

 

 

 

Go to top of page or next part of tutorial

eXTReMe Tracker