Working with Multiple Database Tables

1
Chapter 9

• Working with Multiple Database Tables

2
Objectives

• Understand master-detail relationships
• Bind control instances to display multiple rows
  from a table
• Define a DataSet relation
• Bind DataGrid controls
• Synchronize two DataGrids
• Save DataSet changes back to a database
• Format a DataGrid
• Navigate through a DataSet and DataTable

3
Master-Detail Relationships (Overview)

• Occurs when a single record in one table
  corresponds to several records in another table
• Master-detail relationships are often called
  one-to many relationships
• A common field links data between the two tables
• One employee (master) has several payroll records
  (detail)
• One credit card (master) may have several
  transactions (detail)

4
Master-Detail Relationships (Example)
Customer 222883 (master)
Transactions for customer 222883 (detail)
5
Configuring DataSets with Multiple Tables

• Create one Connection object
• Create one DataAdapter for each table
• Generate a DataSet for each DataAdapter
• The steps to create a connection and DataAdapter
  are the same as discussed in Chapter 8

6
The SELECT Statement(Syntax)

• Used to select records from a database table
• Syntax
• SELECT FROM tablename WHERE conditionlist
  ORDER BY field1 ASC DESC , field2 ASC
  DESC

7
The SELECT Statement(Syntax Analysis)

• SELECT statement retrieves one or more rows from
  a database table
• FROM keyword and tablename define the table from
  which rows will be selected
• WHERE clause and conditionlist restrict the rows
  that are selected
• ORDER BY clause controls how rows are sorted
• ASC sorts rows in ascending order
• DESC sorts rows in descending order

8
SELECT Statement (Examples)

• Select all records from the table named
  tblCustomers
• SELECT FROM tblCustomers ORDER BY fldLastName
• Select specific fields from tblCustomers
• SELECT fldLastName, fldFirstName FROM
  tblCustomers ORDER BY fldLastName

9
Introduction to Complex Binding

• Complex bound controls display multiple rows
  instead of a single row
• ListBox, ComboBox, and DataGrid are all complex
  bound controls because they display data from
  multiple rows
• Set the DataSource property to the name of the
  DataSet
• Set the DisplayMember property to the navigation
  path
• Navigation path typically contains the table name
  followed by the field name

10
Introduction to a DataSet Relation

• When generating a DataSet, VB .NET creates a
  schema definition file
• File has a suffix of .xsd
• The .xsd file is an XML document
• Use the XML Designer to edit the file either
  visually or by editing the XML directly

11
Schema Definition File
12
Introduction to Constraints

• A constraint is a database rule that cannot be
  violated
• Constraints can prohibit a record from being
  created in a detail table without a corresponding
  record in the master table
• Constraints can prohibit a record from being
  deleted in a master table when corresponding
  records exist in a detail table
• Microsoft Access supports constraints through
  relationships
• These constraints do not cascade to the DataSet
  however. You must create them in VB .NET

13
The XML Designer

• The Schema Definition File (XSD) gets generated
  when you generate the DataSet
• The XML designer allows you to edit the XSD file
  in a visual way (You can also edit the XML
  directly)
• The DataSet tables appear in the XML Designer
• Any relations also appear in the XML Designer
• Relations appear as lines connecting two tables

14
The XML Designer (Illustration)
15
Creating a Relation

• The EditRelation Dialog box allows you to create
  a relation
• Name text box defines the relation name
• Parent element and Child element list box define
  the two tables that will be related
• Key list box defines the key from the master side
  of the relationship
• Fields section contains common fields from master
  and detail tables
• Update rule, Delete rule, and Accept/Reject rule
  controls what happens when key of master record
  is updated or the master record is deleted

16
Edit Relation Dialog Box (Illustration)
Relation name
Master table name
Detail table name
Link fields
17
The DataGrid Control (Introduction)

• Displays data in the form of rows and columns
  much like Access displays a table
• Intersection of a row and column is called a cell
• To bind the DataGrid, set the DataSource property
  to a DataSet
• Set the DataMember property to a table in the
  DataSet

18
The DataGrid Control (Properties 1)

• BackColor property defines the background color
  of the cells in the grid
• BackGroundColor property defines the foreground
  and background colors of the caption appearing at
  the top of the grid
• CaptionVisible property controls whether caption
  is visible or not
• CaptionText contains text appearing in the
  caption
• CurrentRowIndex defines the 0-based index of the
  selected row
• DataMember defines the navigation path to a table
  in the DataSource
• DataSet property defines the DataSet or DataTable

19
The DataGrid Control (Properties 2)

• The GridLineColor and GridLineStyle properties
  define the color and style of the lines that
  divide each cell
• HeaderForeColor and HeaderBackColor properties
  define the text and background colors of the
  header columns
• ParentRowsBackColor and ParentRowsForeColor
  properties define the color of parent rows
• ReadOnly property defines whether the user can
  update records
• TableStyles property is used for custom formatting

20
The DataGrid Control (Methods and Events)

• The Item method gets or sets the value of a
  specific cell
• BeginEdit and EndEdit methods enable and disable
  editing
• Collapse method collapses one or all child rows
  in a master-detail relationship
• Expand method expands child rows in a
  master-detail relationship
• CurrentCellChanged event occurs when a user
  selects a different cell in the DataGrid

21
Anatomy of the DataGrid
CaptionText property appears in title bar
Column headers display column names
Cell
22
Binding DataGrid Controls

• The process is similar to binding a list box or
  combo box
