Jump Start: A DataGrid Sample for the Northwind Shippers Table | Programming Microsoft Visual Basic .NET for Microsoft Access Databases (Pro Developer)

This section constructs an example that pulls together many of the conceptual points and hands-on demonstrations covered in this chapter and Chapter 1. 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, create 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 shown in the sidebar titled, Adding a Data Connection to Server Explorer.

Note

When referring to ADO.NET objects, such as Connection , I will often use the generic name instead of the name specific to an individual .NET data provider. For example, the Connection object has the names OleDbConnection , SqlConnection , and OdbcConnection for the OLE DB, SQL Server, and ODBC .NET data providers, respectively. Using the generic term identifies that we are talking about the same kind of object no matter which data provider an application uses.

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 DataAdapter objects.

Adding a Data Connection to Server Explorer

The following directions describe the steps for adding a connection to Server Explorer. The connection points at the Northwind database in its default path for Access 2002. You can adapt the directions for any Access database file name and path . For the example, I deleted the standard connection that Visual Studio .NET routinely sets for the Northwind database so that I could establish them again under manual control.

Open Server Explorer if it is not already open ; choose View, Server Explorer.
Right-click Data Connections in Server Explorer and choose Add Connection.
On the Provider tab of the Data Link Properties dialog box, select Microsoft Jet 4.0 OLE DB Provider and click Next .
Click the Browse button ( ) next to the Select Or Enter A Database Name box.
In the Select Access Database dialog box, navigate to the Northwind.mdb file in the \Program Files\Microsoft Office\Office10\Samples directory.
Select the database file, and click Open to return to the Data Link Properties dialog box.
Click OK to close the Data Link Properties dialog box and add the connection for the Access Northwind database to Server Explorer.

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.

Figure 2-10: The Data Adapter Preview dialog box that lets you preview the data to which a DataAdapter connects without populating a local dataset for a control on the form

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.

With the DataAdapter still selected, click the Generate Dataset link in the Properties window. This starts the process of adding a local dataset with the structure from the 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 Designer 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) 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 the Chapter Appendix 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 Designer 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 6 and 7. 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 Close at the top of the Toolbox window. 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 to populate the local Shippers DataTable with the Shippers table from the Northwind database. Each time the form opens, the CLR 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 repopulating DataGrid1 without reopening 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 resize the columns in the DataGrid for the width of the data in the columns . Drag the right edge of a column heading to resize 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 and descending. 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 reopen the form, the change disappears. You can close Form1 by clicking the Close control in its top right corner. When you reopen 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 reopen the form.