VB Toolkit: Saving and Restoring Recordsets

Add this XML method to your toolkit

Disconnected recordsets let you perform many tasks. You can use disconnected recordsets to build high-performance applications or to move data. Microsoft increased recordsets' functionality by adding Extensible Markup Language (XML) features that work with ADO recordsets.

To demonstrate one use of disconnected recordsets, here's a small Visual Basic (VB) application that has real-world implications. Subscribers can download the code for this project from http://www.sqlmag.com at the link to this article.

Imagine that your company's salespeople need an application to let them select customer records, save records on their laptops, and work with the records while they're on the road. Then, when they return from their trip, they need to upload the data to the SQL Server database.

Screen 1 shows a simple application that demonstrates the concepts involved in accomplishing this task. The application lets a user select a customer name and save the resulting recordset to disk as an XML document. This method is handy because it lets the user save any customer files to disk and retrieve them later.

Before you begin creating code for this application, you need to configure a Data Source Name (DSN) to use with the Northwind database, which Microsoft ships with SQL Server. The sample code uses the sa login name and a blank password. Northwind is the DSN the sample uses.

The first step in creating this application is to build a general-purpose database module. Listing 1, page 68, shows the code for the module. The GetDSN function supplies the connection information to other parts of the application. GetDSN hides much of the connection complexity in one place and makes the code easy to change by putting all the DSN information in the application together.

The sample code uses the ADODB Recordset object, as Callout A shows. You can also use the ActiveX Data Objects Recordset (ADOR), but be aware that the Microsoft article "Maintaining Binary Compatibility in Components with ADO" (at http://support.microsoft.com/ support/kb/articles /Q216/3/89.ASP) states that "ADOR is now only a sub to the MSADO15.DLL and exists only to maintain backward compatibility." I chose ADODB because it provides the functionality I need and, as Microsoft's scalability test (http://msdn.microsoft.com/vstudio /downloads/scale/pcweek.asp) shows, ADODB is extremely fast.

You can create the RunWithRS and RunWithRS _RW functions, which take a SQL statement as a parameter and execute it, then pass the resulting recordset back as the function's return value, as Callout B in Listing 1 shows. Creating functions such as these hides the ADO complexity and standardizes the ADO work in the application. The recordset that each function passes back lets the calling application work with the recordset, but removes the need for the application to create the recordset or the ADO objects. This function reduces the complexity of the application's code and localizes all the database work.

Next, create the form, as Listing 2, page 69, shows. The code in Callout A establishes the dimensions of global variables that the form uses, including rsCustomerDetail, which is set to an ADODB. Recordset type, and the sSQL variable, which holds the application's SQL statements.

Callout B in Listing 2 shows the first line of code in the Form Load event. This Dim statement establishes the dimensions of the local rsCustomer variable, which is set to an ADODB.Recordset type. The Form Load procedure uses only the rsCustomer variable to create the list of customers. The rsCustomer variable is created separately from the rsCustomerDetail recordset global variable to minimize confusion between these two variables.

The code in Callout C creates the SQL statement, then calls the RunWithRS function to execute the statement. The return value of the function is an ADO recordset, and as with any object reference, you need to use the Set statement to retrieve the return value of RunWithRS.

The next line of code, in Callout D, verifies that rsCustomer is an object. If RunWithRS fails, this statement doesn't return a valid recordset and rsCustomer isn't a recordset object, which is why the first IF statement checks rsCustomer with the IsObject function. The next line of code checks the recordset's EOF property to make sure a record is current. If the SQL statement executes cleanly in RunWithRS but doesn't return any records, you might receive an empty recordset. After the code checks the rsCustomer and EOF property, the code loops through the recordset and loads the combo box, which displays the Customer File list.

When the user selects a customer from the list, the code in Callout E executes, creating a new SQL statement with the customer's name in the WHERE clause and executing the statement with RunWithRS. Then the code extracts several fields from the recordset and places them in the text boxes on the form. Note that the recordset isn't closed or destroyed at this point.

The Save Customer button in Screen 1 executes the code in Callout F. This code creates a filename from the customer name you select in the combo box. For example, if you select Ken Spencer from the Customer Files list, the resulting filename is Customer-Ken Spencer.xml.

The next line of code passes the filename Ken Spencer.xml to the Save method of the recordset. The Save method's second parameter is PersistFormat, which specifies the format of the saved recordset. The adPersistADTG value is the default value, which saves the recordset in Advanced Data Tablegram format. The adPersistXML constant saves the data in XML format. Now, what can you do with this saved recordset?

Look at Screen 1 again. The Refresh Customer Files button executes the code at Callout G, which looks in the c:Temp directory for any XML files. This code loads XML filenames into the Customer Files list combo box at the bottom of the screen. Now the user can load a recordset from any entry in the Customer Files list. When the user selects a customer file from the list, the code in Callout H opens the file.

The first executable line of code at Callout H creates an ADO Recordset object and assigns it to rsCustomerDetail. You need to assign the ADO Recordset object in this part of the code, because the application uses the rsCustomerDetail object and loads it with the recordset in the file. The code combines the directory name with the filename from the Customer Files list to create the filename in the next line. Finally, the next line opens the recordset. The Open method used here looks like an Open method to open an ODBC connection except that it passes in the file path as the first argument instead of using a command object or SQL statement. The last parameter is the Options for Open and, in this case, is set to adCmdFile, which tells the Open command that it's opening a file.

After the file is open, the recordset works as any ADO recordset does. The code in Callout H loads the text boxes in the same manner as in Callout E.

The portability of XML and other saved recordsets gives you many options to build applications. You might find this method of saving and restoring recordsets with XML and ADO worth adding to your toolkit.