 |
| Main ASP .NET Tutorial Index |
Scott Mitchell June 2006 Download the ASPNET_Data_Tutorial_1_CS.exe sample code. Contents of Tutorial 1 (Visual C#)Introduction Step 2: Creating the Data Access LayerWhen working with data one option is to embed the data-specific logic directly into the presentation layer (in a web application, the ASP.NET pages make up the presentation layer). This may take the form of writing ADO.NET code in the ASP.NET page's code portion or using the SqlDataSource control from the markup portion. In either case, this approach tightly couples the data access logic with the presentation layer. The recommended approach, however, is to separate the data access logic from the presentation layer. This separate layer is referred to as the Data Access Layer, DAL for short, and is typically implemented as a separate Class Library project. The benefits of this layered architecture are well documented (see the "Further Readings" section at the end of this tutorial for information on these advantages) and is the approach we will take in this series. All code that is specific to the underlying data source - such as creating a connection to the database, issuing
These methods, when invoked, will connect to the database, issue the appropriate query, and return the results. How we return these results is important. These methods could simply return a DataSet or DataReader populated by the database query, but ideally these results should be returned using strongly-typed objects. A strongly-typed object is one whose schema is rigidly defined at compile time, whereas the opposite, a loosely-typed object, is one whose schema is not known until runtime. For example, the DataReader and the DataSet (by default) are loosely-typed objects since their schema is defined by the columns returned by the database query used to populate them. To access a particular column from a loosely-typed DataTable we need to use syntax like: To return strongly-typed objects, developers can either create their own custom business objects or use Typed DataSets. A business object is implemented by the developer as a class whose properties typically reflect the columns of the underlying database table the business object represents. A Typed DataSet is a class generated for you by Visual Studio based on a database schema and whose members are strongly-typed according to this schema. The Typed DataSet itself consists of classes that extend the ADO.NET DataSet, DataTable, and DataRow classes. In addition to strongly-typed DataTables, Typed DataSets now also include TableAdapters, which are classes with methods for populating the DataSet's DataTables and propagating modifications within the DataTables back to the database. We'll use strongly-typed DataSets for these tutorials' architecture. Figure 3 illustrates the workflow between the different layers of an application that uses Typed DataSets.
Figure 3. All Data Access Code is Relegated to the DAL Creating a Typed DataSet and Table AdapterTo begin creating our DAL, we start by adding a Typed DataSet to our project. To accomplish this, right-click on the project node in the Solution Explorer and choose Add a New Item. Select the DataSet option from the list of templates and name it
Figure 4. Choose to Add a New DataSet to Your Project After clicking Add, when prompted to add the DataSet to the A Typed DataSet serves as a strongly-typed collection of data; it is composed of strongly-typed DataTable instances, each of which is in turn composed of strongly-typed DataRow instances. We will create a strongly-typed DataTable for each of the underlying database tables that we need to work with in this tutorials series. Let's start with creating a DataTable for the Keep in mind that strongly-typed DataTables do not include any information on how to access data from their underlying database table. In order to retrieve the data to populate the DataTable, we use a TableAdapter class, which functions as our Data Access Layer. For our The TableAdapter Configuration Wizard begins by prompting you to select which database to work with. The drop-down list shows those databases in the Server Explorer. If you did not add the Northwind database to the Server Explorer, you can click the New Connection button at this time to do so.
Figure 5. Choose the Northwind Database from the Drop-Down List After selecting the database and clicking Next, you'll be asked if you want to save the connection string in the
Figure 6. Save the Connection String to Next, we need to define the schema for the first strongly-typed DataTable and provide the first method for our TableAdapter to use when populating the strongly-typed DataSet. These two steps are accomplished simultaneously by creating a query that returns the columns from the table that we want reflected in our DataTable. At the end of the wizard we'll give a method name to this query. Once that's been accomplished, this method can be invoked from our presentation layer. The method will execute the defined query and populate a strongly-typed DataTable. To get started defining the SQL query we must first indicate how we want the TableAdapter to issue the query. We can use an ad-hoc SQL statement, create a new stored procedure, or use an existing stored procedure. For these tutorials we'll use ad-hoc SQL statements. Refer to Brian Noyes's article, Build a Data Access Layer with the Visual Studio 2005 DataSet Designer for an example of using stored procedures.
Figure 7. Query the Data Using an Ad-Hoc SQL Statement At this point we can type in the SQL query by hand. When creating the first method in the TableAdapter you typically want to have the query return those columns that need to be expressed in the corresponding DataTable. We can accomplish this by creating a query that returns all columns and all rows from the
Figure 8. Enter the SQL Query Into the Textbox Alternatively, use the Query Builder and graphically construct the query, as shown in Figure 9.
Figure 9. Create the Query Graphically, through the Query Editor After creating the query, but before moving onto the next screen, click the Advanced Options button. In Web Site Projects, "Generate Insert, Update, and Delete statements" is the only advanced option selected by default; if you run this wizard from a Class Library or a Windows Project the "Use optimistic concurrency" option will also be selected. Leave the "Use optimistic concurrency" option unchecked for now. We'll examine optimistic concurrency in future tutorials.
Figure 10. Select Only the "Generate Insert, Update, and Delete statements" Option After verifying the advanced options, click Next to proceed to the final screen. Here we are asked to select which methods to add to the TableAdapter. There are two patterns for populating data:
You can have the TableAdapter implement one or both of these patterns. You can also rename the methods provided here. Let's leave both checkboxes checked, even though we'll only be using the latter pattern throughout these tutorials. Also, let's rename the rather generic If checked, the final checkbox, "GenerateDBDirectMethods," creates
Figure 11. Change the Method Name from Complete the wizard by clicking Finish. After the wizard closes we are returned to the DataSet Designer, which shows the DataTable we just created. You can see the list of columns in the
Figure 12. The At this point we have a Typed DataSet with a single DataTable ( NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new
NorthwindTableAdapters.ProductsTableAdapter();
Northwind.ProductsDataTable products;
products = productsAdapter.GetProducts();
foreach (Northwind.ProductsRow productRow in products)
Response.Write("Product: " + productRow.ProductName + "<br />");
This code did not require us to write one bit of data access-specific code. We did not have to instantiate any ADO.NET classes, we didn't have to refer to any connection strings, SQL queries, or stored procedures. Instead, the TableAdapter provides the low-level data access code for us. Each object used in this example is also strongly-typed, allowing Visual Studio to provide IntelliSense and compile-time type checking. And best of all the DataTables returned by the TableAdapter can be bound to ASP.NET data Web controls, such as the GridView, DetailsView, DropDownList, CheckBoxList, and several others. The following example illustrates binding the DataTable returned by the AllProducts.aspx <%@ 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">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>View All Products in a GridView</title>
<link href="Styles.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>
All Products</h1>
<p>
<asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle">
<HeaderStyle CssClass="HeaderStyle" />
<AlternatingRowStyle CssClass="AlternatingRowStyle" />
</asp:GridView>
</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>
AllProducts.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 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. The List of Products is Displayed in a GridView While this example required that we write three lines of code in our ASP.NET page's
Go to top of page or next part of tutorial | ||||
