Jump Start: A DataGrid Sample for the Northwind Shippers Table
This section constructs an example that pulls together many of the conceptual points and hands-on demonstrations covered in Chapters 1 and 2. In addition, this sample works with a database that all experienced Access database developers know about – the sample Northwind database. The presentation of the example reveals how to create a form in a Windows application project that reflects the most recent data in the Shippers table from the Access database file. The directions for constructing the example accomplish nearly everything with graphical techniques, such as dragging controls and making property assignments by choosing an item from a drop-down box. It takes just one line of code to complete this example!
Building the Jump Start Example
To launch this sample, start a new Windows application; this example names the project JumpStart. (One of my favorite mottos is that real programmers do not use spaces.) The folder for the project is stored in the C:\pawvbnet directory. Our first step will be to add the Connection and DataAdapter objects to our project. There are several approaches to this task. One of the easiest is to drag a table from an Access database to a blank form. After installing Visual Studio .NET, you will automatically have a connection to the Northwind database if you have the database file on your computer in the standard location. In case you need to create a new connection to a database, the steps are itemized in the sidebar titled “Adding a Connection to Server Explorer.”
Note: When referring to
You can start to drag a table from the database to Form1 in the JumpStart project by opening the Server Explorer window; do this by choosing View, Server Explorer. Expand Data Connections in Server Explorer. Then expand the data connection for the Northwind database and drag its Shippers table to the project’s form. This adds two objects to a tray below the form; the object names are OleDbConnection1 and OleDbDataAdapter1. These are the names that Visual Basic .NET automatically assigns to our Connection and Database objects.
You are now in a position to preview the data to which the DataAdapter connects (the Shippers table in the Northwind database). Select the DataAdapter in the tray below Form1. If the Properties window is not already open, expose it by choosing View, Properties Window. Next, click the Preview Data link below the list of DataAdapter properties in the Properties window. In the Data Adapter Preview dialog box, click Fill Dataset. Figure 2-10 shows the results from clicking the button in the dialog box.
From the Name on the button (Fill Dataset) that you clicked, you might be persuaded to believe that you have filled the dataset on the form. That’s wrong! In fact, the Jump Start example has not added a dataset to the project yet. The important clues for what’s happening are the name of the dialog box (Data Adapter Preview) and the link to open the dialog box (Preview Data). This process is useful for confirming the data to which a DataAdapter connects. Click Close to close the dialog box and expose the Properties window again.
Figure 2-10.
The Data Adapter Preview dialog box which lets you preview the data to which a DataAdapter connects without populating a local dataset for a control on the form.
With the DataAdapter still selected, click the Generate Dataset link in the Properties window. This starts the process of adding a local dataset with a structure for Shippers table. If this is the first time you are generating a dataset for the application, Visual Basic .NET will automatically designate DataSet1 as the name and Shippers as the table to add to the Dataset. In addition, the Generate Dataset dialog box appears with the Add this dataset to the design check box selected. It is this last check box that adds a DataSet object to the tray below Form1 when you click OK. If you have been following along with the example, you will see a new object, named DataSet11, in the tray below Form1.
Open Solution Explorer (if it is not already open); do this by choosing View, Solution Explorer. Notice that Solution Explorer lists a file named Dataset1.xsd. This is the file that represents the dataset. If you decide to re-create the dataset from scratch, I recommend that you delete this file from Solution Explorer. The .xsd extension denotes the file as an XML schema file consistent with World Wide Web Consortium (W3C)) standards; see Appendix A if you want a brief introduction to XML. This type of file defines the structure of a data source. At design time, this dataset in the project contains the structure, but not the contents, of the Shippers table from the Northwind database file.
Figure 2-11 shows the Form Design view after the addition of the dataset. .(Server Explorer, which was open previously, has been closed.) The figure shows the DataSet11 object in the tray below the form selected because that was the last object manipulated. You can also see the DataSet1.xsd file in Solution Explorer. Are you confused by the fact that the file name (DataSet1) and the object name (DataSet11) are not the same? This is not a problem. The Properties window reveals that the object name and dataset name correspond to different properties of the same object.
Figure 2-11.
The Form Designer view of the Jump Start example after the addition of a dataset to the project.
You are now ready to add a DataGrid control to the form. The DataSet11 object can serve as the source for the control. (DataGrid controls will be covered more fully in chapters 5 and 9. For now, you can think of a DataGrid control as a control for displaying data with the layout of a datasheet.) Open the Toolbox by choosing View, Toolbox. Before dragging a control from the Toolbox, widen the form slightly; a width of 336 pixels should be adequate for the demonstration. Select Form1 and drag its right border. You can use the Size property for Form1 to see how wide your form is. Then, drag the DataGrid control from the Windows Forms tab in the Toolbox to Form1. Drag the DataGrid so that it fills the width of Form1.
In the Properties window, you can see that Visual Basic .NET automatically assigns the name DataGrid1 to the control. You can optionally close the Toolbox to conserve space by clicking the X at the top of the Toolbox. From the Properties window, click the drop-down control in the DataGrid’s DataSource property. Select DataSet11.Shippers. This selects the local Shippers table from the DataSet11 object as the source for the DataGrid control.
We need just one line of code to complete the Jump Start example. In the Form1 load event procedure, we need to fill the Shippers data table in the DataSet11 object with the Shippers table from the Northwind database file. The .NET Framework documentation refers to a table in a DataSet object as a DataTable object. This DataTable object is part of the DataSet object model that Chapter 1 covered in its closing section and Chapter 7 examines more fully. Open a shell for the form load event procedure by double-clicking Form1 in any blank area. Then, insert the following line of code.
OleDbDataAdapter1.Fill(DataSet11,”Shippers”)
This statement in the form load event instructs the CLR Engine to populate the local Shippers DataTable with the Shippers table from the Northwind database. Each time the form opens, the CLR Engine freshly populates the dataset, which in turn shows in DataGrid1. Therefore, if the table changes between two successive openings of the form, the table will show new contents. In the Jump Start example, there is no Refresh button for re-populating DataGrid1 without re-opening the form. In addition, there is no path for data updated in DataGrid1 to find its way back to the Shippers table in the Northwind database file. Samples in Chapters 7 and 8 will demonstrate this kind of functionality.
Demonstrating the Jump Start Example
After completing the design of the Jump Start example, it’s time to see it in action. Choose Debug, Start to open Form1. This menu selection generates the same result as pressing F5. Notice Form1 shows the contents of the Northwind Shippers table in the DataGrid control on the form. You can re-size the columns in the DataGrid for the width of the data in the columns. Drag the right edge of a column heading to re-size a column. Click the column title to sort the rows in the DataGrid by the values in the column. Clicking the same column title successively toggles the sort order between ascending to descending order. Figure 2-12 shows the DataGrid control displaying the rows in alphabetical order by CompanyName; the default order for rows is the primary key, ShipperID, for the Shippers table.
Figure 2-12.
The Jump Start example showing the data from the Shippers table from the Northwind database in a DataGrid control sorted in alphabetical order by CompanyName.
You can make a change to a value in any cell of the DataGrid. However, if you close and re-open the form, the change disappears. You can close Form1 by clicking the Close control in its top right corner. When you re-open the form, it shows the current data from the Northwind Shippers table. In fact, if you change the data in the Shippers table from within Access, the DataGrid control reflects the changed data the next time that you open the form. However, the DataGrid does not reflect changes to the Shippers table in the Access database file until you re-open the form.