Data Relationships
|
Data Relationships |
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:
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: |
using System;
using System.Xml;
using System.Data;
class Exercise
{
static void Main()
{
DataSet dsEmployment = new DataSet();
DataTable dtEmployees = new DataTable("Employees");
DataColumn[] dcEmployees = new DataColumn[3];
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]);
DataRow drEmplRecord = 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"] = "Casson";
drEmplRecord["Department"] = "Accounting";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["FirstName"] = "Anselme";
drEmplRecord["LastName"] = "Thomas";
drEmplRecord["Department"] = "Public Rel";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["FirstName"] = "Bertha";
drEmplRecord["LastName"] = "Colson";
drEmplRecord["Department"] = "Corporate";
dtEmployees.Rows.Add(drEmplRecord);
dsEmployment.Tables.Add(dtEmployees);
Console.WriteLine("============+===============+===============");
Console.WriteLine("First Name | Last Name | Department");
Console.WriteLine("------------+---------------+---------------");
foreach(DataRow row in dsEmployment.Tables["Employees"].Rows)
{
Console.WriteLine("{0}\t | {1}\t | {2}",
row["FirstName"], row["LastName"],
row["Department"]);
}
Console.WriteLine("============+===============+===============\n");
}
}
This would produce:
============+===============+=============== First Name | Last Name | Department ------------+---------------+--------------- Peter | Larsen | Accounting Paul | Banack | IT/IM Helene | Casson | Accounting Anselme | Thomas | Public Rel Bertha | Colson | Corporate ============+===============+=============== Press any key to continue . . . |
To associate each employee with a department, you can first create a table for the departments. Here is an example:
using System;
using System.Xml;
using System.Data;
class Exercise
{
static void Main()
{
DataSet dsEmployment = new DataSet();
DataTable dtEmployees = new DataTable("Employees");
DataColumn[] dcEmployees = new DataColumn[3];
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]);
DataRow drEmplRecord = 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"] = "Casson";
drEmplRecord["Department"] = "Accounting";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["FirstName"] = "Anselme";
drEmplRecord["LastName"] = "Thoma";
drEmplRecord["Department"] = "Public Relations";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["FirstName"] = "Bertha";
drEmplRecord["LastName"] = "Um";
drEmplRecord["Department"] = "Corporate";
dtEmployees.Rows.Add(drEmplRecord);
DataTable dtEmplStatus = new DataTable("EmploymentStatus");
DataColumn dcEmployment = new DataColumn();
dcEmployment = new DataColumn("EmplStatus",
System.Type.GetType("System.String"));
dtEmplStatus.Columns.Add(dcEmployment);
DataRow drEmployment = 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);
dsEmployment.Tables.Add(dtEmployees);
Console.WriteLine("================+===============+===============");
Console.WriteLine("First Name\t|Last Name\t|Department");
Console.WriteLine("----------------+---------------+---------------");
foreach(DataRow row in dsEmployment.Tables["Employees"].Rows)
Console.WriteLine("{0}\t\t|{1}\t\t|{2}",
row["FirstName"], row["LastName"],
row["Department"]);
Console.WriteLine("================+===============+===============\n");
Console.WriteLine("=================");
Console.WriteLine("Employment Status");
Console.WriteLine("-----------------");
foreach(DataRow row in dsEmployment.Tables["EmploymentStatus"].Rows)
Console.WriteLine("{0}", row["EmplStatus"]);
Console.WriteLine("=================");
Console.WriteLine();
}
}
This would produce:
================+===============+=============== First Name |Last Name |Department ----------------+---------------+--------------- Peter |Larsen |Accounting Paul |Banack |IT/IM Helene |Casson |Accounting Anselme |Thoma |Public Relations Bertha |Um |Corporate ================+===============+=============== ================= Employment Status ----------------- Full Time Part Time Contractor Intern =================
|
Creating a Relationship |
|
As it should appear obvious, 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 an 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: |
using System;
using System.Xml;
using System.Data;
class Exercise
{
static void Main()
{
DataSet dsEmployment = new DataSet("Employment");
DataTable dtEmployees = new DataTable("Employees");
DataColumn[] dcEmployees = new DataColumn[4];
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]);
DataRow drEmplRecord = dtEmployees.NewRow();
drEmplRecord["EmployeeID"] = "1";
drEmplRecord["FirstName"] = "Peter";
drEmplRecord["LastName"] = "Larsen";
drEmplRecord["Department"] = "Accounting";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["EmployeeID"] = "2";
drEmplRecord["FirstName"] = "Paulin";
drEmplRecord["LastName"] = "Banack";
drEmplRecord["Department"] = "IT/IM";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["EmployeeID"] = "3";
drEmplRecord["FirstName"] = "Helene";
drEmplRecord["LastName"] = "Casson";
drEmplRecord["Department"] = "Accounting";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["EmployeeID"] = "4";
drEmplRecord["FirstName"] = "Anselme";
drEmplRecord["LastName"] = "Thomas";
drEmplRecord["Department"] = "Public Relations";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["EmployeeID"] = "5";
drEmplRecord["FirstName"] = "Bertha";
drEmplRecord["LastName"] = "Colson";
drEmplRecord["Department"] = "Corporate";
dtEmployees.Rows.Add(drEmplRecord);
DataTable dtEmplStatus = new DataTable("EmploymentStatus");
DataColumn[] dcEmployment = new DataColumn[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]);
DataRow drEmployment = 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(dtEmplStatus);
dsEmployment.Tables.Add(dtEmployees);
Console.WriteLine("===+=============+===============+=================");
Console.WriteLine("ID | First Name\t | Last Name\t | Department");
Console.WriteLine("---+-------------+---------------+-----------------");
foreach(DataRow row in dsEmployment.Tables["Employees"].Rows)
Console.WriteLine(" {0} | {1}\t | {2}\t | {3}",
row["EmployeeID"], row["FirstName"],
row["LastName"], row["Department"]);
Console.WriteLine("===+=============+===============+=================\n");
Console.WriteLine("===+===================");
Console.WriteLine("ID | Employment Status");
Console.WriteLine("---+-------------------");
foreach(DataRow row in dsEmployment.Tables["EmploymentStatus"].Rows)
Console.WriteLine(" {0} | {1}",
row["EmplStatusID"], row["EmplStatus"]);
Console.WriteLine("===+===================");
Console.WriteLine();
}
}
|
This would produce: ===+=============+===============+================= ID | First Name | Last Name | Department ---+-------------+---------------+----------------- 1 | Peter | Larsen | Accounting 2 | Paulin | Banack | IT/IM 3 | Helene | Casson | Accounting 4 | Anselme | Thomas | Public Relations 5 | Bertha | Colson | Corporate ===+=============+===============+================= ===+=================== ID | Employment Status ---+------------------- 1 | Full Time 2 | Part Time 3 | Contractor 4 | Intern ===+=================== 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 and 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. This 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 is 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 applied 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: DataRelation(string relationName, DataColumn parentColumn, DataColumn childColumn); 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: |
using System;
using System.Xml;
using System.Data;
class Exercise
{
static void Main()
{
DataSet dsEmployment = new DataSet("Employment");
DataTable dtEmployees = new DataTable("Employees");
DataColumn[] dcEmployees = new DataColumn[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]);
DataRow drEmplRecord = 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"] = "Paulin";
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"] = "Casson";
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 Rel";
drEmplRecord["EmplStatusID"] = "1";
dtEmployees.Rows.Add(drEmplRecord);
drEmplRecord = dtEmployees.NewRow();
drEmplRecord["EmployeeID"] = "5";
drEmplRecord["FirstName"] = "Bertha";
drEmplRecord["LastName"] = "Colson";
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 Rel";
drEmplRecord["EmplStatusID"] = "4";
dtEmployees.Rows.Add(drEmplRecord);
DataTable dtEmplStatus = new DataTable("EmploymentStatus");
DataColumn[] dcEmployment = new DataColumn[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]);
DataRow drEmployment = 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(dtEmplStatus);
dsEmployment.Tables.Add(dtEmployees);
DataColumn colParent =
dsEmployment.Tables["EmploymentStatus"].Columns["EmplStatusID"];
DataColumn colChild =
dsEmployment.Tables["Employees"].Columns["EmplStatusID"];
DataRelation drEmployeeStatus =
new DataRelation("EmployeeStatus", colParent, colChild);
dsEmployment.Relations.Add(drEmployeeStatus);
Console.WriteLine("===+===================");
Console.WriteLine("ID | Employment Status");
Console.WriteLine("---+-------------------");
foreach(DataRow row in dsEmployment.Tables["EmploymentStatus"].Rows)
Console.WriteLine(" {0} | {1}", row["EmplStatusID"], row["EmplStatus"]);
Console.WriteLine("===+=============+===============+===============+=============");
Console.WriteLine("ID | First Name\t | Last Name\t | Department\t | Empl Status");
Console.WriteLine("---+-------------+---------------+---------------+-------------");
foreach(DataRow row in dsEmployment.Tables["Employees"].Rows)
{
int iRow = int.Parse(row["EmplStatusID"].ToString());
DataRow curRecord = dsEmployment.Tables["EmploymentStatus"].Rows[iRow-1];
Console.WriteLine(" {0} | {1}\t | {2}\t | {3}\t | {4}",
row["EmployeeID"], row["FirstName"],
row["LastName"], row["Department"], curRecord["EmplStatus"]);
}
Console.WriteLine("===+=============+===============+===============+=============\n");
}
}
This would produce:
===+=================== ID | Employment Status ---+------------------- 1 | Full Time 2 | Part Time 3 | Contractor 4 | Intern ===+=============+===============+===============+============= ID | First Name | Last Name | Department | Empl Status ---+-------------+---------------+---------------+------------- 1 | Peter | Larsen | Accounting | Full Time 2 | Paulin | Banack | IT/IM | Contractor 3 | Helene | Casson | Accounting | Part Time 4 | Anselme | Thomas | Public Rel | Full Time 5 | Bertha | Colson | Corporate | Intern 6 | Renée | Bright | IT/IM | Contractor 7 | Jeanne | Tristan | Corporate | Full Time 8 | Sandrine | Holland | Public Rel | Intern ===+=============+===============+===============+============= Press any key to continue . . .
|
|
||
| Previous | Copyright © 2006-2016, FunctionX, Inc. | |
|
|
||