Data Binding with Windows Forms and ADO.NET

  • Article

 

Alison Balter, InfoTechnology Partners, Inc.
Paul D. Sheriff, PDSA, Inc.

November 2001

Summary: This article shows how Windows Forms use Microsoft ADO.NET to allow you to easily bind a form to almost any structure that contains data, and then bind controls on the form to specific elements of the data through both simple and complex data binding. (19 printed pages)

Objectives

  • Learn the basics of working with data binding in the Microsoft .NET platform
  • Learn how to build a simple data-bound form
  • Learn how to add combo boxes and list boxes to the data-bound forms that you build
  • Learn how to base data-bound forms on the results of parameterized queries
  • Learn to bind data to text boxes

Assumptions

The following should be true for you to get the most out of this document:

  • You understand what a relational database is and how to access it
  • You have some knowledge of Structured Query Language (SQL)
  • You have access to a database, such as Microsoft SQL Server™ or Microsoft Access

Contents

The Basics of Data Binding and Windows Forms
Building a Simple Data-Bound Windows Form
Working With Combo Boxes and List Boxes
Displaying Data Based on a Parameterized Query
Binding to Text Boxes
Summary

The Basics of Data Binding and Windows Forms

In essence, data binding refers to the process of automatically setting properties of one or more form controls at run time from a structure that contains data. Microsoft® Windows® Forms use Microsoft ADO.NET under the covers to implement data binding. With data binding, you do not need to explicitly write the code that instantiates a connection and creates a dataset (as you would with an unbound form). The Wizards associated with Windows Forms write the necessary ADO.NET code for you.

Windows Forms allow you to bind easily to almost any structure that contains data. This means that you can use ADO.NET to bind to traditional data stores, such as data stored in a Microsoft Access or Microsoft SQL Server™ table, or you can bind to the result of data read from a file, contained in other controls, or stored in an array. How the data gets into the structure is unimportant for the purposes of this document.

After you have bound a form to data, you can then bind controls on the form to specific elements of the data. The most traditional data binding involves binding the Text property of a text box control to a column of a datasource. You can also bind the graphic of an Image control, the background of a control, or any other property of any control on a form.

Two types of data binding are available for Windows Forms: Simple Data Binding and Complex Data Binding. Each method offers different advantages.

Simple data binding allows you to bind a control to a single data element. The most common use of simple data binding involves binding a single data element, such as the value of a column in a table, to a control on a form. You use this type of data binding for controls that show only one value. Uses of simple data binding include binding data to text boxes and labels.

Complex data binding allows you to bind more than one data element to a control. Using the column example, complex data binding involves binding more than one column or row from the underlying record source. Controls that support complex data binding include data grid controls, combo boxes, and list boxes.

Building a Simple Data-Bound Windows Form

The most basic use of data binding available in the .NET environment displays the contents of a table in a grid. The example below involves the following basic steps (without details):

  1. Build a Windows Form.

  2. Create and configure the dataset to which you wish to bind the form.

  3. Add a data grid control to the form and bind it to the data.

    An example of a form resulting from this process appears in Figure 1.

    Figure 1. The result of creating a simple data-bound Windows Form

Building the Sample Form

The following are the detailed steps required to build the sample form.

  1. Click File, click New, and then click Project. The New Project dialog box appears.
  2. Select the Project Type from the tree-view in the left-hand side of the dialog. For this example, select Visual Basic Projects.
  3. Select Windows Application from the list of templates on the right side of the dialog.
  4. Supply a name and location for the project you wish to create. Click OK to create the project.
  5. Press F4 to display the properties for the form. Change the Name property of the form to frmCustomers.
  6. Change the Text property to Customer Information.

Creating and Configuring the Dataset

Once you create the project and form, you are ready to create and configure the dataset underlying the form. A dataset is an in-memory cache consisting of tables, relations, and constraints. Each table in the dataset has a collection of columns and rows. Because datasets are aware of both their original and current state, they can track the changes that occur. The data in a dataset is considered updateable.

To begin, create a data adapter that contains the SQL statement that you will use to populate the dataset. To create the data connection and data adapter:

  1. In the Toolbox, on the Data tab, select a SQLDataAdapter. Drag-and-drop it to the form. The Data Adapter Configuration Wizard appears. Click Next.

  2. Click New Connection to create a data connection that the data adapter will use. The Data Link Properties dialog box appears as in Figure 2.

    Figure 2. The Data Link Properties dialog box allows you to specify connection information for the data underlying the bound form

  3. Change the server name to point to the name of your SQL Server. In this example, it is (local).

  4. Change the User name and Password that is needed to log into this SQL Server.

  5. Select the Northwind database as the database to be used for the example (see Figure 2).

  6. Click OK to return to the wizard, and then click Next.

  7. The wizard allows you to designate the Query Type as in Figure 3. Select Use SQL statements and click Next.

    Figure 3. Designate the type of query on which the bound form will be based

  8. Type a SQL statement or click Query Builder to have the wizard build the SQL statement for you. For this example, type the following SQL statement:

    SELECT CustomerID, CompanyName, ContactName, 
   ContactTitle, Country FROM Customers

  9. Click Next and then click Finish to complete the process. Notice that a SQLConnection object named SQLConnection1 and a SQLDataAdapter object named SQLDataAdapter1 appear in the Tray of the form.

    The SQLConnection1 object contains information about how to access the selected database. The SQLDataAdapter1 object contains a query defining the tables and columns in the database that you want to access.

    Note In the example, the SQLDataAdapter is selected from the Toolbox because you are using SQL Server. This object only allows you to connect to SQL Server databases. If you wish to connect to some other type of OLEDB data, select the more generic OLEDBDataAdapter.

Generating the Dataset Class

The next step is to have Microsoft Visual Studio® generate a dataset class based on the query selected during the creation of the data adapter. To generate the dataset class:

  1. Click Data, and then click Generate Dataset. The Generate Dataset dialog box appears as in Figure 4.

    Figure 4. The Generate Dataset dialog box allows you to specify the name and other attributes of the dataset that you are generating

  2. Enter dsCustomers for the name of the New dataset you are creating. Make sure that you select the Add This Dataset to the Designer option. Click OK. This generates the dataset.

After this step completes, you will see a new control, dsCustomers1, in the Tray for this form. This new control is a reference to the dsCustomers.xsd file that was also added to your Solution Explorer window. dsCustomers.xsd is an XML schema definition of the SQL statement that you typed in earlier. There is a class behind this XSD file that you can’t see unless you click Project and then click Show All Files from the menu. Then you can click on the plus sign that appears behind the dsCustomers.xsd file to see the dsCustomers.vb file, as shown in Figure 5.

Figure 5. An XML schema definition (XSD) file has a class module containing the code that loads the dataset into memory

The dsCustomers.vb class has properties that correspond to the actual dataset and properties that correspond to each column specified in your SQL statement. Although you don’t need to do anything with this class, it is useful to know that it is there.

Adding a Data Grid Control to Display the Data

You are now ready to create a form to display the data contained in the dataset. In this example, you will add a single data grid control to the form. The data grid control will display the data contained in the dataset. Here's what's involved:

  1. At the top of the window, click the tab to display the frmCustomers form.
  2. On the Windows Forms tab of the Toolbox, drag a data grid control onto the form. Size the control as appropriate.
  3. Press F4 to display the control properties.
  4. For the DataSource property, select dsCustomers1.
  5. For the DataMember property, select Customers.

The steps you just completed have now bound the dataset to the data grid control. There is just one more step to complete to see the data appear in the data grid control.

Populating the Data Grid Control With Data

Although the data grid control is bound to the dataset, the dataset is not automatically populated when the form is loaded. Use the form's Load event procedure to populate the data grid control with data as the form is loaded.

Private Sub frmCustomers_Load( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load

    ' Load the Data Grid Control
    DsCustomers1.Clear()
    SqlDataAdapter1.Fill(DsCustomers1, "Customers")

End Sub

In the Load event procedure, you first clear the dataset and then fill the dataset using the Fill method of the SQLDataAdapter1 object you created earlier. You need to pass in the name of the table to the second parameter, as this is used by the data grid control to retrieve the correct DataMember (Customers) that you specified earlier in the DataMember property.

Try It Out

At this point, you are ready to see the data from the Customers table displayed in the data grid control. There is one more step to complete before you can run this form. Because you changed the name of the form, you need to inform the project what the Startup form for this project will be.

  1. In the Solution Explorer window, click your project name.
  2. Right-click it and choose Properties from the shortcut menu.
  3. Click the Startup Object combo box and choose frmCustomers from the list.
  4. Click OK.
  5. Press F5 to run this project, and if you have done everything correctly, you should now see customer data within the data grid control similar to that shown in Figure 1.

Working With Combo Boxes and List Boxes

The example you just created is fine for small result sets, but would not be very efficient if the table of customers had thousands of records. The problem is that it retrieves all customers from the Customers table and displays them in the data grid control. Although great for a demonstration, this technique is not practical in a production application.

A database server is optimized to process large volumes of data, but you should only return small result sets. The example shown in the previous section would be much more efficient if the user were to first limit the data by selecting a particular country from a combo box, and then display only those customers for the selected country. This section shows you how to create the data-bound combo box. The next section shows you how to use the combo box to limit the data displayed in the data grid control. The example involves the following basic steps:

  1. Add a second data adapter to the form.
  2. Generate a second dataset.
  3. Add a combo box to the form.
  4. Bind the combo box to the second data adapter.
  5. Populate the combo box with data from the second data adapter.

Adding a Second Data Adapter to the Form

Before you add a combo box to the form, first add a second data adapter to the form. You will use this data adapter to generate a dataset containing a unique list of countries from the Customers table. To add the second data adapter:

  1. In the Toolbox, from the Data tab, select a SQLDataAdapter. Drag-and-drop it to the form. The Data Adapter Configuration Wizard appears. Click Next.

  2. Select the connection to the Northwind database that you created in the previous example. Click Next.

  3. Select Use SQL Statements and click Next.

  4. Enter the following SQL statement:

    SELECT DISTINCT Country FROM Customers

  5. Click the Advanced Options button on this dialog.

  6. Clear the options for Generate Insert, Update, and Delete statements as shown in Figure 6. These statements are not necessary for a dataset used solely to populate a combo box. Click OK to close this dialog box.

  7. Click Finish to generate the SQLDataAdapter.

There will now be an object named SQLDataAdapter2 added to the Tray of your form.

Figure 6. Do not choose Generate Insert, Update and Delete Statement when creating a dataset used to bind to a combo box

Generating a Second Dataset

The next step is to generate a dataset class and XSD file for this new SQLDataAdapter containing the unique list of countries. Here are the steps involved:

  1. Click Data, and then click Generate Dataset. The Generate Dataset dialog box appears.
  2. Make sure Customers (SqlDataAdapter2) is selected in the list box of tables.
  3. Click New, and enter the name dsCountries. Make sure that you select the Add This Dataset to the Designer option. Click OK. This generates the dataset.

You have added a new object to the Tray named dsCountries1, and a new XSD file to the Solution Explorer named dsCountries.xsd. These two items together create the dataset class and are used to do the data binding on the combo box you are about to create.

Adding a Combo Box to the Form

You are now ready to add the combo box to the form. You need to increase the height of the form, and move the data grid control down to make some room for this new combo box. After doing this, in the Toolbox, you can drag-and-drop a combo box from the Windows Forms tab onto the form. Place it above the data grid control, as shown in Figure 7.

Figure 7. Form containing data grid control and combo box

Binding the Combo Box to the Data Adapter

The combo box that you added is not bound to any data. To bind the combo box to the dsCountries dataset:

  1. Select the combo box.
  2. Change the name to cboCountry.
  3. Set the DataSource property to dsCountries1. This specifies that this is the data source from which to populate this data grid control.
  4. Change the DropDownStyle property to DropDownList.
  5. Set the DisplayMember property to Customers.Country. You will need to click the drop-down list in the DisplayMember property, and then expand the Customers to display the list of columns that you can use. This step designates the column in the data source that is used to populate the data grid control.
  6. Set the ValueMember property of the combo box to Customers.Country. The ValueMember property is used to designate the actual value used when an item is selected.

Populating the Combo Box With Data

As with the data grid control, you must write some code to populate the combo box with data. The code is added to the Load event procedure just under the code you used to load the data grid control.

Private Sub frmCustomers_Load( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load

    ' Load the data grid control
    DsCustomers1.Clear()
    SqlDataAdapter1.Fill(DsCustomers1, "Customers")

    ' Load the Combo Box
    DsCountries1.Clear()    SqlDataAdapter2.Fill(DsCountries1, "Customers")

End Sub

The code shown in bold above is the code you will add to load the combo box. It is very similar to the code you used to load the data grid control. You are now loading the second DataAdapter control that you created.

Try It Out

Let’s run this code to see if the countries are being loaded.

  1. Press F5 to run this form.

  2. Click the Countries drop-down combo box to see if there are countries loaded into the list. If you have done everything correctly, they should be there and your screen may look something like Figure 8.

    Figure 8. Using a drill-down combo box to display a lesser amount of data in a data grid can improve performance

Displaying Data Based on a Parameterized Query

Now that you have the combo box loaded with countries, it is time to use the country selected from the combo box to select the customers to load in the data grid control. The basic steps are:

  1. Remove the code that populates the data grid control as the form is loaded.
  2. Modify the SqlDataAdapter1 object’s SelectCommand property so that it accepts a parameter for the country whose data you wish to display.
  3. Add code that executes when the user selects an item in the combo box.

Remove the Code That Populates the Control as the Form Is Loaded

Now that the data grid control is populated based on the country selected in the combo box, you no longer want it to load when the form is loaded. Remove the following code from the Load event procedure of the form:

    DsCustomers1.Clear()
    SqlDataAdapter1.Fill(DsCustomers1, "Customers")

Modify the SelectCommand Property

The next step is to modify the data adapter's SelectCommand property so that it is based on a parameterized query. The steps are:

  1. In the Tray of the form, click the SqlDataAdapter1 object.

  2. Press F4 to open the Properties window.

  3. Next to the SelectCommand property, click the plus sign to expand the list of sub-properties.

  4. Click the CommandText property. Click Build (…) to display the Query Builder dialog box. (See Figure 9.)

  5. Add a WHERE clause so that the SQL statement looks like this:

    SELECT CustomerID, CompanyName, ContactName, Country
FROM Customers
WHERE Country = @CountryParam

  6. Click OK to accept this change.

  7. Click the SQLDataAdapter1 object.

  8. Click Data, click Generate Dataset, and then click OK to regenerate the existing dataset.

    Figure 9. The Query Builder dialog helps you build SQL statements for your data adapters

Add Code That Executes When the User Selects an Item in the Combo Box

The final piece of required code repopulates the data grid control when a country is selected in the cboCountry combo box. The basic steps to accomplish this are:

  1. Respond to the SelectedIndexChanged event.
  2. Set the parameter in the dataset with the data retrieved from the selected item in the combo box.
  3. Fill the dataset using this parameter.

Follow these steps to add the code and make this work.

  1. Open the form in design mode.

  2. Double-click the cboCountry combo box to display the SelectedIndexChanged event procedure. This is the event that fires when you choose a new item from the combo box.

  3. Write the following code within this procedure.

    Private Sub cboCountry_SelectedIndexChanged( _
 ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles cboCountry.SelectedIndexChanged

    ' Get the Parameter object and Set value
    With SqlDataAdapter1.SelectCommand.Parameters
        .Item("@CountryParam").Value = _
         cboCountry.SelectedValue
    End With

    ' Clear the dataset
    DsCustomers1.Clear()
    ' Load the dataset using the parameter value
    SqlDataAdapter1.Fill(DsCustomers1, "Customers")
End Sub

You need to use the data adapter’s SelectCommand object to retrieve the specified Parameter object. You can then set the Value property of this parameter to the SelectedValue property of the combo box. The SelectedValue property is filled in with the Country name because you set the ValueMember property of the combo box to use the Country field from the dataset. Once you fill in this value, you can fill in the customer dataset and it will automatically repopulate the data grid control with just the customers for that selected country.

Try It Out

At this point, you can run the application again to see how this works.

  1. Press F5 to run the application.
  2. Select a country from the combo box and you should see customers displayed in the data grid control.
  3. Select a different country to see a different set of customers.

Binding to Text Boxes

The previous examples all displayed data in a data grid control. In building applications, it is also necessary to display individual columns in text boxes on a form for editing. Although you won't learn how to edit data in this document, you will learn to display data in text boxes. The mains steps are as follows:

  1. Build a Windows Form that looks like Figure 10.
  2. Create and configure the dataset to which you wish to bind the form.
  3. Add controls to the form and bind them to the dataset.
  4. Add command buttons that allow the user to navigate from row to row.

Follow the steps outlined in the sections above, titled Building the Sample Form, Creating and Configuring the Dataset, and Generating the Dataset Class. These sections create a dataset based on the Customers table. When adding the SQLDataAdapter to the form, select the existing connection to the NorthWind database and build a SQL statement that includes the CustomerID, CompanyName, ContactName, and ContactTitle fields from the Customers table. You are now ready to add controls to the form and bind them to the dataset.

Figure 10. A simple data entry form

Adding Controls to the Form and Binding Them to the Dataset

Add controls to the form and set their properties as designated in Table 1.

Table 1. Controls Used to Build Forms (as shown in Figure 10)

Control Type Property Value
Label Name Label1
  Text CustomerID
TextBox Name txtCustomerID
  Text Blank
Label Name Label2
  Text Company Name
TextBox Name txtCompanyName
  Text Blank
Label Name Label3
  Text Contact Name
TextBox Name txtContactName
  Text Blank
Label Name Label4
  Text Contact Title
TextBox Name txtContactTitle
  Text Blank
CommandButton Name btnPrevious
  Text <
CommandButton Name btnNext
  Text >

You must now bind each text box to a column in the dataset. To do this:

  1. Select the text box that you want to bind.

  2. Press F4 to view the Properties window.

  3. Click to expand the DataBindings properties.

  4. Under DataBindings, select the Text property.

  5. Open the combo box and bind each text box to the appropriate field. For example, to bind the txtCustomerID text box to the CustomerID field, click the plus sign for DsCustomers1, the plus sign for Customers, and then select the CustomerID field.

    Although each field is bound, as with the data grid control example, you must write code to populate the form with data when the form first loads. The code looks like this:

       Public Sub New()
        MyBase.New()

        ' This call is required by the 
        ' Windows Form Designer.
        InitializeComponent()

        ' Add any initialization 
        ' after the InitializeComponent() call
        DsCustomers1.Clear()
        SqlDataAdapter1.Fill(DsCustomers1, "Customers")
    End Sub

Adding Command Buttons to Navigate from Row to Row

The final step is to add code that allows the user to navigate from row to row. The code behind the previous button appears as follows:

    Private Sub btnPrevious_Click _
        (ByVal sender As System.Object, ByVal e As _
        System.EventArgs) Handles btnPrevious.Click
            Me.BindingContext(DsCustomers1, _
                "Customers").Position -= 1
    End Sub

The code uses the BindingContext method of the form to decrement the record pointer in the dataset. The BindingContext tracks the current item for each datasource being used on the form. The code behind the next button looks like this:

    Private Sub btnNext_Click _
        (ByVal sender As Object, ByVal e As _
        System.EventArgs) Handles btnNext.Click
            Me.BindingContext(DsCustomers1, _
               "Customers").Position += 1
    End Sub

The code behind the next button uses the BindingContext method of the form to increment the record pointer in the dataset. The resulting form should look like that shown in Figure 10.

Note   As with the data grid control example, in a production environment you should limit the data displayed on the form. You could, for example, add a combo box to the top of the form and allow the user to select only customers in a specific country. The navigation buttons would then take the user from row to row for the customers in that country.

What’s Different from Visual Basic 6.0

Data binding with Windows Forms is far more robust than data binding in Visual Basic 6.0. With Visual Basic 6.0, you had little control over how the data was bound or what was going on underneath the covers. Using Visual Basic 6.0, when you used bound forms you added a data control and data entry controls to a form. You were basically stuck with the functionality that the data control provided. It was very difficult to troubleshoot or modify the behavior of the data control, because Microsoft did not reveal the source code behind the control to you.

Using data binding with Windows Forms and ADO.NET, you have much better control how the data is bound and how the form behaves. Data binding utilizes the ADO.NET classes and generates class code that you can view and modify. This means that when things don't work, or don't work the way that you intended them to, you are not left with your hands up in the air as you were with Visual Basic 6.0. Data binding with all its limitations in Visual Basic 6.0 was not the optimal choice for production applications; data binding with Windows Forms and ADO.NET is a viable option for the .NET developer.

Summary

Data binding can help you get an application up and going in a very short amount of time. Using data binding, you do not have to write all of the code required by Visual Basic 6.0 unbound forms, but achieve many of the benefits of unbound forms. Although you learned how to use the SQL Server-specific objects in this paper, there are equivalent objects that allow you to connect to any data source using the same exact methods that you just learned. Usually, these operations can be accomplished with very little coding on your part.

In this document you learned:

  • The basics of data binding
  • How to build data bound forms
  • How to work with text boxes, the data grid control, and combo boxes
  • How to limit the data displayed in a form
  • How to create data entry forms
  • How to traverse from row to row

About the Authors

Alison Balter, founder of InfoTechnology Partners, Inc. (www.infotechnologypartners.com), is a highly experienced independent trainer and consultant, specializing in Windows applications training and development. She is a Microsoft Certified Solutions Developer, author of over 300 internationally marketed computer training videos for KeyStone Learning Systems Corporation, and is featured regularly in the National Technological University live satellite television broadcasts. Alison is a regular contributing columnist for Access/Office/Visual Basic Advisor magazine as well as other computer publications. Alison is the author of Alison Balter’s Mastering Access 2002 Desktop Development, Alison Balter's Mastering Access 2000 Development, and is the author or co-author of 5 other books about Microsoft Access development.

Paul D. Sheriff is the owner of PDSA, Inc. (www.pdsa.com), a custom software development and consulting company in Southern California. Paul is the MSDN Regional Director for Southern California, is the author of a book on Visual Basic 6 called Paul Sheriff Teaches Visual Basic, and has produced over 72 videos on Visual Basic, SQL Server, .NET, and Web Development for Keystone Learning Systems. Paul has co-authored a book entitled ASP.NET Jumpstart.

About Informant Communications Group

Informant Communications Group, Inc. (www.informant.com) is a diversified media company focused on the information technology sector. Specializing in software development publications, conferences, catalog publishing and Web sites, ICG was founded in 1990. With offices in the United States and the United Kingdom, ICG has served as a respected media and marketing content integrator, satisfying the burgeoning appetite of IT professionals for quality technical information.

Copyright © 2001 Informant Communications Group and Microsoft Corporation

Technical editing: PDSA, Inc.