To communicate the concepts inherent in moving data sets around a tiered system, we define two new terms: transport and payload. A transport is the container inside which we pack data, schema information, and error information. The data, schema, and error information inside the transport are referred to as the payload. Therefore, we can say that a transport contains a payload.
There are many different options for defining transports and payloads. Some designs are purely object oriented (OO). In these designs, the transport might be an object that contains a set of different objects constituting the payload. Other designs might not be OO at all. The simplest transport, for example, is a delimited string. In this case, the string variable is the transport and the characters of the string make up the payload. Because objects are easier to deal with in code, OO transports are appealing; however, they are often the poorest performing of the many options.
Along with object orientation and performance, we are concerned with self-definition. A self-defined payload contains not only the data, but also a schema definition. Schema definitions are invaluable because they allow any tier in the application to discover what's in the payload, which in turn can affect how the data is used. Schemas are like a packing list for a shipping container.
With the concepts of transport and payload, we will now survey all the different options for moving data within a tiered system. In this survey, we will be careful to address performance, object-orientation, and self-definition. In this way, you can decide exactly which mechanism is right for your application.
Delimited strings have the following characteristics:
As I indicated earlier in this article, the simplest form of a transport is the delimited string. Delimited strings are a good choice for performance but can be hard to deal with because you must parse them to access the data. This means that you can move the data rapidly between tiers, but you will have difficulty operating on the data within a tier.
Delimited strings can also be self-defined as in the case of Extensible Markup Language (XML). XML has the performance characteristics of a delimited string with the capability to house enhanced information.
The easiest way to create a delimited string for use as a transport is to invoke the GetString method of the ADO Recordset. The GetString method takes an existing Recordset and creates a string, using tabs to separate the columns and carriage returns to separate the rows. This string can then be sent directly to the caller as a return value from the function.
The GetString method is primarily used to gain maximum transport performance. Therefore, when you intend to use this transport, you will want to create the lightest possible cursor. The appropriate cursor type for the delimited string is the firehose cursor. The good news here is that the firehose is the default cursor in ADO. Therefore, returning a delimited string can be very simple. The code in Listing 1 shows a function that returns a delimited string.
Listing 1: Returning a Delimited String
Public Function Lookup() As String Dim objRecordset As ADODB.Recordset Set objRecordset = New ADODB.Recordset 'Run Query objRecordset.ActiveConnection = _ "Provider=SQLOLEDB;Data Source=(local);Database=pubs;UID=sa;PWD=;" objRecordset.Source = "SELECT pub_name FROM Publishers" objRecordset.Open 'Pass string to client Lookup = objRecordset.GetString End Function
Because the GetString method of the Recordset object allows you to specify the column and row delimiter for the string, you can often change the form of the data into something more recognizable. For example, delimiting the data with <TR> and <TD> tags allows you to create an HTML stream that can be displayed directly from the return value.