February 13, 2018

Ado.Net - Unique and PrimaryKey Constraint behavior of DataTable and DataSet

1. About ADO.Net Constraints

We know that “Relational Databases” support “Database Constrains” which helps in maintaining the “Data Integrity” within the database. The same way the constraints can be enforced from the ado.net front end itself. In this article we are going to examine how Unique Constraint and Primary Key constraint can be applied to DataTable. Then we will study the behavior of these constraints under the following conditions:
  1. Adding a DataRow to the Constraint applied DataTable
  2. Merging a DataTable with the DataSet which has same DataTable Schema.

2. About the Ado.Net Constraint Example

The example is created to study the behavior of the Ado.Net DataTable Constraints and if there is an error (Tried our best, if any exists), kill and restart the sample application. Now, have a look at the example:

Ado.Net Constraint Example

In the above picture you can see all the controls and its names are displayed. The “DataGridView Control” (dataGridView1) occupies the most of the form area and it is used to display the data. The Load Data button (btnLoadData) is used to load the data from the database (Title table of Microsoft supplied Pubs database) and when loading it applies the Ado.Net Constraint selected by the user through Radio Button controls.

The set of TextBox Controls are used to get the user data. When user clicks the Add Row button (btnAdd), the data entered by the user is added to the DataGridView. Instead of clicking the Add Row button, a user adds the row to a temporary DataTable by clicking the button btnAddToDataTable. For example, if user adds three rows using this button, the DataTable will have three rows and the ListBox control (lstDTContent) shows what is there in the DataTable so for. The content of the DataTable can be merged to the DataGridView control through a DataTable in the DataSet (You will see more about this when the article progresses) and it happens by clicking the Add Table button (btnAddTable). With this example, we can examine the behavior of the Unique and Primary Constraints of the DataTable.

3. Load Data to DataGridView (Load Button Handler)

1) Before implementing the click event handler for the btnLoadData, we need to first include the required namespace for the sample. Below is the code:

//Sample 01: include the required namespace
using System.Data.SqlClient;

2) Then two private members are used at the form level. The TitleDataSet instance of type “DataSet” is used to hold the title table content under a DataTable. The DT_title instance of type “DataTable” is used to build the DataTable on the fly to experiment the Constraints behavior during the Merge operation. Below is the code:

//Sample 02a: Declare the Dataset for Titles Table
private DataSet TitleDataSet;
//Sample 02b: Datatable we are going to use for Merge
private DataTable DT_title;

3) In the load button handler, first a DataSet instance is created and stored in the variable, TitleDataSet. Next, we are creating the SqlDataAdapter object “adaptor” by supplying the Connection String and Query String that retrieves the data from the  Pubs database.

Once the SqlDataAdapter is ready, we are making call its “Fill()” method. We passed two parameters to this method. First one is the DataSet object TitleDataSet and the second one the string; Titles. Now, the Fill() method queries the pubs table, creates a DataTable called “Titles” and places that in the DataSet object TitleDataSet. Below is the code:

//4.1: Create a DataAdaptor and Fill the Dataset
TitleDataSet = new DataSet();
string select_str = "Select title_id, title, type from titles";
SqlDataAdapter adaptor = new SqlDataAdapter(select_str,
adaptor.Fill(TitleDataSet, "Titles");

3.1 Set Unique Key Constraint on DataTable

First refer the code given below:

//4.2: Set the Constraints
DataTable dtTitles = TitleDataSet.Tables["Titles"];
if (radUnique.Checked == true)
        dtTitles.Columns["title_id"], false);

In the previous step, we created the DataTable (Contained within TitleDataSet) from the Titles table of the pubs database. Now, we are grabbing that DataTable through "Tables Collection" of the DataSet(TitleDataSet). Note that we using the Named Index “Titles” (Passed as second parameter to Fill method to name the DataTable within DataSet as “Titles”) to get the DataTable from the Tables collection of the TitleDataSet.

One we have the Titles DataTable on hand, we are adding the constraints based on the currently selected Radio Box Control. Since the code is running on button click handler, before adding the constraint we are clearing the Constraints collection of the DataTable through "Clear()" method.

To add constraints to the DataTable, we are using the "Add()" method of the "Constraints Collection" of the DataTable; dtTiles. Have a look at the depiction below:

Constraints Collection of DataTable

Here in the example, the first parameter tells the name of the constraint and we named it as “Unique1”. The second parameter tells what column(s) is/are involved in the constraint. In our example, we are applying the constraint to “title_id” column. The third parameter tells whether it is "Unique Constraint" or "Primary Key constraint". In the depiction above, it is unique constraint since we pass Boolean false.

3.2 Set Primary Key Constraint on DataTable

When the PKEY radio box is checked, we are adding the Primary Key Constraint to the DataTable. To do that we are passing the last Boolean parameter as true. Below is the code for it.

if (radPKEY.Checked == true)
        dtTitles.Columns["title_id"], true);

3.3 Set DataSource to DataGridView 

Once the DataTable is Ready with or without constraint, it set to the DataGridView. Then other UI control visibility are adjusted in this load button handler. Below is the code which doesn’t require further explanation:

//4.3: Populate DataGrid Data from DataSet
dataGridView1.DataSource = TitleDataSet.Tables["Titles"];
dataGridView1.Columns[1].Width = 350;

//4.4 Diable Load button
btnLoadData.Visible = false;
radNone.Visible = false;
radUnique.Visible = false;
radPKEY.Visible = false;

4. Clear button Handler

We can test the behavior of the DataTable displayed in the DataGridView by loading it with Unique and Primary Key Constraints. For that we need to implement the Clear Button's Click Event handler. Below is the code for the Clear Button handler:

