none
relations between multiple tables RRS feed

  • 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.

    Thursday, July 26, 2012 4:21 AM

Answers

  • 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

    Saturday, July 28, 2012 4:51 PM

All replies

  • 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!

    Thursday, July 26, 2012 8:18 AM
  • 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,

    Friday, July 27, 2012 3:57 AM
  • 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

    Saturday, July 28, 2012 4:51 PM