ADO Guide: This article mainly introduces the basic characteristics of ado.net, and some of the code used to show how to establish a database connection in ado.net, send query commands and use DataReader objects to quickly browse data sets.
--------------------------------------------------------------------------------
Translation finishing:. NET technology Network (www.51dotnet.com) Gao Fei small lion
English version: http://www.itpeople.com.cn/tech/tech-news.asp?fatherid=137&newsid=31148&classtype=152
Ado. NET is the next generation product of Microsoft's Microsoft ActiveX Data Objects (ADO), it is at Microsoft. NET to create an application development interface (API) for distributed and data-sharing applications.
Ado. NET can be used in any user's application, and it needs to connect and communicate with OLE Db-compliant data sources, such as Microsoft SQL Server.
At the same time, Ado.net maintains a number of key concepts related to the previous ADO model, it has been greatly perfected, and provides a way to obtain structured data from different sources----a platform text file, data obtained from the database management system, or hierarchical XML data----However, all are in accordance with a compatible , a standardized design model to perform.
This article is intended to briefly introduce the key features of Ado.net, focusing on accessing data in a relational database management system (RDBMS).
Quick Browsing
SQL Server 7.0 (and later versions) and any data sources that can be accessed through OLE DB providers. These are also referred to as managed providers (Managed Provider). NET Framework's data Access API provides two ways to identify and process two types of data sources, SQL Server 7.0 (and later versions), and any data sources that can be accessed through OLE DB providers. SQL (System.Data.SQL) libraries can be directly connected to data in SQL Server, and ADO (System.Data.ADO) libraries are available to any other data source that is accessed through OLE DB providers.
SQL Server-managed providers use a proprietary protocol called "Tabulardata stream" in MS SQL Server 7.0 or later, instead of using OLE DB, ADO, or ODBC.
Ado. NET managed providers are able to work under these OLE DB providers.
Driver Driver
Provider Provider
Sqloledb
SQL OLE DB Provider
Msdaora
Oracle OLE DB Provider
JOLT
Jet OLE DB Provider
Msdasql/sqlserver ODBC
SQL Server ODBC Driver via OLE DB for ODBC Provider
Msdasql/jet ODBC
Jet ODBC Driver via OLE DB Provider for ODBC Provider
Ado.net now does not support Msdasql/oracle ODBC Driver (Oracle OLE DB Driver for ODBC).
The following sections describe the core components of ado.net that are available to each managed provider
connections--Connect and manage database transactions.
commands--the command sent to the database.
datareaders--Direct read stream data.
Datesets and datesetcommands--store and manipulate data residing in memory.
The core ado.net function can basically be summed up as follows:
The Connection object establishes a connection between the Web page and the database. The Commands object sends commands to the database provider, and the returned results flow through the connections in a streaming manner. The result set can be read quickly with DataReaders, or it can be stored in a Datesets object that resides in memory, and then the user is allowed to access and manipulate records in the dataset through the Datesetcommands object. Developers can use the Dateset built-in method to work with datasets on the underlying data source.
In order to use. NET Framework, the following namespace (namespaces) needs to be included in the. aspx page.
SQL Managed Provider:
<%@ Import namespace= "System.Data.SQL"%>
ADO Managed Provider:
<%@ Import namespace= "System.Data.ADO"%>
Connections
Microsoft in. NET Framework provides two connection objects to establish a connection to a specific database: SqlConnection and ADOConnection. The connection object can be explicitly opened by calling the Open method on a connection that has already been created. The following code fragment demonstrates creating and opening a connection with either provider.
SqlConnection
[C #]
String connectionString = "server=localhost;" Uid=sa; pwd=; Database=northwind ";
SqlConnection myconn = new SqlConnection (connectionString);
MyConn.Open ();
[VB]
Dim connectionString as String = _
M connectionString as String = _
"Server=localhost; Uid=sa; pwd=; Database=northwind "
Dim myconn As SqlConnection = New SqlConnection (connectionString)
MyConn.Open
ADOConnection
[C #]
String connectionString = "Provider=SQLOLEDB.1;" Data Source=localhost; Uid=sa; pwd=; Initial Catalog=northwind; "
ADOConnection myconn = new ADOConnection (connectionString);
MyConn.Open ();
[VB]
Dim connectionString as String = _
Ost Uid=sa; pwd=; Initial Catalog=northwind; "
ADOConnection myconn = new ADOConnection (connectionString);
MyConn.Open ();
[VB]
Dim connectionString as String = _
"Provider=SQLOLEDB.1; Data Source=localhost; "& _
"UID=SA; pwd=; Initial Catalog=nohwind "
Dim myconn as ADOConnection = New adoconnection (connectionString)
MyConn.Open ()
Commands
After the connection is established, the next step is to run the SQL statement against the database. The simplest and most straightforward approach is to implement it through ADO and SQL command objects.
The command object can give the provider some instructions on how to manipulate the database information.
A command can be expressed in a typical SQL statement, including a select query to return a recordset, execute an action query to update (add, edit, or delete) a database record, or create and modify a database's table structure. The command, of course, can also pass parameters and return a value.
Commands can be clearly defined, or call stored procedures in the database. The next little piece of code demonstrates how to issue a SELECT command after a connection is made.
SQLCommand
[C #]
String sqlstmt = "SELECT * from Customers";
SQLCommand mycommand = new SQLCommand (sqlstmt, myconn);
[VB]
Dim sqlstmt as String = "SELECT * from Customers"
Dim mycommand as SQLCommand = New SQLCommand (sqlstmt, myconn)
Adocommand
[C #]
String sqlstmt = "SELECT * from Customers";
Adocommand mycommand = new Adocommand (sqlstmt, myconn);
[VB]
Dim sqlstmt as String = "SELECT * from Customers"
Dim mycommand as Adocommand = New Adocommand (sqlstmt, myconn)
DataReaders
When you work with large amounts of data, the amount of memory that is consumed can cause performance problems. For example, a connection (connection) with a traditional ADO Recordset object to read records of 1000 of rows of databases, must allocate memory to this connection for the 1000-line records until the end of the connection's lifecycle. If 1000 users are doing the same thing at the same time on the same computer, the excessive use of memory can be a critical issue.
In order to solve these problems,. NET Framework includes the DataReaders object, which simply returns a read-only, forward-only stream from the database. And there is only one record at a time in the current memory.
The DataReader interface supports a variety of data sources, such as relational data and hierarchical data. DataReader can be applied to just return a simple read-only recordset after running a command.
The following code fragment illustrates how to declare a variable to an instance of a DataReader object, and also includes the result of the Command object as code executes. When the command object executes a method, the command object must have been created and passed as a parameter. Continue with the above example:
SqlDataReader
[C #]
SqlDataReader myreader = null;
Mycommand.execute (out myreader);
[VB]
Dim myreader As SqlDataReader = Nothing
Mycommand.execute (myreader)
Adodatareader
[C #]
Adodatareader myreader = null;
Mycommand.execute (out myreader);
[VB]
Dim myreader as Adodatareader = Nothing
Mycommand.execute (myreader)
The next step is a simple format that uses DataReader
[C #]
while (Myreader.read ()) {
[C #]
while (Myreader.read ()) {
Do your thing with the "Current row" here
}
[VB]
While Myreader.read
' Do your thing and the current row
End While
The following example shows what we've been discussing so far: establish a connection to a SQL data source, send a SELECT command for a connection, save the returned results with a DataReader object, and then get the data through the loop DataReader.
Here is the complete code written in C #.
<%@ Import namespace= "System.Data"%>
<%@ Import namespace= "System.Data.SQL"%>
<script language= "C #" runat= "Server" >
Public SqlDataReader myreader;
public String html;
protected void Page_Load (Object Src, EventArgs E) {
SqlConnection mysqlconnection = new SqlConnection ("server=localhost;uid=sa;pwd=;d atabase=northwind");
SQLCommand Mysqlcommand = new SQLCommand ("SELECT * from Customers", mysqlconnection);
try {
Mysqlconnection.open ();
Mysqlcommd.execute (out myreader);
. Execute (out myreader);
Html= "<Table>";
html+= "<TR>";
html+= "<td><b>customer id</b> </TD>";
html+= "<td><b>company name</b></td>";
html+= "</TR>";
while (Myreader.read ()) {
html+= "<TR>";
html+= "<TD + myreader[" CustomerID "]. ToString () + "</TD>";
+ myreader["CustomerID"]. ToString () + "</TD>";
html+= "<TD>" + myreader["CompanyName"]. ToString () + "</TD>";
html+= "</TR>";
}
html+= "</Table>";
}
catch (Exception e) {
Html=e.tostring ();
}
Finall y {
Meader. Close ();
Ader. Close ();
Mysqlconnection.close ();
}
Response.Write (HTML);
}
</script>
<body>
</body>
Note that the true capture block is already included in the "Try ... catch" statement. This provides a number of ways to handle exceptions when handling connections. The code in the "finally" block is always executed, regardless of whether the "try" or "catch" block has been executed, so it becomes the logical location to turn off reader and Conncetion objects.
Also notice how the value of the field in the DataReader is conveniently accessed and delivered.
Summary: This article mainly introduces the basic features of ado.net, and some of the code used shows how to establish a database connection in ado.net, send query commands and use DataReader objects to quickly browse data sets. Of course, as Microsoft for distributed applications and data sharing of the next generation of ADO products, it is really the essence of the Datesets object. For this purpose, this website will be launched in the upcoming "ADO." NET in-depth research, the Datesets object is deeply discussed.
