Migrate from ADO to ADO. Net (1)

In recent years, as the preferred method for data access in Windows-based applications, ADO plays an important role. A large number of ADO applications are currently in use, and a large number of developers are familiar with ADO development. With the emergence of. NET Framework, ADO. net, the ultimate version of ADO, is also released. Although there are many similarities between ADO and ADO. net, their operation methods and implementation basics are quite different. To help you achieve smooth migration to ADO. net, we will examine how to implement certain public tasks in ADO. net.
I will discuss several data access solutions, demonstrate how to use ADO to implement these solutions, and demonstrate how to use C # to solve the same problem with ADO. Net in ASP. NET. I will start from the similarities between the two and the data source connection, and then I will carefully explore how ADO's recordset object evolved into many distinctive object-oriented and method features in ADO. net. Finally, I will reveal how firehose cursor, how to return a single value from the row set, and how to process XML.

Evolution of ADO
Some traditional ADO features, such as establishing a connection with the data source, have changed little between the two ADO versions. Other functions are greatly changed. For example, a non-connected rowset is used to save the row set as XML, converts a row set into a hierarchical rowset ). One cause of these major changes is the introduction of XML and data-shaping features in the later stage of ADO. net, these features were built in at the beginning of the design.
Compared with earlier data access tools such as Dao and rdo, traditional ADO is very lightweight, one of the reasons that makes ADO popular with Visual Basic 6.0 in n-layer application development is its simple and easy-to-navigate object mode. The connection and command objects of ADO are converted to ADO in a straightforward way. net connection, command object, but ADO recordset object feature conversion to ADO. net becomes several different objects and methods.
Ado becomes a powerful and common data access tool, in part because of its support for XML and its ability to manage non-connected row sets. The recordset of ADO can be disconnected from its data source. You only need to set its cursorlocation attribute to aduseclient, set the cursortype attribute to adopenstatic, and set the locktype attribute to adlockbatchoptimistic. Once a recordset is opened and loaded, you can set its activeconnection attribute to nothing to keep the record set in a non-connection state.

 

''''---Disconnecting an ADO Recordset oRs.CursorLocation = adUseClient oRs.CursorType = adOpenStatic oRs.LockType = adLockBatchOptimistic ''''— Or use adLockReadOnlyoRS.OpenSet oRS.ActiveConnection = Nothing

At first, XML functions were not integrated into ADO. With the popularity of XML, XML support was added to later versions of ADO. The Save method in the ADO recordset object can save the rows and columns of the record set as a predefined XML schema and save it as a file or stream. The XML outline is not flexible, but this is the first attempt to save an ADO rowset as XML and give developers a clear understanding of the future development direction. Recordset can also be loaded from an XML file, provided that it uses the same XML outline. The following code demonstrates how ADO saves a record set as an XML file:

 

.Save "c:/MyRowSet.xml", adPersistXML

Similar to the response object output on an ASP page, the row set can be saved as a stream. For example, some ASP code can accept a request to retrieve a row set and send the row set as a stream to the response object. The latter returns the result to the client. The following code saves the content of a record set as a stream object. You can also set the first parameter of the Save Method to the response object, so that the XML collection stream can be output to the browser:

Dim oStm As ADODB.StreamSetoStm = New ADODB.StreamoRs.Save oStm, adPersistXML

