ASP. NET 2.0 data Tutorial: create a data access layer

Create a data access layer

When dealing with data, one way is to put the data-related logic directly in the presentation layer (in a web application, the asp.net web Page forms a presentation layer ). The format is generally to write ADO. NET encoding in the encoding part of the asp.net Web page or use the SqlDataSource control in the identifier part. In both forms, the data access logic is closely coupled with the presentation layer. However, we recommend that you separate the data access logic from the presentation layer. This separate layer is called the data access layer, abbreviated as DAL, which is generally implemented through a separate class library project. The benefits of this layered framework are described in many documents (see the resources in the "additional books" at the end of this tutorial). We will use this method in this series.

All the encodings related to the underlying data source, such as the database connection, the SELECT, INSERT, UPDATE, and DELETE commands, should be placed in the DAL. The presentation layer should not contain any reference to the data access encoding, but should call the encoding in the DAL for all data access requests. The data access layer contains methods for accessing the underlying database data. For example, the Northwind database contains two tables, Products and Categories, which record the Products available for sale and the classification of these Products. In our DAL, we will have the following method:

GetCategories (), returns information of all categories

GetProducts () returns information about all products.

GetProductsByCategoryID (categoryID) returns the information of all products belonging to the specified category

GetProductByProductID (productID), returns the information of the specified product

After these methods are called, they connect to the database, issue appropriate queries, and return results. It is important to return these results. These methods can directly return the DataSet or DataReader filled by the database query, but the ideal way is to return these results in the form of a strong object. The schema of a strongly typed object is strictly defined during compilation. In contrast, the schema of a weakly typed object is unknown before it is run.

For example, DataReader and common DataSet are weak objects, because their schema is defined by filling fields returned by their database queries. To access a specific field in a weak type able, we need to use the Syntax: DataTable. Rows [index] ["columnName"]. The weak type of DataTable in this example is that we need to access the field name through a string or serial number index. On the other hand, all the fields of a strong-type able are implemented in the form of attributes, and the access encoding will be like this: DataTable. rows [index]. columnName.

To return a strongly typed object, a developer can create a custom business object or use a strongly Typed DataSet. The attributes of a developer-implemented business object class are usually ing the fields of the corresponding underlying data table. A strongly typed DataSet is a class generated by Visual Studio Based on the Data Warehouse schema. The type of its members is determined by this schema. A strongly typed DataSet itself is composed of DataSet, able, and child classes of the DataRow class inherited from ADO. NET. In addition to the strongly typed able, the strongly Typed DataSet now also includes the TableAdapter class, which contains various methods for filling the DataTable in the DataSet and returning the DataTable changes to the database.

Note: For more information about the advantages and disadvantages of using a strong DataSet than business objects, see designing data layer components and transmitting data between layers..

In the architecture of these tutorials, we will use a strongly Typed DataSet. Figure 3 demonstrates the workflow between different layers of an application using a strong DataSet ).

 

Figure 3: Delegate all data access codes to the DAL

Create a data access layer: create a strong DataSet and Table Adapter

We started to create our DAL and added a strong DataSet to our project. The procedure is as follows: Right-click the project node in solution manager and select "Add a New Item )". Select DataSet in the template column and name it Northwind. xsd.

 

Figure 4: Add a new DataSet to your project

After you click "Add", Visual Studio will ask whether to Add DataSet to the App_Code folder and select "Yes ". Visual Studio then displays the strong DataSet Designer and starts the TableAdapter Configuration Wizard, allowing you to add the first TableAdapter to your strong DataSet.

A strongly-Typed DataSet acts as a set of Strongly-typed objects. It consists of a strongly-typed able instance. Each strongly-typed DataTable consists of a strongly-typed DataRow instance. We will create a corresponding DataTable for each data table used in this tutorial series. Let's start by creating a able for the Products table first.

Remember, a strongly typed able does not include any information about how to access the corresponding underlying data table. To obtain the data used to fill the DataTable, we use the TableAdapter class, which provides the data access layer function. For our Products DataTable, the corresponding TableAdapter class will include methods such as GetProducts () and GetProductByCategoryID (categoryID), and we will call these methods at the presentation layer. DataTable is used to transmit data between layers.

The TableAdapter Configuration Wizard requires you to select which database to use. The drop-down list lists the databases in the server resource manager. If you have not added the Northwind database to the server resource manager in advance, you can click the new connection button to add it.

 

Figure 5: select the Northwind database from the drop-down list

After selecting the database, click "Next". The Wizard will ask if you want to store the connection string in the Web. config file. Store the connection string on the Web. in the config file, you can avoid writing the connection string hard in the TableAdapter class encoding. If you change the connection string information in the future, this will greatly simplify the encoding modification. If you choose to store the connection string in the configuration file, the connection string will be placed in the section, which can be encrypted to improve security, you can also use the new asp.net 2.0 attribute page in the IIS graphical interface management tool to modify the attributes. Of course, this tool is more suitable for administrators.

 

Figure 6: store the connection string in Web. config

In the next step of creating the data access layer, we need to define the schema of the first strong-type DataTable and provide the first method for the TableAdapter class used to fill the strong-type DataSet. In these two steps, you can create a query to return fields of the data table corresponding to the DataTable. At the end of the wizard, We will name the method corresponding to this query. After completion, this method can be called at the presentation layer. It will execute the configured query and then fill in a strongly typed able.

