Basically Visual

July/August 1999

by Peter G. Aitken (c)1999

Originally published in Visual Developer magazine


Data Sources, Data Sinks - part II

 This is the second in a series of two columns on creating custom data sources and data sinks (or data consumers, as Microsoft calls them) using Visual Basic 6. I'll review the material from the first column briefly, but if you have not done so already you might want to read that column before starting on this one. If you cannot find that copy of Visual Developer you can read my previous columns at http://www.pgacon.com/visualbasic.htm.

 To review, then, much of Visual Basic's database programming power comes from the ease with which you can use data sources and data sinks (also called data consumers), collectively referred to as data-aware controls, to construct your application. A data source, such as the ADO Data control, provides the link between your program and the external database file. A data sink, such as a Text Box control. makes use of the data that is provided by the data source. A data sink can be bound to a data source which means that the transfer of data between the database file and the control is done automatically with no need for special programming on your part.

 

The data aware controls provided with Visual Basic give you a great deal of database programming power, but many programmers want to take the technology further for highly customized applications. Creating a custom data source has been a possibility for Visual Basic programmers for quite a while, and custom ActiveX controls can be created to "consume" the data. However programming could get rather complex because one part of the equation was missing - the ability to bind custom data sources and data sinks together so that the transfer of data is handled automatically without the need for writing code. For the first time, Visual Basic 6 adds this missing ingredient. Actually this capability is more flexible than you might think, because you are not limited to binding controls on a form but can bind any bind any ADO/OLE DB data source to any ADO/OLE DB data sink.

The BindingCollection object is used to define a binding between a data source and a data sink. Each BindingCollection object is associated with a single data source and defines bindings between that data source and one or more data sinks. A binding can be simple which means that one field from the data source is bound to the data sink, or it can be complex which means that an entire record, or row, from the data source is bound to the data sink. The binding behavior of a class is determined by its DataBindingBehavior property which can be set to vbNone, vbSimpleBound, or vbComplexBound. The BindingCollection object is utilized only for simple bindings

In the previous column I demonstrated a basic example of simple binding, creating an ActiveX control that had one property bound to a field in a data source (an ADO Data Control). I promised to explain creating a data sink that uses complex binding in this column, but that was before I started looking into the details. In the Visual Basic documentation Microsoft talks about complex binding of custom data sinks but provides no information about doing it. All my efforts with other sources of information have turned up precious little aside from some rather obscure looking C++ code, so I must (with some embarrassment) postpone an explanation of complex data binding of custom data sinks to some future column after I have managed to figure it out for myself. Even so there's a lot of interesting stuff to cover.

Semi-Complex Binding

In the previous column I showed you how to create an ActiveX control that served as a  data sink, with a single property bound to a data source (an ADO Data control). You are not, however, limited to binding a single property to a single field -  you can bind as many properties as you like. The properties are each bound individually, with a separate entry in the BindingCollection object, so this is not the same as true complex binding where an entire row is bound. In some ways, however, it lets you accomplish the same results, so I refer to it as semi-complex binding.

To demonstrate, I'll use the same Access database table that we created previously. It has a single table named Contributors which contains only three fields, a type Text field (length = 50) called Name, a type Single field called Percent, and a type Long field called Index with AutoIncrement set to True. If you have this file from before, that's fine. Otherwise, use Visual Data Manager to create it and then add a few records for testing purposes.

Next, start a new ActiveX control project. Set the UserControl's DataBindingBehavior property to vbSinpleBound. Place three Text Box controls and three Label controls on the UserControl, and assign the following name properties to the Text Boxes:

txtIndex
txtName
txtPercentage