Traditional ADO and ASP support stream processing. Of course, in. net, Web Services also support streaming, and Web Services provide much more functions than traditional ADO and ASP. In fact, Web Services is a technology that processes data requests and transmits data in XML format through HTTP. Ado can save XML in the form of a stream, but this is a later improvement of ADO (afterthought ). The DataSet object of ADO. Net outputs its content as an XML file or stream through the writexml and writexmlschema methods. Therefore, the SAVE method of the recordset of ADO is improved to output its content as an XML, and the dataset of ADO. Net has this feature at the beginning.
Ado. Net can not only save dataset as XML, but also load dataset from XML, and it uses its class XML structure to other purposes. For example, because dataset can be expressed as XML, it can be easily transmitted between the physical layer and the logic layer. This means that XML can be transmitted over HTTP over a secure network, or in text-based XML format.
Since it is built on XML, ADO. Net can work in a non-connection state. The traditional ADO recordset can work both in the connection status and non-connection status, depending on the following attributes (for example, cursortype = adopenstatic and cursorlocation = aduseclient), while in ADO. in. net, the rowset object is divided into the connected state (datareader) and the non-connected state (Dataset ).

Connection
The process of creating a connection is very similar in ADO and ADO. net. First, declare your connection object, initialize, set its connection string, and open it. See figure 1. The first example demonstrates how to use ASP and ADO to open a connection, and the second example does the same work in ASP. NET and ADO. net.
ADO and ADO. the main difference between establishing a connection is that ADO uses a connection object to complete all the connections with various data sources, while ADO.. net uses different connection objects to represent connections to different data sources. For example, ADO. Net contains a namespace named system. Data. sqlclient, which contains all ADO. Net objects (including sqlconnection objects) dedicated to SQL Server ). The sqlconnection object is used to communicate with the SQL Server database. Therefore, it is the fastest interaction and most functional object with SQL Server. There is also a more general namespace system. Data. oledb, which can communicate with all compatible ole db data sources. Therefore, in ADO.. net, you can create multiple data provider namespaces to connect to a specific data source, so that data access is faster and more efficient, each namespace is allowed to take full advantage of the functions of the target data provider. If a program must change the type of the data provider at runtime, or depend on a data provider, the data provider does not include specific ADO. net connection object, this program is best to use oledbconnection.

Recordset to readers
The behavior (function) of recordset In ADO varies depending on its attribute settings. In ADO. net, recordset is divided into many different objects and methods. This avoids the disadvantages of recordset and focuses on assigning different roles to each object. Therefore, such decomposition in ADO. Net can significantly improve the efficiency. The recordset In ADO can be a connected row set or a non-connected row set. It can act as a forward-only read-only cursor, or allow forward, backward, and center movement of the positions in the rows. Ado recordset allows you to directly modify data in the database, save data modifications, and send them to the database in batch. The problem is that the recordset of ADO undertakes too many functions. In ADO. net, these functions are divided into multiple objects to complete specific tasks. Therefore, the functions of ADO recordset are divided into DataSet object, datareader object, dataadapter and command object.
Forward and read-only cursors must be connected to the data source. Ado. net uses the datareader object to ensure that it remains connected when it is opened. Datareader is designed for a data provider such as SQL Server, Oracle, or a more general OLE DB data provider. Therefore, the sqldatareader object can be connected to the SQL Server database and act as a firehose cursor, which loops between a large number of records. Sqldatareader also provides fast-forward-only access to query results. It retrieves a record from the query results of the database and keeps the connection open so that the next record can be retrieved successfully. The datareader of ADO. NET is extremely efficient because it does not support all the functions of ADO recordset. The example in Figure 2 demonstrates how to implement a forward-only cursor in traditional ADO and ADO. net. It is worth noting that in ADO. net, the read method of the datareader object automatically moves the location to the next record. This avoids the endless loop caused by the negligence of calling the movenext method of ADO recordset when developers use traditional ADO.
Another difference between ADO and ADO. NET is the way to populate the forward cursor. All row sets in ADO are included in the recordset object regardless of the forward or other. The recordset object is opened through the recordset. Open method or the execute method of the connection or command object. ADO. net, there is a special method to get the data provided to datareader: that is, the executereader method of the command object, this method tells the command object to explicitly propose a datareader object for the data provider to process the results in an optimized forward mode. As shown in the previous example. This command object also has a method called executexmlreader, which tells the command object to generate query results for xmlreader objects to process. Xmlreader objects can be used to convert and process XML query results. See the following ASP. NET example:

oCn.Open();SqlCommand oCmd = new SqlCommand("SELECT * FROM Orders FOR XML AUTO",oCn);oCmd.CommandType = CommandType.Text;XmlReader oXR = oCmd.ExecuteXmlReader();while(oXR.Read()) {     Response.Write(oXR.ReadOuterXml()); }

 

This example demonstrates how XML is sent to the called browser. However, XML can also be aggregated and streamed to the response object.

Single-value command
Sometimes, most applications need to obtain the information of a single value from database queries. In traditional ado, the standard practice is to create an SQL statement and open a record set containing the query results. Because there is only one row and one column in the result, it seems unnecessary. The following code retrieves a single value from a query: In this example, the number of rows in the orders table is obtained:

''''-- ASP and ADO                Set oRs = Server.CreateObject("ADODB.Recordset")oRs.ActiveConnection = oCnoRs.Open "SELECT COUNT(*) As iRowCount FROM Orders"iCount = oRs.Fields("iRowCount").Value 

If you only want to obtain the information of only one row and one column, ADO. NET introduces a new method for getting a single value from the query. Use the executescalar method in the ADO. Net command object to return the information of the first row and column in the related query. Because you do not have to create a row set, find this value, and close the row set, the overhead is very small. The executescalar method is the best method for retrieving a single value. The following code completes the same task as the worker, but the executescalar method in ASP. NET and ADO. NET is used:

string sSql = "SELECT COUNT(*) As iRowCount FROM Orders";SqlCommand oCmd = new SqlCommand(sSql, oCn); oCmd.CommandType = CommandType.Text;int iCount = (int)oCmd.ExecuteScalar();

Another way to obtain a single value is to use the output parameters of the stored procedure. This technology also needs to retrieve a large number of values. It can be used in both ADO and ADO. net. The difference is that ADO. Net extends the output parameter function. To obtain the output parameter value from a command object in ADO. net, executenonquery is used to execute the query statement. This method tells ADO. Net that the query does not return a row set, thus avoiding the overhead of dataset or datareader:

oCmd.ExecuteNonQuery(); oCmd.UpdatedRowSource = UpdateRowSource.OutputParameters;int iOrderID = (int)oCmd.Parameters["@OrderID"].Value;

The above Code sets the updatedrowsource attribute to point to the output parameter, provided that the output parameter has been set. Then you can retrieve the output value. In traditional ado, the execute method of the connection object uses a fuzzy parameter to complete the above tasks, while ADO. NET is implemented in a straightforward way. Of course, there is also an optimal method in ADO. Net to return a standard row set-the execute method of the command object.
Traditional ADO recordset objects can use update, insert, delete, and other statements. These statements are required to modify the underlying database with the changed value of recordset. Although this function is convenient, it still brings a lot of overhead because it must be returned to the database to learn how to perform this operation.
Ado. Net implements this through the commandbuilder object, although it also brings about system overhead. In most development cases, the specific select, insert, update, and delete operations can be determined at the design time. In traditional ado, there is no convenient way to associate query behavior with recordset so that recordset can take advantage of these advantages. In ADO. Net, a dataadapter has four different command objects associated with it, representing each query action and select statement respectively. This allows the dataadapter to use a query result to fill in a dataset and tell the dataadapter which query actions are taken on the database in advance. Of course, this requires more code in the design phase, but these additional code is worth Improving the performance, not to mention the Code's maintainability, because the code is self-explanatory. I discussed in detail how to use dataadapter in the "data point" column in March.
In this article, I have discussed the evolution of some key features from ADO to ADO. net, including firehost cursor and continuous XML. In subsequent columns, I will explore more about the evolution of cursors and demonstrate how to handle concurrency issues and how to Perform Batch update in traditional ADO and ADO. net.

E-mail address that sends questions and suggestions to John: mmdata@microsoft.com