Advanced ADO Data Providers

If you have some special data source that you want to extract data from, you can create an ADO data provider. Peter moves beyond simple read-only providers to show you how to create a provider that performs updates, accepts parameter strings, and fires events.

In last month’s issue I showed you how to create an ADO data provider that you could use in the same way you use the Jet or SQL Server providers that come with ADO (“Creating an ADO Data Provider” in the February 2000 issue). However, the provider that I demonstrated in that article was a basic provider. This article will show you how to add the rest of the features that a provider needs: the ability to perform updates, accept parameters, and fire events back to the clients that use them.

Updates

The first thing that you’ll probably want to add to your provider is the ability to perform updates. To do this, you must indicate to ADO that the records that you return are updateable by using the getRWStatus method:

Private Function _

     OLEDBSimpleProvider_getRWStatus _

   (ByVal iRow As Long, _

    ByVal iColumn As Long) As OSPRW

OLEDBSimpleProvider_getRWStatus = OSPRW_REAONLY

End Function

This method will be passed a row and column number, and you must return one of the four enumerated values given in Table 1. If the row parameter is —1, you should return a value that indicates the updateability of the specified column for all the rows that you are returning; if the column parameter is —1, the updateability of all columns for the specified row should be returned; if both values are —1, then you should return the updateability for all of the records that you’re managing.

For my sample provider, which returns data about an Access database, I returned the easiest answer: OSPRW_READONLY for all combinations of the iRow and iColumn parameters. This indicates that that none of the columns and none of the rows are updateable

If you do return one of the other values, then you must write the routine to update your data. ADO will call your module’s setVariant routine when an update is made, passing the row and column number of the field to be updated, along with the field’s new value.You must add the code that allows your provider to update the data source from which you draw your information. For my data provider, the only data source is my internal Recordset, so an updated version of the provider would have a setVariant routine that looks like this:

Private Sub OLEDBSimpleProvider_setVariant _

(ByVal iRow As Long, ByVal iColumn As Long, _

ByVal format As OSPFORMAT, _

ByVal Var As Variant)



  m_RS.AbsolutePosition = iRow + 1

  m_RS.Fields(iColumn - 1) = var

End Sub

The code in this example uses the iRow value passed to the routine to set the AbsolutePosition property of the Recordset. Since the first iRow value passed is a 0 and AbsolutePosition begins at 1, I have to add 1 to iRow. The requested row now becomes the current row in the Recordset. The iColumn value is used to select the field to be updated and the var parameter is used to update the field. Here again, the lowest value passed to the routine (1) has to be adjusted to match the lowest value in the Recordset’s Fields collection (0). You would want to provide error handling routines to handle a request for a row or column that didn’t exist or receiving an inappropriate value.

Table 1. Values to be returned by the getRWStatus method.

Update Status Return Values Description
OSPRW_DEFAULT Updates allowed
OSPRW_MIXED Update status not known, or a combination of updateable and non-updateable (say, when the column or row parameter is set to —1 and some rows/columns can be updated and others can’t)
OSPRW_READONLY Updates not allowed
OSPRW_READWRITE Updates allowed

Parameter strings

My code for processing the commands passed to my bare-bones provider was very simple and just accepted the name of the database concatenated with the name of the object to retrieve (“Form” or “Report”). A more flexible system would allow users to pass a set of parameters that my provider could then process, like this:

rs.Open "ObjecType=Form;Database=C:\MyDB.MDB", _

   "Provider=Databinding;"

The command string passed to my provider now matches the format used by ODBC and ADO connection strings: parameter name, an equals sign, a parameter value, and a semicolon, with all four items repeated indefinitely. This string will be passed to the GetDataMember of the class that controls my provider where I can parse it and use it to set properties or methods of my provider class.

To process a parameter string, I use Visual Basic 6’s Split function. This function returns an array of all of the values between a delimiter. Passed a string and a semicolon, for instance, the split function will find all of the substrings that exist between the semicolons and create an array of them. Passed the parameter string I used above, Split will return an array with “ObjectType=Form” in its first position and “Database=C:\MyDB.MDB” in the second position. I can then use the Split function on each position in the array to find the data before and after the equals sign. Using Split on “ObjectType=Form” will create an array with “ObjectType” in the first position and “Form” in the second. A simple Select statement will let me pass the right data to the right property:

Private Sub Class_GetDataMember( _

  DataMember As String, _

  Data As Object)

Dim objData As clsMDBInfo

Dim ParmArray As Variant

Dim CommandArray As Variant

Dim var As Variant



Set objData = New clsMDBInfo



ParmArray = Split(DataMember,";")

  For each var in ParmArray

       CommandArray = Split(var,"=")

       Select Case CommandArray(0)

              Case "ObjectType"

                     objData.Command = CommandArray(1)

              Case "Database"

                      objData.DB = CommandArray(1)

       End Select

Next

objData.CreateRecordset

Set Data = objData

End Sub

Events

ADO Recordsets can fire events. A client can declare a Recordset using the WithEvents keyword and then write code to respond to events fired by the Recordset. As an example, this code will be executed prior to any updates in the Recordset:

Dim WithEvents rs As Recordset



Private Sub rs_WillChangeField( _

  ByVal cFields As Long, _

  ByVal Fields As Variant, _

  adStatus As ADODB.EventStatusEnum, _

  ByVal pRecordset As ADODB.Recordset)



 'pre-update processing