The ActiveX control has three properties, corresponding to the three fields in the database table it will be bound to. They are called Idx, Named, and Percentage (I used the property names Idx and Named instead of Index and Name to avoid confusion with the control's intrinsic properties of those names). The required property procedure code is shown in Listing 1.

Listing 1. Property procedures in the ActiveX data sink.

Public Property Get Idx() As Variant 
   
Idx = txtIndex.Text  
End Property

Public Property Let Idx(ByVal vNewValue As Variant) 
   
txtIndex.Text = vNewValue  
End Property

Public Property Get Named() As Variant 
   
Named = txtName.Text  
End Property

Public Property Let Named(ByVal vNewValue As Variant) 
   
txtName.Text = vNewValue  
End Property

Public Property Get Percentage() As Variant 
   
Percentage = txtPercentage.Text  
End Property

Public Property Let Percentage(ByVal vNewValue As Variant) 
   
txtPercentage.Text = vNewValue  
End Property

That's all that's required for the ActiveX data sink. To test the control you need a standard EXE Visual Basic project as part of a project group along with the ActiveX control. Procedures for doing this were explained in the previous column. The test project requires one ADO Data Control on its form, as well as one instance of the ActiveX control you just created. You must set the ADO Data Control's ConnectionString property to link it to the database file, and set its RecordSource property to "select * from contributors" to select all fields and all records in the table.

The only code required in the test project is to create the data bindings. You need to create a BindingCollection object, associate it with the ADO Data Control, then add a binding for each of the field/property combinations. This code is shown in Listing 2, and assumes that the ADO Data Control is named ADODC1 and the instance of the ActiveX control is named MyControl1.

Listing 2. Binding the three properties of the ActiveX data sink to the fields of the database table.

Dim OBColl As BindingCollection  

Private Sub Form_Load()  

Set OBColl = New BindingCollection  
Set OBColl.DataSource = Adodc1  
OBColl.Add MyControl1, "Named", "Name",,"Name"  
OBColl.Add MyControl1, "Idx", "Index"  
OBColl.Add MyControl1, "Percentage", "Percent",,"Percent"  

End Sub

Note that we have assigned Key properties to two of the members of the collection, permitting them to be accessed using the Key value. The reason for this will be seen later. When you run the project, you'll see that the three text boxes in the ActiveX control are bound to the three fields in the database table. Use the buttons on the ADO Data Control to move from record to record, and the corresponding data is displayed. In this simple demo, changes to the data made by the user are not written to the database table - doing so would require a little extra programming, our next topic.

Saving Changes

The most simple kind of binding, as in the example just presented, moves the data from the data source to the data sink but not in the other direction. In other words, if the user edits any of the data in the bound control, those changes are not written to the database table. Saving of changes is controlled by the DataChanged property of the Binding object. Each binding that is defined in the BindingCollection is represented by a Binding object, and the default setting of the DataChanged property is False. If this property is True, however, then moving the data source to another record will automatically invoke the Edit and Update methods, and any changes that were made to the data in the bound data sink will be written to the database table.

For a simple demonstration, change the code in the demo program's Form_Load procedure as shown in Listing 3. The last two lines of the modified procedure set the DataChanged property to True for the Binding objects representing the Name and Percentage field (Now you can see why we set a Key property for these Binding objects earlier).

Listing 3. Setting the DataChanged property to True so that data changes will be saved.

Private Sub Form_Load()

Set OBColl = New BindingCollection  
Set OBColl.DataSource = Adodc1  
OBColl.Add MyControl1, "Named", "Name", , "Name"  
OBColl.Add MyControl1, "Percentage", "Percent", , "Percent"  
OBColl.Add MyControl1, "Idx", "Index", , "Idx"  
OBColl("Name").DataChanged = True  
OBColl("Percent").DataChanged = True

End Sub

After these changes, when you run the program you'll see that any changes to the Name or Percentage values in the control are saved to the database table and will be displayed next time you open the file. Using the DataChanged property gives you complete control over which, if any, data changes are saved. You could, for example, set it to True for all Binding objects when the program starts, making saving of data changes the program default, but also provide a "Cancel" button that permits the user to abandon data changes by setting DataChanged to False for all or selected bindings.

Data formatting the automatic way. Another property of the Binding object that you should become familiar with is the DataFormat property. DataFormat refers to a stdDataFormat object which controls the formatting of the data that the binding deals with. In other words, as the data moves from the data source to the bound control, it is formatted according to the specifications of the associated stdDataFormat object. When data moves in the opposite direction, from control to data source, the formatting is removed. I cannot go into the details here but you can locate more information in the Visual Basic Help system.

 Creating a Data Source

 To quote from the Visual Basic Help system, "A data source is an object that binds other objects to data from an external source." The external source is a database file, and the "other objects" are the data aware data sinks that we have already covered. At its most basic, a data source is a class module that exposes one or more interfaces to the external data. While not strictly required, custom data sources are usually created as ActiveX components because this greatly simplifies the task of sharing the class between multiple applications or using it as a software component that is used by many programmers. An ActiveX data source can take the form of an ActiveX control, an ActiveX DLL, or an ActiveX EXE. As an ActiveX component your custom data source can easily be shared. Of course, it can interface with data in a wide variety for sources, such as a local Access file, a private OLE DB data store, or a remote SQL server database.

As you might expect, the topic of custom data sources in Visual Basic is very complex if only because it encompasses essentially the entire range of Visual Basic's data access capabilities, and that's a wide range indeed. It may help to simplify if look at custom data sources as having two related topics. First is the general topic of data access in Visual Basic regardless of whether or not the access is part of a custom data source. Second is the question of encapsulating that data access within a custom data source. In the remainder of this column I will try to scratch the surface of the second topic, which taken on its own is not all that complicated.

One of the things possible with a custom data source is to create an emulation of the existing ADO Data Control. Why would you want to do this? One reason is to provide a different visual interface, a not-unreasonable idea since the ADO Data Control's appearance leaves a lot to be desired, at least in my opinion. Another reason is to customize the behavior of the control in some way that better suits your needs. For demonstration purposes I'll combine these two rationales and show you how to create a modified clone of the ADO Data Control that has a better visual interface and is dedicated to a specific database file.

To start, create a new ActiveX Control project. Open the Project Properties dialog box and enter CustomDataSource as the Project Name and Data Source for MYDATA.MDB as the Project Description. Change the UserControl's Name property to CDS (for Custom Data Source) and change its DataSourceBehavior property to vbDataSource.

Next, place a control array of 4 Command Buttons on the UserControl. Do not worry about exact size and placement as these will be set in code. Change the Caption property of the four Command Buttons as follows:

Index           Caption
0               First
1               Previous
2               Next
3               Last

Now add a Label control, changing its Alignment property to 2 - Center. Again, the size and position will be set in code, in the UserControl's Resize event procedure, as shown in Listing 4.

Listing 4. Control sizes are set in the Resize event procedure.

Private Sub UserControl_Resize()  

Dim i As Integer  
Dim x As Integer, y As Integer  
Dim w As Integer, h As Integer  

For i = 0 To 3 
   
x = i * ScaleWidth / 4 
   
y = 0 
   
w = ScaleWidth / 4 
   
h = ScaleHeight * 0.8 
   
Command1(i).Move x, y, w, h  
Next i

Label1.Move 0, ScaleHeight * 0.8, ScaleWidth, ScaleHeight * 0.2  

End Sub

The next step is to define a Caption property, which will be displayed by the Label control. This is accomplished by adding the two property procedures shown in Listing 5.

Listing 5. Property procedures for the Caption property.

Public Property Get Caption() As String 
   
Caption = Label1.Caption  
End Property

Public Property Let Caption(ByVal NewCaption As String) 
  
Label1.Caption = NewCaption 
  
PropertyChanged "Caption"  
End Property

We need two object variables to refer to the ADO connection and recordset that the control will use. These are declared in the general declarations section of the code as shown in Listing 6. You must also open the References dialog box and select Microsoft ActiveX Data Objects 2.0 Library so the class definitions will be available for early binding.

Listing 6. Declaring global object variables to reference the connection and recordset objects.

Private cn As ADODB.Connection  
Private WithEvents rs As ADODB.Recordset

The GetDataMember Event

At the center of a data source is the GetDataMember event. This event is not normally part of an ActiveX control, and is added only when the class's DataSourceBehavior property is set to vbDataSource. GetDataMember is called whenever a bound data sink requests a new data source, or requests a data source for the first time. The event procedure looks like this:

Private Sub object_GetDataMember(DataMember As String, Data As Object)

...

End Sub

The DataMember argument is a string containing the name of the data member to be bound as a data source - in other words, the DataMember property of the data sink that the control is bound to. The Data argument is a reference to the ADO recordset object (or OLEDBSimple provider, but that's a technology I will not cover here). The Data argument is used to return information from the procedure. What this usually means is that code in the GetDataMember event procedure creates a recordset containing the required data, then returns a reference to that recordset in the Data argument. Alternatively, the recordset objects can be created elsewhere, and code in GetDataMember needs only to return a reference to the correct one.

Let's look at an example. Suppose you have written a custom data source, and bound it to a Text Box control. This means that the Text Box's DataSource property is set to the name of the data source control. Suppose also that the Text Box's DataMember property is set to "Sales". Then, when the program starts the data source's GetDataMember event procedure will be called, and the value of the DataMember argument will be "Sales". If, during program execution, code changes the Text Box's DataMember property to "Agents" then the GetDataMember event procedure will be called again, this time with the DataMember argument equal to "Agents".

The code here shows this (note - this code is not part of the demo project). Assume that code elsewhere in the program has established the connection to the database file and created two recordsets, rsSales and rsAgents, that contain the appropriate data.

Private Sub UserControl_GetDataMember(DataMember As String, Data As Object)  

Select Case DataMember 
   
Case "Sales"' 
       
Set Data = rsSales 
   
Case "Agents" 
       
Set Data = rsAgents 
   
Case Else 
       
Err.Raise "DataSource", "Invalid DataMember"  
End Select

End Sub

Returning to the demo project, we can now write the code for the GetDataMember event procedure. Because the project is relatively simple, and the program never requests a change in the data source, the GetDataMember event will fire only once, when the data binding is first established. Since we will not make use of the data sink's DataMember property, this too can be ignored (a blank string is passed to the DataMember argument of GetDataMember if the bound data sink's DataMember property is blank). Therefore we can use this event procedure to set up the ADO Connection and Recordset objects and establish the connection.

he code is shown in Listing 7. You'll note that I have hard-coded the connection string required to connect to the MYDATA.MDB database file that we are using. You will likely need to edit the path information to indicate the location of your database file. Note also that the code sets the ActiveX control's Caption property to display the name of the record source.

Listing 7. Code in the GetDataMember event procedure.

Private Sub UserControl_GetDataMember(DataMember As String, Data As Object)  

Dim ConString  
Dim RecordSource  

RecordSource = "Contributors"  
Caption = RecordSource  
ConString = "Provider=Microsoft.Jet.OLEDB.3.51;"  
ConString = ConString & "Persist Security Info=False;Data Source="  
ConString = ConString & "C:\documents\VB Projects\binding\mydata.mdb"

Set cn = New ADODB.Connection  
Set rs = New ADODB.Recordset  
cn.ConnectionString = ConString  
cn.Open  
rs.Open RecordSource, cn, adOpenKeyset, adLockPessimistic  
Set Data = rs  

End Sub

This completes the coding for the data source - it's time to take it for a spin.

Testing the Data Source

To test the data source, create a project group by adding a Standard EXE project to the Visual Basic workspace. Be sure that this new project is designated as the StartUp project. Remember, the UserControl designer must be closed to make the ActiveX control available on the toolbar. Then, working with the new Standard EXE project, place an instance of the ActiveX control on the form. It's default name will be CDS1. Next, place a Text Box on the form. With the Text Box selected, select the DataSource property in the properties window. You want CDS1 as the data source, and you'll see that this is available on the drop-down list for the property.

Next, set the Text Box's DataField property to "Name." The drop-down list for this property does not contain a list of available fields because the data source will not be connected to the database table until runtime.

You can now run the project. You'll see that the binding works just the way it should - you can use the buttons on the custom control to move from record to record in the database table, and the Name field for each record is displayed in the Text Box.

But wait, there's more. A custom data source such as the one we just designed can also be complex bound to a data sink. It's remarkably simple, as the difficult details of complex binding are all taken care of you behind the scenes. You can easily see this using the demo project that we just created. Add a Data Grid control to the test form, and set its DataSource property to CDS1. Run the project again and the DataGrid control displays all of the records in the database table.

 Summing Up

 In this and the previous column I have shown you the basics of creating custom data sources and data sinks ( or data consumers in Microsoft-speak) using Visual Basic 6. If there's a single most important take-home message I learned from this, it's how simple it all it is! As is so often the case, Visual Basic takes care of most of the dirty work for you, so that you can devote your energies to the more important aspects of program design. Database programming overall remains a fairly demanding task, made easier (but not easy!) by Visual Basic. As in so many other areas of development, Visual Basic provides you with two ways to approach a project: pre-programmed components that can be dropped in and used for many tasks, and the ability to create your own custom components as needed.