Tutorials - True DataControl > Tutorial 14 - Creating Custom Data Sources in Code |
This tutorial demonstrates how to create your own data source by programming event procedures for special True DataControl events. Since the subject of this tutorial is an advanced True DataControl feature, it will be necessary for you to have extensive programming experience to fully comprehend this tutorial. With that in mind, this tutorial presents a brief introduction, followed by a complete code sample.
As described in the previous tutorials, True DataControl's DataMode property specifies the nature of the control’s data source. If DataMode is 0 - DataSource, the control has its data source in a database or any other OLE DB data provider. If DataMode is 1 - MemoryArray, the control stores its data internally in a dedicated XArrayDB object.
The third DataMode setting, 2 - UserEvents, is intended for custom data sources that don’t have a readily available OLE DB provider or for cases where a large volume of data precludes array-based storage. In user events mode, True DataControl gives you full control over the data and sends notifications whenever bound controls request that data be retrieved, updated, added, or deleted. You respond to these notifications by implementing handlers for special True DataControl events: UserReadData, UserWriteData, UserAddData, UserDeleteRow.
UserReadData is fired whenever True DataControl needs to retrieve data from your custom data source. In response, you provide data for the number of rows requested, or for as many rows as you can, if an end-of-file is encountered. Row data consists of the values for each field in the row as well as a bookmark that uniquely identifies the row.
UserWriteData is fired when the end-user has modified some fields in the current row and these changes must be saved to your custom data source.
UserAddData is fired when the end user has added a new row, which must be saved to your custom data source. Your event handler must also provide a bookmark that uniquely identifies the newly added row.
UserDeleteRow is fired when the end user has deleted a row and your custom data source must update itself accordingly.
A special object, RowBuffer, is used to pass row information to and from the event procedures. RowBuffer is essentially an array of rows, each holding a bookmark and the data values for all fields. In response to the UserReadData event, you return rows to the TData control by filling an empty RowBuffer object, passed as a parameter, with retrieved bookmarks and values. In the UserWriteData and UserAddData events, True DataControl passes as a parameter a RowBuffer object containing the modified field values, which your event handler then uses to update your custom data source.
To use True DataControl in user events mode, you must set the DataMode property to 2 - UserEvents on the DataSource property page (or in the Visual Basic Properties window).
Next, you must define fields that will form the structure of your data. This is done using the Fields property page, just as if you were specifying fields for a memory-based data source (as in Tutorial 13). You can also specify fields at run time, using the collection returned by the Fields property.
Start a new project.
Place the following controls on the form (Form1) as shown in the figure: two TData controls (TData, TData2) and two DataGrid controls (DataGrid1, DataGrid2).
Set properties as follows:
Example Title |
Copy Code
|
---|---|
DataGrid1.DataSource TData1 DataGrid1.AllowAddNew True DataGrid1.AllowDelete True DataGrid2.DataSource TData2 DataGrid2.AllowAddNew True DataGrid2.AllowDelete True |
Set the DataMode property of TData1 to 2 - UserEvents and define two fields: CountryNo and Country, with Data Type set to 2 - Integer and 8 - String, respectively. Follow these steps:
Open the True DataControl property pages for TData1. On the DataSource property page select 2 - UserEvents in the DataMode combo box. Select the Fields property page. For each of the two fields, create a new field by clicking the right mouse button and choosing New Field from the context menu. Change the default FIELD_0 name to the appropriate field name by typing it in the Name text box. Select the appropriate type in the Data Type combo box. Press OK to save changes.
5 Set the DataMode property of TData2 to 2 - UserEvents and define three fields with the following Name and Data Type settings: CountryNo (Integer), City (String), and Population (Long). Refer to the previous step if necessary. On the General property page, specify TData1 as the Master of TData2. Next, go to the Fields property page and add a range condition for the CountryNo field by right-clicking it and selecting New Range Condition from the context menu. Type:
Example Title |
Copy Code
|
---|---|
TData1.CountryNo |
in the Value Expression text box and
Example Title |
Copy Code
|
---|---|
TData1.CountryNo <> 0 |
in the Condition text box located to the right of it. This creates a range condition, TData2.CountryNo = TData1.CountryNo, connecting TData2 to its master, TData1. However, the range condition does not apply when TData1.CountryNo = 0, (that is, when Country = All). Click OK to close the property pages.
6 Paste the following source code into the project, which can be found in Tutor14 in the TUTORIAL directory.
Example Title |
Copy Code
|
---|---|
'VB arrays storing our data Dim CountryNumbers, Countries, CityNames, CityCountryNumbers, CityPopulation As Variant 'VB arrays storing our data Dim Begin, Last As Long Private Sub Form_Initialize() 'Fill the storage arrays. In this simplified example correspondence 'between the arrays is obtained simply by putting corresponding data in the same index position. 'And we don't bother to fill the arrays with real data. CountryNumbers = Array(0, 1, 2) Countries = Array("All", "Canada", "USA") CityNames = Array("New York", "Chicago", "Toronto", "Montreal") CityCountryNumbers = Array(2, 2, 1, 1) CityPopulation = Array(10000000, 5000000, 4000000, 3000000) 'not real numbers, of course End Sub Private Sub Form_Load() TData1.ApproxCount = UBound(CountryNumbers) - LBound(CountryNumbers) + 1 End Sub Private Sub TData1_UserReadData(ByVal RowBuf As TrueData60Ctl.RowBuffer, ByVal StartLocation As Variant, ByVal Offset As Long, ApproximatePosition As Long) 'The ReadData event functionality for TData1 and TData2 has much in common, 'that's why it is encapsulated in a ReadData function. 'The ReadData function fills two variables: Begin and Last, so we know where to get 'the data in the storage arrays If ReadData(RowBuf, StartLocation, Offset, Countries) Then For I = Begin To Last 'Now we just need to copy data from storage arrays to RowBuf. 'RowBuf.Bookmark is already filled by the ReadData function. For j = 0 To RowBuf.ColumnCount - 1 Select Case RowBuf.ColumnIndex(j) Case 0 RowBuf.Value(I - Begin, j) = CountryNumbers(I) Case 1 RowBuf.Value(I - Begin, 1) = Countries(I) End Select Next j Next I End If If ApproximatePosition <> -1 Then ApproximatePosition = Begin + 1 End If End Sub Private Sub TData2_UserReadData(ByVal RowBuf As TrueData60Ctl.RowBuffer, ByVal StartLocation As Variant, ByVal Offset As Long, ApproximatePosition As Long) 'The ReadData event functionality for TData1 and TData2 has much in common, 'that's why it is encapsulated in a ReadData function. 'The ReadData function fills two variables: Begin and Last, so we know where to get 'the data in the storage arrays If ReadData(RowBuf, StartLocation, Offset, CityNames) Then For I = Begin To Last 'Now we just need to copy data from storage arrays to RowBuf. 'RowBuf.Bookmark is already filled by the ReadData function. For j = 0 To RowBuf.ColumnCount - 1 Select Case RowBuf.ColumnIndex(j) Case 0 RowBuf.Value(I - Begin, j) = CityCountryNumbers(I) Case 1 RowBuf.Value(I - Begin, j) = CityNames(I) Case 2 RowBuf.Value(I - Begin, j) = CityPopulation(I) End Select Next j Next I End If End Sub Private Sub TData1_UserWriteData(ByVal RowBuf As TrueData60Ctl.RowBuffer, WriteLocation As Variant) Dim I As Long I = CLng(WriteLocation) If Not IsNull(RowBuf.Value(0, 0)) Then 'if Value is Null, it means the field is unchanged CountryNumbers(I) = RowBuf.Value(0, 0) 'save the changed field value to storage array End If If Not IsNull(RowBuf.Value(0, 1)) Then 'if Value is Null, it means the field is unchanged Countries(I) = RowBuf.Value(0, 1) 'save the changed field value to storage array End If End Sub Private Sub TData1_UserAddData(ByVal RowBuf As TrueData60Ctl.RowBuffer, NewRowBookmark As Variant) Dim I As Long I = UBound(CountryNumbers) + 1 'this is the index of the new row - at the end of array NewRowBookmark = I 'we must return the bookmark of the newly added row from the event procedure 'ReDim storage arrays to accommodate the new row ReDim Preserve CountryNumbers(NewRowBookmark) ReDim Preserve Countries(NewRowBookmark) 'fill the new row with data entered by the user If Not IsNull(RowBuf.Value(0, 0)) Then 'if Value is Null, it means the field value was not entered CountryNumbers(NewRowBookmark) = RowBuf.Value(0, 0) 'save the field value to storage array End If If Not IsNull(RowBuf.Value(0, 1)) Then 'if Value is Null, it means the field value was not entered Countries(NewRowBookmark) = RowBuf.Value(0, 1) 'save the field value to storage array End If End Sub Private Sub TData1_UserDeleteRow(Bookmark As Variant) Dim I As Long I = CLng(Bookmark) 'this is the index of the row to be deleted 'shift the arrays' contents to account for row deletion For j = I To UBound(CountryNumbers) - 1 CountryNumbers(j) = CountryNumbers(j + 1) Countries(j) = Countries(j + 1) Next j 'ReDim the arrays, each of them now has one element less ReDim Preserve CountryNumbers(UBound(CountryNumbers) - 1) ReDim Preserve Countries(UBound(CountryNumbers)) End Sub Private Sub TData2_UserWriteData(ByVal RowBuf As TrueData60Ctl.RowBuffer, WriteLocation As Variant) Dim I As Long I = CLng(WriteLocation) If Not IsNull(RowBuf.Value(0, 0)) Then 'if Value is Null, it means the field is unchanged CityCountryNumbers(I) = RowBuf.Value(0, 0) 'save the changed field value to storage array End If If Not IsNull(RowBuf.Value(0, 1)) Then 'if Value is Null, it means the field is unchanged CityNames(I) = RowBuf.Value(0, 1) 'save the changed field value to storage array End If If Not IsNull(RowBuf.Value(0, 2)) Then 'if Value is Null, it means the field is unchanged CityPopulation(I) = RowBuf.Value(0, 2) 'save the changed field value to storage array End If End Sub Private Sub TData2_UserAddData(ByVal RowBuf As TrueData60Ctl.RowBuffer, NewRowBookmark As Variant) Dim I As Long I = UBound(CityCountryNumbers) + 1 'this is the index of the new row - at the end of array NewRowBookmark = I 'we must return the bookmark of the newly added row from the event procedure 'ReDim storage arrays to accommodate the new row ReDim Preserve CityCountryNumbers(NewRowBookmark) ReDim Preserve CityNames(NewRowBookmark) ReDim Preserve CityPopulation(NewRowBookmark) 'fill the new row with data entered by the user If Not IsNull(RowBuf.Value(0, 0)) Then 'if Value is Null, it means the field value was not entered CityCountryNumbers(NewRowBookmark) = RowBuf.Value(0, 0) 'save the field value to storage array End If If Not IsNull(RowBuf.Value(0, 1)) Then 'if Value is Null, it means the field value was not entered CityNames(NewRowBookmark) = RowBuf.Value(0, 1) 'save the field value to storage array End If If Not IsNull(RowBuf.Value(0, 2)) Then 'if Value is Null, it means the field value was not entered CityPopulation(NewRowBookmark) = RowBuf.Value(0, 2) 'save the field value to storage array End If End Sub Private Sub TData2_UserDeleteRow(Bookmark As Variant) Dim I As Long I = CLng(Bookmark) 'this is the index of the row to be deleted 'shift the arrays' contents to account for row deletion For j = I To UBound(CityCountryNumbers) - 1 CityCountryNumbers(j) = CityCountryNumbers(j + 1) CityNames(j) = CityNames(j + 1) CityPopulation(j) = CityPopulation(j + 1) Next j 'ReDim the arrays, each of them now has one element less ReDim Preserve CityCountryNumbers(UBound(CityCountryNumbers) - 1) ReDim Preserve CityNames(UBound(CityCountryNumbers)) ReDim Preserve CityPopulation(UBound(CityCountryNumbers)) End Sub Private Function ReadData(ByVal RowBuf As _ TrueData60Ctl.RowBuffer, StartLocation As Variant, _ ByVal Offset As Long, A As Variant) As Boolean 'This is a utility function helping us to implement the TData_ReadData event procedure. 'This function fills two variables: Begin and Last. 'They are the first and the last index in storage arrays where we can get 'our data from to put it in the RowBuf. 'Begin is calculated from StartLocation and Offset. 'Last is Begin + RowBuf.RowCount - 1 if it does not exceed the array's bounds '(remember that RowBuf.RowCount is the number of rows requested by TData), 'and it is appropriately truncated if it goes past the array end. 'A is the array, we need its lower and upper bounds. 'The function returns True if at least one row is retrieved. 'It also fills the RowBuf.RowCount with the number of rows returned, 'and fills the RowBuf's bookmarks. The only thing remaining to be filled 'in RowBuf is actual data - RowBuf.Value(I,J) Dim I As Long ReadData = False If IsNull(StartLocation) Then 'StartLocation=Null means it is either BOF or EOF If Offset = 0 Then 'There is no sense in Offset=0 in this case RowBuf.RowCount = 0 Exit Function End If If Offset > 0 Then 'StartLocation is BOF, Begin is determined by Offset from BOF Begin = LBound(A) + Offset - 1 End If If Offset < 0 Then 'StartLocation is EOF, Begin is determined by Offset back from EOF Begin = UBound(A) + Offset + 1 End If Else 'StartLocation is a valid bookmark, Begin is determined by that bookmark '(turned into index) and Offset Begin = CLng(StartLocation) + Offset End If If Begin < LBound(A) Or Begin > UBound(A) Then 'Take care of all cases when Begin falls out of array's bounds RowBuf.RowCount = 0 Exit Function End If Last = Begin + RowBuf.RowCount - 1 'this is the normal Last value 'if there is enough rows in the array If Last > UBound(A) Then 'truncate Last so that it won't go past the array bound Last = UBound(A) End If RowBuf.RowCount = Last - Begin + 1 'fill the number of rows returned 'Fill bookmarks for returned rows, each bookmark is just an index in array For I = Begin To Last RowBuf.Bookmark(I - Begin) = I Next I ReadData = True End Function |
One grid displays countries and the other displays cities of the currently selected country. If you select All in the first grid, the second grid displays all cities.
You can add new entries to either grid. You can also delete entries in both grids.
Admittedly, this tutorial requires a significant amount of code to accomplish basically the same results as the memory array project of Tutorial 13. This comparative example was included to demonstrate the power and flexibility of user events mode. For more information on creating custom data sources, see User Events Mode.