Updated date:

Establish DataTable Relation Using C# Ado.net DataRelation With an Example

Author:

I am a software engineer. I have been working with C++, MFC, and .net technologies for 15 years. I like video games and reading books.

1. About DataRelation

Microsoft Dotnet Framework provides DataRelation Class to set relationship between two DataTables. The relationships are set using the data columns in the DataTable. While picking the column the data type should match between the columns.

In this example, we will set DataRelation between three DataGridViews. In our example, we will set DataTable as Data source for these three DataGridViews. We actually set the Relationship between the DataTables and the result looks like there is the relationship between the DataGridViews.

Once, the relationships are established, we will study how DataGridViews behave when we select a row in the DataGridView.

2. About the Example

Now look at the screenshot below and this is the example, we will develop in this Article.

DataRelation Example

DataRelation Example

There are three DataGridView Controls in this Example. All the grids Load when user clicks the Load button. After loading the grids, the user can click on the grid rows to see how the DataRelation behaves. For Example, when one clicks a row in the "List of Stores" grid, the second grid which we call as "Sales by Stores" displays all the book title sold by the select store. In the same manner, when we select a row in the Sales grid, the third DataGridView Control shows all the contributing authors who belongs to the selected title.

All right!. Let us develop this Example.

3. Database Requirement

We need pubs database to go through this example. With simple, Google search you can get Microsoft supplied Pubs and NorthWnd Database. We will use the tables from the Pubs database for this example. However, it is easy to create similar tables with same relationship.

4. Designing the Form

The below screenshot helps in designing the form for this example:

DataRelation Example - Form Design

DataRelation Example - Form Design

We have three Labels, Three DataGridView and two buttons. Control names are shown in the above screenshot.

5. Coding the Example

Most of the code we write goes into the Load Button’s click handler. But, before that let us handle the Close Button handler. When the Close Button is clicked, we quit from the application and below is the code for it:

//Sample 00: Exit the application on Close button click
private void cmdClose_Click(object sender, EventArgs e)
{
    Application.Exit();
}

To work with this application, we need to include the SqlClient name-space into the project. The code is below:

//Sample 01: Inlucde required Namespace
using System.Data.SqlClient;

There are two member variables added to the Form Class. One is DataSet variable "dsDataRelEx" to hold all the DataTable. It will also maintain the relationship between them. The other one is a String which takes the Connection String information from the application settings. The code is below:

//Sample 02: Declare a DataSet
private DataSet dsDataRelEx = null;
private string PubsCon =
    DataRelationExample.Properties.Settings.Default.PubsCon;

The below video shows how to create the Connection String as application property. Once created, we can refer it in the application as shown in the above code snippet.

Video 1: Creating Connection String as Application Property

5.1 Fill the DataTables

We create three different DataTables as part of the DataSet, dsDataRelEx. The First DataTable in the First DataGrid takes information from the Stores table of the Pubs Database. Using a SqlDataAdapter, we are filling the DataSet with a DataTable called “Stores”. The code for this is given below:

//Sample 04: Fill Store List DataGrid
string SqlStr = 
@"Select stor_id, Stor_Name,
Stor_Address,City from stores";
SqlDataAdapter sqlDa = new SqlDataAdapter(
    SqlStr, PubsCon);
sqlDa.Fill(dsDataRelEx, "Stores");
sqlDa.Dispose();

The Same way, other two DataTables Sales and Authors are created and those take part in the DataSet reference dsDataRelEx. The code is given below:

//Sample 05: Fill Sales List DataGrid
SqlStr =
@"Select Ord_num, T.title, Qty, 
stor_id, T.title_id
from Sales S Inner Join titles T 
On S.title_id = T.title_id";
sqlDa = new SqlDataAdapter(
    SqlStr, PubsCon);
sqlDa.Fill(dsDataRelEx, "Sales");
sqlDa.Dispose();

//Sample 06: Fill Authors DataGrid
SqlStr =
@"Select T.title_id, T.title,   
au_lname + ' ' + au_fname 
as Author, phone, address, 
city	 
from Titles T 
Inner Join titleauthor TA
On T.title_id = TA.title_id
Inner Join authors A
On TA.au_id = A.au_id";
sqlDa = new SqlDataAdapter(
    SqlStr, PubsCon);
sqlDa.Fill(dsDataRelEx, "Authors");
sqlDa.Dispose();