//Sample 03: Define the Dataset for Titles Table
private void btnClear_Click(object sender, EventArgs e)
    //3.1: Clear the Data Grid Control
    dataGridView1.DataSource = null;

    //3.2: Clear the DataSet and DataTable

    //3.3: Enable Load button
    btnLoadData.Visible = true;
    radUnique.Visible = true;
    radPKEY.Visible = true;
    radNone.Visible = true;

    //3.4: Clear the UI fields
    txtTitleDesc.Text = "";
    txtTitleID.Text = "";
    txtTitleType.Text = "";

Now let us test the constraint behavior before proceeding with the next section. Watch the video below which tests the load button with different conditions.

Video 1: Ado.net Unique and Primary Key Behaviour testing on DataGrid-1

5. Add Row to DataTable with Unique or Primary Key Constraint

Now, we will add the row dynamically to the DataTable and render that to the DataGridView.  This time, in place of editing the DataTable directly in DataGridView, we are going to add new Data Row through C-Sharp Code. The user enters the book details in the TextBoxes; txtTitleID, txtTitleType and txtTitleDesc. After entering the details, user clicks the Add Row button to add the new DataRow to the DataGridView control. 

Below is the code for the Add Row button handler:

//Sample 05: Add Row to a Data Grid
private void btnAdd_Click(object sender, EventArgs e)
        //5.1: Grab the Data Table from DataGrid
        DataTable dtTitle = TitleDataSet.Tables["Titles"];

        //5.2: Create New Row and Assign Data
        DataRow Title_Row = dtTitle.NewRow();
        Title_Row["title_id"] = txtTitleID.Text;
        Title_Row["title"] = txtTitleDesc.Text;
        Title_Row["type"] = txtTitleType.Text;

        //5.3: Add the new row and and refresh the Grid
    catch (Exception Ex)

The video below tests the Adding a Row to the DataTable when:
  1. There is no constraint
  2. There is a Unique Constraint applied to the Ado.Net DataTable
  3. There is a Primary Key Constraint applied to the Ado.Net DataTable.

Video 2: Ado.net Unique and Primary Key Behaviour – Add Row Dynamically to DataTable

5. The Result of Merging DataTables With Primary Key or Unique Constraint

Till now, we had not seen any difference between Unique and Primary Key while adding a new row to a constrained DataTable.  But, we can see a difference in behavior when we merge the DataTables. In our example, we are going to build in-memory DataTable (Source) and merge that with the DataTable in the DataSet (Destination).

In the Load button handler, we are creating the in-memory DataTable. The DataTable is named as Titles to match the name of the DataTable contained in the TitleDataSet. While adding the columns, we are making sure that it matches the column name and data type of the destination DataTable. Below is code which creates in-memory DataTable in the Load button’s Click handler. 

//4.5: Add columns similar to DataTable in DataSet
DT_title = new DataTable("Titles");

5.1 Adding Rows to in-memory DataTable

When the vertical button btnAddToDataTable is clicked, we are creating a new DataRow by calling the "NewRow()" function of the DataTable. Then, we are populating the column values from the TextBoxes - txtTitleID, txtTitleType and txtTitleDesc. After this, the DataRow is added to the "Rows Collection" of the DataTable. Note that we are also forming a helper string that gets added to the ListBox lstDTContent. This list box helps user to get a glimpse of the in-memory DataTable. Below is code which accumulates the Rows in the In-Memory DataTable.

//Sample 6.0: Add rows to DataTable 
private void btnAddToDataTable_Click(object sender, EventArgs e)
    //6.1 Show the content of DataTable in the Listbox
        txtTitleID.Text + "," +

    //6.2 Append a new row to Data Table
    DataRow NewRow = DT_title.NewRow();
    NewRow["title_id"] = txtTitleID.Text;
    NewRow["title"] = txtTitleDesc.Text;
    NewRow["type"] = txtTitleType.Text;

5.2 Merging the DataTable with another DataTable

The in-memory DataTable that we are building by user interaction is the source table and the destination table is contained in the DataSet which got filled by the DataAdaptor. We are making call to Merge() method of the DataSet by supplying the in-memory table as parameter. The Merge() method merges the rows of the source table with the destination.

Let us say, the source DataTable DT_title is having a duplicate column value for the column title_id, the merge will have the following behaviour:

  1. No Error when destination DataTable does not have constraint.
  2. Constraint violation Error when destination DataTable has unique constraint applied to the title_id columns. 
  3. The title_id column is used to match the row in the destination DataTable and other columns values title, type is replaced by the source DataTable.

The Effect of the merge in case PrimaryKey constraint is shown in the below picture:

Merging two DataTables with Primary Key Constraint

Here, TC7777 in the source DataTable (in-memory) is matched with TC7777 in the destination DataTable (shown in Red) and then the remaining columns are replaced in the Destination Table (Shown in blue)

Below is code which performs the merge:

//Sample 7.0: Merge the Dynamic DataTable
private void btnAddTable_Click(object sender, EventArgs e)
        //7.1: Merge the Dynamic Table Created by user to DataSet 

        //7.2:  Clear the Dynamic Table (Already Merged) and other 
        //      Controls
        txtTitleID.Text = txtTitleDesc.Text = txtTitleType.Text = "";

    catch (Exception Ex)
        txtTitleID.Text = txtTitleDesc.Text = txtTitleType.Text = "";

Video 3: Ado.net Unique and Primary Key Behaviour – Merging DataTables

Source Code: Download

Like this site? Tell it to your Friend :)