Data Binding

Data Binding

Now we are going to take a look at data binding, which permits us to bind data from a data set to a control. In the sample program we'll construct in this section, we'll examine both data binding and the new DataView object.

A common scenario might be one in which we have our employees' names listed in a drop-down box. When an employee is selected, a data grid is populated with all the orders that the employee has taken. One way to program this functionality is to bind the Employees.LastName field to a combo box. We could then bind the Employees.Title field to a text box and the Employees.EmployeeID to another text box. Then, when a name is selected from the combo box, we use a DataView object to filter for only those records in the child Orders table that pertain to the selected employee. With the built-in functionality of the data grid, we still have the ability to sort on any column. The program is shown in Figure 11-21.

Figure 11-21

The ADO.NET data binding sample program.

Creating the Program

Let's put our program together and see how it works.

1. Start a new Windows project, and name it DataBinding. On the default form, add a combo box, two text boxes, and a data grid, as shown in Figure 11-22. Set the properties for the form and its controls as shown in Table 11-5.

   Table 11-5  Properties and Values for the DataBinding Sample

   Object	Property	Value
   Form	Text	ADO Binding Example
   Combo box	Name	cbEmployee
   	Text	""
   Text box	Name	tbTitle
   	Text	""
   Text box	Name	tbEmployeeID
   	Text	""
   Data grid	Name	dgDataGrid

   Figure 11-22

   The default form with controls added for the data binding sample.

2. After you add the controls, add a SqlDataAdapter object from the Data tab of the toolbox. Select the Northwind connection we've been using in the other sample programs. Keep the Use SQL Statements option selected for the Query Type. In the text box on the Generate The SQL Statements screen, add "Select * From Employees" and complete the wizard's screens. Notice that both the data adapter and the new SqlConnection1 objects have been added.

3. Add a second SqlDataAdapter object and use the same defaults, except add "Select * From Orders" for the SQL statement. Our first data adapter will hold the contents of the Employees table, and the second data adapter will hold the Orders table records.

4. Right-click the form, and select Generate Dataset. Keep the default name, and be sure that both SqlDataAdapter objects are selected. Be sure that Add This Dataset To The Designer is also selected. Click OK to add the new DataSet11.

Adding the Code That Wires the Controls to the Data Set

Because we will be accessing our DataView object from various procedures, we need to give it class-level scope. Add the dvDataView reference variable at the top of our form class.

Public Class Form1 Inherits System.Windows.Forms.Form Dim dvDataView As DataView

In the form's New constructor routine, call setupdata, which initializes our controls and fills the data set. As soon as we're sure that the form and controls are sited and ready to be accessed, we can start building our data elements.

'Add any initialization after the ' InitializeComponent() call setupdata()

Next add the subroutine named setupdata. This subroutine will do the hard work of first filling our data set with the Employees and Orders table records. Then it initializes our two DataTable objects. After that it dimensions and initializes two DataColumn objects and then builds the parent/child relationship between the tables. The combo box and text boxes are bound to fields in the Employees table after that. And finally our DataView object is created.

Private Sub setupdata() Dim tEmployeesTable As DataTable Dim tOrdersTable As DataTable Dim drDataRelation As DataRelation SqlDataAdapter1.Fill(DataSet11, "Employees") SqlDataAdapter2.Fill(DataSet11, "Orders") tEmployeesTable = DataSet11.Tables("Employees") tOrdersTable = DataSet11.Tables("Orders") Dim colParent As DataColumn Dim colChild As DataColumn colParent = _ DataSet11.Tables("Employees").Columns("EmployeeID") colChild = _ DataSet11.Tables("Orders").Columns("EmployeeID") '--Build the Relationship drDataRelation = New DataRelation("EmployeesOrders", _ colParent, colChild) DataSet11.Relations.Add(drDataRelation) '--Complex Binding -- cbEmployee.DataSource = tEmployeesTable cbEmployee.DisplayMember = "LastName" '-- Simple Binding -- tbTitle.DataBindings.Add("Text", tEmployeesTable, "Title") tbEmployeeID.DataBindings.Add("Text", tEmployeesTable, _ "EmployeeID") dvDataView = New DataView(tOrdersTable) End Sub Private Sub setUpGrid() dvDataView.RowFilter = "EmployeeID = '" & _ tbEmployeeID.Text & "'" dgDataGrid.DataSource = dvDataView End Sub Private Sub Form1_Activated(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Activated setUpGrid() End Sub Private Sub TbEmployeeID_TextChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles tbEmployeeID.TextChanged setUpGrid() End Sub

