Migrating from ADO to ADO.NET (1) Original: John PAPA Translation: MSDN Magazine Jul 2004 (Data Point) In recent years, in Windows-based applications, as the preferred method of implementing data access, ADO Holding a plentiful role. There is currently a large number of ADO applications in use, and a large number of developers are well developed. With the appearance of .NET Framework, ADO's improved version ADO.NET also released. Although there are many similarities between ADO and ADO.NET, the operation methods of the two are very different. In order to help you implement smooth migration to ADO.NET, we look at how to implement certain public tasks in ADO.NET. I will discuss several data access programs that demonstrate how to use ADO to implement these programs while demonstrating how to use C # to use ADO.NET to solve the same problem. I will start from the similarities between the two and the data source, and then I will detaine how ADO's RecordSet object evolve is developed into many of ADO.NET, and the characteristics of object-oriented and methods. Finally, I will reveal the Firehose cursor, how to return a single value from the line, and how to deal with XML. The evolution of ADO has some traditional ADO features, such as establishing a connection between two ADO versions in two ADO versions. Other functions are large, such as representing a non-connected rowset, saving the row to XML, converting the row set into a hierarchical rowset. One reason for these major changes is to introduce XML and Data-Shaping feature in ADO, and in ADO.NET, these features are built in it when designing. Compared with the earlier data access tool such as DAO and RDO, traditional ADO is very lightweight, and one of the reason why ADO can develop with Visual Basic 6.0 in N-layer application development is its simplicity. Object mode for easy navigation. ADO's Connection, Command object transitions to ADO.NET's Connection, Command object, but ADO's RECORDSET object transitions to ADO.NET and become several different objects and methods. The reason why ADO becomes a powerful and universal data access tool is part of its support for XML and managing non-connected rows. ADO's RecordSet can be connected to its data source, as long as it sets its CursorLocation property to aduseclient, set the CURSORTYPE attribute to AdopenStatic, and set the LockType property to AdlockBatchOptimistic. Once the recordset is opened and loaded, the recording set is kept in a non-connector by setting its ActiveConnection property to Nothing. '' '--- Disconnecting An Ado Recordset
Ors.cursorLocation = aduseclient
Ors.cursortype = AdopenStatic
Ors.lockType = AdlockBatchOptimistic
'' '- or USE AdlockReadonly
Ors.openSetors.ActiveConnection = Nothing At first XML features are not integrated in ADO, with XML's popularity, add support for XML in the later version of the ADO. The Save method in the ADO Recordset object can save the rows and columns of the record set as a predefined XML outline and save it as a file or stream. Using the XML outline is not flexible, but this is the first attempt to save an ADO row (Rowset) as XML, and let developers have a clearer understanding of the future development direction. Recordset can also load from an XML file, provided that it uses the same XML outline. The following code demonstrates how ADO saves a recordset as an XML file: .save "c: /myrowset.xml", AdpersistXML is similar to the response object output on the ASP page, and the routine can be saved as a stream. For example, some ASP code can accept a request, retrieve a rowset, send the rowset to the response object in the stream, the latter returns the result to the client. The following code demonstrates how to save the content of a recordset as a stream object. You can also set the first parameter of the Save method to the Response object, so you can output the XML row flow to your browser: DIM OSTM As Adod.StreamSet
Ostm = New Adodb.Stream
Ors.save OSTM, AdpersistXML Traditional ADO and ASP support flow processing. Of course, in .NET, Web Services also supports streams, and Web Services provides much more features than traditional ADOs and ASPs. In fact, Web Services is a technology that processes data requests and transmits data in XML through HTTP. ADO can save XML in a stream, but this is the ADO later improvement measures (Afterthought). ADO.NET's DataSet object outputs its content to an XML file or stream via WriteXML and WriteXmlschema. Therefore, the SAVE method of the ADO's Recordset is improved to show its content to an XML, while the ADO.NET's DataSet has this feature. ADO.NET can not only save DataSet as XML and load DataSet from XML, and it uses its class XML structure to other purposes. For example, because the DataSet can be represented as an XML form, it can be easily passed between the physical layer and the logic interlayer. This means that XML can be transmitted over HTTP on a secure network, and can also be transmitted in the form of text-based XML. ADO.NET can operate in a non-connection state due to the built-in XML. Traditional ADO RecordSets can work both in connection states in non-connected state, depending on the following properties (such as: curSortype = AdopenStatic and CursorLocation = aduseclient), and in ADO.NET, the RowSet object is divided into a connection state (DataReader) And non-connections (Dataset). The process of connecting to create a connection is very similar in ADO and ADO.NET. First, declare your connection object, initialize, set its connection string, open it. As shown in Figure 1. The first example demonstrates how to use ASP and ADO to open a connection, the second example, the same work in ASP.NET and ADO.NET. The main difference between the ADO and ADO.NET established connections is that ADO uses a Connection object to complete all connections to various data sources, while ADO.NET uses different connection objects to represent connectors to different data sources. For example, ADO.NET contains a System.Data.sqlClient namespace, which contains all SQL Server dedicated ADO.NET objects (including SQLConnection objects). The SQLConnection object is specifically used to communicate with the SQL Server database, which is the fastest in touch with SQL Server. There is also a more common namespace system.data.oledb, which can communicate with all compatible OLE DB data source. Therefore, in ADO.NET, multiple data provider namespaces can be created to connect to specific data sources, making data access speed faster, higher efficiency, allowing each namespace to make full use of target data The function of the program. If a program must change the type of data provider at runtime, or depend on a data provider and the data provider does not contain a specific ADO.NET connection object, the program is best to use OLEDBCONNECTION.
Recordset to Recordset in Readers ADO, its behavior (function) varies depending on its property setting, and Recordset in ADO.NET is decomposed into many different objects and methods. Thus avoiding Recordset's large-scale disadvantages, focusing on each of the subjects, this decomposition in ADO.NET can significantly improve efficiency. The Recordset in the ADO can be a connection to a connection or a non-connected rowset. It can act as a forward-only only read-only cursor or allows forward movement of the line centralized position, movement, and in the middle. The ADO's Recordset allows data modifications to be completed directly in the database, allowing saving data to modify and sends to the database in batches. The problem is that the ADO's Recordset assumes too much function. ADO.NET is broken down into multiple objects to complete specific tasks, so ADO RecordSet is divided into DataSet objects, DataReader objects, DataAPter, and Command objects. The forward and read-only cursor requires that the data source is always connected. ADO.NET uses the DataReader object to ensure that it is in a connection when it is turned on. DataReader is written for a data provider such as SQL Server, Oracle, or a more common OLE DB data provider. Therefore, the SQLDataReader object can be connected to the SQL Server database and act as a Firehose cursor role in a large number of recording. SqlDataReader also has a fast-forward-only access function for query results. It retrieves a record from the query result of the database and remains connected to open status to successfully retrieve the next record. ADO.NET DataReader is extremely high due to all functions of Ado Recordset. The example in Figure 2 demonstrates how to implement an example of a forward-moving cursor in traditional ADO and ADO.NET. It is worth noting that in the ADO.NET, the Read method of the DataReader object is automatically moved to the next record. This avoids the appearance of the chance of death cycles due to negligence of the MoveNext method that calls ADO Recordset during the use of traditional ADOs. Another difference between ADO and ADO.NET is to fill the way forward cursor. All rows in ADO are included in the Recordset object, no matter what is or otherwise. The RecordSet object is opened via the RecordSet.open method or the EXECUTE method of the Connection or Command object. ADO.NET, there is a special method to provide the forward data to the DataReader: The ExecuteReader method of the Command object, which tells the command object to clearly propose DataReader objects to the data provider to process the result in an optimized forward manner. As shown in the examples. This command object has a method called ExecuteExmlReader, which is to tell the command object to generate a query result for XMLReader object processing. XmlReader objects can be used to convert and process XML query results, see the ASP.NET example below: 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 calling browser. However, XML can also accumulate and fluidize to the Response object. Some single-value commands are sometimes, most applications require information from gaining a single value from the database query. The standard practice in traditional ADO is to create a SQL statement and open a recordset that contains the results of the query. Because there is only one row in the result, it seems to be unnecessary. The following codes get a single value from the query: This example gets the number of rows in the ORDERS table: '' '- ASP AND ADO
SET ORS = Server.createObject ("AdoDb.Recordset")
Ors.ActiveConnection = OCN
Ors.open "Select Count (*) as Irowcount from Orders
iCount = Ors.fields ("irowcount"). Value If you just want to get only one line of information, the ADO.NET introduces a new way to get a single value from the query. Using the ExecuteScalar method in the ADO.NET command object, it can return to the first row and column of the relevant query. Since you don't have to create a rowset, find the value, turn off the routine, so that the overhead is very small. The ExecuteScalar method is the best way to retrieve a single value. The following codes are completed in the same task, but the use of the ExecuteScalar method in ASP.NET and ADO.NET: String SSQL = "Select Count (*) as Irowcount from Orders;
SQLCommand OCMD = New SQLCOMMAND (SSQL, OCN);
Ocmd.commandtype = commandtype.text;
Int iCount = (int) Ocmd.executeScalar (); another method for acquiring single value is to use the output parameters of the stored procedure. This technology also needs to retrieve a large number of values. ADO and ADO.NET can be used, different, ADO.NET expands the functionality of output parameters. In order to get the value of the output parameter from a command object in ADO.NET, use the ExecuteNonquery method to perform query statements. This method tells ADO.NET, which does not return a rowset, so it can avoid the development of DataSet or DataReader: Ocmd.executenonQuery ();
Ocmd.Updatedrowsource = updaterowsource.outputParameters;