Before defining an SQL query, we must first select the method in which we want TableAdapter to execute the query. We can directly use an ad-hoc SQL statement, create a new stored procedure, or use an existing stored procedure. In these tutorials, we will use the SQL statement of ad-hoc. See the example of using stored procedures in Brian Noyes's article "using Visual Studio 2005 DataSet Designer to create a data access layer.

 

Figure 7: query data using SQL statements

At this point, you can manually enter an SQL query. When you generate the first method of TableAdapter, you generally want your query to return the fields that need to be stored in the corresponding able. We can create a query that returns all fields from the Products table and all data rows to achieve our goal:

 

Figure 8: Enter SQL query in the text box

Alternatively, we can use the Query Builder to construct a Query on a GUI, as shown in figure 9.

 

Figure 9: generate a query using the query Editor

After the query is generated, click "Advanced Options" before moving to the next screen. By default, "generate insert, update, and delete statements" is the only option selected for a website project. If you run this wizard in a class library or Windows project, the optimistic concurrency option will also be selected. Do not select "Use optimized concurrency control. In future tutorials, we will discuss in detail the optimized concurrency control.

 

Figure 10: Select "generate insert, update, and delete statements ".

After verifying the advanced options, click "Next" to go to the last screen. Here, the Configuration Wizard will ask you how to add the TableAdapter. There are two modes for data filling:

Fill DataTable-this method will generate a method that accepts a DataTable parameter and fills the DataTable Based on the query results. For example, the DataAdapter class of ADO. NET implements this mode in its Fill () method.

Return DataTable-This method generates a method that creates and fills in a DataTable and uses it as the return value of the method.

You can enable TableAdapter to implement one or both modes. You can also rename the methods provided here. Let's not change the options of the two check boxes, although we only need to use the following mode in these tutorials. At the same time, let's change the general GetData method name to GetProducts.

The last check box, "GenerateDBDirectMethods (GenerateDBDirectMethods)", will automatically generate the Insert (), Update (), and Delete () methods for TableAdapter. If you do not select this option, all updates must be implemented through the unique Update () method of TableAdapter. This method accepts a strongly typed DataSet, A able, or a single DataRow, or a DataRow array. (If you remove the "generate, update, and delete statements" option in the advanced attributes shown in Figure 9, this check box does not work ). Let's keep the option of this check box.

 

Figure 11: change the method name from GetData to GetProducts

Click "finish" to end the wizard. After the Wizard is closed, we return to the DataSet Designer, which will display the newly created able. You can see the field columns (ProductID, ProductName, etc.) of the Products DataTable, as well as the Fill () and GetProducts () Methods of the ProductsTableAdapter.

 

Figure 12: Products DataTable and ProductsTableAdapter are added to a strongly Typed DataSet.

So far, we have generated a strongly-Typed DataSet containing a single able class (Northwind. Products) and a strongly-class DataAdapter class (NorthwindTableAdapters. ProductsTableAdapter) containing the GetProducts () method ). The following column encoding can be used to obtain the list of all products:

 
 

  
  
NorthwindTableAdapters.ProductsTableAdapter   
  
  
 
  
  
productsAdapter = new   
  
  
 
  
  
NorthwindTableAdapters.ProductsTableAdapter();  
  
  
Northwind.ProductsDataTable products;  
  
  
 
  
  
products = productsAdapter.GetProducts();  
  
  
 
  
  
foreach (Northwind.ProductsRow productRow in products)  
  
  
    Response.Write("Product: " +   
  
  
 
  
  
productRow.ProductName + "");  
  
  
 
 
 

This encoding does not require us to write a row of data-related encoding. We do not need to generate any ADO. NET class instances. We do not need to specify any connection strings, any SQL query statements, or any stored procedures. TableAdapter provides the underlying data access code!

In this example, each object is strongly typed, allowing Visual Studio to provide intelliisense help and compile-time type check. The best thing is that the DataTable returned from TableAdapter can be directly bound to the asp.net Data Web control. Such controls include the GridView, DetailsView, DropDownList, CheckBoxList, and several other controls. The following example demonstrates that you can bind the DataTable returned from the GetProducts () method to a GridView by adding a short three-line encoding in the Page_Load event handler function.

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">  
  
  
 
  
  
< html xmlns="http://www.w3.org/1999/xhtml" >  
  
  
< head runat="server">  
  
  
    < title>View All Products in a GridViewtitle>  
  
  
    < link href="Styles.css"   
  
  
 
  
  
rel="stylesheet"   
  
  
 
  
  
type="text/css"   
  
  
 
  
  
/>  
  
  
head>  
  
  
< body>  
  
  
    < form id="form1" runat="server">  
  
  
    < div>  
  
  
        < h1>  
  
  
            All Productsh1>  
  
  
        < p>  
  
  
            < asp:GridView ID="GridView1" runat="server" 
  
  
             CssClass="DataWebControlStyle">  
  
  
               < HeaderStyle CssClass="HeaderStyle" />  
  
  
               < AlternatingRowStyle CssClass="AlternatingRowStyle" />  
  
  
            asp:GridView>  
  
  
             p>  
  
  
 
  
  
    div>  
  
  
    form>  
  
  
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: list of products in the GridView

In this example, we need to write three lines of code in the Page_Load event processing function of the asp.net webpage. In future tutorials, we will discuss how to use ObjectDataSource to retrieve data from the DAL in a declarative manner. If ObjectDataSource is used, we do not need to write a line of code, and we can still get paging and sorting support!

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