December 10, 2018 | Posted in: .Net

So, this is a very short article about creating a relationship between two data tables in a dataset.

In my case, I was returning a dataset, with two data tables and I needed to add a relationship between the tables for the purposes of displaying that data in a nested fashion in a grid control (rough example – not complete code):

DataSet dataSource;

………..

DataRelation newRelation = new DataRelation(
“RelationshipName”,
headers.Columns["ParentID"],
lines.Columns["ChildID"]);

dataSource.Relations.Add(newRelation);

At the point of adding the DataRelation to the dataset, an error would bubble up as per the title of this blog post, “These columns don’t currently have unique values.”. Having gone through the data returned from the stored procedure I did in fact find ParentID in there twice which caused this error, however I found that I could overcome the error by passing in false on the next DataRelation param called bool createConstraints which may be something you want to do:

DataRelation newRelation = new DataRelation(
“Line”,
headers.Columns["RecId"],
lines.Columns["RecID"],
false);

In my case I didn’t end up doing this as I wanted to see the error and find the data issues (it might be an good idea to add a unique constraint in your database if this does not follow the required database design and duplicates should not exist). A join in the stored procedure was my problem, returning the same record twice.

“Notice that when the DataRelation is created for the Customers and Orders tables, no value is specified for the createConstraints flag (the default is true). This assumes that all the rows in the Orders table have a CustomerID value that exists in the parent Customers table. If a CustomerID exists in the Orders table that does not exist in the Customers table, a ForeignKeyConstraint causes an exception to be thrown.
When the child column might contain values that the parent column does not contain, set the createConstraints flag to false when adding the DataRelation. In the example, the createConstraints flag is set to false for the DataRelation between the Orders table and the OrderDetails table. This enables the application to return all the records from the OrderDetails table and only a subset of records from the Orders table without generating a run-time exception.”

Source: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/navigating-datarelations

 

Be the first to comment.

Leave a Reply

*


nine + 9 =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>