Run the Program

You can now click the drop-down combo box to select an employee. When a new employee is selected, the person's title and EmployeeID are automatically updated in the respective text boxes because we bound them to the Employees table, as you can see in Figure 11-23. Then the data grid is updated to display orders made by that employee. Of course, you can click any column of the data grid to sort the records.

Figure 11-23

Employee information is displayed in controls bound to fields in the table.

How It Works

We know that our form's constructor calls its base constructor to ensure that each of the parent classes is fully initialized. Next the form calls its own InitializeComponent routine to create and initialize the visible controls—the text boxes, combo box, data grid, and various SQL commands inserted by our DataAdapter. When this work is complete, control of the program returns to the constructor and the setupdata routine is called.

Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the ' InitializeComponent() call setupdata() End Sub

The setupdata routine fills our data sets; builds and initializes the Data Table, DataColumns, and DataRelation objects; binds controls; and creates and initializes the DataView object. We will create two tables from the data in our data set as well as a relationship between them.

	A DataTable object represents one table of in-memory data. The DataTable is a central object in the ADO.NET library. Other objects that use the DataTable include the DataSet and the DataView. A bit of esoterica about DataTable objects: they are conditionally case sensitive. By that I mean that if one DataTable is named "dtdatatable" and another is named "dtDataTable", a string used to search for one of the tables is regarded as case sensitive. However, if "dtdatatable" exists and "dtDataTable" does not, the search string is regarded as case insensitive.

The schema of a table is defined by the DataColumnCollection, the collection of DataColumn objects. The DataColumnCollection is accessed through the Columns property. The DataTable also contains a collection of Constraint objects that can be used to ensure the integrity of the data. Constraints are rules that are applied when rows are inserted, updated, or deleted in a table. You can define two types of constraints:

• A unique constraint, which checks that the new values in a column are unique in the table.

• A foreign-key constraint, which defines rules for how related child records should be updated when a record in a master table is updated or deleted.

To determine when changes are made to a data table, use the RowChanged, RowChanging, RowDeleting, or RowDeleted event.

We also dimension a DataRelation object. As you know, there are many times when your application needs to work with related tables. Although our data set contains tables and columns as in a database, it does not inherently include the database's native ability to relate tables. To accomplish this, we create DataRelation objects that establish a relationship between a parent and a child table based on a common key. This is done in memory on the client side.

Private Sub setupdata() Dim tEmployeesTable As DataTable Dim tOrdersTable As DataTable Dim drDataRelation As DataRelation

We have used the Fill method of data adapters before. Here we are simply adding both the Employees and Orders tables to DataSet11.

SqlDataAdapter1.Fill(DataSet11, "Employees") SqlDataAdapter2.Fill(DataSet11, "Orders")

Next we initialize our two DataTable objects. The DataSet class includes the Tables collection of data tables. The DataTable class includes the Rows collection of table rows, the Columns collection of data columns, and the Child Relations and ParentRelations collections of data relations.

tEmployeesTable = DataSet11.Tables("Employees") tOrdersTable = DataSet11.Tables("Orders")

A DataTable contains a collection of DataColumn objects referenced by the Columns property of the table. This collection of columns, along with any constraints, defines the schema, or structure, of the table. You create DataColumn objects within a table by using the DataColumn constructor or by calling the Add method of the Columns property of the table, which is a DataColumnCollection. We need a column in each table to use as a basis for the parent/child relationship.

Dim colParent As DataColumn Dim colChild As DataColumn colParent = _ DataSet11.Tables("Employees").Columns("EmployeeID") colChild = _ DataSet11.Tables("Orders").Columns("EmployeeID")

Now that we have the parent column in the Employees table and the child column in the Orders table defined, we are ready to build the relationship between the tables. In the constructor, we first give the relationship a name, EmployeesOrders, and then the two columns that we want to relate. When the DataRelation is built, it's added to the Relations collection of DataSet11.

'-- Build the Relationship drDataRelation = New DataRelation("EmployeesOrders", _ colParent, colChild) DataSet11.Relations.Add(drDataRelation)

