Data binding is one of the common tasks that developers working with data have to perform. In Windows Forms 2.0, the new BindingNavigator and BindingSource objects can save you a lot of effort in data binding. This article will show how to perform tasks such as sorting and searching records using data binding. I will also show how to display master-detail relationships between tables using data binding in Windows Forms 2.0.

Building the Application

Using Visual Studio 2005, create a new Windows application by choosing New Project from the File menu. Click on the Visual Basic (Windows) project type, and select the Windows Application template. Call the new app something like Databinding, and click OK.

Note that when an author’s first name is selected in ListBox1, its last name is automatically selected in ListBox2 (and vice versa).

The first step is to add a new data source to the project. You do so via the Data menu then pick the Add New Data Source… menu item (Figure 1).

Figure 1: Adding a new data source to the project.

The Data Source Configuration Wizard will appear (Figure 2). You have the option to add a database, a Web service, or a business object as the data source. For simplicity, my example in this article will bind directly to a database. In a real-world scenario, it is a best practice to bind directly to a business object so as to achieve a multi-tier architecture.

Figure 2: Choosing a dat Select Database as the source of data and click Next. In the Data Source Configuration Wizard window, click New Connection…. to establish a connection with the SQL Server. For the server name, enter .\SQLEXPRESS (for locally installed SQL Server 2005 Express) and select the pubs database (Figure 3). Click Test Connection to verify the connection. Click OK to return to the Data Source Configuration Wizard window and then click Next to proceed to the next step.a source.
Figure 3: Specifying the information of the database server.

In the next step, you will be asked to save the connection string created in the previous step to the application configuration file (Figure 4). You should accept this option as this allows you to change your database server easily by modifying the app.config file after the application has been deployed. Click Next.

Figure 4: Saving the connection string into app.config.

In the next step, you will select the table(s) that you want to use from the pubs database. Expand the Tables node and check the authors table (Figure 5). Click Finish.

Figure 5: Selecting the authors table.

To view the data source that you have just added to your project, go to the Data menu and choose Show Data Sources. You should see the authors table with its various fields listed under the pubsDataset object (Figure 6).

Figure 6: Viewing the data source in the Data Sources window.

Drag the authors item in the Data Sources window and drop it onto the default Form1. You will see that a DataGridView control is automatically added to the form along with a BindingNavigator control and three others (the forth-AuthorsBindingNavigator-is actually the BindingNavigator control) at the bottom of the form (Figure 7).

Figure 7: Controls added to the form for databinding.

Here are the uses of the various controls:

  • PubsDataSet-A dataset used to represent the tables and relationships in the pubs database.

AuthorsBindingSource-The component that binds your controls (in this case the DataGridView) to the data sources.

  • AuthorsTableAdapter-Used to fill the dataset (in this case the PubsDataSet) with records from the data sources.

AuthorsBindingNavigator-Used for navigating records during runtime using the VCR-style navigation buttons.

When the form is loaded, the AuthorsTableAdapter is used to fill the PubsDataSet object (the code below is automatically generated by Visual Studio 2005; there is no need for you to write it).

Private Sub Form1_Load( _
   ByVal sender As System.Object, _
   ByVal e As System.EventArgs) _
   Handles MyBase.Load
    
'TODO: This line of code loads     
' data into the     
' PubsDataSet1.titles'     
' table. You can move, or remove     
' it, as needed.    
    Me.AuthorsTableAdapter.Fill(_
       Me.PubsDataSet.authors)
    
End Sub

To test the application, press F5. You should see the records in the authors table displayed in the DataGridView control (Figure 8). Use the navigational buttons to move between records. To add a new record, click on the Add New button and enter the details for the new record. To save the changes to the database, click the Save Data button. Likewise, to delete a record, select a row and then click the Delete button and then the Save Data button to persist the changes to the database. To edit a row, make the changes in the required cell and then click the Save Data button.

Figure 8: Testing the application.

How does the BindingNavigator add, edit, and delete the records? Actually, the code is all written for you (in the Save Data button; see Listing 1).

Changing the Binding

By default, the authors table is bound to a DataGridView control. You can change it to bind to some other controls. In the Data Sources window, click on the drop-down listbox for authors and select Details (Figure 9). Also, change the binding for the au_id field to a Label control.

Figure 9: Changing the controls type to bind.

Remove all of the controls on Form1 and drag and drop the authors table (in the Data Sources window) onto Form1. Instead of the DataGridView control, you will now see several Label and TextBox controls (Figure 10). The text in the Label controls are based on the field names in the table. For example, the au_id field is interpreted as “au id”. Visual Studio 2005 is smart enough to guess the appropriate name to use for the Label controls. (Of course, in this example the label texts are less than ideal, but if you have a field name like employee_name, then it works out pretty well). Also, note that a Label control is used for the value of the au_id field, while the other fields use TextBox controls. This is based on the changes that you have just made.

Figure 10: Using Label and TextBox controls for databinding.

Press F5 to test the application. You can now view individual records by using the BindingNavigator control (Figure 11).

Figure 11: Testing the application.

Sorting

While the BindingNavigator control allows you to navigate records by using the VCR-style controls, it is not a practical solution when you have a large number of records. It would be much better to have a list of records that users can select directly.

If you now double-click on the pubsDataSet.xsd object in Solution Explorer, you will be able to visually examine the relationships between the three tables (see Figure 19).

To enhance the application, add two ListBox controls and two Button controls onto the form (Figure 12).

Figure 12: Modifying the form.

