When to Use Strongly Typed DataSet Objects

A History Lesson

Before we look at how to submit pending changes using ADO.NET, let's take a look at how the process works in ADO.NET's predecessor, ADO. ADO.NET will not automatically generate updating logic for you, but ADO does. By taking a cursory look at how the ADO cursor engine submits updates "automagically," we'll gain insight into how and why the ADO.NET development team chose to go a different route by pushing developers towards writing their own updating logic. Understanding how the ADO cursor engine submits your changes will also make it easier to understand how to generate your own updating logic in ADO.NET.

The ADO cursor engine supports functionality similar to the ADO.NET DataSet. You can use a client-side ADO Recordset object as an offline data cache. The Recordset object is also ADO's mechanism for submitting updates to the database.

The following code snippet retrieves the contents of the order we discussed earlier, modifies the contents of the order, and then submits those pending changes to the database:

Visual Basic "Classic" and ADO 2.x

Dim strConn As String, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _          "FROM [Order Details] WHERE OrderID = 10503 " & _          "ORDER BY ProductID" Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open strSQL, strConn, adOpenStatic, adLockBatchOptimistic,          adCmdText rs.Delete rs.MoveNext rs.Fields("Quantity") = 2 * rs.Fields("Quantity") rs.Update rs.AddNew rs.Fields("OrderID") = 10503 rs.Fields("ProductID") = 1 rs.Fields("Quantity") = 24 rs.Fields("UnitPrice") = 18 rs.Update rs.UpdateBatch rs.Close cn.Close

This code snippet demonstrates many of the benefits and drawbacks of submitting updates using a Recordset in the ADO object model, as I'll explain in the upcoming sections.

Benefits of Submitting Updates Using ADO Recordset Objects

The first major benefit of this approach is that it requires minimal code. You open the Recordset, modify its contents, and then submit the changes to your database. You can accomplish a lot of work in just a few lines of code.

The code has no updating logic because ADO generates this logic automatically at run time. That's the other major benefit. ADO does not require you to supply updating logic programmatically. In fact, someone with minimal knowledge of the SQL language can write this code: you can use the ADO cursor engine's updating features without any understanding of concurrency, locking, or how to generate a SQL UPDATE query. The fact that developers can build working data access applications without this knowledge is a testament to the technology's design. It continually amazes me—in a good way—that so many developers use the ADO cursor engine to submit updates but have no idea how the ADO cursor engine accomplishes this task.

Drawbacks of Submitting Updates Using ADO Recordset Objects

Unfortunately, the ADO cursor engine's updating features also have their drawbacks—less than stellar performance and a lack of control. Countless developers have relied on the ADO cursor engine to submit changes to their databases since the release of ADO 1.5, so these problems aren't monumental. However, they are significant. To better understand these drawbacks, let's take a quick look at how the ADO cursor engine submits the changes to the database.

When you call the UpdateBatch method of the Recordset object, the ADO cursor engine scans the Recordset for modified rows and translates the changes to each modified row into a SQL query that will modify the corresponding row in the database. Earlier, I made reference to developers generating their own SQL UPDATE, INSERT, and DELETE queries to modify the contents of a database. The ADO cursor engine builds similar statements.

You can use SQL Profiler to watch the SQL calls to your database. If you watch the queries that the ADO cursor engine generates to submit your changes, you'll actually see a call to the SQL Server sp_executesql stored procedure with a batch of parameterized queries. That stored procedure call is equivalent to the following queries:

DELETE FROM [Order Details] WHERE OrderID = 10503 AND ProductID = 14 UPDATE [Order Details] SET Quantity = 40     WHERE OrderID = 10503 AND ProductID = 65 AND Quantity = 20 INSERT INTO [Order Details] (OrderID, ProductID, Quantity, UnitPrice)     VALUES (10503, 1, 24, 18)

After you reexamine the initial query and the changes made to the Recordset in code, these queries should look relatively straightforward to you—that is, you can look at the queries and understand their purpose even if you're really not comfortable constructing such queries yourself. Translating the changes in the Recordset into SQL queries is pretty simple if you know where the data came from.

It's pretty obvious to us where the data came from, but how did the ADO cursor engine discover this information? When the ADO cursor engine fetched the results of your query, it also asked the database for additional metadata. To construct the UPDATE query shown earlier, the cursor engine needed to know the base table and column names for each of the columns in the result set, along with primary key information for the tables referenced by the query.

You can examine this metadata yourself by using the ADO Field object's Properties collection in code such as this:

With rs.Fields("Quantity")     Debug.Print "BaseTableName = " & .Properties("BaseTableName")     Debug.Print "BaseColumnName = " & .Properties("BaseColumnName")     Debug.Print "KeyColumn = " & .Properties("KeyColumn") End With

This brings us to the first major drawback of the ADO cursor engine's updating features: performance. The queries that the ADO cursor engine issues in order to gather table, column, and primary key data from the database constitute a significant performance hit. Most developers who write data access code know where the data comes from. Unfortunately, ADO offers no way to provide this metadata in code to save you from having to query the database for this information every time you open your Recordset.

The ADO cursor engine is a "black box" technology. It does not let you define your own updating logic. This is the second major drawback of the ADO cursor engine's updating features. Even though the ADO cursor engine's updating logic is impressive, it offers you little or no control over your updating logic. You cannot choose to submit the updates cached in your Recordset via stored procedure calls. If you don't like the updating logic that the ADO cursor engine generates, you're on your own.