This step-by-step exercise creates a Windows application that displays a data table in a grid.
Begin a New Project
STEP 1: Begin a new Windows application called Ch03SBS (for step-by-step). Make sure that the option Create directory for solution is not selected, or you will create two folders, one inside the other. Click the Save All button to save the project.
STEP 2: In the Solution Explorer, change the form’s filename to Employee- Form.vb and answer Yes to the dialog box. This step also renames the form’s class.
STEP 3: Change the form’s Text property to Employees .
Add a Data Source
STEP 1: If the Data Sources window is not visible, check for its tab docked
with the toolbox. If it is there, click on it; if not, select Show Data Sources from the Data menu.
STEP 2: Click on Add New Data Source in the Data Sources window and the Data Source Configuration Wizard appears. Choose Database for the Data Source type and click Next .
STEP 3: Click on the New Connection button, select Microsoft SQL Server Data- base File (SqlClient) for Data Source , and browse to find the Pubs.mdf database file. (You can find this file in the StudentData folder from the text Web site ( www.mhhe.com/AdvVB2008/ ).) Click OK on the dialog box and Next in the wizard.
Note : If you are using files downloaded from Microsoft, the file- name is lowercase. Just keep the name lowercase and realize that your names will not quite match those in the figures. Alternately, you
can rename the file (and its accompanying pubs_log.ldf, if present) using Windows Explorer before you select the database in the project. Do not attempt to rename the database after adding it to the project.
STEP 4: A message appears indicating that the connection you selected uses a local data file that is not in the current project and asks if you would like to copy it. SelectYes .
STEP 5: Next you have the option to save the connection string. ClickNext withYes selected (the default).
STEP 6: Expand theTables node from the list of database objects. Then expand the node for the employee table. Note that you can expand the table and use the check boxes to select the fields that you want. Select emp_id, fname, lname, and hire_date.
STEP 7: The default name is PubsDataSet, which is just fine. ClickFinish . Notice the icon in front of the table ( Figure 3.5 ). The icon indicates that the table is set to DataGridView. The drop-down arrow at the end of the name allows you to select different views. Later you will work with the details view.
Drop down to display the view options DataGridView icon
F i g u r e 3 . 5
The PubsDataSet in the Data Sources window.
Display Data in a Grid
STEP 1: Select the employee table from the Data Sources window and drag it
to your form.
STEP 2: Take a look at the component tray. It should contain PubsDataSet,
EmployeeBindingSource, EmployeeTableAdapter, TableAdapterMan- ager, and EmployeeBindingNavigator.
STEP 3: You will want to resize the form and reposition and size the grid as you wish them to appear ( Figure 3.6 ).
STEP 4: Start the program; the data automatically display ( Figure 3.7 ). How
much code did you have to write to display the data?
STEP 5: Click on the form’s Close button to stop the program. Resize the grid, if necessary.
Notice the bar across the top of the form; this is a BindingNavigator bar, created from the BindingNavigator component in the component tray. The bar allows you to reposition to any record in the table and use the buttons to add a record, delete a record, or save the data. Recall that changes are made to the in-memory DataSet, which is disconnected from the original data file.
Examine the Code
After stopping execution, take a look at the automatically generated code. The EmployeeForm_Load event handler contains the code to fill the DataSet from the table adapter:
Me.EmployeeTableAdapter.Fill(Me.PubsDataSet.employee)
You also can see a SaveItem event handler for the BindingNavigator. You will learn more about saving changes in Chapter 5, where you learn to handle updating the database.
Change the Column Headings
You can improve the readability of the grid by changing the column headings. Follow these steps to set each column’s HeaderText property to the desired value.
STEP 1: Click on the grid’s smart tag arrow and select Edit Columns .
STEP 2: In the Edit Columns dialog ( Figure 3.8 ), notice that the fields (columns) appear in the left pane and the properties for the selected column display in the Bound Column Properties pane on the right. The Header- Text property determines the heading displayed on the grid.
F i g u r e 3 . 6
The DataGridView control on the form, with the database components in the form’s component tray.
F i g u r e 3 . 7
Display the DataSet in the DataGridView.
Click on a column header in a grid at run time to sort the data by the
selected column. ■
STEP 3: Change the HeaderText for emp_id to Employee ID , fname to First
Name , lname to Last Name , and hire_date to Hire Date . Make sure
that you are changing the HeaderText and not the Name property.
STEP 4: Click OK and run the program again to see the changes.
Grid Properties
The DataGridView control has many properties that allow you to specify how the user can interact with the grid. Following are some useful properties:
F i g u r e 3 . 8
Change the HeaderText property for the selected column.
Header text
Property Default setting
AllowUserToAddRows True AllowUserToDeleteRows True AllowUserToOrderColumns False AllowUserToResizeColumns True AllowUserToResizeRows True MultiSelect True RowHeadersVisible True
The DataSet Designer
After you add a new data source to your project, a file with the extension .xsd is added to the files in the Solution Explorer. The .xsd file contains the schema definition. When you double-click on the .xsd file, you see the DataSet
Designer ( Figure 3.9 ).
The DataSet Designer is a visual tool that you can use to view and modify the definition of a DataSet. In the DataSet Designer, you can add fields, tables, and relationships. You also can view the data in the database using the designer. The visual display shows a key icon in front of the primary keys for each table.
Notice the TableAdapter for each table, which handles the Fill and
GetData methods for the table. Right-click on the Fill,GetData() row under
the TableAdapter and select Configure to see the SQL statement used to select the data for the DataSet. Click Cancel to close the dialog box when finished looking at it.
Note : There are several other techniques for showing and editing the TableAdapter’s SQL statement in the Query Builder. You will see these tech- niques later in this chapter in the section “SQL.”
Preview the Dataset Data
STEP 1: In the Solution Explorer, double-click on the PubsDataSet.xsd file to
view the DataSet Designer.
STEP 2: Right-click on the table name and select Preview Data .
STEP 3: Click on the Preview button ( Figure 3.10 ).
Table
TableAdapter DataSet
F i g u r e 3 . 9
You can use the DataSet Designer to modify the properties of the DataSet.
Click to view the data
F i g u r e 3 . 1 0
View the Designer’s Code
You can view the code generated by the designer in a couple of ways. You can display the code in the DatasetName.Designer.vb file, which appears in the Solution Explorer when you select Show All Files . To see the code as it executes, place a breakpoint in your program on the line of code containing the Fill method (in the Form_Load event handler). Run the program and step into the code using the Step Into command (F8 or F11, depending on the keyboard setting).