ListBox1 and ListBox2 will respectively be used to display the first name and last name of the authors. Button1 allows you to sort the authors by first name while Button2 sorts the authors by last name. In the smart tag of the ListBox1 control, select the AuthorsBindingSource as the DataSource, au_fname as DisplayMember, and au_id as ValueMember (Figure 13). Do likewise for ListBox2, except that its DisplayMember should be set to au_lname.

Figure 13: Binding the ListBox control to the appropriate BindingSource control.

Double-click on Button1 and code the following.

Private Sub Button1_Click( _
   ByVal sender As System.Object, _
   ByVal e As System.EventArgs) _
   Handles Button1.Click
    
AuthorsBindingSource.Sort = _
   "au_fname"
    
End Sub

Essentially, you are sorting the authors by the author’s first name. Likewise, add this code to Button2 so you can sort by last name.

Private Sub Button2_Click( _
   ByVal sender As System.Object, _
   ByVal e As System.EventArgs) _
   Handles Button2.Click
    
AuthorsBindingSource.Sort = _
   "au_lname"
    
End Sub

To test the application, press F5. You can now either select an author’s first name or last name and the corresponding author’s detail will be shown in the controls on the right (Figure 14).

Figure 14: Testing the application.

You can also sort the names by either first name or last name (Figure 15).

Figure 15: Sorting the records by first or last name.

Filtering

Besides sorting records, you can also filter them. This is useful if you want to search for certain records.

In Form1, add the controls as shown in Figure 16.

Figure 16: Adding the new controls.

Double-click on Button3 and code the following.

Private Sub Button3_Click( _
   ByVal sender As System.Object, _
   ByVal e As System.EventArgs) _
   Handles Button3.Click
    
    If TextBox1.Text = "" Then
       AuthorsBindingSource. _
          RemoveFilter()
    Else
       AuthorsBindingSource.Filter = _
          "au_id LIKE '*" & _
          TextBox1.Text & "*'"
End If    
    
End Sub

The above code searches for records based on au_id. If the search string is empty, the filter will be removed and all records will be displayed. To test the application, press F5. Enter some numbers and all records with au_id field containing the search string will be displayed (Figure 17).

Figure 17: Searching for a record.

Displaying Master-Details Relationships

Another common scenario in data binding is displaying master-details relationships. For example, in the pubs database, several tables are related. The authors table is related to the titles table through the titleauthor table. For example, using the above example, if a particular author is selected, it might be useful to display the title of all books written by that author.

To do so, let’s add two more tables to the pubsDataSet object. In the Data Sources window, right-click on pubsDataSet and select the Configure DataSet with Wizard… item (Figure 18).

Figure 18: Configuring the dataset.

In the Choose your database objects dialog box, choose the titleauthor and titles tables. The pubsDataSet object should now contain three tables: authors, titleauthor, and titles (Figure 19).

Figure 19: The relationship between the three tables in the pubs database.

To bind the titleauthor table to a ListBox control, you need to change its binding in the Data Source window. Click on the drop-down list for the titleauthor item and select Customize… (Figure 20).

Figure 20: Customizing the binding for a table.

Under the Associated controls section (Figure 21), check the ListBox checkbox and click OK.

Figure 21: Selecting the ListBox checkbox.

In the Data Sources window, ensure that the titleauthor object is bound to the ListBox and the titles object is bound to Details (Figure 22).

Figure 22: The bindings for the two tables.

Expand the width of Form1 and drag and drop the titleauthor and titles object onto the form. Figure 23 shows how the form looks after these changes. Now add two label controls onto the form and set their Text properties as shown.

Figure 23: The updated Form1.
Figure 24: The four new controls added.

You should also observe that there are four new objects created after the drag-and-drop operation (Figure 24).

Figure 25: Setting the Value Member of the ListBox control to title_id.

In the smart tag of the ListBox control, set its ValueMember to title_id (Figure 25).

In the code-behind of Form1, add the following subroutine.

Public Sub updateTitles()
    
TitlesBindingSource.Filter = _
   "title_id='" & _
       TitleauthorListBox. _
       SelectedValue & "'"
    
End Sub

The updateTitles() subroutine will apply a filter to the TitlesBindingSource control (which is bound to the various controls on the right of the form, showing the details of a book) so that only titles with title_id matching the title_id selected in the TitleauthorListBox control is shown.

Double-click on the ListBox1 control so that when an author’s first name is selected, a filter is applied to the TitleauthorBindingSource control to display all titles authored by the selected author in the TitleauthorListBox control.

Private Sub _    
   ListBox1_SelectedIndexChanged( _
   ByVal sender As System.Object, _
   ByVal e As System.EventArgs) _
   Handles _
      ListBox1.SelectedIndexChanged
    
   TitleauthorBindingSource.Filter = _
  "au_id='" & _
      ListBox1.SelectedValue & "'"
   updateTitles()
    
End Sub

Note that you only need to implement the SelectedIndexChanged event for either ListBox1 or ListBox2.

Likewise, double-click the TitleauthorListBox control and code the following.

Private Sub _
TitleauthorListBox_SelectedIndexChanged( _
   ByVal sender As System.Object, _
   ByVal e As System.EventArgs) _
   Handles _
      TitleauthorListBox. _
      SelectedIndexChanged
    
    updateTitles()
    
End Sub

The above code will ensure that when the items in the TitleauthorListBox control are refreshed, the detailed book information of the first title is displayed.

To test the application, press F5. Figure 26 shows the flow of the application.

Figure 26: Testing the application.

Summary

In this article, you have seen how data binding works in Windows Forms 2.0. There is no need for you to write lots of code in order to perform some common tasks. You just need to know the function of each control (such as BindingSource) and configure them accordingly.