• Do not define the navigation path to a particular
  field because the DataGrid control displays all
  fields
• Typically, bind the DataGrid to the DataRelation
  in a one-to-many scenario

23
Saving DataSet Changes

• The DataSet and DataGrid are bound
• The process to save data is similar to simple
  bound controls
• First, get the added, changed, or deleted records
• Second, update the changes
• Finally, call AcceptChanges on the DataAdapter

24
Saving the DataSet Changes (Example)
Dim pdsInsertedRows As DataSet Dim
pdsModifiedRows As DataSet Dim pdsDeletedRows As
DataSet pdsInsertedRows DsCustomersTransactionsB
ind11. _ GetChanges(DataRowState.Added) pdsMod
fiedRows DsCustomersTransactionsBind11. _
GetChanges(DataRowState.Modified) pdsDeletedRows
DsCustomersTransactionsBind11. _
GetChanges(DataRowState.Deleted)   If Not
pdsInsertedRows Is Nothing Then
odbdaTransactionsBind1.Update(pdsInsertedRows) End
If   If Not pdsModifiedRows Is Nothing Then
odbdaTransactionsBind1.Update(pdsModifiedRows) End
If   If Not pdsDeletedRows Is Nothing Then
odbdaTransactionsBind1.Update(pdsDeletedRows) End
If DsCustomersTransactionsBind11.AcceptChanges()
25
Formatting the DataGrid (Introduction)

• The DataGridTableStyle class represents the
  visual table drawn in a DataGrid control instance
  at run time
• Its purpose is to define the appearance of the
  DataGrid while the program runs
• Standard prefix is "dgts"

26
DataGridTableStyle (Properties)

• AlternatingBackColor property defines the
  background color of alternating rows
• BackColor and ForeColor properties define
  background and text color of cells
• GridLineColor defines the color of the lines that
  divide the cells
• MappingName property contains a text string that
  maps the table to a DataSource (more later)
• The RowHeadersVisible property controls whether
  row headers are visible
• RowHeaderWidth property defines the width of the
  row headers
• SelectionForeColor and SelectionBackColor
  properties define the text and background color
  of the currently selected row

27
Creating a DataGridTableStyle Object

• Create an instance of the DataGridTableStyleClass
• Set the desired properties
• Define the mapping name
• Mapping name contains the name of the table
• Use the TableName property of the DataTable
  object
• Add the DataGridTableStyle to the TableStyles
  Collection of the DataGrid

28
Creating a DataGridTableStyle (Example)
Dim dgtsTransactions As New DataGridTableStyle()
dgtsTransactions.AlternatingBackColor
Color.LightBlue dgtsTransactions.GridLineColor
Color.Silver dgtsTransactions.GridLineStyle
DataGridLineStyle.Solid dgtsTransactions.Selection
ForeColor Color.Blue dgtsTransactions.SelectionB
ackColor Color.Silver dgtsTransactions.MappingN
ame _ DsCustomersTransactionsBind11.tblTrans
actions.TableName dgTransactions.TableStyles.Add(
dgtsTransactions)
29
The GridColumnStyle Class (Introduction)

• Defines a customized view or format for a column
• One instance exists for each column
• HeaderText property allows you to change the text
  appearing in a column header
• Alignment property left justifies, right
  justifies, or centers text
• Width property controls the column width
• One element is automatically added to the
  GridColumnStyles collection when the TableStyles
  are added to the DataGrid

30
DataGridColumnStyle Relationships
GridColumnStyles(0) is the first column
HeaderText Account Alignment Center
31
DataGridColumnStyle (Example)
dgtsTransactions.GridColumnStyles(0).HeaderText
"Account" dgtsTransactions.GridColumnStyles(0).Al
ignment _ HorizontalAlignment.Center
32
Working with Database Objects

• DataSet contains one or more DataTable objects
• Each DataTable contains DataRow objects
• These objects can be accessed programmatically
• Use these objects so as to
• navigate between records
• calculate totals
• perform other tasks

33
Determining Rows in a Table

• DataTable supports a collection named Rows
• Rows collection in turn has a Count property
  containing the number of rows
• Example assume DsCustomersTransaction11 is a
  DataSet, and tblCustomers is the DataTable

Dim pintRows As Integer pintRows _
DsCustomersTransactions11.tblCustomers.Rows.Count
34
Examining all Rows in a DataTable

• Calculate a total of the field named fldAmount by
  examining all table rows

Dim pintCount As Integer Dim pdblTotal As
Double For pintCount 0 To dtTransactions.Rows.Co
unt - 1 pdblTotal _
dtTransactions.Rows(pintCount).Item("fldAmount")
Next
35
Working with Child Rows in a DataRelation
(Introduction)

• Work with the child rows so as to get child rows
  for a particular master record in a
  master-detail relationship
• GetChildRows method takes a DataRelation as its
  argument and returns an array of DataRow objects
• Array contains detail (child) records for a
  particular master record
• GetChildRows methods applies to a DataTable object

36
GetChildRows Method (Syntax)

• Overloads Public Function GetChildRows ( ByVal
  relationname As String ) As DataRow
• relationname contains the name of a DataRelation
  object
• Method returns and array of DataRow objects.

37
GetChildRows Method (Example)

• Get the child rows for the first master record in
  tblCustomers. DataRelation name is
  DsCustomersTransactionsBind11

Dim drArray() As DataRow drArray
DsCustomersTransactionsBind11. _
tblCustomers(0). _ GetChildRows(drtblCustomers
tblTransactions)