ASP. NET database programming

In ASP. NET, ADO. Net corresponds to ADO in ASP. It is an improved version of ADO. In ADO. net, the application provided by the managed provider Program APIS allow you to easily access data from various data sources, including databases supported by oledb and supported by ODBC.

The following describes the two most important concepts of ADO. Net: managed provider and dataset.

Managed provider

In the past, two-layer connection-based programming model was used for data access through ADO. With the increasing demand for multi-tier applications, programmers need a connectionless model. Ado. Net came into being. The managed provider of ADO. Net is a multi-layered, connectionless, consistent programming model.

Managed provider provides the connection between dataset and data centers (such as ms SQL. Managed provider contains a series of interfaces for accessing a data center (database. There are three main components:

1. The connection object, command object, and parameter object parameter provide interfaces between data sources and dataset. The datasetcommand interface defines the ing between data columns and tables, and finally retrieves a dataset.

2. Data streams provide a high-performance, forward data access mechanism. With idatareader, can you easily and efficiently access data streams? /P>

3. Lower-level objects allow you to connect to the database and then execute specific commands at the database system level.

In the past, data processing mainly relied on two-layer structures and was connection-based. If the connection is disconnected, the data cannot be accessed. Currently, data processing is extended to a layer-3 structure. Correspondingly, programmers need to switch to a connectionless application model. In this way, datasetcommand plays an extremely important role in ADO. net. It can retrieve a dataset and maintain a "bridge" between a data source and dataset for data access, modification, and storage. Datasetcommand automatically converts various data operations to appropriate SQL statements related to the data source. The figure shows that four command objects: selectcommand, insertcommand, updatecommand, and deletecommand Replace the query, insert, update, and delete operations of the database.

Managed provider uses the local oledb to access data through COM InterOP. Oledb supports automatic and manual transaction processing. Therefore, managed provider also provides the transaction processing capability.
Dataset

Dataset is the central concept of ADO. net. You can think of dataset as a database in memory. It is precisely because of dataset that programmers can shield the differences between databases during programming to obtain a consistent programming model.

Dataset supports multiple tables, inter-Table relationships, and data constraints. These are basically the same as the relational database model.

Access the database through ADO. net

From the perspective of syntax or from the perspective of style and design goals, ADO. NET is significantly different from ADO. To access a database through ADO in ASP, follow these four steps:

1. Create a link to the database, that is, ADO. connection;

2. query a data set, that is, execute SQL to generate a recordset;

3. Perform required operations on the data set;

4. Disable the data link.

In ADO. net, these steps have greatly changed. One of the most important concepts of ADO. NET is dataset. Dataset is an independent data set independent of databases. Independence means that dataset is still available even if the data link is disconnected or the database is closed. If you have used a connectionless recordset in ASP, dataset is the most thorough alternative to this technology.
With dataset, the steps for accessing the database from ADO. NET are changed accordingly:

1. Create a database link;

2. Request a record set;

3. Save the record set to dataset;

4. If necessary, return Step 1 (dataset can accommodate multiple data sets)

5. Shut down the database link;

6. perform the required operations on dataset.

Dataset uses XML to describe data internally. Because XML is a platform-independent and language-independent data description language, it can also describe data of complex data relationships, such as parent-child data, therefore, dataset can actually accommodate data with complex relationships and is no longer dependent on database links.

Ado. Net has many objects. Let's first look at the most basic and commonly used objects. First, let's look at adoconnection. Corresponds to the ADODB. connection object of ADO, and adoconnection maintains a link to the database. To use the ADO. Net object, we need to introduce two namespaces: system. Data and system. Data. Ado. You can use the import command of ASP. NET:

<% @ Import namespace = "system. Data" %>

<% @ Import namespace = "system. Data. Ado" %>

Similar to the connection object of ADO, The adoconnection object also has two methods: open and close. The following example shows how to connect to the pubs database on the local ms SQL Server.

<% @ Import namespace = "system. Data" %>
<% @ Import namespace = "system. Data. Ado" %>
<%
'Set the connection string...
Dim strconnstring as string
Strconnstring = "provider = sqloledb; Data Source = (local );"&_
"Initial catalog = pubs; user id = sa"

'Create an object adoconnection
Dim objconn as adoconnection
Objconn = new adoconnection

'Set the connection string of the adoconnection object
Objconn. connectionstring = strconnstring

Objconn. open () 'Open the data link

'Database operationsCodeOmitted

Objconn. Close () 'Close the data link
Objconn = nothing 'clear object
%>
 

There is no big difference between the above Code and ADO. It should be mentioned that ADO. NET provides two Database Connection Methods: ADO and SQL. Here we connect to the database through ADO. For more information about establishing a database connection, we will discuss it later.

Adodatasetcommand

Another object that must be mentioned is adodatasetcommand, which is used to create the DataSet object we mentioned earlier. Another important ADO. Net object is dataview, which is a view of dataset. Do you still remember that dataset can accommodate complex data with various relationships? With dataview, we can limit the data in dataset to a specific range.

The following code uses the adodatasetcommand to populate data with Dataset:

'Create an SQL string
Dim strsql as string = "select * from authors"

'Create the object adodatasetcommand and Dataset
Dim objdscommand as adodatasetcommand
Dim objdataset as dataset = new dataset
Objdscommand = new adodatasetcommand (strsql, objconn)

'Fill data into Dataset
And name the data set "author information"
Objdscommand. filldataset (objdataset, "author information ")
 

Display Dataset

We have prepared the data before. Next let's take a look at how to display data in dataset. In ASP. net, display dataset commonly used control is DataGrid, It is ASP. a html control in. Net can be used as a table, and the appearance of the table can be controlled at will, or even displayed by page. Here we only need to simply use it:

<Asp: DataGrid id = "datagridname" runat = "server"/>

The remaining task is to bind the dataset to this DataGrid. Binding is an important concept of ASP. NET. We will explain it in another article. Generally, you need to bind a dataview to the DataGrid instead of the dataset directly. Fortunately, dataset has a default dataview. Next we will bind it to the DataGrid:
Myfirstdatagrid. datasource = _
Objdataset. Tables ("author information"). defaultview
Myfirstdatagrid. databind ()

Dataset usage
Dataset is not a simple recordset version. In a certain sense, dataview is more similar to recordset. If datareader is the easiest way to access data, dataset is the most complete data access object. With dataset, you can operate on existing data, create dataset through programs, add tables to dataset, and establish relationships between these tables.

Steps for using Dataset

Step 2: create a connection to the Data source:

Sqlconnection con = new sqlconnection ("Server = localhost; uid = sa; Pwd =; database = pubs ");

Step 2: Create a datasetcommand object, specify the name of a stored procedure or an SQL statement, and specify the data link;

Sqldatasetcommand cmd = new sqldatasetcommand ("select * from authors", con );

Step 2: Create a DataSet object

Dataset DS = new dataset ();

Step 2: Call the filldata method of datasetcommand to fill data with dataset. Note: There is no need to open the data link. If the data link is closed, the filldata function will open it and close the data link after filldata. If the data link is opened, the data link remains open after filldata.

Int irowcount = cmd. filldataset (DS, "Authors ");

Step 2: operate data. Since filldata returns the number of records, we can construct a loop to manipulate the data in dataset.

For (INT I = 0; I <irowcount; I ++ ){
Datarow DR = Ds. Tables [0]. Rows [I];
Console. writeline (Dr ["au_lname"]);
}

Data Binding Technology

The repeater, datalist, and DataGrid controls are page components in the system. Web. UI. webcontrols namespace. These controls display the data bound to them through HTML, and they are then "list-bound controls" (list-bound controls ).

Like other Web Components, these components not only provide a consistent programming model, but also encapsulate HTML logic related to the browser version. This feature allows programmers to program the object model without considering the differences and inconsistencies between different browser versions.

These three controls have the ability to "translate" their related data into various appearances. These appearances include tables, multi-column lists, or any HTML stream. They also allow you to create any display effect. In addition, they encapsulate the functions of processing submitted data, status management, and event excitation. Finally, they provide various levels of standard operations, including selection, editing, paging, sorting, and so on. With these controls, you can easily complete the following Web applications: Reports, shopping carts, product lists, query result display, navigation menus, and so on.