End Sub

In your code, you can use the OLEDBSimpleProviderListener object to fire events back to the clients using your provider. As each client connects to your provider, the addOLEDBSimpleProviderListener event will fire in your provider and pass you a reference to an OLEDBSimpleProviderListener object. As each client disconnects, the removeOLEDBSimpleProviderListener event will fire. The OLE Listener object is your connection to the clients using your provider.

In the addOLEDBSimpleProviderListener event, you can set a variable that will reference the Listener object passed in. With that reference to the Listener object, you can use its methods to notify your clients of the actions that your provider is taking or is about to take. Each method of the Listener object causes a corresponding event to fire in the client using the provider. This code, for instance, fires events before and after any update to my internal Recordset:

Dim iosp As OLEDBSimpleProviderListener



Private Sub _

  OLEDBSimpleProvider_addOLEDBSimpleProviderListener _

  (ByVal pospIListener As _

      OLEDBSimpleProviderListener)

Set iosp = pospIListener

End Sub



Private Sub OLEDBSimpleProvider_setVariant _

(ByVal iRow As Long, ByVal iColumn As Long, _

 ByVal format As OSPFORMAT, _

 ByVal Var As Variant)

  iosp.aboutToChangeCell iRow, iColumn

  m_RS.AbsoultePosition = iRow + 1

  m_RS.Fields(iColumn - 1) = var

  iosp.cellChanged iRow, iColumn

End Sub



Private Sub _

OLEDBSimpleProvider_removeOLEDBSimpleProviderListener _

  (ByVal pospIListener As _

    OLEDBSimpleProviderListener)

 Set iosp = Nothing

End Sub

When the provider calls the aboutToChangeCell method, the client receives the WillChangeField event; calling the cellChanged method fires the FieldChangeComplete event in the client using the provider. There doesn’t seem to be any way to respond to the client setting the adStatus variable that’s passed to the client in the event that results from calling the Listener’s methods.

If your provider can be used by multiple clients, you’ll need to keep references to all of the Listener objects that are passed to you. A collection is an excellent choice for holding those references. This version of the addListener method adds each Listener object to a collection:

Dim colListeners As Collection



Private Sub Class_Initialize()

  Set colListeners = New Collection

End Sub



Private Sub _

  OLEDBSimpleProvider_ _

addOLEDBSimpleProviderListener _

(ByVal pospIListener As _

   OLEDBSimpleProviderListener)



 If Not (pospIListener Is Nothing) Then

       colListeners.Add pospIListener

 End If



End Sub

As the code in your provider executes and you want to fire events back to your clients, you’ll need to loop through the collection, firing the appropriate methods:

Private Sub OLEDBSimpleProvider_setVariant _

(ByVal iRow As Long, ByVal iColumn As Long, _

 ByVal format As OSPFORMAT, _

 ByVal Var As Variant)



Dim iosp As OLEDBSimpleProviderListener



  For Each isop In colListeners

       iosp.aboutToChangeCell iRow, iColumn

  Next



  m_RS.AbsoultePosition = iRow + 1

  m_RS.Fields(iColumn - 1) = var



  For Each isop In colListeners

     iosp.cellChanged iRow, iColumn

  Next

End Sub

The removeListener event fires as each client disconnects from your provider. In the remove event, you’ll have to find the Listener object in the collection that’s associated with the disconnecting client. The remove event is passed a reference to the Listener for the disconnecting client, so it’s just a matter of finding the matching object in the collection and then removing it:

Private Sub _

  OLEDBSimpleProvider_ _

removeOLEDBSimpleProviderListener _

  (ByVal pospIListener As _

    OLEDBSimpleProviderListener)



Dim iosp As OLEDBSimpleProviderListener

Dim intColPos As Integer



  intColPos = 1

  For Each isop In colListeners

       intColPos = intColPos + 1

        If iosp Is pospIListener Then

               ColListeners.Remove intColPos

               Exit Sub

        End If

  Next



End Sub

And that’s all there is to it. This article provides you with the finishing touches that every ADO provider should have. Armed with this information, you can create data providers that you can use from Access 97 or 2000, and that have all the capabilities of the providers that come from Microsoft.

Sidebar: Consumers, Providers, and Service Providers

OLE DB, on which ADO rests, provides a set of objects that allow you to work with data. OLE DB divides these objects into three groups: consumers, providers, and service providers. Consumers are objects or applications that use data provided by the other two kinds of objects. A provider is an object that can extract data from a data source and return it in the standard OLE DB format. A service provider can’t extract data, but, like a consumer, it can accept data from a provider. A service provider, unlike a straight consumer, also manipulates the data and can pass it on to a consumer or another service provider. Since a service provider can work with any ADO provider, service providers can be very flexible. Microsoft’s search engine, Index Server, has a data provider that you can use to extract data from the search engine’s catalog (for example “Find me all of the documents containing the words ‘Smart Access’”). Since the Index Server data provider returns data in the standard ADO format, you can use the Data Shaping server to manipulate that data. All of these features are available from any development tool that can work with objects, including Access 97 and Access 2000.

 

About Peter Vogel

After 10 years of editing Smart Access, Peter continues to develop with Access (though he also does a lot of .NET stuff). Peter is also still editing other people's article--he even self-published a book on writing user manuals ("rtfm*") with a blog here.
This entry was posted in Other Topics. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.