At this stage, we have our DataTables ready and DataSet contains these three DataTables. Also, note that we have not introduced any relationship between them. These tables not yet linked to our DataGridView as well.

5.2 Set Relationship Between DataTables

Before we proceed, look at the depiction below:

DataRelation And DataTables

DataRelation And DataTables

The above picture shows what we will achieve in the coming section. At present we have three DataTables in the DataSet. First, we will set the relationship between Sales and Stores by making use of store_id column in the DataTables. Note that the field should match in the Data Type. The same way, we set relationship between Sales and Authors through the Title_id column. Finally, we will link these DataTables with the DataGridView in the Form. Now, we know what we will write and it is time to start our second round of coding.

5.2.1 Create DataRelation between Three DataTables

We use the DataRelation class to establish the Relationship between the DataTables. While creating the DataRelation Class, we pass all the required data in the constructor itself. For Example, consider the below piece of code:

//Sample 07: Create DataRelation
//7.1 Stores and Sales
DataRelation StoreSale = new DataRelation(
    "StoreSales",
    dsDataRelEx.Tables["Stores"].Columns["stor_id"],
    dsDataRelEx.Tables["Sales"].Columns["stor_id"],
    false);

Here, the first parameter specifies the Relationship Name. We specify Relationship candidates through second and third parameters. In our example, we specified the stor_id columns of the DataTables Stores and Sales as second and third parameter to the constructor. Also note that the second parameter passed to the constructor is the parent and the third parameter is a child. In our case, the parent is the stor_id column of the Stores Table.

The last parameter to the constructor tells if a constraint is required. In our case, we asked the Dotnet not to create any constraint.

The same way, we establish Relationship between Sales and Authors DataTables. The code for that is below:

//7.2 Sales and Authors
DataRelation StoreSaleTitleAuth = new DataRelation(
    "TitleAuthors",
    dsDataRelEx.Tables["Sales"].Columns["title_id"],
    dsDataRelEx.Tables["Authors"].Columns["title_id"],
    false);

Now, we have two DataRelation instances with us. We use the DataRelationCollection of the DataSet to add the above created DataRelation. The code is below:

//7.3 Add These Relationship to DataSet
dsDataRelEx.Relations.Add(StoreSale);
dsDataRelEx.Relations.Add(StoreSaleTitleAuth);

At this stage, the DataSet knows of the Relationship between the three DataTables. Now, we will bind all the DataTables and its Relationship with the DataGridView.

5.2.2 Bind DataGridView with DataRelation

We want to display all the stores in the Stores DataGridView Control. So, we can assign the DataSet as its DataSource. But, the Dataset contains three tables in it and we will end up with an ambiguity. Hence, we set the DataMember Property with the DataTable name of the DataSet. In our example, we set this member with the string denoting the Stores DataTable. Below is the code:

//8.0 Now DataSet Tables exists with Relation
//    Bind the DataSet With Relation. Use DataMember
//8.1 Bind DataGridView - Stores
dgStoreList.DataSource = dsDataRelEx;
dgStoreList.DataMember = "Stores"; //DataTable Name

When we click a Store Data Row in this first DataGridView, we want to display all the corresponding sales records in the second DataGridView called dgStoreSales. Here, comes the tricky part. The DataSource property is still set with our DataSet. But, the DataMember is set with a string representing the Relation. It is not just a DataTable name. Here, the below picture explains how the DataMember string is formed so that DataGridView can respond to the DataRow click of the Parent Grid.

DataRelation vs DataMember of DataGridView

DataRelation vs DataMember of DataGridView

First, we will talk about the dgStoreSales DataGridView. When we click a DataRow in the dgStoreList, the dgStoreSales shows the corresponding Sales rows in it.

The third DataGridView also behaves the same way. While we click a row in the second DataGridView called dgStoreSales, the contributing authors are getting displayed in the bottom most grid. The code snippet is below:

//8.2 Bind DataGridView - Sales
dgStoreSales.DataSource = dsDataRelEx;
dgStoreSales.DataMember = "Stores.StoreSales";

//8.3 Bind DataGridView - Authors
dgTitleAuth.DataSource = dsDataRelEx;
dgTitleAuth.DataMember = "Stores.StoreSales.TitleAuthors";

Video 2: Examine the DataRelation between DataTables

Source Code: Download

Source Code: Download

© 2018 sirama

Comments

Nikolay on August 27, 2020:

Thank you very much for the detailed explanation.