How to display relational data between more than two tables.

Question

Hi
i have 3 tables :

Roles (roleId [int], roleName [varchar])
Objects (objectId [int], objectName [varchar])
Permissions (permissionId [int], roleId [int], objectId [int], allowView [bit], allowChange [bit])

Note : Permissions.roleId is foreign key for Roles.roleId
and Permissions.objectId is foreign key for Objects.objectId

in my form, i have :

comboBox to display avialble roles (data come from Roles table)
datagridview to display available objects (data come from Objects table)
two checkBox (View , Change )

i want, when user click on each object in dataGridView, security info for selected Role and object display by two checkBoxes,
but i don't know which dataSource is appropriate for checkBoxes .

Note : i have a DataSet in my app with that 3 tables included with their relationShips ,
but i don't know how to bind this controls to display correct data for each Role and Object.

---

this is my Signature

Answer 1

So in your DataSet, you created a DataRelation?

//We will assume your DataTables have already been filled and appropriately named.
roles.PrimaryKey = new DataColumn[] { role.Columns[0] };
roles.TableName = "Roles";
objects.PrimaryKey = new DataColumn[] { objects.Columns[0] };
objects.TableName = "Objects";
permissions.PrimaryKey = new DataColumn[] { permissions.Columns[0] };
permissions.TableName = "Permissions";

//Add the Tables to DataSet
DataSet ds = new DataSet();
ds.Tables.Add(roles);
ds.Tables.Add(objects);
ds.Tables.Add(permissions);

//Let's create the relations
DataSet ds = new DataSet();
ds.Relations.Add("RolesPermissions",
         ds.Tables["Roles"].Columns["RoleID"],
         ds.Tables["Permissions"].Columns["RoleID"],
         true); 
ds.Relations.Add("ObjectsPermissions",
         ds.Tables["Objects"].Columns["ObjectID"],
         ds.Tables["Permissions"].Columns["ObjectID"],
         true); 

//Let's bind
cbRoles.DataSource = ds.Tables["Roles"];
cb.Roles.DisplayMember = "RoleName";
cbRoles.ValueMember = "RoleID";

dgObjects.DataSource = ds.Tables["Objects"];

//Create event
 private void dgObjects_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
     Int32 objectID = Convert.ToInt32(dgObjects.Rows[e.RowIndex].Cells["ObjectID"].Value);

     //Example 1
     DataRow row = ds.Tables["Objects"].Rows.Find(objectID);
     DataRow[] childRows = row.GetChildRows("ObjectsPermissions");    

     //Example 2
     DataView dv = new DataView(ds.Tables["Permissions"]);
     dv.RowFilter = String.Format("ObjectID = {0}", objectID);
     DataTable results = dv.ToTable();
}

Many ways to get to the data. I wouldn't necessarily bind a checkBox, but you can set its Tag property to whatever you want.

By the way, in the future, try this forum for your ADO.NET DataSet questions:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/threads

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Answer 2

Or of course you can use LINQ to query your stuff:

Int32 objectID = Convert.ToInt32(dgObjects.Rows[e.RowIndex].Cells["ObjectID"].Value);
DataRow[] rows = ds.Tables["Permissions"].AsEnumerable()
       .Where(r => r.Field<Int32>("ObjectID") == objectID).ToArray();

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Answer 3

Many thanks John
but i can't bind my checkBoxes to result data :

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            object obj = this.dataGridView1.Rows[e.RowIndex].Cells["Id"].Value;
            int objectId = Convert.ToInt32(obj);

            DataView dv = new DataView(ShareData.dsSecurityTest.P);
            dv.RowFilter = String.Format("ObjectId = {0}", objectId);
            DataTable results = dv.ToTable();

            if (this.chkAdmin.DataBindings.Count > 0) 
                this.chkAdmin.DataBindings.Clear();
            this.chkAdmin.DataBindings.Add("Checked", results, "isAdmin");

            if (this.chkView.DataBindings.Count > 0)
                this.chkView.DataBindings.Clear();
            this.chkView.DataBindings.Add("Checked", results, "allowView");
        }

---

this is my Signature

Answer 4

Hi again John
as u can see in my first post, Permissions table has relashionShip between roles and objects, so in your code relashionship between Roles and Permissions did not noted :

//Example 1
     DataRow row = ds.Tables["Objects"].Rows.Find(objectID);
     DataRow[] childRows = row.GetChildRows("ObjectsPermissions");

---

this is my Signature

Answer 5

First of all, did you examine the results? Is isAdmin a row? Does it contains a bool value or string value?

Does results bring back many rows or just a row?

Lastly, you can bind the results by going to the row of the results and finding your bool string
chkAdmin.Checked = Boolean.Parse(results.Rows["isAdmin"][0].ToString());

Answer 6

Hi Hamed_1983,

We are marking John's posts as answers.
If you still have problem, please feel free to unmark as the answer and change the issue type back to "Question" and follow up with more necessary information. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions. Thank you!

Best regards,
Guo

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.