Delivering Pizza with a Dump Truck | Ado Examples and Best Practices

Team-Fly

Developers trying to move data from one place to another are confronted with a variety of choices. Usually the "places" include a component run on a server somewhere and a remote client application. As I see it, developers face a number of problems when trying to solve this problem.

The first is what delivery vehicle should be used. All too often, we choose an oversized delivery vehicle for the situation—like delivering pizza with a giant truck. It works, but it scares the customers, not to mention the neighbor's dog.

The second problem is that we tend to return far more data than the customer can consume before it starts getting cold. This is like delivering the pizzas you anticipate the customer will order next week.

The third problem relates to the others, but is a little more complex. Developers, and the interfaces we choose, have started packaging the data in wrappers that describe the information down to the finest detail. This is like getting our pizza in a package that describes the crust and each mushroom, pepperoni, and anchovy, and explains how the ingredients were gathered, processed, and prepared. This technique is most useful when you need to deliver pizza to space aliens who might not know what they are getting, and who need to know how to consume it.

So, let's take a longer look at these problems and your choices for solving them. First, consider the type of data you want to move. If you simply want to pass something simple back to the client, consider a simple approach. That is, if you only want to pass back a string of characters, a "yes," a "no," or a number, then you don't really need a complex data structure to do so. Yes, more complex data structures are handy if the client needs to know how to deal with the data and does not really know what to expect. They are also handy if you want the other end to handle the updating of the data for you. For example, if your customers tell you to deliver something hot and Italian, you might want to include a description and reheating instructions, as well as legal disclaimers—unless you send out a sweaty tenor.

So, if you need an ADO Recordset object to define the data being transmitted, use a Recordset. But you also have other alternatives that might be far easier to construct, transmit, and parse on the other end. For example, Visual Basic has a number of new extensions that know how to deal with delimited strings. A typical Recordset can take 100K to store a few rows, while a delimited string contains very little overhead—one to three extra characters per field, and two extra characters per row.

Transmitting a Recordset means that your client can use ADO to move around in the rows, and to filter, sort, and find specific rows in the data after it arrives. But these approaches begin to smack of the second problem. Why return data that the customer will discover is cold and stale when the customer finally gets to it? When working with static data (with a long shelf life), sending out extra data can seem like a good idea, but if the client never uses it, you have wasted network, server, and client resources gathering, packaging, and transmitting it. Many more successful applications have found that transmitting rows "just in time" and "only as needed" works better than simply dumping 100 boxes of hot pizza on the doorstep.

The following text is an example of a delimited string result set. I replaced the tab characters with the text "<tab>" for readability. As you can see, all we get here is data. This approach assumes you know what the data you asked for is going to look like.

 3<tab>Boddie, John<tab>1947<tab>0-1355423-9-1<tab>1996<tab>0-1355423-9- 1<tab>Managing a Programming Project : People and Processes 244<tab>Vaughn, "Wm"<tab>1947<tab>1-5561590-6-4<tab>1995<tab>1-5561590-6-4    <tab>Hitchhiker's Guide to Visual Basic & Sql Server

When "Hold the Anchovies" Isn't Enough

Sometimes the client needs to be able to change the data and sometimes (but not nearly always) an updatable cursor is called for. In this case, the ADO Recordset seems to fit the bill. In many respects, the ADO Recordset object is really quite sophisticated. It contains a wealth of information about the data and where it came from. Some of this information can be essential when it comes time to update the data in the original source. RDO (rdoResultset) and ADO Recordset objects also support the concept of "multiple" resultsets, so you can execute a complex stored procedure and return 1, 2, or 20 resultsets, and use an automorphing Recordset object to refer to the first and to each subsequent result set. The ADO Recordset object also supplies a bevy of properties that describe a number of complex aspects of the data structure, often making it possible to identify the root source of the data.

However, some applications need something even more generic and less binary than an ADO Recordset, especially when updatability is not that important. That's where XML comes in. XML is touted as the new universal format for data on the Web. XML allows developers to describe and deliver rich, structured data from any application in a standard, consistent way. XML is an internationally recognized format for result set (schema + data) persistence. Not only does XML define the data and its structure (even hierarchical or multiple rowset data), but it can persist changes to the data as well. Well-formed XML can be read by anyone, in any language, anywhere in the world—perhaps even on other worlds. Simply put, XML is yet another way to pass data from one point to another and carry with it some of the information you need to know about the dataset to view it, change it, and manage it. You don't need ADO to decipher an XML data structure, just an XML parser. No, there's nothing built-in in an XML data structure that tells you where the data came from, just what it looks like, so it is tough to create an update statement based simply on an XML dataset. In addition, you can't tell that the data was drawn from Table X or Stored Procedure Y.

When working with ADO, you can save Recordsets to an XML stream (in ADO 2.5) or to a file (in ADO 2.1 or later). ADO's adPersistXML format, aka XMLData is also referred to as a canonical format. So, the XML structure could look like the data shown below or any other well-formed XML. However, ADO understands how to read well-formed XML—regardless of the source.

XML data structures are broken into sections that first define the data rows—at least the names of the fields (columns) and their datatypes. This is the </s:Schema> section. The following section, </rs:data>, contains the data itself. Each and every field is tagged, so the data structure repeats the field names over and over—once in each row as shown here:^[2]

 <rs:data> <z:row Au_ Author="Vaughn, William" Year_Born="1947" ISBN="1-5561590-6-4" Year_Published="1995" Expr1="1-5561590-6-4" Title="Hitchhiker's Guide to Visual Basic & Sql Server — Fourth Edition (Book and Disk)" /> <z:row Au_ Author="Viescas, John" Year_Born="1947" ISBN="1-5561519-8-5" Year_Published="1989" Expr1="1-5561519-8-5" Title="Quick Reference Guide to Sql" /> <z:row Au_ Author="Viescas, John" Year_Born="1947" ISBN="1-5561523-7-X" Year_Published="1990" Expr1="1-5561523-7-X" Title="dBASE IV (Programmer's Quick Reference Series)" </rs:data>

This permits a degree of flexibility in that you can define an XML dataset that could potentially contain different fields for each row, but multiple resultsets are not (yet) supported by Visual Basic or ADO.

It is important to note that in the schema section, ADO stores enough information to build the right UPDATE statements for an UpdateBatch—this enables you to modify the Recordset and, if necessary, send it back to the server to have the changes committed, after setting the ActiveConnection back, of course. In reality, an XML-persisted Recordset is still a Recordset with nothing left out.

My point is that we developers need to build solutions using the right packaging, with the right contents, with the right amount of useful information. Don't send more than is needed—no more fields or DDL or rows than the customer can consume at a sitting. This means that you should use a bicycle when the pizza place is close, and hold the anchovies.

^[2]While this may seem scandalous regarding the amount of network traffic generated, XML's main transport is intended to be HTTP, which, since version 1.1, has native compression that greatly reduces the overhead of these repeated tags.

Team-Fly