Search This Blog

June 1, 2018

Ado.Net - Setting-up Foreign Key Relation

1. Introduction to Ado.net Foreign Key

In “Relational Databases” like Oracle and SQL Server, a “Foreign Key” always refers to a Primary Key in a table. This establishes a relationship between two tables which is called as “Referential Integrity”. We know that a table can have only one Primary Key and can have one or more Unique Keys. In Ado.Net a Foreign Key is not restricted to refer only to Primary Key. That means, it can refer to a Unique Key column also.

In this article, we will see an example that demonstrates establishing a Foreign Key relation on 
“DataTables”. We will also explore how “Cascading” option works.

2. About the Example


The example we are going to create is shown below:

Ado.Net ForeignKey Example
Ado.Net ForeignKey Example

In this example, we are going to use Microsoft SQL Server as backend and also we need the Pubs database. One can download it from the internet or the SQL Script for creating this database is available in the SQL 2005 section of this website.
The controls numbered 1 and 2 are DataGridView Controls. The first DataGridView Control is used to display the Publishers information from the Pubs database. The second DataGridView Control is used to display the Employees information. In this Ado.net code example, we are going to set Primary Key - Foreign Key relationship between these two DataGridViews.
The “Load Data” (Marked as 3) will load the data into both the grids. So, the initial data is loaded without any Relationship between the grids. When the “Enable FKey” (Marked as 4) is checked, a foreign key is assigned to pub_id of the Employees grid and it points to the Primary Key on the Publishers. This will create the Referential Integrity between these grids.
By default, the “Cascade” (Marked as 5) checkbox is checked and when we remove the check-mark, we will be removing the "Cascading Effect" which we will see while progressing with the article. The “Clear” button will reset the form so that we can start our experiment a Fresh.

3. Configure the Database

First, let us connect to the SQL Server Database and try running the below two queries.

Query 1:
Select * from publishers;

Query 2:
SELECT 
  publishers.pub_name, employee.emp_id, 
  employee.fname + ' ' + employee.minit 
  + ' ' + employee.lname  as EmpName,
  employee.pub_id,  
  jobs.job_desc
FROM    
  employee 
  INNER JOIN jobs ON employee.job_id = jobs.job_id 
  INNER JOIN publishers ON employee.pub_id = publishers.pub_id
Order By Pub_Name;



Once we can run the above queries in SQL Server Management Studio and able to see the results, we in good shape to use the queries in our ado.net example. The first query is used by the publisher data grid. And, the second query is used by Employees data grid. Next, we must set a connection to our SQL Server as an application property. The video below helps to set the connection string. Set the “Connection String” name it as PubCon.


Video 1: Setting Connection to SQL Server Database



4. Setting up the Form

1) Include the required namespace to the project.
//Snippet 00: Namespace required for this Sample
using System.Data.SqlClient;


2) Next, we declare global “DataSet” variable called PkFkDS. In this DataSet, we are going to hold data for both Publishers and Employees data grid. 

//Snippet 01: Global DataSet
private DataSet PkFkDS = null;

3) In the form load handler, we allow access to Load button only. This is to avoid validation overhead and also to allow the form navigation in a specific sequence. 
private void PKeyFKey_Load(object sender, EventArgs e)
{
    //Snippet 02: Disable the Checkboxes and Clear buttons
    btnClear.Enabled = false;
    chkKeys.Enabled = false;
    chkCascade.Enabled = false;
}


4) We are resetting the controls when clear button is clicked. Note that we are also clearing the DataSet. Since the DataSet supply data to the Grids, the grids will get cleared through the DataSet function calls “Clear()” and “Reset()”. The clear button will leave the form to its initial state when it got loaded.

private void btnClear_Click(object sender, EventArgs e)
{
    //Snippet 03: Reset the Controls
    PkFkDS.Clear();
    PkFkDS.Reset();
    btnClear.Enabled = false;
    chkKeys.Enabled = false;
    chkCascade.Enabled = false;
    btnLoadData.Enabled = true;
}


5. Loading Data into Parent and Child DataGridViews


The load button will load the data into the Publisher and Employees DataGridView controls. This time, we are going to apply only Primary Key Constraint on the pubs-id column of the Publisher. Towards the end of this section, we will explore the behavior when there is no Referential Integrity.

5.1 Creating DataTables


1) The first thing we should do is avoiding the Multiple Loads without clearing the DataSet. So, disable the Load button and we know that it will get enabled in the Close button handler. The chkKeys CheckBox is enabled as it will allow applying the constraints when it is ticked. We want to do that once data is Loaded. 

//Snippet 04: Load the Data
private void btnLoadData_Click(object sender, EventArgs e)
{
    //Snippet 4.1: Enable Close. Avoid Multiple click of Load
    btnClear.Enabled = true;
    chkKeys.Enabled = true;
    btnLoadData.Enabled = false;


2) The “SqlConnection” object Con is created based on the Connection String. This connection string is configured as part of application setting (Refer Video 1). 

