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

Updated on August 29, 2018
sirama profile image

I am a software engineer. I have been working with C++, MFC, and .net technologies for 15 years. I like playing video games & 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 | Source

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 | Source

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 | Source

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 | Source

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

Questions & Answers

    © 2018 sirama

    Comments

      0 of 8192 characters used
      Post Comment

      No comments yet.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, owlcation.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://owlcation.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
      ClickscoThis is a data management platform studying reader behavior (Privacy Policy)