relations between multiple tables

Question

Dear all,

I'm new to ADO.NET. I have problem about define relations in DataSet. We have 3 tables.

User (identified by User id)

User_Role (identified by User_id, Role_id)

Role_Function (identified by Role_id, Function ID)

I have problem when define relation

relUserRole  = New DataRelation( _
                "UserRole", ds.Tables("User").Columns("User_Id"), ds.Tables("Role").Columns("User_Id"))
            ds.Relations.Add(relUserRole) ' This is ok!
            relRoleFunc = New DataRelation( _
                "RoleFunc", ds.Tables("Role").Columns("ROLE_ID"), ds.Tables("Function").Columns("ROLE_ID"))
            ds.Relations.Add(relRoleFunc) ' It abort for exception that "These columns don't currently have unique values."

Do I use wrong syntax, or I can't define parent - child - grand child relation? If it can't be defined, what workaround can be done?

Terence.

Answer 1

Matching (child) column does not have to contain unique values, but parent must. Is it possible that parent column contains multiple rows with NULL value or blank string in your case? .Check the following example.

Error Relation Statement

 DataSet dst = new DataSet();
DataTable dtUser = new DataTable("User");
dtUser.Columns.Add("UserId", typeof(int));
dtUser.Rows.Add(1);
dtUser.Rows.Add(2);
dtUser.Rows.Add(3);
dtUser.Rows.Add(4);
dtUser.Rows.Add(5);
DataTable dtRole = new DataTable("Role");
dtRole.Columns.Add("UserId", typeof(int));
dtRole.Columns.Add("RoleId", typeof(int));
dtRole.Rows.Add(1, 1);
dtRole.Rows.Add(1, 2);
dtRole.Rows.Add(2, 2);
dtRole.Rows.Add(3, 1);
dtRole.Rows.Add(4, 4);
dtRole.Rows.Add(5, 4);
DataTable dtRoleFunction = new DataTable("Role_Function ");
dtRoleFunction.Columns.Add("RoleId", typeof(int));
dtRoleFunction.Columns.Add("FunctionId", typeof(int));
dtRoleFunction.Rows.Add(1, 1);
dtRoleFunction.Rows.Add(2, 2);
dtRoleFunction.Rows.Add(3, 3);
dtRoleFunction.Rows.Add(4, 1);
dtRoleFunction.Rows.Add(5, 4);
dst.Tables.Add(dtUser);
dst.Tables.Add(dtRole);
  dst.Tables.Add(dtRoleFunction);
DataRelation relation = new DataRelation("UserRole",dtUser.Columns["UserId"], dtRole.Columns["UserId"]);
DataRelation relRoleFunc = new DataRelation("RoleFunc", dtRole.Columns["RoleId"], dtRoleFunction.Columns["RoleId"]);
dst.Relations.Add(relation);

Correct relation Statement

DataSet dst = new DataSet();
DataTable dtUser = new DataTable("User");
dtUser.Columns.Add("UserId", typeof(int));
dtUser.Rows.Add(1);
dtUser.Rows.Add(2);
dtUser.Rows.Add(3);
dtUser.Rows.Add(4);
dtUser.Rows.Add(5);
DataTable dtRole = new DataTable("Role");
dtRole.Columns.Add("UniqueId", typeof(int));
dtRole.Columns.Add("UserId", typeof(int));
dtRole.Columns.Add("RoleId", typeof(int));
dtRole.Rows.Add(1,1, 1);
dtRole.Rows.Add(2,1, 2);
dtRole.Rows.Add(3,2, 2);
dtRole.Rows.Add(4,3, 1);
dtRole.Rows.Add(5,4, 4);
dtRole.Rows.Add(6,5, 4);
 DataTable dtRoleFunction = new DataTable("Role_Function ");
dtRoleFunction.Columns.Add("RoleId", typeof(int));
dtRoleFunction.Columns.Add("FunctionId", typeof(int));
dtRoleFunction.Rows.Add(1, 1);
dtRoleFunction.Rows.Add(2, 2);
dtRtRoleFunctioleFunction.Rows.Add(3, 3);
don.Rows.Add(4, 1);
dtRoleFunction.Rows.Add(5, 4);
dst.Tables.Add(dtUser);
dst.Tables.Add(dtRole);
dst.Tables.Add(dtRoleFunction);
DataRelation relation = new DataRelation("UserRole",dtUser.Columns["UserId"], dtRole.Columns["UserId"]);
DataRelation relRoleFunc = new DataRelation("RoleFunc", dtRole.Columns["UniqueId"], dtRoleFunction.Columns["RoleId"]);
dst.Relations.Add(relation);
dst.Relations.Add(relRoleFunc);

---

With Thanks and Regards
Sambath Raj.C
click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
Happy Programming!

Answer 2

Dear Sambath,

We have exactly the same issue you show in Error Relation Statement. How how can I achieve what we want. I want 3 DataGridView show the rows for

1) Users

2) selected User's Roles

3) selected Role's Function.

Many user will be the same roles (e.g. data input clerk). If we use unique id in users' role table. We need define role function for each users, that make the definition very complicate. Any work around that we can show the information as we needed.

Thanks and Regards,

Answer 3

Instead of using DataRelations, you could use DataViews, the easist being simply using the DefaultView of the DataTables. The only difference is that you have to manage the filters for the views (like when a row changes in one of the tables, use event handlers for that):

ds.Tables.["Role"].DefaultView.RowFilter = "User_Id = " + ds.Tables["User"].Rows[WhateverUserRow]["User_Id"];
ds.Tables.["Function"].DefaultView.RowFilter = "ROLE_ID = " + ds.Tables["Role"].Rows[WhateverRoleRow]["ROLE_ID"];

You'd still bind to the DataTables, because the binding mechanisms actually use the DefaultView of a DataTable.

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com