Create a data access layer for ASP. NET2.0 data operations (1)

As web developers, we live our lives around data operations. We create a database to store data, write code to access and modify data, and design WebpageTo collect and summarize data. This article is the first of a series of tutorials on how to implement these common data access modes in ASP. NET 2.0. We will create SoftwareAt the beginning of the framework, the components of this framework include a data access layer (DAL) using a strong data type DataSet, a business logic layer (BLL) That Implements user-defined business rules ), and an ASP.. NET page. After laying the foundation for this backend, we will begin to switch to reports to demonstrate how to display, summarize, collect, and verify web application data. These tutorials are designed to be concise and concise. They use many screens and provide step-by-step guidance to take you through this development process. Each tutorial has C # and VB versions, with the complete encoding involved Download. (This is a long tutorial, but other tutorials will be available in the future at a much easier time .)

In these tutorials, we will use the mini-soft SQL Server 2005 Express version of the Northwind database in the App_Data directory. In addition to database files, the App_Data directory also contains SQL scripts used to create databases, in case you want to use other database versions. If you want to, you can also download these scripts directly from Microsoft. If you use another SQL Server version of the Northwind database, you need to update the setting of NORTHWNDConnectionString in the Web. config file. The web application in this tutorial is a file system-based website project created using Visual Studio 2005 Professional Edition. However, all the tutorials can be run in Visual Web Developer, a free version of Visual Studio 2005.

In this tutorial, we will create a data access layer (DAL) from the beginning, and then create a business logic layer (BLL) in the second tutorial ), in the third tutorial, design the page layout and navigation. The subsequent tutorials will be based on these three tutorials. In the first tutorial, we will discuss a lot of content, so open Visual Studio and let's get started!

　　Step 1: create a Web project and configure database connection

Before creating the data access layer (DAL), we need to create a website and a database. We start from creating an ASP. NET Website Based on a file system. The order is as follows. Open the File menu and select a New website. The system displays a New website dialog box, and select ASP. NET website template, set the Location list option to File System, and then select the folder where the website is located, then select C # as the programming language #.

Figure 1: create a file system-based website

Visual Studio generates a new website for you and a webpage named Default. aspx and an App_Data folder.

After the website is generated, the next step is in Visual StudioServerAdd a reference for your database in Resource Manager (Server Explorer ). After adding a database to the server resource manager, you can add data tables, stored procedures, views, and so on in the Visual Studio environment. You can also view the data in the database, and create your own Query statements manually or using the Query Builder GUI. In addition, when creating a strongly typed DataSet for the DAL, we need to point Visual Studio to the target database as the DataSet data source. Although we can provide the specified region when appropriate ??? The database connection information involved in o & M. However, if we pre-register these databases in the server resource manager, Visual Studio automatically fills these databases in a drop-down list.

The steps to add the Northwind database to the Server resource manager depend on the SQL Server 2005 Express database you want to use in the App_Data folder, or you want to use the SQL Server 2000 or 2005 database Server that has been created.

Use a database in the App_Data folder

If you do not have an accessible SQL Server 2000 or 2005 Server, or you want to avoid adding a database to the database Server, you can use the Northwind database of SQL Server 2005 Express, the database is located in the App_Data folder (NORTHWND. MDF ).

Databases placed in the App_Data folder are automatically added to the server resource manager. Suppose you have installed SQL Server 2005 Express on your machine, you should see a file named NORTHWND in the Server resource manager. MDF node, you can expand this node, browse the data tables, views, stored procedures, and so on (see figure 2 ).

The App_Data folder can also contain Microsoft Access. mdb database files. Similar to SQL Server Data Base files, these Access files are automatically added to the Server resource manager. If you do not want to use any SQL Server database, you can download the Northwind database file of Microsoft Access and place it in the App_Data folder. But remember, the Access database is not as versatile as SQL Server, and it is not designed to be used in website scenarios. In addition, the database-level functions not supported by the Access database will be used in the following tutorials.

Connect to the database in Microsoft SQL Server 2000 or 2005 Database Server

Alternatively, you can connect to the Northwind database installed on the database server. If you have not installed the Northwind database on the database server, you must first run the installation script in the downloaded file of this tutorial to add the database to the database server, or you can also go to the Microsoft websiteDirectly download the SQL Server 2000 Northwind database and installation script.

After the database is installed, go to the server resource manager in Visual Studio, right-click the Data Connection node, and select "Add Connection )". If you cannot see the server resource manager, go to View and click server resource manager, or press Ctrl + Alt + S to open the server resource manager. This will open the Add connection dialog box, where you can set the server to be connected, authentication information, and database name. After you successfully configure the database connection information and press OK, the database will be added to a node under the data connection node. Then, you can expand database nodes to view data tables, views, stored procedures, and so on.

Figure 2: Add a connection to the Northwind database on your database server

　　Step 2: create a data access layer

When dealing with data, one way is to put data-related logic directly in the presentation layer (in a web application, ASP. NET web pages constitute 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 (CategoryIDReturns the information of all products under 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 set these resultsStrongly typed 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 following 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, a strong type of able, all its fields 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. A developer's business object class usually maps 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.

　　In the architecture of these tutorials, we will use a strongly Typed DataSet. Figure 3 demonstrates the application of using a strong-type DataSetProgramDifferent layers of flow (workflow ).

Figure 3: Delegate all data access codes to the DAL

　　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 ??? In solution manager, right-click the project node 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 includes 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 coding of the TableAdapter class. If the connection string information is changed in the future, this method 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 <connectionStrings> section, this section can beEncryptionTo improve security, you can also use the new ASP. NET 2.0 attribute page in the IIS graphical interface management tool to modify. Of course, this tool is more suitable for administrators.

Figure 6: store the connection string in Web. config

Next, we need to define the schema of the first strongly typed able, and provide the first method for the TableAdapter class used to fill the strongly Typed 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 set query and fill in a strong type of 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.

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 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 generates a method that accepts the parameters of a able and fills in 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. This method creates and fills in a able 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.