Data Points
Migrating from ADO to ADO.NET, Part 2
John Papa
Code download available at:DataPoints0408.exe(111 KB)
Contents
Recordset Divergence
Firehose Cursors
Data-Sensitive Cursors
Server-Side Cursors and Concurrency
Batch Updates
Disconnected Rowsets
As you move forward with your use of ADO.NET, you'll need to know how to approach situations that you previously learned to handle in ADO and now have to tackle with ADO.NET. Just as n-tiered solutions developed using Visual Basic®, C++, and ASP often rely on ADO for their data access needs, Windows® Forms, Web Forms, and Web services rely on ADO.NET. Last month's Data Points column discussed how to tackle several data access scenarios using ADO.NET from the perspective of developing with traditional ADO (see Data Points: Migrating from ADO to ADO.NET). Some of these topics include persisting rowsets to XML, dealing with forward-only firehose cursors, and the multiple ways that Command objects can be executed.
In this month's column I will continue to discuss development situations using ADO.NET and how they are handled with traditional ADO techniques. I will start by discussing situations in which forward-only, static, keyset, and dynamic cursors of classic ADO were commonly used. I will also discuss how concurrency issues are handled and how disconnected rowsets have evolved from ADO to ADO.NET. Then, I will demonstrate how to take code that handles batch updates in traditional ADO and translate that to work with ADO.NET using the DataAdapter and its four command objects.
Recordset Divergence
Most of the ADO Recordset's features are split into three key objects in ADO.NET: the DataReader, the DataSet, and the DataAdapter (see Figure 1).
Figure 1** The ADO Recordset **
The ADO.NET DataReader object is designed to be a server-side, forward-only, read-only cursor. The ADO.NET DataSet object is a disconnected storage tool for rowsets. It stores records without holding a connection to a data source and, in fact, it does not care from what data source its rowsets are derived. The DataSet is a binary object when stored in memory, but it can also be serialized easily to and from XML. It is similar to the ADO Recordset object with its CursorType set to adOpenStatic and CursorLocation set to adUseClient, when it has been disconnected from its associated Connection object (via the Recordset's ActiveConnection property). The ADO.NET DataAdapter object is a bridge between the connection and DataSet objects. It can load a DataSet from a data source using a connection and it can update a data source with the changes stored in a DataSet. The behavior of the ADO Recordset depends on its property settings (including the CursorType and CursorLocation properties). In ADO.NET there are separate objects built to handle these specific situations instead of requiring one object to fit all scenarios.
Firehose Cursors
Traditional ADO exposes four different types of cursors that change how the ADO Recordset object functions. An ADO Recordset object behaves very differently depending on how its CursorType property is set. For example, by setting the CursorType to adOpenForwardOnly, the Recordset remains connected to its data source and must be traversed in a forward-only direction. However, when you set the CursorType property to adOpenDynamic, the Recordset can be traversed forward or backward, or you can even jump the cursor to a particular row. Through its CursorType and CursorLocation properties, the ADO Recordset object takes the approach of wrapping many solutions into a single object. ADO.NET takes a different approach where it has distinct objects and methods that are designed to handle specific situations.
In classic ADO forward-only, read-only cursors are implemented by setting the CursorType to adOpenForwardOnly and the CursorLocation to adUseServer (which are also the default settings). This makes the Recordset object take the form of a forward-only, server-side cursor. The MoveNext method repositions the Recordset to the next row and the MovePrevious method is not allowed at all. You can, however, invoke the MoveFirst method on a Recordset. It is a bit misleading, however, as it does not reposition itself to the beginning of the current rowset. Rather, it invokes the original SQL statement and refills the Recordset from scratch, thus moving to the first record again.
This can easily be seen by opening the SQL Profiler tool and watching the SQL execute every time the MoveFirst method is executed on a traditional ADO Recordset with a CursorType of adOpenForwardOnly. This type of cursor is commonly used in applications where thousands (or more) rows need to be traversed one at a time or when a smaller rowset is required but only needs to be traversed once, perhaps to be loaded into a pick list:
'-- Forward-only Firehose Cursor in ASP and ADO Set oRs.ActiveConnection = oCn oRs.LockType = adLockReadOnly oRs.CursorType = adOpenForwardOnly oRs.CursorLocation = adUseServer oRs.Open sSQL
The closest equivalent to this type of traditional ADO firehose cursor is the ADO.NET DataReader object. Just like the traditional forward-only ADO Recordset, the DataReader remains connected to its data source while it is open and can be traversed only in a forward direction. However, there are differences. One is that individual DataReader types are written specifically for a data provider such as SQL Server™ (the SqlDataReader class) or an ODBC data source (the OdbcDataReader class). The ADO.NET DataReader object is very efficient as it is built specifically for the purpose of implementing forward-only, read-only cursors. The classic ADO forward-only cursor is implemented through the same Recordset object as a disconnected Recordset or even as a data source-sensitive Recordset. The DataReader is designed solely to be a lean firehose cursor.
' ASP.NET and ADO.NET in Visual Basic .NET Dim oDr As SqlDataReader = oCmd.ExecuteReader() // ASP.NET and ADO.NET in C# SqlDataReader oDr = oCmd.ExecuteReader();
For more information on the ADO.NET DataReader, see the June 2004 Data Points column.
Data-Sensitive Cursors
Forward-only cursors in traditional ADO are now handled by the DataReader object. But what happened to the keyset and dynamic server-side cursors (CursorType = adOpenKeySet and CursorType = adOpenDynamic) that are sensitive to changes in the underlying database? The current versions of ADO.NET do not expose a multidirectional, scrollable, updateable, server-side cursor. However, ADO.NET does provide ways around these types of server-side cursors since there are other recommended techniques that you should consider.
Outside of using the DataSet combined with the DataAdapter to retrieve and update data, there are some good alternatives to using scrollable server-side cursors on the client. For example, you could use stored procedures, which are very efficient at running server-side SQL processing. You could also retrieve the data through a server-side forward-only cursor via a DataReader and then make separate updates via command objects. However, often there are more efficient ways to make data modifications than using updateable server-side cursors.
One of the issues with server-side scrollable cursors in an n-tiered environment is that they require state to be held on the server. Therefore, if an application used a server-side scrollable cursor in the middle tier, the client tier would need to maintain a connection to the business tier where the scrollable cursor resides. Thus, the business objects might linger for the life of the client application's screen that uses the scrollable cursor as well. These scalability issues are well documented, but, of course, there are situations in which server-side cursors are valuable, such as when the need for a forward-only firehose cursor exists. For example, server-side cursors have been put to work when an application wants to be made aware of data concurrency issues.
Using traditional ADO, a Recordset could be opened using a dynamic cursor which would allow the Recordset to see all inserts, changes, and deletions by other users. This type of server-side cursor is sensitive to changes made by other users and can be used in applications to take a proactive approach when concurrency issues arise. For example, dynamic cursors in traditional ADO were often used when an application was about to save changes to the database but wanted to make sure it knew if anyone else made changes to the same record first. When a user changed a value of a record that was in a dynamic Recordset, the value was automatically updated in the server-side dynamic Recordset:
'-- Dynamic Cursor in ASP and ADO Set oRs.ActiveConnection = oCn oRs.LockType = adLockOptimistic oRs.CursorType = adOpenDynamic oRs.CursorLocation = adUseServer oRs.Open sSQL
Server-Side Cursors and Concurrency
Concurrency problems are valid and common problems in many enterprise applications; however, the cost of using server-side cursors to accomplish this can be too high. So what alternatives exist in ADO.NET to deal with concurrency issues? A common technique is to allow the application to submit the changes to the database and have it throw a special type of exception when it sees a concurrency issue.
There is a special type of exception called a DBConcurrencyException which derives from the base Exception class. Because the DataSet stores the original and proposed values of each column in a row, it knows how to compare the values to the database to look for concurrency issues automatically. For example, assume that a user changes the value of the firstname field from Lloyd to Lamar and submits the changes to the database. The changes, which are stored in a DataSet, are passed on to the database via the DataAdapter's Update method. Before the data is actually saved, if the name in the database is no longer Lloyd but is now Lorenzo, a DBConcurrencyException exception will be thrown. This exception can be caught and handled appropriately in whatever way suits your application's needs best (you could, for example, enforce a "last in wins" rule, giving the user the option to cancel the changes, override, or force the update anyway, or some other technique).
In the code in Figure 2 you'll notice a sample method that accepts a DataSet and submits the changes in the DataSet to the database via a DataAdapter's Update method. (The stored procedures and the code from this figure can be downloaded from the MSDN®Magazine Web site.) If a concurrency issue is detected, the DBConcurrencyException exception will be thrown and caught by the specific catch block. At that point, the transaction could be rolled back and then the exception could be rethrown to eventually be caught by the client application so that the user could be notified and asked how they want to deal with it. Also, consider the order of the catch blocks carefully. For example, if the generic catch block appeared first, then it would have trapped the concurrency issue. A key to exception handling is to make sure that the catch blocks for the more specific exceptions appear before other catch blocks so that they are caught first.
Figure 2 Trapping Concurrency Issues in ADO.NET
' Visual Basic .NET Public Function SaveData(ByVal oDs As DataSet) As DataSet Dim sMethodName As String = "[public void SaveData(DataSet oDs)]" '========================================================== '-- Establish local variables '========================================================== Dim sProcName As String Dim sConnString As String = "Server=(local);Database=Northwind;" & _ "Integrated Security=SSPI" Dim oDa As SqlDataAdapter = New SqlDataAdapter Dim oTrn As SqlTransaction = Nothing Dim oCn As SqlConnection = Nothing Dim oInsCmd As SqlCommand = Nothing Dim oUpdCmd As SqlCommand = Nothing Dim oDelCmd As SqlCommand = Nothing Try '========================================================== '-- Set up the Connection '========================================================== oCn = New SqlConnection(sConnString) '========================================================== '-- Open the Connection and create the Transaction '========================================================== oCn.Open() oTrn = oCn.BeginTransaction() '========================================================== '-- Set up the INSERT Command '========================================================== sProcName = "prInsert_Order" oInsCmd = New SqlCommand(sProcName, oCn, oTrn) oInsCmd.CommandType = CommandType.StoredProcedure oInsCmd.Parameters.Add(New SqlParameter("@sCustomerID", _ SqlDbType.NChar, 5, "CustomerID")) oInsCmd.Parameters.Add(New SqlParameter("@dtOrderDate", _ SqlDbType.DateTime, 8, "OrderDate")) oInsCmd.Parameters.Add(New SqlParameter("@sShipCity", _ SqlDbType.NVarChar, 30, "ShipCity")) oInsCmd.Parameters.Add(New SqlParameter("@sShipCountry", _ SqlDbType.NVarChar, 30, "ShipCountry")) oDa.InsertCommand = oInsCmd '========================================================== '-- Set up the UPDATE Command '========================================================== sProcName = "prUpdate_Order" oUpdCmd = New SqlCommand(sProcName, oCn, oTrn) oUpdCmd.CommandType = CommandType.StoredProcedure oUpdCmd.Parameters.Add(New SqlParameter("@nOrderID", _ SqlDbType.Int, 4, "OrderID")) oUpdCmd.Parameters.Add(New SqlParameter("@dtOrderDate", _ SqlDbType.DateTime, 8, "OrderDate")) oUpdCmd.Parameters.Add(New SqlParameter("@sShipCity", _ SqlDbType.NVarChar, 30, "ShipCity")) oUpdCmd.Parameters.Add(New SqlParameter("@sShipCountry", _ SqlDbType.NVarChar, 30, "ShipCountry")) oDa.UpdateCommand = oUpdCmd '========================================================== '-- Set up the DELETE Command '========================================================== sProcName = "prDelete_Order" oDelCmd = New SqlCommand(sProcName, oCn, oTrn) oDelCmd.CommandType = CommandType.StoredProcedure oDelCmd.Parameters.Add(New SqlParameter("@nOrderID", _ SqlDbType.Int, 4, "OrderID")) oDa.DeleteCommand = oDelCmd '========================================================== '-- Save all changes to the database '========================================================== oDa.Update(oDs.Tables("Orders")) oTrn.Commit() oCn.Close() Catch exDBConcurrency As DBConcurrencyException '======================================================= '-- Roll back the transaction '======================================================= oTrn.Rollback() '-------------------------------------------------------- '-- May want to rethrow the Exception at this point. '-- This depends on how you want to handle the concurrency '-- issue. '-------------------------------------------------------- '-- Throw exDBConcurrency Catch ex As Exception '========================================================== '-- Roll back the transaction '========================================================== oTrn.Rollback() '-------------------------------------------------------- '-- Rethrow the Exception '-------------------------------------------------------- Throw Finally oInsCmd.Dispose() oUpdCmd.Dispose() oDelCmd.Dispose() oDa.Dispose() oTrn.Dispose() oCn.Dispose() End Try oCn.Close() Return oDs End Function // C# public DataSet SaveData(DataSet oDs) { string sMethodName = "[public void SaveData(DataSet oDs)]"; //========================================================== //-- Establish local variables //========================================================== string sProcName; string sConnString = "Server=(local);Database=Northwind;Integrated" + "Security=SSPI"; SqlDataAdapter oDa = new SqlDataAdapter(); SqlTransaction oTrn = null; SqlConnection oCn = null; SqlCommand oInsCmd = null; SqlCommand oUpdCmd = null; SqlCommand oDelCmd = null; try { //========================================================== //-- Set up the Connection //========================================================== oCn = new SqlConnection(sConnString); //========================================================== //-- Open the Connection and create the Transaction //========================================================== oCn.Open(); oTrn = oCn.BeginTransaction(); //========================================================== //-- Set up the INSERT Command //========================================================== sProcName = "prInsert_Order"; oInsCmd = new SqlCommand(sProcName, oCn, oTrn); oInsCmd.CommandType = CommandType.StoredProcedure; oInsCmd.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5, "CustomerID")); oInsCmd.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate")); oInsCmd.Parameters.Add(new SqlParameter("@sShipCity", SqlDbType.NVarChar, 30, "ShipCity")); oInsCmd.Parameters.Add(new SqlParameter("@sShipCountry", SqlDbType.NVarChar, 30, "ShipCountry")); oDa.InsertCommand = oInsCmd; //========================================================== //-- Set up the UPDATE Command //========================================================== sProcName = "prUpdate_Order"; oUpdCmd = new SqlCommand(sProcName, oCn, oTrn); oUpdCmd.CommandType = CommandType.StoredProcedure; oUpdCmd.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); oUpdCmd.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate")); oUpdCmd.Parameters.Add(new SqlParameter("@sShipCity", SqlDbType.NVarChar, 30, "ShipCity")); oUpdCmd.Parameters.Add(new SqlParameter("@sShipCountry", SqlDbType.NVarChar, 30, "ShipCountry")); oDa.UpdateCommand = oUpdCmd; //========================================================== //-- Set up the DELETE Command //========================================================== sProcName = "prDelete_Order"; oDelCmd = new SqlCommand(sProcName, oCn, oTrn); oDelCmd.CommandType = CommandType.StoredProcedure; oDelCmd.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); oDa.DeleteCommand = oDelCmd; //========================================================== //-- Save all changes to the database //========================================================== oDa.Update(oDs.Tables["Orders"]); oTrn.Commit(); oCn.Close(); } catch (DBConcurrencyException exDBConcurrency) { //======================================================= //-- Roll back the transaction //======================================================= oTrn.Rollback(); //-------------------------------------------------------- //-- May want to rethrow the Exception at this point. //-- This depends on how you want to handle the concurrency //-- issue. //-------------------------------------------------------- //-- throw(exDBConcurrency); } catch (Exception ex) { //========================================================== //-- Roll back the transaction //========================================================== oTrn.Rollback(); //-------------------------------------------------------- //-- Rethrow the Exception //-------------------------------------------------------- throw; } finally { oInsCmd.Dispose(); oUpdCmd.Dispose(); oDelCmd.Dispose(); oDa.Dispose(); oTrn.Dispose(); oCn.Dispose(); } oCn.Close(); return oDs; }
Batch Updates
One situation in which the ADO Recordset object is commonly used is to make batch updates. For example, in an n-tier application, an ADO Recordset could retrieve a rowset, disconnect itself from the data source, and send the Recordset to the client tier. In the client application, changes could be made to several rows in the disconnected ADO Recordset. Then the Recordset could be sent back to the middle tier, reconnected to the database via an ADO Connection object, and its changes could be applied to the database via the UpdateBatch method. The UpdateBatch method takes all of the changed rows in the Recordset and applies the changes to the source table that is indicated in the Recordset's source query.
ADO.NET also has built-in abilities to handle batch updates. The DataSet is a self-contained, always disconnected collection of rowsets which stores both the original and current values of its rowsets rows and columns. A DataSet can be sent to a client application where changes could be made to it. The DataSet could then be sent to the middle tier where its changes could be applied to the database via a DataAdapter object.
Figure 2 shows how the ADO.NET DataAdapter can use its various Command objects to represent the SELECT, INSERT, UPDATE, and DELETE SQL commands:
' Setting the ADO.NET DataAdapter's command objects in Visual Basic .NET oDa.InsertCommand = oInsCmd oDa.UpdateCommand = oUpdCmd oDa.DeleteCommand = oDelCmd oDa.Update(oDs.Tables("Orders")) // Setting the ADO.NET DataAdapter's command objects in C# oDa.InsertCommand = oInsCmd; oDa.UpdateCommand = oUpdCmd; oDa.DeleteCommand = oDelCmd; oDa.Update(oDs.Tables["Orders"]);
The ADO.NET technique allows greater flexibility than the traditional ADO batch update technique regarding how it can apply the updates to the database. Traditional ADO decides where to save the changes by examining the Source property of the ADO Recordset. For example, assume the source of the Recordset is:
SELECT OrderID, CustomerID, OrderDate, ShipCity, ShipCountry FROM Orders
In this case, after batch changes have been made to the Recordset and the Recordset's UpdateBatch method is invoked, the Recordset has to infer where to send the changes. It looks at the source's SQL statement and determines that the primary key (OrderID) of the Orders table is in the statement and there is only one table in use. Thus, it can use the SQL statement to create UPDATE, INSERT, and DELETE statements to hit the Orders table. In order for the Recordset to derive the action queries, it has to know the unique row identifier. Also, stored procedures cannot be used with this technique to update the database since the statements are implicitly created. The following ADO 2.x code shows how the Recordset containing Orders could be updated in a client application:
'-- Using traditional ADO, '-- Updating two rows in the client tier oRs.Find "CustomerID = 'VINET'" oRs.Fields("ShipCity") = "Somewhere" oRs.Update oRs.Find "CustomerID = 'CHOPS'" oRs.Fields("ShipCity") = "Elsewhere" oRs.Update
Then, once the Recordset is passed back to the middle tier, the following code snippet could be invoked to apply the changes you've made back to the database:
'-- Using traditional ADO, '-- Sending both updated rows to the database, in the business tier oRs.UpdateBatch
The ADO.NET CommandBuilder works similarly in that it can automatically generate the UpdateCommand, InsertCommand, and DeleteCommand objects for the DataAdapter. The process of automatically generating the commands comes with overhead. It is more efficient to explicitly specify the INSERT, UPDATE, and DELETE statements that you want to use. The use of the ADO.NET CommandBuilder means slower performance and less control over how changes are applied to a data source compared to explicitly indicating the commands.
The exact SELECT, INSERT, UPDATE, and DELETE statements are generally known at design time and can be used to populate the ADO.NET DataAdapter's four separate Command properties. They can even be represented by the name of a stored procedure. This feature of ADO.NET was one of the key pieces missing in traditional ADO that would have made its batch update techniques much more flexible. This requires a bit more coding at design time, but it's worth the effort to get the flexibility it offers in allowing you to specify very specific SQL statements, or more often stored procedures for each of the operations.
Disconnected Rowsets
The ADO Recordset object can be disconnected from its data source by setting some of its properties appropriately. By being disconnected, it can store the entire rowset in memory and be passed around between applications that use traditional ADO. The following code sample will disconnect a Recordset:
'-- Disconnecting an ADO Recordset oRs.CursorLocation = adUseClient oRs.CursorType = adOpenStatic oRs.LockType = adLockBatchOptimistic oRs.Open Set oRs.ActiveConnection = Nothing
The key properties involved in disconnecting a Recordset are the CursorType and CursorLocation. The CursorLocation must be set to adUseClient, which signifies that the rowset should be stored within the Recordset's memory. The CursorType should be set to adOpenStatic, which allows the rowset's cursor to move in any direction but does not allow the rowset to be automatically sensitive to changes in the underlying database. By combining these two settings, the Recordset can become disconnected. However, the final step is yet to come. Once the Recordset has been opened, for it to truly become disconnected, its ActiveConnection property should be set to the keyword Nothing, as you saw in the last code sample.
The ADO Recordset was built to work in both disconnected and connected modes. However, in ADO.NET the DataReader remains connected while the DataSet is entirely disconnected. The DataSet is the successor to the disconnected ADO Recordset as it implements a client-side cursor that can be scrolled in any direction. In addition, the ADO.NET DataSet supports many additional features. The traditional ADO Recordset object provides very limited disconnected features while the ADO.NET DataSet object is designed to be disconnected. Unlike the Recordset, the DataSet can store and represent multiple rowsets within DataTable objects and even relate them to one another using DataRelation objects. Here's a common way to create and populate a DataSet using a SqlDataAdapter's Fill method:
' Creating and filling an ADO.NET DataSet in Visual Basic .NET Dim oDs As New DataSet("MyDataSet") oDa.Fill(oDs) // Creating and filling an ADO.NET DataSet in C# DataSet oDs = new DataSet("MyDataSet"); oDa.Fill(oDs);
The DataSet can enforce relations, primary key constraints, and foreign key constraints, and it can even implement column expressions. The ADO Recordset also has the ability to return hierarchical results using its data shaping features. While these hierarchical features are available, they require the use of what I believe is an awkward data-shaping syntax. The ADO.NET DataSet is more efficient and more intuitive in this respect because it is built to work with relational data structures using its DataRelation and DataTable objects.
Over the past two columns I have discussed several techniques commonly used in traditional ADO 2.x development and how to translate them to ADO.NET. ADO.NET is the next step in the evolution of ADO 2.x and many features found in traditional ADO have been entirely revamped in ADO.NET. For example, while features were added in ADO 2.x to the Open and Save methods in order to support XML, XML support was in the blueprints for ADO.NET from the beginning. Some features of ADO have changed considerably. One such change is that the Recordset's many cursor types are split into many distinct and focused objects such as the DataReader, the DataSet, and the DataAdapter. Other features such as the connection and command classes have changed in less obvious ways. However, in the end, the migration path from ADO to ADO.NET is relatively straightforward as ADO.NET is designed to build upon your experience with traditional ADO.
Send your questions and comments for John to mmdata@microsoft.com.
John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive.