Basically Visual

May/June 1999

by Peter G. Aitken (c)1999

Originally published in Visual Developer magazine

Data Sources, Data Sinks (part 1)

Visual Basic has a lot of strong points, but I think that most programmers would agree that the strongest of its strong points is its database programming capabilities. I read that according to Microsoft 85% of Visual Basic applications involve database access of some sort. Nowhere, to my reasonably extensive knowledge (hey, I'm a columnist so I must know something!), can you find a combination of a powerful programming environment and ready-to-use database tools that even comes close to Visual Basic. Compared with other approaches, Visual Basic makes database application programming as easy as falling off a log. OK, it's not quite that easy but you get the picture! The database power of Visual Basic is, by the way, something I do not advertise to my paying clients. When I bring in yet another project on-time and on-budget I am perfectly content to let them continue to think of me as some sort of programming wizard guru type guy. They need never know that I am merely a competent Visual Basic programmer!

Much of Visual Basic's database power comes in the form of data aware controls. These controls have special capabilities built in to take much of the tedium out of database programming. They fall into two categories. Data sources provide a link between your program and the external data (the database file), while data consumers make use of the data in your program, displaying it to the user for example or using it to create a chart. I tend to prefer the term sink to consumer, partly because of my engineering background and partly because I think of a consumer as a person running amok with a credit card. Much as a heat sink absorbs the heat produced by a hot-running CPU, a data sink "absorbs" the data produced by a data source. Remember, however, that consumer is Microsoft's preferred term.

 If you have done even a little database programming with Visual Basic you have seen data sources and sinks in action. Collectively, data sources and sinks are referred to as data-aware classes. The Data control, or its smarter younger brother the ADO Data control, is probably the best known data source. With a few keystrokes a Data control can be connected to just about any database file. When your program runs it has, through the Data control, access to all the information in the database file. You can view records, navigate from record to record, add new records, and delete or edit existing ones. All of the hairy programming required to directly access a database file is taken care of for you. Alone, however, a data source is not enough.

To make use of the data provided by a data source your program needs one or more data sinks. A data sink is an object that can be bound to external data that is made available by a data source. A Text Box control can act as a data sink. You bind the Text Box to a specific field in the external database - the LastName field in an address list database, for example - and the Text Box will automatically display the LastName data for whatever record is current in the data source. Likewise, if the user edits the data in the Text Box the changes will automatically be made in the database. Quite a few of Visual Basic's controls can act as data sinks, including Check Box, Picture Box, List Box, Masked Edit, and Label. Most data sinks, including those just listed, can be bound to a single field in the source. A few others, including the DataList and DataGrid controls, can be bound to an entire record and display data from multiple fields at once. 

In Visual Basic versions 5 and earlier, data binding was static at run-time. This meant that the binding between a data sink and a data source had to be defined at design time. The inability to modify data bindings at run-time led to a lot of programming complexity. If you wanted a form to display data from two different data sources at different times, you had to create two complete sets of data sink controls, with each set bound to a different data source. Then one set of sinks was made visible, and the other hidden, as needed. With Visual Basic 6 the data binding of the TextBox, CheckBox, Label, FlexGrid, Hierarchical FlexGrid, DataGrid, DataCombo, DataList, Image, and PictureBox controls can be changed dynamically in code. This is permitted only with the new ADODB data sources, and not with the older Data and Remote Data controls.

So far so good, but what I have told you so far is stuff that most Visual Basic programmers already know about. The data sources and sinks that are provided with the Visual Basic development environment are indeed great, but you can go beyond them and create your own data sources and sinks. To be more specific, you can program data-aware classes, and of course the ability to program your own always brings with it the potential for greater flexibility and power. This is an important feature of Visual Basic 6, even though a fairly small percentage of developers will ever use it. This is a fairly extensive topic so I will be devoting two columns to it. This issue I will cover the background and show you how to create a simple-bound data sink. In the next column I'll expand the project to include complex binding and also show how to program a data source.

Some of you may be thinking "Well of course I can program a data source class! There's nothing new about putting code into a class that accesses external data." You would be correct, but missing the point. The important feature of a data source, at least as the term is being used here, is not merely that that it can access external data but that it can be bound to other objects - namely, data sinks. A data source you create can be a lot more powerful than the Data control, and it does not need to have a visual representation. A data source can be an ActiveX control or an ActiveX DLL or EXE, depending on whether or not a visual interface is required. To permit a class to serve as a data source you must change its DataSourceBehavior property from the default vbNone to vbDataSource.

As for data sinks, you have the same choice between an ActiveX control and a DLL or EXE. To serve as a data sink a class's DataBindingBehavior must be set to one of two values. vbSimpleBound permits the class to be bound to a single field in the data table, while vbComplexBound permits binding to an entire record, or row, in the data table.

In the remainder of this column I assume you have at least a fundamental familiarity with databases (tables, records, fields, etc.) and also with the process of creating an ActiveX control. Since we need a database to work with, I created a simple database table, using the Visual Data Manager add-in in Visual Basic. The table has 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. Create a primary index based on the Index field. The table is called Contributors and the file itself is MYDATA.MDB (sorry, that's the best I could come up with!). Add a few records to the table, using any names you like but making sure that the values you enter in the Percent field fall in the range 0-100 (you'll see why later).

Creating the Data Sink

 For demonstration purposes I have purposely devised a simple demonstration, but one that covers all of the important aspects of creating your own data sink. The goal is an ActiveX control that displays a bar graph-style indication of a percentage value, with the bar at half-maximum height for 50% and so on. We will create an ActiveX control that contains two Shape controls as its visual interface. The first Shape control will be sized to fill the entire ActiveX control to provide a white background and a thin black border. The second Shape control will be a different color, and its size will reflect the percentage value that the control is displaying. For example, if the percentage value is 33 then the second shape control will fill the lower 1/3 of the control.

Start a new ActiveX control project and change the Name property of the UserControl to PC (short for Percent Chart). Also change its DataBindingBehavior property to 1 -vbSimpleBound. Place a Shape control on the UserControl and set its Backstyle property to 1 - Opaque, leaving all other properties at their default settings. Add a second Shape control on top of the first one. Change its Backstyle property to 1 - Opaque, its BackColor property to any bright color you like (I used red), and its BorderStyle property to 1 - Transparent. Do not worry about the precise sizes or positions of the Shape controls as they will be set in code. In the Project Properties dialog box change the Project Name to PercentChart and, on the debugging tab, select the Wait for Components to be Created option.

The control's code is fairly simple. It will have a single property called PercentValue. We will use the UserControl's Resize event procedure to position the first Shape control to fill the UserControl. Also, there will be a procedure named Update that sizes the second Shape control to fill the appropriate percentage of the space. The code for the control is shown in Listing 1.

Listing 1. Code in the data consumer ActiveX control.

Dim pPercentValue

Private Sub UserControl_Resize()

' Fill control with Shape1.

Shape1.Move 0, 0, ScaleWidth, ScaleHeight

End Sub

Public Property Get PercentValue() As Variant

PercentValue = pPercentValue

End Property

Public Property Let PercentValue(ByVal vNewValue As Variant)

' Restrict values to 0-100.

If vNewValue <= 100 And vNewValue >= 0 Then

    pPercentValue = vNewValue

    ' Update the display.

    Update

End If

End Property

Private Sub Update()

' Position Shape2 at the bottom of Shape1 and

' with a height equal to the PercentValue property.

Shape2.Move 0, _

  Shape1.Height * (1 - pPercentValue / 100), _

  Shape1.Width, _

  Shape1.Height * pPercentValue / 100

End Sub

That's it - the data sink is complete. You may be thinking that you missed something - other than setting the UserControl's DataBindingBehavior property to VBSimpleBound we have not done anything differently than for a regular ActiveX control that is not a data sink. That's because most of the action takes place outside the control, and involves something called a BindingCollection

The BindingCollection Object

The information that links, or binds, one or more data sinks to a data source is kept in a Binding Collection object. A Binding Collection is a collection, and has many things in common with other Visual Basic collections. There are some differences, however, and note also that the name Binding Collection departs from the usual Visual Basic practice of naming collections as the plural of the member object. Since it is a collection of Binding objects it would normally be called Bindings but because it has some extra properties and methods specific to ADO data binding the name Binding Collection is used instead.

Each Binding Collection object can be associated with only a single data source at any one time. Within the collection, each Binding object defines a link between a field in the data source and a property in the data sink object. The following table describes the properties of the Binding object.

Table 1. Properties of the Binding object.

The Binding Collection object has its own properties and methods. They are described in Tables 2 and 3.

Table 2. Properties of the Binding Collection object.

Table 3. Methods of the Binding Collection object.

The Add method has the following syntax which, as you can see, is very similar to the Add method for other Visual Basic collections:

Add(object, PropertyName, DataField, DataFormat, Key)

object is the data sink that will be bound.

PropertyName is the property of the data sink that will be bound.

DataField. Is the field in the data source that will be bound to PropertyName.

DataFormat is an optional reference to a stdDataFormat object that specifies data formatting.

Key is an optional unique string identifier for the Binding object.

Let's look at an example. Suppose you had a data source named MyDataSource that connected to a database table that included a field named City. Suppose also that you had a data sink named MyDataSink and it had a property named Text. Here's how you would create a BindingCollection object and add a Binding to the collection to link these:

Dim BindColl As New BindingCollection

Set BindColl.DataSource = MyDataSource

BindColl.Add MyDataSink, "Text", "City"

While a Binding Collection is limited to a single data source, you can bind as many different data sinks to that source as you need - or, as is sometimes useful, multiple properties of the same data sink.

Testing the Data Sink

To see our data sink in action we will need a regular Visual Basic project that contains a data source. You can use the usual procedures for testing an ActiveX control, namely creating a project group that includes both the ActiveX control project and a standard EXE project. To create the group, with the ActiveX control project still open select Add Project from the File menu and select Standard EXE. Now you'll have two projects listed in the Project Explorer window. When you close the UserControl designer window by clicking on the X in its title bar, the ActiveX control becomes available for use in the other project, as indicated by the UserControl icon appearing in the Toolbox. Then you can place an instance of the ActiveX control on the form in the Standard EXE project and test it. You can also re-open the UserControl designer to modify the ActiveX control code as needed. To ensure that the Standard EXE project runs when you press F5 or click the Start button on the Visual Basic toolbar, you must set it as the startup project. You do this by right-clicking the project name in the Project Explorer window and selecting Set As Startup from the popup menu.

The test program's form will contain only three controls - an instance of the ActiveX control we just created, of course, plus one Text Box and one ADO Data control to serve as the data source. Remember, in order to use the ADO Data control you must select it in the Components dialog box. After placing the ADO Data control, set its CommandString property to link to the database MYDATA.MDB you created earlier, and set its RecordSource property to "select * from Contributors". Now, when the project runs, the ADO Data control will provide the application with a RecordSet containing all fields from all records in the table Contributors.

The next step is to bind the Text Box control to the Name field. This is done in the usual way, by setting its DataSource property to Adodc1 (the name of the ADO Data control) and its DataField property to Name.

All that's left is to bind the ActiveX control to the data source. Here is where the BindingCollection comes in. You must declare a variable to refer to the BindingCollection object as follows:

Private OBColl As BindingCollection

Then, in the Form_Load procedure, add the code shown in Listing 2. Let's look at what these three lines of code do:

Line 1: Creates an instance of the BindingCollection object.

Line 2: Specifies that the BindingCollection object's data source will be the ADO Data control on the form, named Adodc1.

Line 3: Specifies a binding between the field "Percent" in the data source and the property "PercentValue" in the object "PC1" (which is the default name assigned to the ActiveX control when you placed it on the form).

Listing 2. The test project's Form_Load procedure.

Private Sub Form_Load()

Set OBColl = New BindingCollection

Set OBColl.DataSource = Adodc1

OBColl.Add PC1, "PercentValue", "Percent"

End Sub

The project is ready to run now. When you run it, you'll see the name of the first record displayed in the Text Box, and a representation of the percent value displayed by the ActiveX control. When you use the ADO Data control to navigate between records, you can see the Text Box and the ActiveX control both update automatically.

That was pretty easy, wasn't it? You can create even more impressive data-aware controls when you use complex binding. That's a topic that I'll leave for my next column, when I'll also cover creating a custom data source.