Using Updatable Recordsets

[Previous] [Next]

Updatable Recordset objects are definitely a RAD concept. Forget about having to fetch data from a Recordset object and into your own data structures, such as Variant arrays, collections, business objects, user-defined types (UDTs), and eXtensible Markup Language (XML) tags! No longer do you have to create and call stored procedures or generate your own action queries! Say goodbye to writing a bunch of code! Decrease the amount of time required to build the database connectivity layer of your application! Why, you can even leverage a plethora of data-bound controls, which can reduce the time you spend developing the user interface layer!

I intentionally worded the previous paragraph to make updatable Recordsets sound as sensational as an infomercial would. (What scares me is that the spelling checker didn't blink at my use of "infomercial.") You might be wondering whether updatable Recordsets will let you scramble an egg while it's still inside its shell. Used properly, updatable Recordsets can save you more time than anything sold in an infomercial. Plus, there's no shipping or handling costs. And just look how this code can cut a tin can in half and still remain sharp enough to slice a tomato!

 strSQL = "SELECT * FROM Customers" Set rsCustomers = New ADODB.Recordset rsCustomers.Open strSQL, cnDatabase, adOpenKeyset, _ adLockOptimistic, adCmdText rsCustomers("BalanceDue").Value = rsCustomers("BalanceDue").Value + 50 rsCustomers.Update 

Somewhere between the marketing hype (marketers who say that you can build a multiuser database application in five minutes missed their calling as used car salespeople) and the stubborn and sometimes fanatical developers who'd rather perish than use someone else's code (trust no one…else's code) lies the middle ground where you'll find the truth. Let's take a brief look at updatable server-side and client-side Recordsets and examine their pros and cons.

Updatable Server-Side Recordsets

Chapter 7 covered keyset cursors and dynamic cursors—the types of server-side cursors that support updating. In this section, you'll see how to use the Recordset object to interact with these cursors.

When you modify the Value property of the various Field objects in your Recordset and then call the Recordset's Update method, ADO passes that information to the server-side cursor through the OLE DB provider or ODBC driver. But who updates the database? Basically, whoever manages the cursor handles that job.

Remember that when you work with SQL Server, the OLE DB provider and ODBC driver communicate with the database and allow you to access the data in the cursor. SQL Server actually manages the cursor. SQL Server also updates data in your database as you modify the contents of the cursor.

The Access OLE DB provider and ODBC driver manage the cursor and directly communicate with the database as well as let you interact with the cursor. Therefore, they're also responsible for updating your database based on the changes made to the cursor.

The Microsoft ODBC driver for Oracle exposes and manages the cursor. (Perhaps someday the related OLE DB provider will do this too.) As you change data in the cursor, the ODBC driver interprets these changes and tells the Oracle database server how to modify the underlying data in your database.

Pros and cons of using server-side Recordsets

What are the pros and cons of using server-side Recordsets to update your database? Let's look at the advantages first:

  • You don't have to write as much code as you would using action queries or stored procedures. You can use the Recordset object to interact with the server-side cursor that maintains the results of your query. You don't need to write code to determine how to update your database. Instead, you simply access and modify your data through the Recordset object.
  • You can take advantage of ADO features that apply to server-side cursors, such as searching and filtering.
  • If you're working with a SQL Server database, you're keeping less data in your application. The Recordset's CacheSize property controls how many records of data are cached in the ADO libraries.
  • You can use the features of a keyset cursor or a dynamic cursor.
  • You can take advantage of data-bound controls. Visual Basic, for example, ships with data-bound controls (text boxes, list boxes, combo boxes, and grids) that you can bind to your Recordset to decrease the amount of code you need to write.

Now let's examine the disadvantages of updating your database by using server-side Recordsets:

  • Server-side Recordsets require a live connection to your database. As you navigate through the Recordset, you will likely incur network round- trips—even if the data in your cursor has not changed.
  • Server-side Recordsets sometimes scale poorly in multiuser situations. (See the discussion of server-side cursors in Chapter 7.)
  • Because of the requirement of a live connection to your database, you cannot effectively use server-side Recordsets in a Component Services or Microsoft Transaction Server (MTS) component.
  • Because server-side Recordsets rely on the database system, the OLE DB provider, or the ODBC driver to actually update the database, you might need to develop code that is specific to the type of database you're using. For example, Microsoft Access and SQL Server do not handle optimistic updates from live cursors the same way.

Updatable Client-Side Recordsets

Client-side Recordsets offer a middle ground between action queries and server-side Recordsets. When it comes to modifying the Recordset object, client-side Recordsets behave the same as server-side Recordsets. However, instead of communicating with a cursor that's maintained by the database, the OLE DB provider, or the ODBC driver, ADO retrieves the results of your query and stores that data in its own Cursor Engine.

The data in your Recordset is static, meaning you won't see changes other users make to the data in the database. Once the ADO Cursor Engine has retrieved the results of your query, you can disconnect your Recordset and you won't need to communicate with your database until you're ready to modify its contents or retrieve more data.

The ADO Cursor Engine examines the changes you make to the Recordset and translates those changes into action queries. Based on the number of records modified by those action queries, the Cursor Engine determines whether the action query successfully updated the data in your database. The next few chapters will explore this process in much more depth.

Pros and cons of using client-side Recordsets

Now let's examine the advantages and disadvantages of using client-side Recordsets to update your database. First, we'll take a look at the advantages:

  • As with server-side Recordsets, the biggest benefit of using client-side Recordsets is that you don't have to write as much code as you would with action queries and stored procedures.
  • Since the ADO Cursor Engine maintains the Recordset results, client-side Recordsets scale better than server-side Recordsets.
  • ADO offers a great deal of functionality for client-side Recordsets: searching, filtering, sorting, batch updating, persistence, hierarchical Recordsets, and the ability to pass a client-side Recordset object across process boundaries.
  • As with server-side Recordsets, you can use data-bound controls.

There's only one significant drawback to using client-side Recordsets to update your database. By allowing the ADO Cursor Engine to maintain the results of your queries and update your database, you still give up some control.



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net