In Chapter 3, you formatted the columns of a DataGridView at design time by adding a DataSet to the form. This technique has some drawbacks, since the DataSet actually comes from the data tier, and if you remove the form’s Data- Set, you can no longer modify the column formatting.
You can format the columns of a DataGridView at run time, but be aware that binding the grid resets all properties. So if your program performs binding, place the statements to format the columns after the binding is complete.
Private Sub StoreNameComboBox_SelectionChangeCommitted( _ ByVal sender As Object, ByVal e As System.EventArgs) _ Handles StoreNameComboBox.SelectionChangeCommitted ' Retrieve the sales information for the grid. Dim StoreIDString As String
' Retrieve the ID of the selected store.
StoreIDString = StoreNameComboBox.SelectedValue.ToString ' Initialize the grid’s binding.
If Not GridInitializedBoolean Then ' Bind and format the grid.
SalesDataGridView.DataSource = SalesBindingSource
SetUpGridColumns()
GridInitializedBoolean = True
End If
' Filter the grid’s data.
SalesBindingSource.Filter = "stor_id = '" & StoreIDString & "'" End Sub
Private Sub SetUpGridColumns()
' Set up the columns for the grid. Try
With SalesDataGridView
.Columns!stor_id.Visible = False
.Columns!ord_num.HeaderText = "Order Number" .Columns!ord_date.HeaderText = "Date"
.Columns!qty.HeaderText = "Quantity" .Columns!payterms.HeaderText = "Terms" .Columns!title_id.HeaderText = "Title ID" End With
Catch ex As Exception
MessageBox.Show("Error setting up the grid. " & ex.Message) End Try
End Sub
F ormatting code for the data grid should follow the code for bind-
ing. ■
F i g u r e 4 . 2 3
The components for the hands- on programming example.
F i g u r e 4 . 2 2
The planning sketch of the hands-on programming example. StoreNameComboBox StoreIDTextBox CityTextBox SalesDataGridView File Store Name Store ID City Sales
Order Number Date Quantity Terms Title ID
Your Hands-On Programming Example
Create a program to display the sales for a selected store using a multitier ap- plication. Allow the user to select the store name from a combo box. Display the selected store’s sales in a grid.
Planning the Project
Sketch a form ( Figure 4.22 ) that your users sign off as meeting their needs.
Plan the Objects, Properties, and Methods Plan the two tiers. Determine the objects and property settings for the form and its controls and for the data tier class. Figure 4.23 shows the diagram of the program components.
Private module-level variables
ASalesTableAdapter AStoresTableAdapter APubsDataSet
Data Tier
Object Property Setting
Class Name PubsDataTier
Methods Actions—Pseudocode
New (constructor) Instantiate the two TableAdapters and the DataSet.
Fill the DataSet.
GetDataSet Return the DataSet.
Event handlers/methods Actions—Pseudocode
ExitToolStripMenuItem Exit the project.
Form_Load Instantiate the data tier.
Retrieve the DataSet table to fill the combo box. Set up the binding source.
Set the combo box properties. Bind the other controls.
Clear the initial values in the controls. Set listLoadedBoolean = True.
StoreComboBox_SelectionChangeCommitted If listLoadedBoolean = True
Retrieve the data for the selected list item. Set up the binding source for the data grid. Bind the data grid.
SetUpGridColumns.
SetUpGridColumns Hide the first column (the store ID).
Set the column headings for the remaining columns.
Presentation Tier
Object Property Setting
StoreSalesForm Name StoreSalesForm
Text Store Sales
StoreNameComboBox Name StoreNameComboBox
StoreIDTextBox Name StoreIDTextBox
CityTextBox Name CityTextBox
SalesDataGridView Name SalesDataGridView
F i g u r e 4 . 2 4
The completed form for the hands-on programming example.
Write the Project Following the sketch in Figure 4.22 , create the form. Fig- ure 4.24 shows the completed form.
• Set the properties of each of the form objects, according to your plans.
• Create the data-tier component and write the methods, following the pseudocode.
• Write the code for the form. Working from the pseudocode, write each procedure.
• When you complete the code, test the operation several times. Compare the screen output to the data tables to make sure that you are displaying the correct information.
The Project Coding Solution
The Form
'Program: Ch04HandsOn
'Programmer: Bradley/Millspaugh 'Date: June 2009
'Class: StoreSalesForm
'Description: Display store information and sales for a selected ' store. This is the presentation tier, which uses ' the services of the data tier.
Imports System.Data
Public Class StoreSalesForm ' Module-level variables.
Private APubsDataTier As PubsDataTier Private APubsDataSet As PubsDataSet
Private StoresBindingSource As BindingSource Private SalesBindingSource As BindingSource
Private Sub StoreForm_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load
' Set up the data for the combo box and text boxes. Try
APubsDataTier = New PubsDataTier
APubsDataSet = APubsDataTier.GetDataSet ' Set up stores binding source.
StoresBindingSource = New BindingSource With StoresBindingSource
.DataSource = APubsDataSet .DataMember = "stores" .Sort = "stor_name" End With
' Bind the form controls. With StoreNameComboBox
.DataSource = StoresBindingSource .DisplayMember = "stor_name" .ValueMember = "stor_id"
.DataBindings.Add("text", StoresBindingSource, _ "stor_name", False, DataSourceUpdateMode.Never) .SelectedIndex = -1
End With
StoreIDTextBox.DataBindings.Add("text", _ StoresBindingSource, "stor_id", False, _ DataSourceUpdateMode.Never)
CityTextBox.DataBindings.Add("text", _ StoresBindingSource, "city", False, _ DataSourceUpdateMode.Never)
' Clear initial contents. StoreIDTextBox.Clear() CityTextBox.Clear()
' Set up the sales binding source. SalesBindingSource = New BindingSource With SalesBindingSource
.DataSource = APubsDataSet .DataMember = "sales" End With
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message) End Try
End Sub
Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click ' End the program.
Me.Close() End Sub
Private Sub StoreNameComboBox_SelectionChangeCommitted( _ ByVal sender As Object, ByVal e As System.EventArgs) _ Handles StoreNameComboBox.SelectionChangeCommitted ' Retrieve the sales information for the grid. Dim StoreIDString As String
' Retrieve the ID of the selected store.
StoreIDString = StoreNameComboBox.SelectedValue.ToString ' Initialize the grid’s binding.
If Not GridInitializedBoolean Then ' Bind and format the grid.
SalesDataGridView.DataSource = SalesBindingSource SetUpGridColumns()
GridInitializedBoolean = True End If
' Filter the grid’s data.
SalesBindingSource.Filter = "stor_id = '" & StoreIDString & "'" End Sub
Private Sub SetUpGridColumns()
' Set up the columns for the grid. Try
With SalesDataGridView
.Columns!stor_id.Visible = False
.Columns!ord_num.HeaderText = "Order Number" .Columns!ord_date.HeaderText = "Date"
.Columns!qty.HeaderText = "Quantity" .Columns!payterms.HeaderText = "Terms" .Columns!title_id.HeaderText = "Title ID" End With
Catch ex As Exception
MessageBox.Show("Error setting up the grid. " & ex.Message) End Try
End Sub End Class
The Data-Tier Class
'Program: Ch04HandsOn
'Programmer: Bradley/Millspaugh 'Date: June 2009
'Class: PubsDataTier
'Description: Data tier for the store sales application.
' Fills and returns the DataSet with stores and sales. Imports System.Data
Public Class PubsDataTier ' Module-level variables. Private ASalesTableAdapter _
As PubsDataSetTableAdapters.salesTableAdapter Private AStoresTableAdapter _
As PubsDataSetTableAdapters.storesTableAdapter Private APubsDataSet As PubsDataSet
Public Sub New() Try
' Instantiate the TableAdapters and DataSet. AStoresTableAdapter = New _
ASalesTableAdapter = New _
PubsDataSetTableAdapters.salesTableAdapter() APubsDataSet = New PubsDataSet
' Fill the DataSet.
AStoresTableAdapter.Fill(APubsDataSet.stores) ASalesTableAdapter.Fill(APubsDataSet.sales) Catch ex As Exception Throw ex End Try End Sub
Public Function GetDataSet() As PubsDataSet ' Return the DataSet.
Return APubsDataSet End Function
End Class
S u m m a r y
1. Data in a relational database are stored in multiple related tables. The primary table is the parent or master and the second table is referred to as the child or detail table.
2. The primary key of a table uniquely identifies each record. When the primary key of one table is included in a second table to link the tables together, the key included in the second table is called a foreign key.
3. Relationships may be one-to-many (1:M), many-to-many (M:N), or one-to- one (1:1). An M:N relationship requires a third table, called a junction
table , to join the tables.
4. Constraints may be unique contraints or foreign-key constraints. Enforcing constraints is handled by the database management system to maintain referential integrity.
5. A DataSet with related tables needs one table adapter for each table. A
Fill is required for each adapter.
6. In a 1:M relationship, the one is the parent table and the many is the child.
7. To set up master/detail records, use the node in the Data Sources window that shows the child table beneath the parent table.
8. Relationships can be viewed or edited in the DataSet Designer.
9. You can create bound controls on a form either by dragging tables and/or fields to a form or by dragging to an existing control, which sets up the binding for that control.
10. A parameterized query creates a new DataSet based on the parameter that you supply. A filter selects records from an existing DataSet according to the criteria that you specify.
11. The actual data in a DataSet are held in DataRow objects in the DataRows collection of the table. You can assign a record to a DataRow object and retrieve the data items from each field.
12. You can assign the value of a field to a control, which is referred to as an
unbound control.
13. You can retrieve the parent row of a given child row by using the
GetParentRow method, which returns a DataRow object. You can retrieve
the child rows of a given parent by using theGetChildRows method, which returns an array of DataRow objects.
14. When working with an M:N relationship, each of the tables has a 1:M rela- tionship with the junction table, which is considered a child to both of the other tables. To retrieve related records from the two master tables, get the child records for a row in one master and then get the parent rows from the second master table.
15. In a multitier database application, all data access should be performed in the data tier and all output formatting should be in the form.