asp.net2.0 data access layer to create data operations (1)

Asp.net| Create | access | data

　　 Introduction

As Web developers, our lives revolve around data manipulation. We build databases to store data, write code to access and modify data, and designWeb pageTo collect and summarize the data. This article is the first of a lengthy series of tutorials that explore the techniques for implementing these common data access patterns in asp.net 2.0. We will create aSoftwareThe framework begins with a data access layer (DAL) that uses a strongly typed DataSet, a business logic layer that enforces user-defined business rules (BLL), and a presentation layer composed of asp.net pages that share page layouts. After laying the groundwork for this backend, we will begin to turn to the report, demonstrating how to display, summarize, collect, and validate data for Web applications. These tutorials are designed to be concise, use a lot of screenshots, and provide step-by-step-by-step instructions to take you through the development process. Each tutorial has a C # version and a VB version, and is accompanied by a complete code that involvesDownload。 (This first tutorial is long, but other tutorials will be available in a more digestible space later.) )

In these tutorials, we will use the Microsoft SQL Server Express version of the Northwind database, which is placed in the App_Data directory. In addition to the database files, the App_Data directory has SQL scripts for creating the database, in case you want to use a different database version. You can also download these scripts directly from Microsoft, if you like. If you use a different SQL Server version of the Northwind database, you need to update the northwndconnectionstring settings in the Web.config file. The Web application in this tutorial is a file-based Web site project built with the visual Studio 2005 Professional Edition. However, all tutorials can be run in the free version of Visual Studio 2005, the Visual Web developer.

In this tutorial, we'll start from scratch, create a data access layer (DAL), then create a business Logic layer (BLL) in the second tutorial, and design the page layout and navigation in the third tutorial. Future tutorials will be based on these three tutorials. In the first tutorial, we have a lot to discuss, so please open visual Studio and let's get our hands on it!

　　 First step: Create a Web project, configure the database connection

Before we start creating the data Access Layer (DAL), we first need to create a Web site and build a database. We started by creating a asp.net Web site based on the file system. Order below, open the File menu, select a new Web site, the system will display a new Web Site dialog box, select the ASP.net site template (Web site template), set the positioning (Location) List of options for the file system ( File System), then select the folder where this site is placed, and then choose the programming language for C #.


Figure 1: Creating a File system based Web site

Visual Studio generates a new Web site for you, along with a Web page called Default.aspx, and a App_Data folder.

After the site is built, the next step is in Visual StudioServerIn the resource Manager (Server Explorer), add a reference (reference) to your database. After you add a database to Server Explorer, you can add datasheets, 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 by hand or by using the graphical interface of Query Builder (Builder). Also, when we create 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 Yun 敳 Mongoose 搨 in due course??? Oё The database connection information involved, but if we register these databases in advance in Server Explorer, Visual Studio will automatically populate the database with a drop-down list.

The steps to add the Northwind database to Server Explorer depend on whether you want to use the SQL Server Express version database that is placed in the App_Data folder, or if you want to use the SQL Server 2000 or 2005 that you have built The database server.

use a database that is placed in the App_Data folder

If you don't have a SQL Server 2000 or 2005 server that you can connect to, or you just want to avoid adding a database to the database server, you can use the SQL Server Express version of the Northwind database, which is located in the download source App_ In the Data folder (Northwnd. MDF).

The database that is placed in the App_Data folder is automatically added to the Server Explorer. Assuming that you have installed the SQL Server Express version on your machine, you should see a node named Northwnd.mdf in Server Explorer, and you can extend the node to browse through the data tables, views, stored procedures, and so on (refer to Figure 2 )。

The App_Data folder can also place Microsoft's Access.mdb database files, similar to SQL Server's database files, which are automatically added to Server Explorer. If you don't want to use any SQL Server database, you can always download the Microsoft Access version of the Northwind database file and place it in the App_Data folder. But remember, an Access database is not as powerful as SQL Server, and it's not designed to be used in a site scenario. In addition, the features of the database hierarchy that are not supported by an Access database are used in the following tutorials.

