Do-It-Yourself RecordSets

Rob Macdonald

Rob, like many VB developers, realizes that Microsoft's enterprise architecture is built on NT, MTS, and ADO, and he's already written a classic foundation article on the latter ("ADO: Learn to Love It," in the April 1998 issue). This month, in another "keeper" article, Rob illustrates an innovative technique for building your own custom data provider with OLE DB. This article represents a generalization of lessons learned in deploying a large, complex MTS-based project that uses a gateway DLL and where RecordSet data, returned as a variant array, is used to service the OLEDBSimpleProvider interface calls.

It's gratifying when you read that one of the most exciting new features of VB6 is something you've been doing in VB5 for more than a year. In this article, I'll explain how to create your very own data provider in VB6 that returns RecordSets à la carte -- so you won't have to be constrained by what a database can provide -- and then show how exactly the same result can be achieved in VB5 with just a little more effort.

First of all, though, why would you want to create a "custom" data provider? Well, sometimes a single SQL query isn't really enough -- your application might require additional logic or complex validation code to achieve the right effect -- and using a simple data bound control isn't always the proverbial "piece of cake" you'd like it to be. Now let your imagination shift a little to data that isn't even in a database, or can't be accessed through SQL -- data structures in your program, for example, or the contents of a file, or even the controls on a VB form. Wouldn't it be nice if you could present this sort of data using data bound techniques, or using familiar programming constructs such as RecordSets to access it?

Well, you can. It's all tied up with the extra benefits you can get when you switch (from DAO/RDO and ODBC) to using ADO and OLE DB. In my view, the ability to write your own VB components that present their data as RecordSets is bound (no pun intended) to have a profound effect on the way you think about programming. But before I get too carried away, let me give a straightforward example.

Has it ever occurred to you how easy end users have it? Most of the data that interests them is in a database, and there are plenty of great tools to analyze and report on that data. That isn't true for the data of most interest to programmers -- source code. Granted, we can view it and edit it, but what about a query language that allows us to find out what controls are on a form, or how many of our procedures are commented? To demonstrate the approach required for creating custom data providers, I'm going to show you how to view the contents of a VB FRM file as a RecordSet -- which you'll be able to manipulate in the same way as data from a database.

Building the client

For example, Figure 1 shows you a very simple VB form (saved on my hard drive as "c:\frmselect.frm"), and Figure 2 shows you a VB application that lists each control from "c:\frmselect.frm" in a grid.

Of course, Figure 2 in itself isn't terribly exciting -- that is, until you realize that the application running in Figure 2 doesn't require any code. All it contains is a form and two of VB6's new controls -- the ADO Data Control (much the same as the standard or remote data controls from VB5, but using ADO) and the new DataGrid control. If you're following along with VB5, you could make do with a FlexGrid control and ISG's ADO Data Control, which can be downloaded for free from www.isgsoft.com .

In either case, all you need to do to create your program is set the DataSource property of the grid to refer to the appropriate Data Control (standard VB stuff), and then set two properties on the relevant Data Control:

ConnectionString	Provider=CustomData;
RecordSource	select controls from c:\frmselect.frm

If your data were coming from a database, the ConnectionString property would most likely be an ODBC data source name, or maybe an Access MDB filename. In this case, however, it's the name of an OLE DB provider that I've written in VB called "CustomData". So long as I write my CustomData component in a certain way ("by the book," so to speak), OLE DB -- and therefore ADO and a whole range of ADO-compliant controls -- can't tell it apart from a "normal" database.

In the database world, the RecordSource would be a table name of a SQL statement. The preceding RecordSource is "SQL-like," but it's no SQL that you've ever seen before because I just made it up. The point here is that I decided what the RecordSource looks like because I wrote the component that processes it. In effect, I've created my own version of SQL, which can be as simple or as complex as I choose.

If you're the kind of person who's never used data bound controls much because you want greater control (I used to be this kind of person, but now that I can write my own RecordSets I can have all the control I like written into my data component!), Listing 1 shows how you can access the data component using regular ADO code.


Listing 1
. Standard ADO code that creates the RecordSet and adds the 

control's name and type into a list box. You need to have set a reference 

to the ADO object library. 
  
 Dim rs As Recordset 
 Set rs = New Recordset 
 rs.Open "select controls from c:\frmselect.frm", _ 
         "Provider=CustomData;" 
 lstControls.Clear 
 While Not rs.EOF 
   lstControls.AddItem rs!Name & "  " & rs!Type 
   rs.MoveNext 
   Wend

The heavy bit
It's hard work writing a database -- just ask the SQL Server development team -- and it's hard work writing a full-blown OLE DB provider that offers decent performance. It's highly unlikely that you'd ever need to write a complete custom data provider with all the sophistication of a relational database engine -- a handful of firms specialize in that sort of thing for firms that need to access data that's in a non-standard, proprietary data format. However, you might not realize that Microsoft has developed a special interface called the OLE DB Simple Provider (or OSP) interface that puts customization within the reach of us normal programmers by removing the need to code a whole load of complex OLE DB routines. Components that implement this interface can support most of the basic ADO functionality, such as opening and closing connections and RecordSets, RecordSet navigation, and updates, deletes, and inserts. And, although the OSP interface doesn't support more esoteric features such as batch updating or transaction management, I've found that, with a little imagination, such features can be added if required.

What exactly do I mean by "implementing an interface"? If you read my August article ("Interface in a Nutshell"), you know that VB supports a special keyword called "Implements" that allows you to say that your project supports a pre-defined set of functionality. Client programs can then call your program, knowing that it fully supports the functionality they require.

Well, when you write a custom data provider, OLE DB will be a client of your project, and it will also be a server to the front-end program using your data (via ADO or the Data Control). OLE DB will do a lot of work making your data look like ADO, but it will expect your program to respond correctly to a set of functions it wants to call in order to retrieve the actual data you're providing. To get at the interface definition, you'll need to set a reference (Tools | References) to "Microsoft OLE DB Simple Provider 1.5 Library" (which refers to a Type Library called SIMPDATA.TLB). VB6 installs this library for you, but if you're using VB5, you'll need to search for this file and then register it using the Browse button in the Tools | References dialog box. Even then, it's quite likely that you won't have this file unless you've installed VB6 or OLE DB 1.5 (earlier versions aren't sufficient). You can download the ADO/OLE DB 2.0 distributable components (or, better still, the entire OLE DB SDK) from Microsoft's Web site at www.microsoft.com/data -- they're both big downloads, so be prepared to wait.

Table 1 shows all the methods that comprise the OSP interface, and every object you write that's going to present a RecordSet must implement all of these methods. This seems like quite a lot of work, but many of the methods perform a pretty standard function, and therefore, once you've written them once, you'll find that you can reuse the same code again and again. All the same, implementing 14 methods isn't a trivial task, and I've broken them up into two groups: those that are used in all RecordSets, and those that are used only for read/write RecordSets. Again, whenever you use the Implements keyword, you must implement all of the methods in the interface you're implementing, even if they won't be used for anything useful. However, you can put dummy functionality in those methods you don't care about.

Table 1: Methods defined in the OSP interface that must be supported by your objects.

OSP methods used in all RecordSets
getRowCount	Returns the number of rows
getEstimatedRows	Returns the total estimated number of rows in the data set
getColumnCount	Returns the number of columns
getVariant	Retrieves the value of the cell at the indicated row and column
getRWStatus	Returns the read/write status of a column, row, cell, or data set
find	Searches for the specified set of values
getLocale	Indicates the locale of the data
IsAsync	Indicates whether data is being populated asynchronously
StopTransfer	Requests that the OSP discontinue asynchronous transfer of data
Used in Read/Write RecordSets
setVariant	Sets the value of the cell at the row and column indicated
insertRows	Inserts a specified number of empty rows
deleteRows	Deletes a specified number of rows
addOLEDBSimpleProviderListener	Registers an event handler
removeOLEDBSimpleProviderListener	Unregisters an event handler

In this article, I'm only going to use read-only RecordSets, so I'm going to put dummy code in the five methods relevant to read-write RecordSets. This also means that the getRWStatus method will simply return an "OSPRW_READONLY" flag. OSP allows you to return data asynchronously. This is useful if you have masses of data to return because you can return the first bit so the client can continue processing while your component prepares the rest of the data in the background, but, for this example, I'm going to "ignore" the "IsAsync" and "StopTransfer" methods. I'm also going to "ignore" the "find" and "getLocale" methods, as clients can get by without this functionality. Because the "getEstimatedRows" method can return the same value as the "getRowCount" method, I've reduced my real task down to implementing only three methods -- not bad for such a powerful result. In fact, once I've set up my dummy code, most effort will be spent preparing the data that will be returned when the "getVariant" method is called (which actually returns the data in the RecordSet).

To recap, in order to create a custom RecordSet, you have to implement all the methods defined in the pre-defined OSP interface. Of the 14 methods in this interface, 10 can be coded using standard or "dummy" code for simple, read-only, synchronous RecordSets. You'll need to code "getRWStatus" and "isAsync", as shown in Listing 2 . For all the other methods -- apart from "getRows", "getEstimatedRows", "getColumns", and "getVariant" -- you can add a comment into the code stub to let the compiler know you haven't forgotten about them.


Listing 2
. Standard code that can be used for simple read-only RecordSets. 
  
 Private Function OLEDBSimpleProvider_getRWStatus _ 
 (ByVal iRow As Long, ByVal iColumn As Long) _ 
  As MSDAOSP.OSPRW 
  OLEDBSimpleProvider_getRWStatus = OSPRW_READONLY 
 End Function 
  
 Private Function OLEDBSimpleProvider_isAsync() As Long 
 OLEDBSimpleProvider_isAsync = False 
 End Function

Creating the custom functionality
The actual functionality that processes the VB FRM file isn't really what this article is about; it simply provides a vehicle that illustrates the kinds of things you can do inside a Custom Data provider. The real answer is just about anything. There are only two constraints that apply.

First, all the information that's required to know what data to prepare must be provided in an input string to the component. This will be provided by the client in the form of the command (or query) string, passed as an argument to the ADO RecordSet created by the client program. In the case of my example provider, this input string is of the form:

 "select controls from c:\frmselect.frm"

To process this input, I've written a simple parser that breaks a string up into a collection of tokens where each token is delimited by one or more spaces. The name of the FRM file will therefore be the fourth item in this collection. Of course, I could have just supplied the filename as the entire argument, but the parser allows me to offer a more friendly syntax for users of my component, and it also allows me to add extra functionality in the future. For example, I could allow users to enter "select procs from ..." and return a RecordSet containing information about the subroutines and functions contained in the form. Besides which, the parser is also very useful for processing the FRM file.

The second constraint is that I must be able to present all the data in my RecordSet in response to calls to the getVariant method in the OSP interface. OLE DB can call getVariant at any time and can request data in any order it likes, so I need to prepare my data so that I can return the requested data efficiently on demand. GetVariant will provide a row number and a column number, and my component will use these arguments to work out what to return. By convention, if the row number is 0, the component should return a column name -- for any other value, it returns data from the appropriate row in the RecordSet.

A consequence of this constraint is that it's a good idea to prepare all the data in the form of a two-dimensional structure as soon as the RecordSet is requested. An array would be a perfectly acceptable way to hold the data, but I've opted for a more flexible approach. I've defined a class called "vbObject" with properties to reflect the different attributes of a control that I want to return in my RecordSet. Listing 3 shows the definition of this class.


