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.