Connect to a database in a Microsoft SQL Server 2000 or 2005 database server

Alternatively, you can connect to the Northwind database installed on the database server. If the Northwind database is not installed on the database server, you must first run the installation script in the download file of this tutorial to add the database to the database server, or you can also from the Microsoft websiteDownload the Northwind database for SQL Server 2000 and install the script directly。

After you have installed the database, go to Server Explorer in Visual Studio, press the right mouse on the data connection (connections) node, and select Add Connection. If you don't see Server Explorer, go to Menu "View" click "Server Explorer" or press the key combination ctrl+alt+s to open Server Explorer. This opens the dialog box where you add the connection, where you can set up the server to which you want to connect, the authentication information, and the name of the database. After you have successfully configured the database connection information, press the OK button, the database will be added as a node under the Data Connection node. You can then extend the database nodes to browse the datasheet, view, stored procedure, and so on.


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

　　Step Two: Create a data access layer

When dealing with data, one approach is to put data-related logic directly in the presentation layer (in a Web application, ASP. NET Web pages form the presentation layer. The form is typically written ado.net encoding in the encoding section of the ASP.net Web page or using the SqlDataSource control in the identifier section. In both of these forms, this approach tightly coupled the data access logic with the presentation layer. But the recommended approach is to separate the data access logic from the presentation layer. This separate layer is called the data Access layer, which is abbreviated to DAL, and is typically implemented through a separate class library project. The benefits of this layered framework are set out in a number of literatures (see the resources in the "Additional Readings" section at the end of this tutorial), and we will use this approach in this series.

All encodings related to the underlying data source, such as the connection to the database, the encoding of Select,insert, UPDATE, and delete commands, should be placed in the DAL. The presentation layer should not contain any references to these data access encodings, but should call the code in the Dal to make all data access requests. The data access layer contains methods for accessing the underlying database data. For example, in the Northwind database, there are two tables for products and categories that record the product that is available for sale and the categories to which those products belong. In our DAL, we will have the following methods:

• GetCategories (), returns information for all categories
• GetProducts (), returns information on all products
• Getproductsbycategoryid (CategoryID), returns information for all products that belong to the specified category
• Getproductbyproductid (ProductID), returns information for the specified product

These methods, when invoked, will connect to the database, issue the appropriate query, and return the results. How we return to these results is very important. These methods can be directly returned to the database query filled dataset or DataReader, but the ideal way is to put these results tostrongly typed objectsreturned in the form. A strongly typed object whose schema is strictly defined at compile time, and by contrast, a weakly typed object whose schema is unknown before runtime.

For example, DataReader and regular datasets are weakly typed objects because their schemas are defined by the fields that are used to populate their database queries. To access a specific field in a weakly-typed DataTable, we need to use this syntax:DataTable. rows[Index] ["ColumnName"]。 The weak type nature of the DataTable in this example is that we need to access the field name through a string or ordinal index. In another respect, a strongly typed DataTable, all of its fields are implemented in the form of attributes, and the access code is 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 implements a business object class whose properties are often mapped to fields of the underlying data table. A strongly typed DataSet is a class that Visual Studio generates for you based on the database schema, and the types of its members are determined by this schema. The strongly typed dataset itself is made up of subclasses that inherit from the dataset,datatable in Ado.net, and the DataRow class. In addition to the strongly typed DataTable, the strongly typed dataset now includes the TableAdapter class, which contains various methods for populating the DataTable in the dataset and returning the changes of the DataTable back to the database.

　　In the architecture of these tutorials, we will use a strongly typed DataSet. Figure 3 demonstrates the application of a strongly typed datasetprogramThe different layers of the process (workflow).


Figure 3: Delegating all data access coding to the DAL

　　 Create a strongly typed DataSet and table Adapter

We started to create our Dal and first added a strongly typed dataset to our project. The procedure is as follows, in the Yun 敳 Mongoose 搨??? Press the right mouse on the project node in Oё Solution Manager and select Add New Item (add a). Select the dataset in the template Liedanli and name it northwind.xsd.


Figure 4: Add a new dataset to your project

When you click the Add button, Visual Studio asks if we want to add the dataset to the App_Code folder and select Yes. Visual Studio then displays the designer for the strongly typed DataSet and starts the TableAdapter Configuration Wizard, allowing you to add the first TableAdapter to your strongly typed DataSet.

A strongly typed dataset acts as a collection of strongly typed objects, consisting of strongly typed DataTable instances, and each strongly typed DataTable is then composed of strongly typed DataRow instances. We will build a corresponding strongly typed DataTable for each datasheet that this tutorial series will use. Let's start by creating a DataTable for the Products table first.

Remember that a strongly typed DataTable does not include any information on how to access the underlying datasheet. To get the data to populate the DataTable, we use the TableAdapter class, which provides the functionality of the data access layer. For our products DataTable, the corresponding TableAdapter class will include GetProducts () and Getproductbycategoryid (CategoryIDAnd so on, and we will call these methods at the presentation layer. The function of a DataTable is to transfer data between tiers.

The TableAdapter Configuration Wizard first wants you to choose which database to use. The Drop-down box lists the databases in Server Explorer. If you have not previously added the Northwind database to the Server Explorer, you can add it by clicking on the New Connection button.


Figure 5: Selecting the Northwind database in the dropdown box

Once you have selected the database, press the Next button and the wizard asks if you want to store the connection string in the Web.config file. By storing the connection string in the Web.config file, you can avoid writing the connection string in the TableAdapter class encoding, which will greatly simplify the coding changes if the connection string information changes in the future. If you choose to store the connection string in the configuration file, the connection string will be placed in the <connectionStrings> paragraph, which can beEncryptTo improve security, or you can modify it by using the new ASP.net 2.0 property page in the IIS graphical Interface management tool. Of course, this tool is more suitable for administrators.


Figure 6: Storing the connection string in the Web.config

Next, we need to define the schema of the first strongly typed DataTable and provide the first method for the TableAdapter class used to populate the strongly typed DataSet. These two steps can be completed at the same time by creating a query that returns a field that corresponds to the DataTable's datasheet. At the end of the wizard, we will name the method corresponding to this query. When finished, this method can be invoked at the presentation layer, which executes the set query and populates a strongly typed DataTable.

Before we start defining SQL queries, we must first select how we want to TableAdapter execute the query. We can use AD-HOC SQL statements directly, or create a new stored procedure, or use an existing stored procedure. In these tutorials, we'll use the Ad-hoc SQL statement.


Figure 7: Querying data with SQL statements

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


Figure 8: Entering the SQL query in the text box

Alternatively, we can construct the query using the graphical interface of Query Builder (Builder), as shown in Figure 9.


Figure 9: Generating a query from the Query Editor

After you build the query, click the Advanced Options button before moving to the next screen. Advanced In a Web site project, by default, generate INSERT, UPDATE, DELETE statement is the only option that has been selected. If you run this wizard in a class library project or a Windows project, the "optimistic concurrency" option will also be selected. Let's not choose the "concurrency Control with optimizations" option. In future tutorials we will discuss the optimization concurrency control in detail.


Figure 10: Select "Generate Inserts, UPDATE and DELETE statements" option only

After verifying the advanced options, press the Next button to go to the last screen. Here, the Configuration Wizard asks us what methods we want to add to the TableAdapter selection. There are two modes of populating data:

• Populating a DataTable -this approach generates a method that takes the parameters of a DataTable and populates the DataTable based on the results of the query. For example, ADO. NET's DataAdapter class is to implement this pattern in its fill () method.
• return DataTable – This practice generates a method that creates and populates a DataTable and then takes it as the return value of the method.

You can let TableAdapter implement one of these patterns or both. You can also rename the methods provided here. Let's make no changes to the two checkbox options, although we only need to use the following pattern in these tutorials. At the same time, let's change that very general GetData method name to GetProducts.