Listing 3
. Properties of the vbObject class. 
  
 Public sName As String 
 Public sType As String 
 Public sParent As String 
 Public sDisplayName As String 
 Public iIndex As Integer 
 Public lHeight As Long 
 Public lWidth As Long 
 Public lTop As Long 
 Public lLeft As Long

I then have a file-processing routine that reads the FRM file in question and creates an instance of vbObject for each control in the form, finally adding these instances to a collection called colRecords. Each object will then represent one record in my RecordSet. I'm not going to go into the details of the file-processing operation (the full code can be found in the accompanying Download file. ), but a segment of "frmSelect.frm" is reproduced in Listing 4 so that you can gauge the work involved.


Listing 4
. Fragment of frmSelect.frm. 
  
 Begin VB.CheckBox chkRemember  
       Caption         =   "Remember current selection" 
       Height          =   315 
       Left            =   3000 
       TabIndex        =   6 
       Top             =   1500 
       Value           =   1  'Checked 
       Width           =   2595 
    End 
    Begin VB.Frame frmOptions  
       Caption         =   "Options" 
       Height          =   1155 
       Left            =   3000 
       TabIndex        =   3 
       Top             =   240 
       Width           =   2595 
       Begin VB.OptionButton optDetails  
          Caption         =   "Option2" 
          Height          =   315 
          Index           =   1 
          Left            =   180 
          TabIndex        =   5 
          Top             =   720 
          Width           =   2175 
       End 
       Begin VB.OptionButton optDetails  
          Caption         =   "Option1" 
          Height          =   315 
          Index           =   0 
          Left            =   180 
          TabIndex        =   4 
          Top             =   300 
          Width           =   1995 
       End 
    End

It's easy enough to see how this file can be used to create instances of "vbObject." However, you might have noticed that it requires a recursive function to do the job properly. That's because controls such as the frame control can have other controls contained within them, and controls can be nested quite deeply once tab controls and the like start being used. [See Rod Stephens' column on recursion, "Recursion: The Good, the Bad, and the Ugly," in the November issue. -- Ed.]

Once you've created the collection of vbObjects, you can define the remainder of the OSP interface functions (that is, the ones that aren't covered by "standard" code). GetRowCount and getEstimatedRows will both return the number of items in the colRecords collection, while getColumnCount will simply return 9 (the number of properties of vbObject). GetVariant is a bit more long-winded, but it's not exactly complicated. The code for getVariant is shown in Listing 5 .


Listing 5
. The getVariant method. 
  
 Private Function OLEDBSimpleProvider_getVariant _ 
    (ByVal iRow As Long, ByVal iColumn As Long, _ 
    ByVal format As MSDAOSP.OSPFORMAT) As Variant 
 Dim oVB As vbObject 
 Dim vValue As Variant 
 If iRow = 0 Then 
    Select Case iColumn 
    Case 1 
       vValue = "Name" 
    Case 2 
       vValue = "Type" 
    Case 3 
       vValue = "Parent" 
       'code omitted here. . . 
    Case 9 
       vValue = "Height" 
   End Select 
 Else 
 Set oVB = colRecords(iRow) 
    Select Case iColumn 
    Case 1 
       vValue = oVB.sName 
    Case 2 
       vValue = oVB.sType 
    Case 3 
       vValue = oVB.sParent 
       'code omitted here. . . 
    Case 9 
       vValue = oVB.lHeight 
   End Select 
 End If 
 OLEDBSimpleProvider_getVariant = vValue 
 End Function

The entire routine is nothing more than two "Select" statements. The first returns a column name if the row number is 0. The other identifies the appropriate object in the colRecords collection and returns one of its properties, according to the column number requested.

Creating and using the custom provider in VB6
To create a "true" OSP, it's necessary to create some special Registry entries for the component. I'll come to this shortly, but in VB6 it's possible to avoid this chore. In fact, given what we've done so far, only a few more steps are required using some of VB6's new features.

