1. About DataRelation
Microsoft Dotnet Framework provides DataRelation Class to set the 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 a 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. This is the example we will develop in this article.
There are three DataGridView Controls in this example. All the grids load when the 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 "Sales by Stores", displays all the book titles 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 belong to the selected title.
All right!. Let us develop this Example.
3. Database Requirement
We need a Pubs database to go through this example. With a simple Google search, you can get the Microsoft-supplied Pubs and NorthWnd databases. We will use the tables from the Pubs database for this example. However, it is easy to create similar tables with the same relationship.
4. Designing the Form
The below screenshot helps in designing the form for this example:
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:
To work with this application, we need to include the SqlClient name-space into the project. The code is below:
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:
The below video shows how to create the Connection String as an application property. Once created, we can refer to 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:
In the same way, the other two DataTables Sales and Authors are created, and those take part in the DataSet reference dsDataRelEx. The code is given below:
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 are not yet linked to our DataGridView as well.
5.2 Set Relationship Between DataTables
Before we proceed, look at the depiction below:
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 the store_id column in the DataTables. Note that the field should match the Data Type. In the same way, we set the 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:
Here, the first parameter specifies the Relationship Name. We specify Relationship candidates through the second and third parameters. In our example, we specified the stor_id columns of the DataTables Stores and Sales as the second and third parameters 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.
In the same way, we establish Relationship between Sales and Authors DataTables. The code for that is below:
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:
At this stage, the DataSet knows of the Relationship between the three DataTables. Now, we will bind all the DataTables and their 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:
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.
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 bottommost grid. The code snippet is below:
Video 2: Examine the DataRelation between DataTables
Source Code: Download
© 2018 sirama
Nikolay on August 27, 2020:
Thank you very much for the detailed explanation.