ado| Access | data | Database Access database via Ado.net
Author: unknown
--------------------------------------------------------------------------------
Ado. NET access to a database
Whether from the perspective of grammar, or from the style and design goals, ADO. NET is a significant difference from ADO. In ASP, access to the database through ADO, generally through the following four steps:
1, create a link to the database, that is, ado.connection;
2, query a data set, that is, execute SQL, generate a recordset;
3, the data collection to carry out the required operation;
4, close the data link.
In Ado.net, these steps have changed a lot. Ado. One of the most important concepts of net is dataset. A dataset is a separate collection of data that is not dependent on a database. The so-called independence is: even if the data link is disconnected, or the database is closed, the dataset is still available. If you have used a collection of disconnected records (connectionless Recordset) in an ASP, then the dataset is the most thorough alternative to this technique.
With the dataset, then, ADO. NET access to the database changes accordingly:
1, create a database link;
2. Request a collection of records;
3, the collection of records to the dataset;
4, if necessary, return to step 2nd; (DataSet can hold multiple data sets)
5, close the database link;
6. Do the required operation on the dataset.
The dataset internally uses XML to describe the data. Since XML is a platform-independent, language-independent data Description language, and can describe data for complex data relationships, such as parent-child relationships, datasets can actually accommodate data with complex relationships and no longer rely on database links.
Ado. NET object Model Overview
ADOConnection
Ado. NET has many objects. Let's take a look at the basics and the most commonly used ones. First look at the adoconnection. Corresponds to the Adodb.connection object of ADO, ADOConnection maintains a link to the database. In order to use the Ado.net object, we need to introduce two NameSpace:System.Data and System.Data.ADO, using the asp.net import directive:
<%@ Import namespace= "System.Data"%>
<%@ Import namespace= "System.Data.ADO"%>
Similar to the Connection object of ADO, the ADOConnection object also has open and close two methods. The following example shows how to connect to the pubs database on a 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 Object ADOConnection
Dim objconn as ADOConnection
objconn = New adoconnection
' Set the connection string for the ADOConnection object
objconn.connectionstring = strconnstring
objConn.Open () ' Open Data link '
' Database operation code omitted
Objconn.close () ' Close data link
objconn = Nothing ' Clears object
%>
The code above is not much different from ADO. It should be mentioned that ADO. NET provides two ways of database connection: ADO method and SQL method. Here we are connected to the database through ADO. For more information on establishing a database connection, we'll talk about it in a later space.
Adodatasetcommand
Another Ado.net object that has to be mentioned is Adodatasetcommand. This object is specifically responsible for creating the DataSet object we mentioned earlier. Another important Ado.net object is DataView, which is a view of the dataset. Remember the dataset can hold complex data for a variety of relationships? By DataView, we can limit the dataset's data to a specific range.
The following code shows how to populate the DataSet with Adodatasetcommand:
' Create SQL string
Dim strSQL as String = "SELECT * FROM Authors"
' Create object Adodatasetcommand and dataset
Dim Objdscommand as Adodatasetcommand
Dim objdataset As DataSet = New DataSet
Objdscommand = New Adodatasetcommand (strSQL, objconn)
' Populate data to DataSet '
"and name the data collection" Author Information "
Objdscommand.filldataset (Objdataset, "Author information")
Display DataSet
We've got the data ready before. Let's take a look at how to display the data in the dataset. In ASP.net, the commonly used control for displaying a DataSet is a DataGrid, an HTML control in asp.net that can be well represented as a table, with arbitrary control over the appearance of the table and even pagination. Here we just need to use it simply:
<asp:datagrid id= "Datagridname" runat= "Server"/>
The rest of the task is to bind the DataSet to this DataGrid. Binding is an important concept of asp.net, which we will explain in another text. Generally, you need to bind a DataView to a DataGrid instead of binding the dataset directly. Fortunately, the dataset has a default DataView, and we bind it to the DataGrid:
Myfirstdatagrid.datasource = _
Objdataset.tables ("Author information"). DefaultView
Myfirstdatagrid.databind ()
Complete code (122301.aspx)
<%@ 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 Object ADOConnection
Dim objconn as ADOConnection
objconn = New adoconnection
' Set the connection string for the ADOConnection object
objconn.connectionstring = strconnstring
objConn.Open () ' Open Data link '
' Create SQL string
Dim strSQL as String = "SELECT * FROM Authors"
' Create object Adodatasetcommand and dataset
Dim Objdscommand as Adodatasetcommand
Dim objdataset As DataSet = New DataSet
Objdscommand = New Adodatasetcommand (strSQL, objconn)
' Populate data to DataSet '
"and name the data collection" Author Information "
Objdscommand.filldataset (Objdataset, "Author information")
Objconn.close () ' Close data link
objconn = Nothing ' Clears object
Authors.datasource = _
Objdataset.tables ("Author information"). DefaultView
Authors.databind ()
%>
<HTML>
<BODY>
<asp:datagrid id= "Authors" runat= "Server"/>
</BODY>
</HTML>