The main task is to create the Server component that will provide the custom data. This is best developed as an ActiveX DLL project, not just for performance reasons (there will be a lot of calls to getVariant), but also because that gives you the option of deploying it with MTS. Mine has the project name "CustomData". CustomData has three classes implemented within it:

1. The CustomConnection class will be seen through ADO as the Connection object. Clients connect to it, pass in a command string, and get a RecordSet object back for their troubles. If you've looked at VB6 at all, you might have noticed that the Properties box for classes has grown some more entries. One of these is the "DataSourceBehavior" property. Setting this property to vbDataSource will, among other things, add a new event called "GetDataMember" to the standard "Initialize" and "Terminate" events. This event will fire when a connection to my component is requested, and I've coded it as follows:

 Private Sub Class_GetDataMember _ 
        (DataMember As String, Data As Object) 
 Dim oCustom As CustomRecords 
 Set oCustom = New CustomRecords 
 oCustom.processFile DataMember 
 Set Data = oCustom 
 End Sub

This event procedure receives the command string from the client in the "Datamember" argument and must return an object that implements the OSP interface. In my case, this object will be an instance of the "CustomRecords" class (see the next point), which has a "processFile" method to handle the command string.

2. The CustomRecords class will be seen through ADO as a RecordSet object. Its job is to implement the OSP interface and to provide any custom functionality required for the RecordSet. The latter is achieved by coding the "processFile" method that reads the VB source file and creates the "colRecords" collection. (The full source is included in the accompanying Download file .) To achieve the former, a reference must be set to "Microsoft OLE DB Simple Provider Interface 1.5 Library." This is the correct interface to use, even if you're using ADO 2. You might also want to reference the "Microsoft OLE DB Error Library," as this defines a set of standard errors you could raise should anything go wrong. Once these references are set, you can code all the OSP methods, using code presented or discussed earlier.

3. The vbObject class is private to the component and simply holds the data for each record that will be accessed when getVariant is called. Once again, this class has been discussed previously.

Once the classes are in place, the project can be compiled to a DLL. As mentioned earlier, VB6 provides an easy, if restricted, way to use the component as it stands. To try this out, create a standard EXE project, set a reference to the component you've just compiled, and place a DataGrid on the form (you'll need to use the "Components" menu box dialog to select the latest Microsoft DataGrid control for OLE DB). Then set a reference to the "Microsoft Data Adapter Library." This will allow your component to be linked to a DataGrid. Enter the following code in the Form_Load event procedure of the test client program to complete the application:

 Dim oAdapter As New DataAdapter 
 Dim oSource As New CustomConnection 
 Set oAdapter.Object = oSource 
 DataGrid1.DataMember = "select controls from " & _ 
 " c:\frmselect.frm" 
 Set DataGrid1.DataSource = oAdapter

When you run the program, it will look remarkably like Figure 2 .

Turning the component into a "real" OLE DB provider
What we've achieved so far is pretty exciting, but it's still not a true OLE DB provider because it's neither readily accessible by OLE DB consumers nor easily accessible via ADO programming, for example. You'll be pleased to learn that the final touch requires no additional programming, but it does require the creation of a REG file to import to the Registry. The purpose of this file is to tell OLE DB about your component, so that it recognizes it as an OLE DB provider. This will allow your provider's name to be used in the "Provider=" part of an OLE DB connection string, and it will also cause it to appear in the list of OLE DB providers whenever this is presented.

To make this task easy, I've created a template REG file (also included in the accompanying Download file ) into which you need to add four values. The full name can be anything you like, but the Component and Connection must correspond to the component (that is, project) and connection object names in your provider. The GUID must be one of those unique 128-bit identifiers that litter the Registry. VB creates dozens of these, but the correct way for you to generate a GUI is to use the "UUIDGEN" command line utility that comes with the Win32 SDK.

You'll need to replace each template key occurrence with your own values before running the REG file (which runs like an EXE or BAT by double-clicking in Windows Explorer) to import the values into the Registry (see Table 2 ).

