Records Management
|
|
Records Management |
|
Records Maintenance |
|
Introduction |
Once a table has been filled with records, you can perform maintenance operations on it such as changing some records or removing others.
Editing a record consists of changing one of the values of the record under a particular column. There are various ways you can do this. For a console application, the general steps you can (we will) follow are:
To perform these steps, you use a combination of the techniques we has reviewed so far: locate the table, display the records, locate the record, locate the column, assign the value to the column of a record, save the table.
|
Editing a Record by the Columns' Object Names |
In the previous lesson, we saw that you could isolate a record based on the object names of the columns such as Director or Title for a table of videos. Once you have identified a column for a record, you can assign the desired value. Here are examples:
using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Collections;
namespace VideoCollection3
{
public class VideoCollection
{
public DataSet dsVideos;
private DataColumn colShelfNumber;
private DataColumn colTitle;
private DataColumn colDirector;
private DataColumn colYear;
private DataColumn colLength;
private DataColumn colRating;
private DataTable tblVideos;
// These are accessory strings
string strDirectory;
string strFilename;
public VideoCollection()
{
CreateCollection();
strDirectory = @"C:\Programs\Video Collection";
strFilename = strDirectory + "\\" + "videos.xml";
DirectoryInfo dirInfo = new DirectoryInfo(strDirectory);
// If the folder doesn't exist already, create it
if (!dirInfo.Exists)
dirInfo.Create();
}
public void CreateCollection()
{
dsVideos = new DataSet("Videos");
tblVideos = new DataTable("Video");
colShelfNumber = new DataColumn("ShelfNumber",
Type.GetType("System.String"));
tblVideos.Columns.Add(colShelfNumber);
colTitle = new DataColumn("Title", Type.GetType("System.String"));
tblVideos.Columns.Add(colTitle);
colDirector = new DataColumn("Director",
Type.GetType("System.String"));
tblVideos.Columns.Add(colDirector);
colYear = new DataColumn("Year", Type.GetType("System.Int32"));
tblVideos.Columns.Add(colYear);
colLength = new DataColumn("Length", Type.GetType("System.String"));
tblVideos.Columns.Add(colLength);
colRating = new DataColumn("Rating", Type.GetType("System.String"));
tblVideos.Columns.Add(colRating);
dsVideos.Tables.Add(tblVideos);
}
public void ShowTables()
{
int i = 1;
Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
Console.WriteLine("Video Collection - Tables");
Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
foreach (DataTable tbl in dsVideos.Tables)
Console.WriteLine("{0}. {1}", i++, tbl.TableName);
Console.WriteLine("----------------------------");
}
public void ShowColumns(string table)
{
int i = 1;
DataTable tbl = dsVideos.Tables[table];
Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
Console.WriteLine("Video Collection - {0} Columns", table);
Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
foreach (DataColumn col in tbl.Columns)
Console.WriteLine("{0}. {1}", i++, col.ColumnName);
Console.WriteLine("----------------------------");
}
public void CreateVideo()
{
string ShelfNumber, Title, Director, Length;
int Year;
string Rating;
Console.WriteLine(
"Enter the following pieces of information about the video");
Console.Write("Shelf Number: ");
ShelfNumber = Console.ReadLine();
XmlDocument xmlVideos = new XmlDocument();
if (File.Exists(strFilename))
{
xmlVideos.Load(strFilename);
dsVideos.ReadXml(strFilename);
XmlElement nodRoot = xmlVideos.DocumentElement;
XmlNodeList nodShelfNumbers =
nodRoot.GetElementsByTagName("ShelfNumber");
foreach (XmlNode nodShelfNumber in nodShelfNumbers)
{
if (nodShelfNumber.InnerText == ShelfNumber)
{
Console.WriteLine("The shelf number {0} exists already",
ShelfNumber);
return;
}
}
}
else
dsVideos.WriteXml(strFilename);
Console.Write("Title: ");
Title = Console.ReadLine();
Console.Write("Director: ");
Director = Console.ReadLine();
Console.Write("Year Released: ");
Year = int.Parse(Console.ReadLine());
Console.Write("Length (ex 118mins): ");
Length = Console.ReadLine();
Console.WriteLine("Specify corresponding rating");
Console.WriteLine("G");
Console.WriteLine("PG");
Console.WriteLine("PG-13");
Console.WriteLine("R");
Console.WriteLine("NC-17");
Console.WriteLine("N/R");
Console.Write("Your Choice? ");
Rating = Console.ReadLine();
DataRow rowVideo = this.tblVideos.NewRow();
rowVideo[0] = ShelfNumber;
rowVideo[1] = Title;
rowVideo[2] = Director;
rowVideo[3] = Year;
rowVideo[4] = Length;
rowVideo[5] = Rating;
this.tblVideos.Rows.Add(rowVideo);
this.dsVideos.WriteXml(strFilename);
}
public void ShowVideos()
{
if (File.Exists(strFilename))
{
dsVideos.ReadXml(strFilename);
Console.WriteLine("========================================");
Console.WriteLine(" Video Collection");
Console.WriteLine("=========================================");
foreach (DataRow vdo in tblVideos.Rows)
{
Console.WriteLine("Shelf #: {0}", vdo["ShelfNumber"]);
Console.WriteLine("Title: {0}", vdo["Title"]);
Console.WriteLine("Director: {0}", vdo["Director"]);
Console.WriteLine("(c) Year: {0}", vdo["Year"]);
Console.WriteLine("Length: {0:C}", vdo["Length"]);
Console.WriteLine("Rating: {0}", vdo["Rating"]);
Console.WriteLine("-----------------------------------------");
}
}
}
public void EditVideo()
{
if (File.Exists(strFilename))
{
bool found = false;
int iYear = 0;
string strShelfNumber = "AA-000",
strTitle = "Unknown",
strDirector = "Unknown",
strLength = "N/A",
strRating = "N/A";
dsVideos.ReadXml(strFilename);
DataRow rowVideo = null;
Console.WriteLine("\nHere is the current list of videos");
Console.WriteLine("========================================");
Console.WriteLine(" Video Collection");
Console.WriteLine("=========================================");
foreach (DataRow vdo in tblVideos.Rows)
{
Console.WriteLine("Shelf #: {0}", vdo["ShelfNumber"]);
Console.WriteLine("Title: {0}", vdo["Title"]);
Console.WriteLine("Director: {0}", vdo["Director"]);
Console.WriteLine("(c) Year: {0}", vdo["Year"]);
Console.WriteLine("Length: {0:C}", vdo["Length"]);
Console.WriteLine("Rating: {0}", vdo["Rating"]);
Console.WriteLine("-----------------------------------------");
}
Console.Write("Enter the shelf number of the video you want to edit: ");
strShelfNumber = Console.ReadLine();
foreach (DataRow vdo in tblVideos.Rows)
{
string str = (string)vdo["ShelfNumber"];
Console.WriteLine(str);
if( str == strShelfNumber )
{
//rowVideo = vdo;
found = true;
Console.WriteLine("\n-----------------------------------------");
Console.WriteLine("Here is the video");
Console.WriteLine("1. Title: {0}", vdo["Title"]);
Console.WriteLine("2. Director: {0}", vdo["Director"]);
Console.WriteLine("3. (c) Year: {0}", vdo["Year"]);
Console.WriteLine("4. Length: {0}", vdo["Length"]);
Console.WriteLine("5. Rating: {0}", vdo["Rating"]);
Console.WriteLine("-----------------------------------------");
strTitle = (string)vdo["Title"];
strDirector = (string)vdo["Director"];
iYear = (int)vdo["Year"];
strLength = (string)vdo["Length"];
strRating = (string)vdo["Rating"];
Console.Write("Enter the index of the column " +
"whose value you want to change: ");
int col = int.Parse(Console.ReadLine());
switch (col)
{
case 1:
vdo["ShelfNumber"] = strShelfNumber;
Console.Write("Enter the new video title: ");
strTitle = Console.ReadLine();
vdo["Title"] = strTitle;
vdo["Director"] = strDirector;
vdo["Year"] = iYear;
vdo["Length"] = strLength;
vdo["Rating"] = strRating;
this.dsVideos.WriteXml(strFilename);
break;
case 2:
vdo["ShelfNumber"] = strShelfNumber;
vdo["Title"] = strTitle;
Console.Write("Enter the new director of the video: ");
strDirector = Console.ReadLine();
vdo["Director"] = strDirector;
vdo["Year"] = iYear;
vdo["Length"] = strLength;
vdo["Rating"] = strRating;
this.dsVideos.WriteXml(strFilename);
break;
case 3:
vdo["ShelfNumber"] = strShelfNumber;
vdo["Title"] = strTitle;
vdo["Director"] = strDirector;
Console.Write("Enter the right year released of the video: ");
iYear = int.Parse( Console.ReadLine());
vdo["Year"] = iYear;
vdo["Length"] = strLength;
vdo["Rating"] = strRating;
this.dsVideos.WriteXml(strFilename);
break;
case 4:
vdo["ShelfNumber"] = strShelfNumber;
vdo["Title"] = strTitle;
vdo["Director"] = strDirector;
vdo["Year"] = iYear;
Console.Write("Enter the new length of the video: ");
strLength = Console.ReadLine();
vdo["Length"] = strLength;
vdo["Rating"] = strRating;
this.dsVideos.WriteXml(strFilename);
break;
case 5:
vdo["ShelfNumber"] = strShelfNumber;
vdo["Title"] = strTitle;
vdo["Director"] = strDirector;
vdo["Year"] = iYear;
vdo["Length"] = strLength;
Console.Write("Enter the right rating for the video: ");
strRating = Console.ReadLine();
vdo["Rating"] = strRating;
this.dsVideos.WriteXml(strFilename);
break;
}
return;
}
}
if (found == false)
{
Console.WriteLine("No video with that shelf number was found");
return;
}
}
}
}
}
|
Editing a Record by the Columns' Indices |
We saw that another technique of recognizing a record was by using the index of each column applied to the DataRow object of the record. You can apply this concept to identify each column. Once you do, you can then assign the desired value.
|
Deleting Records |
|
Deleting the Current Row |
|
If you happen to have a record you don't need or don't find necessary in a table, you can remove that record from the table. To start, you must establish what record you want to delete. Once again, you would need a way to uniquely identify a record. For our video collection, you can use the shelf number column. Once you have located the undesired record, you can delete it. To assist you with removing a record, the DataRow class is equipped with a method named Delete. Its syntax is simply: public void Delete(); This method must be called by the record that wants to be deleted. Here is an example: |
using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Collections;
namespace VideoCollection3
{
public class VideoCollection
{
. . . No Change
public void DeleteVideo()
{
if (File.Exists(strFilename))
{
bool found = false;
string strShelfNumber = "AA-000";
dsVideos.ReadXml(strFilename);
Console.WriteLine("\nHere is the current list of videos");
Console.WriteLine("========================================");
Console.WriteLine(" Video Collection");
Console.WriteLine("=========================================");
foreach (DataRow vdo in tblVideos.Rows)
{
Console.WriteLine("Shelf #: {0}", vdo["ShelfNumber"]);
Console.WriteLine("Title: {0}", vdo["Title"]);
Console.WriteLine("Director: {0}", vdo["Director"]);
Console.WriteLine("(c) Year: {0}", vdo["Year"]);
Console.WriteLine("Length: {0:C}", vdo["Length"]);
Console.WriteLine("Rating: {0}", vdo["Rating"]);
Console.WriteLine("-----------------------------------------");
}
Console.Write("Enter the shelf number of the video you want to delete: ");
strShelfNumber = Console.ReadLine();
foreach (DataRow vdo in tblVideos.Rows)
{
string str = (string)vdo["ShelfNumber"];
if (str == strShelfNumber)
{
found = true;
Console.WriteLine("\n-----------------------------------------");
Console.WriteLine("Here is the video you want to delete");
Console.WriteLine("1. Title: {0}", vdo["Title"]);
Console.WriteLine("2. Director: {0}", vdo["Director"]);
Console.WriteLine("3. (c) Year: {0}", vdo["Year"]);
Console.WriteLine("4. Length: {0}", vdo["Length"]);
Console.WriteLine("5. Rating: {0}", vdo["Rating"]);
Console.WriteLine("-----------------------------------------");
Console.Write("Do you still want to delete this video (y/n)? ");
char ans = char.Parse(Console.ReadLine());
if ((ans == 'y') || (ans == 'Y'))
{
vdo.Delete();
dsVideos.WriteXml(strFilename);
Console.WriteLine("The video has been deleted!");
}
return;
}
}
if (found == false)
{
Console.WriteLine("No video with that shelf number was found");
return;
}
}
}
}
}
|
Removing a Row From the Collection of Records |
Besides the DataRow class, the DataRowCollection class provides its own means of deleting a record from a table. To delete a record, you can call the DataRowCollection.Remove() method. Its syntax is:
public void Remove(DataRow row);
This method takes as argument a DataRow object and checks whether the table contains it. If that record exists, it gets deleted, including all of its entries for each column. Here is an example:
using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Collections;
namespace VideoCollection3
{
public class VideoCollection
{
. . . No Change
public void DeleteVideo()
{
if (File.Exists(strFilename))
{
bool found = false;
string strShelfNumber = "AA-000";
dsVideos.ReadXml(strFilename);
Console.WriteLine("\nHere is the current list of videos");
Console.WriteLine("========================================");
Console.WriteLine(" Video Collection");
Console.WriteLine("=========================================");
foreach (DataRow vdo in tblVideos.Rows)
{
Console.WriteLine("Shelf #: {0}", vdo["ShelfNumber"]);
Console.WriteLine("Title: {0}", vdo["Title"]);
Console.WriteLine("Director: {0}", vdo["Director"]);
Console.WriteLine("(c) Year: {0}", vdo["Year"]);
Console.WriteLine("Length: {0:C}", vdo["Length"]);
Console.WriteLine("Rating: {0}", vdo["Rating"]);
Console.WriteLine("-----------------------------------------");
}
Console.Write("Enter the shelf number of the video you want to delete: ");
strShelfNumber = Console.ReadLine();
foreach (DataRow vdo in tblVideos.Rows)
{
string str = (string)vdo["ShelfNumber"];
if (str == strShelfNumber)
{
found = true;
Console.WriteLine("\n-----------------------------------------");
Console.WriteLine("Here is the video you want to delete");
Console.WriteLine("1. Title: {0}", vdo["Title"]);
Console.WriteLine("2. Director: {0}", vdo["Director"]);
Console.WriteLine("3. (c) Year: {0}", vdo["Year"]);
Console.WriteLine("4. Length: {0}", vdo["Length"]);
Console.WriteLine("5. Rating: {0}", vdo["Rating"]);
Console.WriteLine("-----------------------------------------");
Console.Write("Do you still want to delete this video (y/n)? ");
char ans = char.Parse(Console.ReadLine());
if ((ans == 'y') || (ans == 'Y'))
{
tblVideos.Rows.Remove(vdo);
dsVideos.WriteXml(strFilename);
Console.WriteLine("The video has been deleted!");
}
return;
}
}
if (found == false)
{
Console.WriteLine("No video with that shelf number was found");
return;
}
}
}
}
}
|
Deleting a Record by its Index |
When calling the DataRowCollection.Remove() method, you must pass an exact identification of the record. If you don't have that identification, you can delete a record based on its index. To do this, you would call the DataRowCollection.RemoveAt() method. Its syntax is:
public void RemoveAt(int index);
This method takes as argument the index of the record you want to delete. If a record with that index exists, it would be deleted.
|
Deleting all Records of a Table |
To delete all records of a table, call the DataRowCollection.Clear() method. Its syntax is:
public void Clear();
This method is used to clear the table of all records.
|
Data 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.
|
Table Preparation |
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:
| First Name | Last Name | Department |
| Peter | Larsen | Accounting |
| Paul | Banack | IT/IM |
| Helene | Cassavoy | Accounting |
| Anselme | Thomas | Public Relations |
| Bertha | Um | Corporate |
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 int 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");
return 0;
}
}
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;
public class Exercise
{
static int 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();
return 0;
}
}
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 © 2008-2016, FunctionX, Inc. | Home |
|
|
||