//Snippet 4.2: Create Connection to SQL Server 
SqlConnection Con = new SqlConnection(
    Pkey_Fkey.Properties.Settings.Default.PubsCon);


3) Next, we create two “DataTables” called dtabPublishers and dtabEmployees. Through “SqlDataAdapter::Fill()” method, we are populating both the DataTables with Query1 and Query2 results which we already explored in Section 3.

string SqlQuery1 = "Select * from publishers";
string SqlQuery2 = 
"SELECT publishers.pub_name, " +
"employee.emp_id, employee.fname + ' ' + employee.minit " +
"+ ' ' + employee.lname  as EmpName, employee.pub_id, " + 
"jobs.job_desc " +
"FROM employee " +
"INNER JOIN jobs ON employee.job_id = jobs.job_id " +
"INNER JOIN publishers ON employee.pub_id = publishers.pub_id " +
"Order By Pub_Name"; 
DataTable dtabPublisher = new DataTable("publishers");
DataTable dtabEmployees = new DataTable("Employees");
SqlDataAdapter adaptor1 = new SqlDataAdapter(SqlQuery1, Con);
SqlDataAdapter adaptor2 = new SqlDataAdapter(SqlQuery2, Con);
adaptor1.Fill(dtabPublisher);
adaptor2.Fill(dtabEmployees);

5.2 Adding DataTables to DataSet

Once data is packed into the DataTables, we are creating our global DataSet object and then adding both the DataTables to it through its Add() method of the “Tables Collection”. When we created the DataTable, we named it by passing the string value to its constructor. This naming can be used, while we want to retrieve the DataTable from the Tables collection. 

//Snippet 4.4: Add DataTables to DataSet
PkFkDS = new DataSet();
PkFkDS.Tables.Add(dtabPublisher);
PkFkDS.Tables.Add(dtabEmployees);

5.3 Binding DataGridView

The “DataSource Property” of the DataGridView is set with the DataTables that we created earlier. Note that we are referring our DataTable from the Tables collection using Named Index like Tables[<DataTable-Name>]. Once this “Data Binding” is done, both the DataGridViews shows the table of data. 

//Snippet 4.5: Bind DataTable to DataViews
GridViewPub.DataSource = PkFkDS.Tables["Publishers"];
GridViewEmp.DataSource = PkFkDS.Tables["Employees"];

5.4 Apply PrimaryKey

The “Constraints Collection” of the DataTable is used to define the constraints on one or more columns. We are first taking the Publishers table from the DataSet and then adding a PrimaryKey constraint to the pub_id column. We named our key as “PKey-PubId”. After adding the key, we do the clean-up.

//Snippet 4.6: Set Primary Key Cobstraint on Publisher
//DataTable
DataTable dtabPub = PkFkDS.Tables["publishers"];
dtabPub.Constraints.Add(
    "PKey-PubId",
    dtabPub.Columns["pub_id"],
    true);
//Snippet 4.7: Clean-Up Connection and Adaptors
Con.Close();
adaptor1.Dispose();
adaptor2.Dispose();


Now let us examine the behavior of the application. So far we had written code to populate the DataGridView Controls. Since there is no Foreign key relation, we can add any data to the Employees table. Ado.Net doesn’t complain about the data integrity between Parent (Publisher) and Child(Employees). The behavior is recorded in the below video.


Video 2: Parent-Child behavior without Foreign Key

6. Adding and Removing Foreign Key Constraint


The Constraints Collection of the DataTable is used to Add or remove the constraint dynamically. The DataGridView which bound to the DataTable will recognize these changes automatically. The “Add()” function overload requires both Referring and Referred Columns to establish the Parent-Child relationship through Foreign Key. The “Remove()” function is used to remove the already added constraints. 


The “Contains()” function is used to check whether a constraint is part of the Constraints Collection or not. This function is useful when we are adding or removing a constraint. While adding the constraint, it is safe to check that there should not be any constraint with the same name. The same way, while removing we should make sure that the constraint we want to remove exits in the DataTable. 


All right. Let us come to the point. When the “Enable FKey” checkbox is ticked, we should enable the Foreign Key constraint on the pub-id column of the Employees DataTable. Furthermore, we should remove the Foreign Key dynamically when the tick is removed.


6.1 Add Foreign Key Column


