Data and Relationships
|
Data and Relationships |
|
|
|
|
Introduction |
|
In the database world, a relationship is a link that exists between two objects, mainly tables, so that data can flow from one object to another. A relationship can do even more than that: it can be used to check the accuracy of information from one object to another, it can be used to update the information in one object when related information in another object has been changed. Remember that whenever you are dealing with a group of records, also called a set of records, or a set of data, or a data set, you need an object that can "translate" the values of your records into values that are data-oriented. The object used to take care of this aspect is implemented through the DataSet class. In this case, since we want to address relationships among tables, we will need a data set object to manage such links.
Imagine that you are creating a list of employees in a
mid-size to large company and want to categorize them by their employment
status. You can start by creating a list of the employees as follows:
This is a classic table. There are two common ways you can create a table. You can generate a table from a database, or you can use the DataTable class that allows you to manually create a table. Here is an example: |
Imports System.Data
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents btnCreate As System.Windows.Forms.Button
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.btnCreate = New System.Windows.Forms.Button
Me.DataGrid1 = New System.Windows.Forms.DataGrid
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'btnCreate
'
Me.btnCreate.Location = New System.Drawing.Point(8, 8)
Me.btnCreate.Name = "btnCreate"
Me.btnCreate.TabIndex = 0
Me.btnCreate.Text = "Create"
'
'DataGrid1
'
Me.DataGrid1.AlternatingBackColor = System.Drawing.Color.Lavender
Me.DataGrid1.BackColor = System.Drawing.Color.WhiteSmoke
Me.DataGrid1.BackgroundColor = System.Drawing.Color.LightGray
Me.DataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.None
Me.DataGrid1.CaptionBackColor = System.Drawing.Color.LightSteelBlue
Me.DataGrid1.CaptionForeColor = System.Drawing.Color.MidnightBlue
Me.DataGrid1.DataMember = ""
Me.DataGrid1.FlatMode = True
Me.DataGrid1.Font = New System.Drawing.Font("Tahoma", 8.0!)
Me.DataGrid1.ForeColor = System.Drawing.Color.MidnightBlue
Me.DataGrid1.GridLineColor = System.Drawing.Color.Gainsboro
Me.DataGrid1.GridLineStyle = System.Windows.Forms.DataGridLineStyle.None
Me.DataGrid1.HeaderBackColor = System.Drawing.Color.MidnightBlue
Me.DataGrid1.HeaderFont = New System.Drawing.Font("Tahoma", 8.0!, System.Drawing.FontStyle.Bold)
Me.DataGrid1.HeaderForeColor = System.Drawing.Color.WhiteSmoke
Me.DataGrid1.LinkColor = System.Drawing.Color.Teal
Me.DataGrid1.Location = New System.Drawing.Point(8, 40)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.ParentRowsBackColor = System.Drawing.Color.Gainsboro
Me.DataGrid1.ParentRowsForeColor = System.Drawing.Color.MidnightBlue
Me.DataGrid1.SelectionBackColor = System.Drawing.Color.CadetBlue
Me.DataGrid1.SelectionForeColor = System.Drawing.Color.WhiteSmoke
Me.DataGrid1.Size = New System.Drawing.Size(408, 216)
Me.DataGrid1.TabIndex = 1
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(424, 262)
Me.Controls.Add(Me.DataGrid1)
Me.Controls.Add(Me.btnCreate)
Me.Name = "Form1"
Me.Text = "Relationships Fundamentals"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim dsEmployment As DataSet = New DataSet
Dim dtEmployees As DataTable = New DataTable("Employees")
Dim dcEmployees(3) As DataColumn
dcEmployees(0) = New DataColumn("FirstName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(0))
dcEmployees(1) = New DataColumn("LastName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(1))
dcEmployees(2) = New DataColumn("Department", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(2))
Dim drEmplRecord As DataRow = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Peter"
drEmplRecord("LastName") = "Larsen"
drEmplRecord("Department") = "Accounting"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Paul"
drEmplRecord("LastName") = "Banack"
drEmplRecord("Department") = "IT/IM"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Helene"
drEmplRecord("LastName") = "Cassavoy"
drEmplRecord("Department") = "Accounting"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Anselme"
drEmplRecord("LastName") = "Thomas"
drEmplRecord("Department") = "Public Relations"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Bertha"
drEmplRecord("LastName") = "Um"
drEmplRecord("Department") = "Corporate"
dtEmployees.Rows.Add(drEmplRecord)
dsEmployment.Tables.Add(dtEmployees)
Me.DataGrid1.DataSource = dsEmployment
Me.DataGrid1.DataMember = "Employees"
End Sub
End Class
 |
To associate each employee with a department, you can first create a table for the departments. Here is an example:
Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim dsEmployment As DataSet = New DataSet
Dim dtEmployees As DataTable = New DataTable("Employees")
Dim dcEmployees(3) As DataColumn
dcEmployees(0) = New DataColumn("FirstName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(0))
dcEmployees(1) = New DataColumn("LastName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(1))
dcEmployees(2) = New DataColumn("Department", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(2))
Dim drEmplRecord As DataRow = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Peter"
drEmplRecord("LastName") = "Larsen"
drEmplRecord("Department") = "Accounting"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Paul"
drEmplRecord("LastName") = "Banack"
drEmplRecord("Department") = "IT/IM"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Helene"
drEmplRecord("LastName") = "Cassavoy"
drEmplRecord("Department") = "Accounting"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Anselme"
drEmplRecord("LastName") = "Thomas"
drEmplRecord("Department") = "Public Relations"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Bertha"
drEmplRecord("LastName") = "Um"
drEmplRecord("Department") = "Corporate"
dtEmployees.Rows.Add(drEmplRecord)
dsEmployment.Tables.Add(dtEmployees)
Me.DataGrid1.DataSource = dsEmployment
Me.DataGrid1.DataMember = "Employees"
Dim dtEmplStatus As DataTable = New DataTable("EmploymentStatus")
Dim dcEmployment As DataColumn = New DataColumn
dcEmployment = New DataColumn("EmplStatus", System.Type.GetType("System.String"))
dtEmplStatus.Columns.Add(dcEmployment)
Dim drEmployment As DataRow = dtEmplStatus.NewRow()
drEmployment("EmplStatus") = "Full Time"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatus") = "Part Time"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatus") = "Contractor"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatus") = "Intern"
dtEmplStatus.Rows.Add(drEmployment)
dsEmployment.Tables.Add(dtEmplStatus)
Me.DataGrid1.DataSource = dsEmployment
Me.DataGrid2.DataSource = dsEmployment
Me.DataGrid1.DataMember = "Employees"
Me.DataGrid2.DataMember = "EmploymentStatus"
End Sub
 |
|
Creating a Relationship |
|
As it should appear obviously, a relationship makes more sense in the presence of at least two tables, although a table can (also) have some type of relationship with itself. By the rules and suggestions of relational databases, for a table to participate in a relationship, the table should provide at least one column that would be used to uniquely represent or identify each record. Such a column is usually called an index. Although it can be positioned anywhere in the table, it is usually the first or most-left column. Also traditionally, the name of this column ends with No, such as RecordNo, or ID suffix, such as RecordID. In our first table, to uniquely identify each record, we can create a column called EmployeeID and add an incremental number to each record. In the same way, a column used to identify each department in the second table of our example can be called EmploStatusID. The tables would then be created as follows: |
Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim dsEmployment As DataSet = New DataSet
Dim dtEmployees As DataTable = New DataTable("Employees")
Dim dcEmployees(4) As DataColumn
dcEmployees(0) = New DataColumn("EmployeeID", System.Type.GetType("System.Int32"))
dtEmployees.Columns.Add(dcEmployees(0))
dcEmployees(1) = New DataColumn("FirstName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(1))
dcEmployees(2) = New DataColumn("LastName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(2))
dcEmployees(3) = New DataColumn("Department", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(3))
Dim drEmplRecord As DataRow = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Peter"
drEmplRecord("LastName") = "Larsen"
drEmplRecord("Department") = "Accounting"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Paul"
drEmplRecord("LastName") = "Banack"
drEmplRecord("Department") = "IT/IM"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Helene"
drEmplRecord("LastName") = "Cassavoy"
drEmplRecord("Department") = "Accounting"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Anselme"
drEmplRecord("LastName") = "Thomas"
drEmplRecord("Department") = "Public Relations"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("FirstName") = "Bertha"
drEmplRecord("LastName") = "Um"
drEmplRecord("Department") = "Corporate"
dtEmployees.Rows.Add(drEmplRecord)
Dim dtEmplStatus As DataTable = New DataTable("EmploymentStatus")
Dim dcEmployment(2)
dcEmployment(0) = New DataColumn("EmplStatusID", System.Type.GetType("System.Int32"))
dtEmplStatus.Columns.Add(dcEmployment(0))
dcEmployment(1) = New DataColumn("EmplStatus", System.Type.GetType("System.String"))
dtEmplStatus.Columns.Add(dcEmployment(1))
Dim drEmployment As DataRow = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "1"
drEmployment("EmplStatus") = "Full Time"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "2"
drEmployment("EmplStatus") = "Part Time"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "3"
drEmployment("EmplStatus") = "Contractor"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "4"
drEmployment("EmplStatus") = "Intern"
dtEmplStatus.Rows.Add(drEmployment)
dsEmployment.Tables.Add(dtEmployees)
dsEmployment.Tables.Add(dtEmplStatus)
Me.DataGrid1.DataSource = dsEmployment
Me.DataGrid2.DataSource = dsEmployment
Me.DataGrid1.DataMember = "Employees"
Me.DataGrid2.DataMember = "EmploymentStatus"
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
End
End Sub
|
 |
|
As mentioned already, this type of field is used to uniquely identify each record of a table. Therefore, it is based on this field that a table can be related to another. To actually create the relationship, in our example, to associate each employee to a department, the table that holds the list of employees must have a field that represents the corresponding department from the Departments table. Therefore, you must create such a new column. The most important rule you must observe is that this new field must have the same data type as the column that uniquely identifies each department in the other table. Therefore, the field in the Departments table is referred to as the Primary Key. The new column created in the Employees table is referred to as the Foreign Key because this column acts only as an "ambassador". In the strict sense, it doesn't belong to the table in which it it created and in fact, its values should/must not be changed by its hosting table. After creating the foreign key column, the relationship is not automatically created between both tables, since neither the compiler nor the database engine (if you were working on a database) is aware of the role of this new field. To create a relationship between two DataTable objects, the Microsoft .NET Framework provides the DataRelation class. As this is a small class, its main role is to join two tables. In order to create a relationship, you can declare a variable of type DataRelation and use one of its 5 constructors to initialize the relationship. The first constructor has the following syntax: Public Sub New(ByVal relationName As String, _
ByVal parentColumn As DataColumn, _
ByVal childColumn As DataColumn)
The first argument allows you to specify a name for the relationship. The second argument must identify the primary key column of the table that would supply the values. In our example, this would be the primary key of the Departments table. The third argument is the column used as the foreign key in the table that would receive the values of the other table. In our example, this would be the foreign key of the Employees table. This indicates that you should first define and identify the columns that would be used in the relationship. Based on this description, the relationship can be created as follows: |
Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim dsEmployment As DataSet = New DataSet
Dim dtEmployees As DataTable = New DataTable("Employees")
Dim dcEmployees(5)
dcEmployees(0) = New DataColumn("EmployeeID", System.Type.GetType("System.Int32"))
dtEmployees.Columns.Add(dcEmployees(0))
dcEmployees(1) = New DataColumn("FirstName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(1))
dcEmployees(2) = New DataColumn("LastName", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(2))
dcEmployees(3) = New DataColumn("Department", System.Type.GetType("System.String"))
dtEmployees.Columns.Add(dcEmployees(3))
dcEmployees(4) = New DataColumn("EmplStatusID", System.Type.GetType("System.Int32"))
dtEmployees.Columns.Add(dcEmployees(4))
Dim drEmplRecord As DataRow = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "1"
drEmplRecord("FirstName") = "Peter"
drEmplRecord("LastName") = "Larsen"
drEmplRecord("Department") = "Accounting"
drEmplRecord("EmplStatusID") = "1"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "2"
drEmplRecord("FirstName") = "Paul"
drEmplRecord("LastName") = "Banack"
drEmplRecord("Department") = "IT/IM"
drEmplRecord("EmplStatusID") = "3"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "3"
drEmplRecord("FirstName") = "Helene"
drEmplRecord("LastName") = "Cassavoy"
drEmplRecord("Department") = "Accounting"
drEmplRecord("EmplStatusID") = "2"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "4"
drEmplRecord("FirstName") = "Anselme"
drEmplRecord("LastName") = "Thomas"
drEmplRecord("Department") = "Public Relations"
drEmplRecord("EmplStatusID") = "1"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "5"
drEmplRecord("FirstName") = "Bertha"
drEmplRecord("LastName") = "Um"
drEmplRecord("Department") = "Corporate"
drEmplRecord("EmplStatusID") = "4"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "6"
drEmplRecord("FirstName") = "Renée"
drEmplRecord("LastName") = "Bright"
drEmplRecord("Department") = "IT/IM"
drEmplRecord("EmplStatusID") = "3"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "7"
drEmplRecord("FirstName") = "Jeanne"
drEmplRecord("LastName") = "Tristan"
drEmplRecord("Department") = "Corporate"
drEmplRecord("EmplStatusID") = "1"
dtEmployees.Rows.Add(drEmplRecord)
drEmplRecord = dtEmployees.NewRow()
drEmplRecord("EmployeeID") = "8"
drEmplRecord("FirstName") = "Sandrine"
drEmplRecord("LastName") = "Holland"
drEmplRecord("Department") = "Public Relations"
drEmplRecord("EmplStatusID") = "4"
dtEmployees.Rows.Add(drEmplRecord)
Dim dtEmplStatus As DataTable = New DataTable("EmploymentStatus")
Dim dcEmployment(2)
dcEmployment(0) = New DataColumn("EmplStatusID", System.Type.GetType("System.Int32"))
dtEmplStatus.Columns.Add(dcEmployment(0))
dcEmployment(1) = New DataColumn("EmplStatus", System.Type.GetType("System.String"))
dtEmplStatus.Columns.Add(dcEmployment(1))
Dim drEmployment As DataRow = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "1"
drEmployment("EmplStatus") = "Full Time"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "2"
drEmployment("EmplStatus") = "Part Time"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "3"
drEmployment("EmplStatus") = "Contractor"
dtEmplStatus.Rows.Add(drEmployment)
drEmployment = dtEmplStatus.NewRow()
drEmployment("EmplStatusID") = "4"
drEmployment("EmplStatus") = "Intern"
dtEmplStatus.Rows.Add(drEmployment)
dsEmployment.Tables.Add(dtEmployees)
dsEmployment.Tables.Add(dtEmplStatus)
Dim colParent As DataColumn = dsEmployment.Tables("EmploymentStatus").Columns("EmplStatusID")
Dim colChild As DataColumn = dsEmployment.Tables("Employees").Columns("EmplStatusID")
Dim drEmployeeStatus As DataRelation = New DataRelation("EmployeeStatus", colParent, colChild)
dsEmployment.Relations.Add(drEmployeeStatus)
Me.DataGrid1.DataSource = dsEmployment
Me.DataGrid1.DataMember = "EmploymentStatus"
End Sub
 |
 |
 |
|
|
||
| Previous | Copyright © 2005-2016, FunctionX | |
|
|
||