Now we want to bind the LastName column of the Employees table to the combo box. This way, it will automatically be populated with the employees' last names. There are two types of binding—simple and complex. Simple data binding means a single value within a data set is bound to something such as a property of a control or a form. Any property of a component, such as the Text property of a text box, can be bound to any value in a data set. Complex data binding, on the other hand, means components are able to bind to a data set. So our simple binding of the two text boxes will display a single field, and the combo box complex binding will display all of the values of that field. Some components that can take advantage of complex binding include the data grid and the combo box controls.

'-- Complex Binding -- cbEmployee.DataSource = tEmployeesTable cbEmployee.DisplayMember = "LastName" '-- Simple Binding -- tbTitle.DataBindings.Add("Text", tEmployeesTable, "Title") tbEmployeeID.DataBindings.Add("Text", tEmployeesTable, _ "EmployeeID")

Finally we want to call the constructor of our class-visible DataView object. A DataView can be customized to present a subset of data from the DataTable. You might want to have two DataView objects bound to the same DataTable but with each showing a different view of the data. For example, one control might be bound to a DataView showing all the rows in the table, while a second might be configured to display only the rows that have been deleted from the DataTable. The DataTable also has a DefaultView property that returns the default DataView for the table.

dvDataView = New DataView(tOrdersTable)

We want the DataView to show only orders for a specific employee, so we set the RowFilter property on the DataView when the user selects a specific employee from the combo box.

We just saw how our procedure setupdata is called when the form is initialized. When the form's Activated event handler is fired, we can be sure that all the visible controls on the form have been built, sited, and initialized. If you ever want to programmatically place data in a text box or other control, you should always do this from the Activated event handler. When the Activated event handler fires, signaling that everything is set up, we call the setUpGrid routine to display the order records for the currently selected employee.

Private Sub Form1_Activated(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Activated setUpGrid() End Sub

When the form is initialized, the first employee record will be the current record. When setUpGrid is called, we take the contents of the bound tbEmployeeID text box as part of our DataView.RowFilter string. This string will retrieve only the records in the Orders table for that particular employee. The DataView and DataSet are independent objects. The DataView holds a link to the parent table, Employees in our example. The DataView does not cache the table, nor does it make an internal copy of the data. The DataView object simply contains information about the way and the order in which the content of the table must be shown. Once we define the filter for the data we want to be shown, the dvDataView object is set as the DataSource property of the grid.

Private Sub setUpGrid() dvDataView.RowFilter = "EmployeeID = '" & _ tbEmployeeID.Text & "'" dgDataGrid.DataSource = dvDataView End Sub

The DataView is an interesting animal because in classic ADO there was no way to get an independent view of our data. The filtered recordset in classic ADO was really always the same object, except that it displayed fewer records than were in the recordset. Here we can dim two DataView objects, for example, and have each provide a completely different view of our data source. Not only that, but each view can have its own set of properties, methods, and events. In our example, we build our DataView to show only those orders that are tied to the current salesperson. We could build another to show only those records in the beverage category, for example. Both could be managed independently. Figure 11-24 illustrates the workings of the DataView object.

Figure 11-24

The workings of the DataView object.

Of course, you can only use a DataView when it is linked to a non-empty data table. The DataView constructor gives you a DataView object based on a data table. The DefaultView property of a data table returns a DataView object that is initialized on all the records of that table. The two properties you will use the most often with a DataView are Rowfilter and Sort. The filter string is really a concatenation of expressions. You take any column name that can be compared against a literal, a number, a date, or even another column. The wildcard characters * and % are supported. I find that I use them often with the LIKE command. So you could do something like this:

DvDataView.Rowfilter = "Lname LIKE Sm*th"

This statement would return records with names such as Smith and Smyth.

Updating Our Data Grid

Because the tbEmployeeID text box is bound to the EmployeeID field of the Employees table, the EmployeeID for the current Employee will automatically be displayed. We can simply call the setUpGrid routine whenever the Text Changed event handler fires. When the user selects another employee from the combo box, the tbEmployeeID field will change, so setUpGrid is called to display only the records for that employee.

Private Sub TbEmployeeID_TextChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles tbEmployeeID.TextChanged setUpGrid() End Sub