In the “CheckedChanged” event handler of the checkbox, based on the “CheckState”, either we add or remove the Foreign Key constraint. In the below code, first, we take the DataTable from the DataSet and then add the ForeignKey Constraint to it. Note that we named the constraint as “FKey-PubId”. This name assigned to the Constraint is useful when we want to remove it at later point of time. The second parameter of the Add() function tells the Parent Table’s Column (The Referred Column) and the third parameter tells the Foreign Key column (Referring Column) in the Child Table. 
//Snippet 5.1: Add Foreign Key Constraint
DataTable dtabEmp = PkFkDS.Tables["Employees"];
DataTable dtabPub = PkFkDS.Tables["publishers"];
try
{
    if (!dtabEmp.Constraints.Contains("FKey-PubId"))
        dtabEmp.Constraints.Add(
            "FKey-PubId",
            dtabPub.Columns["pub_id"],
            dtabEmp.Columns["pub_id"]);
}
catch (Exception Ex)
{
    MessageBox.Show(Ex.Message);
    chkKeys.Checked = false;
}


6.2 Remove Foreign Key Column


The same Constraint Collection is used to remove the Foreign Constraint. We pass the Constraint Name to the Remove() function and before that, we make sure that the Constraint named “FKey-PudId” exists in the DataTable. Both Add() and Remove() function calls are wrapped around the Try-Catch block because we may end-up with the run-time failures caused by Parent-Child integrity violations.
//Snippet 5.3: Remove Foreign Key Constrains
DataTable dtabEmp = PkFkDS.Tables["Employees"];
try
{
    if (dtabEmp.Constraints.Contains("FKey-PubId"))
        dtabEmp.Constraints.Remove("FKey-PubId");
}
catch (ConstraintException Ex)
{
    MessageBox.Show(Ex.Message);
}

The complete code is Below:
private void chkKeys_CheckedChanged(object sender, EventArgs e)
{
    //Snippet 05: Add/Remove Foreign Key
    if (chkKeys.CheckState == CheckState.Checked)
    {
        chkCascade.Enabled = true;
        
        //Snippet 5.1: Add Foreign Key Constraint
        DataTable dtabEmp = PkFkDS.Tables["Employees"];
        DataTable dtabPub = PkFkDS.Tables["publishers"];
        try
        {
            if (!dtabEmp.Constraints.Contains("FKey-PubId"))
                dtabEmp.Constraints.Add(
                    "FKey-PubId",
                    dtabPub.Columns["pub_id"],
                    dtabEmp.Columns["pub_id"]);
        }
        catch (Exception Ex)
        {
            MessageBox.Show(Ex.Message);
            chkKeys.Checked = false;
        }

    }
    else
    {
        chkCascade.Checked = true;
        chkCascade.Enabled = false;

        //Snippet 5.3: Remove Foreign Key Constrains
        DataTable dtabEmp = PkFkDS.Tables["Employees"];
        try
        {
            if (dtabEmp.Constraints.Contains("FKey-PubId"))
                dtabEmp.Constraints.Remove("FKey-PubId");
        }
        catch (ConstraintException Ex)
        {
            MessageBox.Show(Ex.Message);
        }
    }
}

Now, we can enable the Foreign Key and try adding a row in the Employees DataGridView. This time, any attempt to add a Publisher ID in the Employees that don’t exist in the Publishers table will throw an Exception. Also, note that an exception is thrown when we try to enable the constraint with existing Integrity Violations. The below video shows the behavior of the Example while adding or removing the Foreign Key constraint.


Video 3: Parent-Child behavior with Foreign Key Enabled

7. The Role of Cascading


What happens to "Referring Columns" in the child table when an Update or Delete happens to the "Referred Column" in the parent row? The answer requires an explanation for “Cascading Action”. In Ado.Net, by default, Cascading is turned-on after establishing the Referential Integrity. When a row in the parent is deleted, all the referring rows will also get deleted. The same happens for the Update also. Note that when the update happens for Referred Column, the same update is propagated to all the Referring Columns.


We have a Cascade Checkbox in our example and by default, it is selected. To turn-off the Cascading Action we should remove the check mark. Below is the code which removes and/or puts back the Default Cascading based on the checkbox status.

private void chkCascade_CheckedChanged(object sender, EventArgs e)
{
    //Snippet 6.0: Remove Delete Casecase From ForeignKey
    //Constraint
    DataTable dtabEmp = PkFkDS.Tables["Employees"];
    ForeignKeyConstraint FKey = (ForeignKeyConstraint)
        dtabEmp.Constraints["FKey-PubId"];
    if (chkCascade.CheckState == CheckState.Unchecked)
        FKey.DeleteRule = FKey.UpdateRule = Rule.SetNull;
    else
        FKey.DeleteRule = FKey.UpdateRule = Rule.Cascade;                
}

The “Rule.Cascade” sets the Cascading behavior and “Rule.None” removes the cascading behavior. First, we are taking the “ForeignKeyConstraint” object from the DataTable. After that, we are setting the “DeleteRule” and “UpdateRule”. Now, watch below video which shows the cascading behavior on Publishers-Employee relationship.

Video 4: Turning ON and Turning OFF the Cascading Behavior


Source Code: Download

Like this site? Tell it to your Friend :)

Subscribe

Subscribe