Table 2: Parameters required for REG file.

Template key	Occurrences	Example
FullName	2	VB Custom Data
GUID	7	dce844e0-4be1-11d2-8e4c-00104bdc2942
Component	5	CustomData
Connection	1	CustomData.CustomConnection

Once the Registry has been updated, you have (at last!) finished. You can write a client program, such as the one shown in Listing 1, and treat the component like any other read-only data source. Equally, you can set the ConnectionString and ResourceString properties of the ADO Data Control and create a front end using Visual Programming. If you're like me, it will be some time before you tire of clicking on the DataField property of a text control linked to your component via a Data Control, and seeing a list of fields appear that was coded into your own getVariant implementation (see Figure 3 ).

Creating and using the custom provider in VB5

I wrote my first OLE DB providers in VB5 and was fully expecting VB6 to take away some of the configuration tasks required before getting down to action. Surprisingly, very little has changed between VB5 and VB6, which is good news for VB5 users who have read this far.

The main difference (apart from the need to explicitly install the required OLE DB components, as mentioned previously) is that VB5 doesn't have the "DataSourceBehavior" option in the property box of the CustomConnection class. To address this difference, there are three short steps to follow. The first is to set a reference to "Microsoft Data Source Interfaces." Second, you won't have a GetDataMember event procedure to code. Instead, you'll need to add a function to the "CustomConnection" class, called "msDataSourceObject". As you can see from the following code, it's very similar to GetDataMember -- it's just the calling convention that's changed.

 Public Function msDataSourceObject(sCommand As _ 
    String) As OLEDBSimpleProvider 
 Dim oCustom As CustomRecords 
 Set oCustom = New CustomRecords 
 oCustom.processFile sCommand 
 Set msDataSourceObject = oCustom 
 End Function

Finally, we need to add a data sourcer listener function. For our read-only provider, it won't do anything useful -- apart from making the program work, that is!

 Public Function addDataSourceListener(ByVal _ 
    pospIListener As DataSourceListener) As Long    
    addDataSourceListener = 0 
 End Function

Once you've prepared and executed the Registry file (as described previously), you're ready to create clients. ADO coding can be done as you would for VB6, but you won't be able to use the VB6 ADO controls. Instead, you can download the free ISGData Control for ADO that will work with both VB5 and VB6 (www.isgsoft.com/products/isgdata ). For VB5 development, you obviously need to make sure that you have VB5 versions of other data-aware controls. You can use both the MSFlexGrid and DBGrid controls with the ISGData Control, for example, to create data bound displays. Figure 4 shows how you would set up the ISGData Control to use the CustomData provider.

Conclusion
I started this article by saying that the ability to write your own data providers would change the way you think about programming -- an admittedly bold claim. The real benefit of the techniques I've described is that they offer a solution to what generally seem like mutually exclusive aims: giving users easy access to data, but also controlling data access. By wrapping up the data in a simple RecordSet interface, we can insulate users from the complexity associated with "raw" data. But by placing our code between the RecordSet and the data that was used to create it, we maintain much better control over data access and rights (read-only vs. read-write, for example).

At first, this seems like a great deal of work to achieve a simple effect. But what you need to remember is that most of this effort represents a once-only cost. Since I started using this technique a year ago with VB5, I've created hundreds of RecordSets. By using standard code libraries and templates it can actually be quicker than other approaches, because I can use standard ADO front ends to display my results. I can also piggy-back on Microsoft's massive investment in ADO, from Web-based delivery mechanisms to desktop applications. So can you.

Download sample code for this article here.

Rob Macdonald is an independent software specialist based in London and southern England. In addition to consulting and training in Windows, client/server, VB, COM, and systems design and management, he also runs the UK ODBC User Group and is author of RDO and ODBC: Client Server Database Programming with Visual Basic, published by Pinnacle. Rob can be contacted in England at +44 1722 782 433 or via e-mail at rob@salterton.com.