Data Binding in Windows Forms 2.0

here’s a lot of new ground to cover about data binding in Windows Forms 2.0, so it’s best to just jump right in. This article assumes you have SQL Server 2005 Express Edition (download here) installed on your computer. I will also use the pubs sample database familiar to most SQL Server developers. 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.

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 (see Figure 1).

?
Figure 1. Add Data Source: The figure shows the process of adding a new data source to the project.
?
Figure 2. Data Source Configuration Wizard: Choose your data source from this wizard.
?
Figure 3. Choosing a Database: The figure shows the process of specifying the information of the database server.

The Data Source Configuration Wizard appears (see Figure 2). You have the option to add a database, a Web service, or a business object as the data source. For simplicity, the example in this article will bind directly to a database; however in a real-world scenario, it’s better practice to bind directly to a business object so as to achieve a multi-tier architecture.

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 (see 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.

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 a Connection String: Here’s how to save the connection string into the app.config file.
?
Figure 5. Table Selection: Select the authors table.

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 (see Figure 5). Click Finish.

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 (see Figure 6).

?
Figure 6. Data Sources: Viewing the data source in the Data Sources window.
?
Figure 7. DataBinding Controls: The four controls at the bottom are added to the form for databinding.

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

Data Binding-related Controls and Usage
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?sed 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
?
Figure 8. Testing: Testing the application.

To test the application, press F5. You should see the records in the authors table displayed in the DataGridView control (see 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.

How does the BindingNavigator add, edit, and delete the records? Actually, the code is all written for you in the Save Data button Click event handler as shown below:

   Private Sub bindingNavigatorSaveItem_Click( _      ByVal sender As System.Object, _      ByVal e As System.EventArgs) _      Handles bindingNavigatorSaveItem.Click   
?
Figure 9. Changing Control Type: Here's how you change the type of control to which you want to bind.
If Me.Validate Then Me.AuthorsBindingSource.EndEdit() Me.AuthorsTableAdapter.Update( _ Me.PubsDataSet.authors) Else System.Windows.Forms.MessageBox.Show( _ Me, "Validation " & "errors occurred.", _ "Save", System.Windows.Forms. _ MessageBoxButtons.OK, _ System.Windows.Forms. MessageBoxIcon.Warning) End If End Sub

Changing Bindings
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” (see Figure 9). Also, change the binding for the au_id field to a Label control.

Remove all of the controls on Form1, and drag and drop the authors table (in the Data Sources window) onto it. Instead of the DataGridView control, you will now see several Label and TextBox controls (see 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 such as employee_name, 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. Binding to Labels and TextBoxes: The figure shows how to use Label and TextBox controls for databinding.
?
Figure 11. Testing: Here’s the application in action.

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

Sorting and Filtering
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.

?
Figure 12. Making Changes: Here’s how to modify the form to add the extra ListBox and Button controls.

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

ListBox1 and ListBox2 will respectively be used to display the first and last names 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 property, au_fname as DisplayMember, and au_id as the ValueMember property (see Figure 13). Do likewise for ListBox2, except that its DisplayMember should be set to au_lname.

Double-click on Button1 and add the following code.

   Private Sub Button1_Click( _      ByVal sender As System.Object, _      ByVal e As System.EventArgs) _      Handles Button1.Click         AuthorsBindingSource.Sort = "au_fname"   End Sub
?
Figure 13. Choosing a Binding Source: Bind the ListBox control to the appropriate BindingSource control.

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 (see Figure 14).

?
Figure 14: Another Test: Here’s how the application should look when you run it.
?
Figure 15. Sorting: You can sort records by first or last name.

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

?
Figure 16: Modifications: Here’s Form1 after adding the new controls.

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

In Form1, add the controls shown in Figure 16.

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   
?
Figure 17. Searching: After making the modifications, you can search for a particular record.
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 (see Figure 17).

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 (see Figure 18).

?
Figure 18. DataSet Configuration: The figure shows the process of configuring the dataset.
?
Figure 19. Table Relationships: You can see the relationship between the three tables in this figure.
?
Figure 20. Customizing Binding: Here’s how you customize the binding for a table.

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).

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?” (see Figure 20).

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

?
Figure 21. Associated Controls: The figure shows where to select the ListBox checkbox.
?
Figure 22. Binding Tables: The figure shows the completed bindings for the two tables.
?
Figure 23. Updated Form: Here’s the updated sample form.

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).

Expand the width of Form1 and drag and drop the titleauthor and titles objects 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.

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

?
Figure 24. More Controls: Adding the bindings adds four new controls.
?
Figure 25. Setting Properties: 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 (see 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 are 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   
?
Figure 26. Final Test: Here's how the application should look after completing all the modifications.
updateTitles() End Sub

The above code ensures 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.

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: