Using Data Sources with the DataGridView Control

The schema file created by the Data Source Configuration Wizard

After you complete the Data Source Configuration Wizard, the new data source is displayed in the Data Sources window you saw in figure 14-1. In addition to this data source, Visual Studio generates a file that contains the schema for the DataSet class. This file defines the structure of the dataset, including the tables it contains, the columns that are included in each table, the data types of each column, and the constraints that are defined for each table. It is listed in the Solution Explorer window and is given the same name you specified for the dataset in the last step of the Data Source Configuration Wizard with a file extension of xsd. In figure 14-5, for example, you can see the schema file named MMABooksDataSet.xsd. As you’ll learn later in this chapter, you can view a graphic representation of this schema by double-clicking on this file.

Beneath the schema file, the Solution Explorer displays the file that contains the generated code for the DataSet class. In this figure, this code is stored in the MMABooksDataSet.Designer.vb file. When you create bound controls from the data source as shown in this chapter, the code in this class is used to define the DataSet object that the controls are bound to. Although you may want to view this code to see how it works, you shouldn’t change it. If you do, the dataset may not work correctly.

By the way, you should know that a dataset that’s created from a dataset class like the one shown here is called a typed dataset. The code in the dataset class makes it possible for you to refer to the tables, rows, and columns in the typed dataset using the simplified syntax you’ll see in this chapter and the next chapter. In contrast, in chapter 16, you’ll learn how to create an untyped dataset. As you’ll see, you create this type of dataset using code.

A project with a dataset defined by a data source

Description

1. After you create a data source, it’s displayed in the Data Sources window. Then, you can use it to create bound controls as shown in this chapter.
   
2. Visual Studio also generates a file that contains the schema for the dataset defined by the data source. This file appears in the Solution Explorer and has a file extension of xsd. It defines the structure of the dataset, including the tables it contains, the columns in each table, the data types of each column, and the constraints for each table.
   
3. Subordinate to the schema file is a file that contains the generated code for the dataset class. Visual Studio uses this class to create a dataset object when you add the data source to a form.

Note

• To see the files that are subordinate to the schema file, click the Show All Files button at the top of the Solution Explorer. Then, expand the node for the schema file.

Figure 14-5.   The schema file created by the Data Source Configuration Wizard

How to use a data source with a DataGridView control

Once you’ve created a data source, you can use a DataGridView control to display a grid that can be used to add, update, or delete that data. The DataGridView control is new to .NET 2.0 and has been designed to work with data sources. Although this control provides much of the same functionality as the DataGrid control that was available with previous versions of .NET, it also contains some significant enhancements.

How to generate a DataGridView control from a data source

By default, if you drag a table from the Data Sources window onto a form, Visual Studio adds a DataGridView control to the form and binds it to the table as shown in figure 14-6. This creates a DataGridView control that lets you browse all the rows in the table as well as add, update, and delete rows in the table. To provide this functionality, Visual Studio adds a toolbar to the top of the form that provides navigation buttons along with Add, Delete, and Save buttons.

To bind a DataGridView control to a table, Visual Studio uses a technique called complex data binding. This just means that the bound control is bound to more than one data element. The DataGridView control in this figure, for example, is bound to all the rows and columns in the Products table.

When you generate a DataGridView control from a data source, Visual Studio also adds four additional objects to the Component Designer tray at the bottom of the Form Designer. First, the DataSet object defines the dataset that contains the Products table. Second, the TableAdapter object provides commands that can be used to work with the Products table in the database. Third, the BindingSource object specifies the data source (the Products table) that the controls are bound to, and it provides functionality for working with the data source. Finally, the BindingNavigator defines the toolbar that contains the controls for working with the data source.

Before I go on, I want to point out that the TableAdapter object is similar to the DataAdapter object you learned about in the previous chapter. However, it can only be created by a designer. In addition, it has a built-in connection and, as you’ll see later in this chapter, it can contain more than one query.

I also want to mention that, in general, you shouldn’t have any trouble figuring out how to use the binding navigator toolbar. However, you may want to know that if you click the Add button to add a new row and then decide you don’t want to do that, you can click the Delete button to delete the new row. However, there’s no way to cancel out of an edit operation. Because of that, you may want to add a button to the toolbar that provides this function. You can learn how to add buttons to a toolbar in the next chapter.

A form after the Products table has been dragged onto it

The controls and objects that are created when you drag a data source to a form

Description

• To bind a DataGridView control to a table in a dataset, just drag the table from the Data Sources window onto the form. Then, Visual Studio automatically adds a DataGridView control to the form along with the other controls and objects it needs to work properly. Because the DataGridView control is bound to the table, it can be referred to as a bound control.
  
• To bind a DataGridView control to a data table, Visual Studio uses a technique called complex data binding. This means that the control is bound to more than one data element, in this case, all the rows and columns in the table.

Figure 14-6.   How to generate a DataGridView control from a data source

How to edit the properties of a DataGridView control

When you generate a DataGridView control from a data source, Visual Studio usually sets the properties of this control and the other objects it creates the way you want them. However, if you want to modify any of these properties, you can do that just as you would for any other type of object. In particular, you’ll probably want to edit the properties of the DataGridView control to change its appearance and function.

To change the most common properties of a DataGridView control, you can use its smart tag menu as shown in figure 14-7. From this menu, you can create a read-only data grid by removing the check marks from the Enable Adding, Enable Editing, and Enable Deleting check boxes. Or, you can let a user reorder the columns by checking the Enable Column Reordering check box.

In addition to editing the properties for the grid, you may want to edit the properties for the columns of the grid. For example, you may want to apply currency formatting to a column, or you may want to change the column headings. To do that, you can select the Edit Columns command to display the Edit Columns dialog box shown in the next figure.

When you run an application that uses a DataGridView control, you can sort the rows in a column by clicking in the header at the top of the column. The first time you do this, the rows are sorted in ascending sequence by the values in the column; the next time, in descending sequence. Similarly, you can drag the column separators to change the widths of the columns. Last, if the Enable Column Reordering option is checked, you can reorder the columns by dragging them. These features let the user customize the presentation of the data.

The smart tag menu for a DataGridView control

Description

1. You can use the smart tag menu of a DataGridView control to edit its most commonly used properties.
   
2. To edit the columns, select the Edit Columns command to display the Edit Columns dialog box. Then, you can edit the columns as described in the next figure.
   
3. To prevent a user from adding, updating, or deleting data that’s displayed in the DataGridView control, uncheck the Enable Adding, Enable Editing, or Enable Deleting check boxes.
   
4. To allow a user to reorder the columns in a DataGridView control by dragging them, check the Enable Column Reordering check box.
   
5. You can edit other properties of a DataGridView control by using the Properties window for the control.

Figure 14-7.   How to edit the properties of a DataGridView control

How to edit the columns of a DataGridView control

Figure 14-8 shows how to edit the columns of a DataGridView control using the Edit Columns dialog box. From this dialog box, you can remove columns from the grid by selecting the column and clicking the Remove button. You can also change the order of the columns by selecting the column you want to move and clicking the up or down arrow to the right of the list of columns.

Finally, you can use the Add button in this dialog box to add a column to the grid. You might need to do that if you delete a column and then decide you want to include it. You can also use the dialog box that’s displayed when you click the Add button to add unbound columns to the grid. You’ll learn how to do that in the next chapter.

Once you’ve got the right columns displayed in the correct order, you can edit the properties for a column by selecting the column to display its properties in the Bound Column Properties window. When you see the Product Maintenance form in the next figure, for example, you’ll see that I changed the HeaderText property for the ProductCode and UnitPrice columns to provide shorter names for the column headers. In addition, I changed the Width property of each column as appropriate. Finally, I used the DefaultCellStyle property to apply currency formatting to the UnitPrice column.

The dialog box for editing the columns of a DataGridView control

Common properties of a column

Description

• You can use the Edit Columns dialog box to control which columns are displayed in the grid and to edit the properties of those columns. To display this dialog box, choose the Edit Columns command from the smart tag menu for the control
  
• To remove columns from the grid, select the column and click the Remove button.
  
• To add a column to the grid, click the Add button and then complete the dialog box that’s displayed. This dialog box lets you add both bound and unbound columns. See chapter 15 for more information on adding unbound columns.
  
• To change the order of the columns, select the column you want to move and click the up or down arrow to the right of the list of columns.
  
• To edit the properties for a column, select the column and use the Bound Column Properties window to edit the properties.

Figure 14-8.   How to edit the columns of a DataGridView control

A Product Maintenance application that uses a DataGridView control

At this point, the DataGridView control and binding navigator toolbar provide all the functionality needed for an application that can be used to maintain the data in the Products table. Figure 14-9 shows how this application appears to the user at runtime. It also presents the code that Visual Studio generates when you create this application, which includes everything that’s necessary to make it work. As a result, you can create an application like this one without having to write a single line of code. If you’ve ever had to manually write an application that provides similar functionality, you can appreciate how much work this saves you.

When this application starts, the first event handler in this figure is executed. This event handler uses the Fill method of the TableAdapter object to load data into the DataSet object. In this example, the data in the Products table of the MMABooks database is loaded into the Products table of the dataset. Then, because the DataGridView control is bound to this table, the data is displayed in this control and the user can use it to modify the data in the table by adding, updating, or deleting rows.

When the user changes the data in the DataGridView control, those changes are saved to the dataset. However, the changes aren’t saved to the database until the user clicks the Save button in the toolbar. Then, the second event handler in this figure is executed. This event handler starts by calling the Validate method of the form, which causes the Validating and Validated events of the control that’s losing focus to be fired. Although you probably won’t use the Validated event, you may use the Validating event to validate a row that’s being added or modified. You’ll see an example of that later in this chapter.

Next, the EndEdit method of the BindingSource object applies any pending changes to the dataset. That’s necessary because when you add or update a row, the new or modified row isn’t saved until you move to another row.

Finally, the Update method of the TableAdapter object saves the Products table in the DataSet object to the MMABooks database. When this method is called, it checks each row in the table to determine if it’s a new row, a modified row, or a row that should be deleted. Then, it causes the appropriate SQL Insert, Update, and Delete statements to be executed for these rows. As a result, the Update method works efficiently since it only updates the rows that need to be updated.

Now that you understand this code, you should notice that it doesn’t provide for any exceptions that may occur during this processing. Because of that, you need to add the appropriate exception handling code for any production applications that you develop so that they won’t crash. You’ll learn how to do that next.

The user interface for the Product Maintenance application

The code that’s generated by Visual Studio

  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 'MMABooksDataSet.Products'
     'table. You can move, or remove it, as needed. 
     Me.ProductsTableAdapter.Fill(Me.MMABooksDataSet.Products)
  End Sub

  Private Sub ProductsBindingNavigatorSaveItem_Click( _
          ByVal sender As System.Object, ByVal e As System.EventArgs) _
          Handles ProductsBindingNavigatorSaveItem.Click
     Me.Validate()
     Me.ProductsBindingSource.EndEdit()
     Me.ProductsTableAdapter.Update(Me.MMABooksDataSet.Products)
  End Sub

The syntax of the Fill method

  TableAdapter.Fill(DataSet.TableName) 

The syntax of the Update method

  TableAdapter.Update(DataSet.TableName)

Description

• Visual Studio automatically generates the code shown above and places it in the source code file when you drag a data source onto a form. If necessary, you can edit this code.
  
• The generated code uses the Fill and Update methods of the TableAdapter object that’s generated for the table to read data from and write data to the database. It also uses the EndEdit method of the BindingSource object to save any changes that have been made to the current row to the dataset.
  
• The Validate method causes the Validating and Validated events of the control that is losing the focus to be fired. You can use the Validating event to perform any required data validation for the form.
  
• Users of a DataGridView control can sort the rows by clicking on a column heading and can size columns by dragging the column separators to the left or right. They can also reorder the columns by dragging them if that option is enabled (see figure 14-7).

Figure 14-9.   A Product Maintenance application that uses a DataGridView control