DataRelations and DataGrids

  • Thread starter Thread starter aualias
  • Start date Start date
A

aualias

I have tables Junk and JunkLookup in a DataSet. Junk has an integer field
junkType, which is a primary key into JunkLookup.

Can I bind to Junk and use a DataRelation to display and update data in a
DataGrid?
Is it better to do a join in the SQL?

Ideally, I would like to copy the database relationships in the DataSet that
I use on the client.

Thanks.

Alfredo
 
Hi,

Yes you can use a DataRelation for this purpose, you will need to define
a DataRelation between your parent tables and child tables and then use
the DataColumn.Expression property to set the value to the
ParentRelations relevant field.

This is the relevant documentation from the MSDN documentation of
DataColumn.Expression property

PARENT/CHILD RELATION REFERENCING

A parent table may be referenced in an expression by prepending the
column name with Parent. For example, the Parent.Price references the
parent table's column named Price.

A column in a child table may be referenced in an expression by
prepending the column name with Child. However, because child
relationships may return multiple rows, you must include the reference
to the child column in an aggregate function. For example,
Sum(Child.Price) would return the sum of the column named Price in the
child table.

If a table has more than one child, the syntax is: Child(RelationName).
For example, if a table has two child tables named Customers and Orders,
and the DataRelation object is named Customers2Orders, the reference
would be:

Avg(Child(Customers2Orders).Quantity)


Also check out these articles

http://msdn.microsoft.com/library/d...inglookuptableforlistboxorcomboboxcontrol.asp
http://msdn.microsoft.com/library/d...reatingLookupTableOnData-BoundWindowsForm.asp

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph
 
Hi Sijin,

I am still confused as to how this is used with a DataGrid. For example, if
I have two tables and want to display the data (forget about editing for
now):

Table1
name (char)
descriptionFK (int)

Table2 - lookup
id (int)
description (char)

Create the DataSet with the two tables - oDataSet.
Create a DataRelation LookupToMain.
Call dataGrid1.SetDataBindings(oDataSet, "Table1");

How do I display the description field as a column in the DataGrid that
displays data from Table1?

Are there any examples that you know of?

This cannot be difficult. I'm missing something here...

Thanks.

Alfredo
 
Hi Mike,

Based on my understanding, you want to make a Master-Details relation
datatables in dataset and display them in DataGrid. Hope I did not
misunderstand you.

Winform DataGrid provides good support for datarelation, what we should do
is adding 2 datatables in dataset, then associate one table's primary key
column with another table's foreign key column. The DataGrid will recognize
the relation and show the tables in Master-Details mode.

Code like this:
private void Form1_Load(object sender, System.EventArgs e)
{
SqlDataAdapter adapter=new SqlDataAdapter("select * from Orders",
"server=localhost;database=northwind;uid=sa;pwd=test");
DataSet ds=new DataSet();
adapter.Fill(ds, "Order");

adapter=new SqlDataAdapter("select * from [Order Details]",
"server=localhost;database=northwind;uid=sa;pwd=test");
adapter.Fill(ds, "Order Details");

ds.Relations.Add(new DataRelation("orderdetails",
ds.Tables["Order"].Columns["OrderID"], ds.Tables["Order
Details"].Columns["OrderID"]));

this.dataGrid1.DataSource=ds;
}

In the code, I retrieve "Orders" and "Order Details" tables from Sql
server's Northwind database, then associate the data realtion.

For more information about doing master-details databinding for DataGrid,
please refer to:
"Walkthrough: Creating a Master-Detail Windows Form"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbwlkwalkthroughcreatingmaster-detailwindowsform.asp

Also, the below article may help you:
"Data Binding Between Controls in Windows Forms"
http://msdn.microsoft.com/msdnmag/issues/02/02/cutting/default.aspx

=============================================================
Thank you for your patience and cooperation. If you have any questions or
concerns, please feel free to post it in the group. I am standing by to be
of assistance.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Yes, that works, but it is not at all what I want. In a very simple
scenario, one table is just a lookup table.
(As far as I can tell, to set up the relation the lookup must be the parent
or you get the following exception:
"This constraint cannot be enabled as not all values have corresponding
parent values." )

Furthermore, and more importantly, I do not want the Master-Details mode. I
would like to display everything in one DataGrid without resorting to
drill-down.

Obviously, I can do a SQL join to retrieve the data. But then I have to
code the logic for an update. (I know, I am mixing the lookup scenario,
where one would use a dropdown, with a straight join across the data where
you would update more than one table)

I guess my basic question is:
Can you use a DataGrid to display data that is contained in more than one
table - without resorting to drill down (just a flat display)?

Thanks for responding.

Alfredo



"Jeffrey Tan[MSFT]" said:
Hi Mike,

Based on my understanding, you want to make a Master-Details relation
datatables in dataset and display them in DataGrid. Hope I did not
misunderstand you.

Winform DataGrid provides good support for datarelation, what we should do
is adding 2 datatables in dataset, then associate one table's primary key
column with another table's foreign key column. The DataGrid will recognize
the relation and show the tables in Master-Details mode.

Code like this:
private void Form1_Load(object sender, System.EventArgs e)
{
SqlDataAdapter adapter=new SqlDataAdapter("select * from Orders",
"server=localhost;database=northwind;uid=sa;pwd=test");
DataSet ds=new DataSet();
adapter.Fill(ds, "Order");

adapter=new SqlDataAdapter("select * from [Order Details]",
"server=localhost;database=northwind;uid=sa;pwd=test");
adapter.Fill(ds, "Order Details");

ds.Relations.Add(new DataRelation("orderdetails",
ds.Tables["Order"].Columns["OrderID"], ds.Tables["Order
Details"].Columns["OrderID"]));

this.dataGrid1.DataSource=ds;
}

In the code, I retrieve "Orders" and "Order Details" tables from Sql
server's Northwind database, then associate the data realtion.

For more information about doing master-details databinding for DataGrid,
please refer to:
"Walkthrough: Creating a Master-Detail Windows Form"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbwlkwalkthroughcreatingmaster-detailwindowsform.asp

Also, the below article may help you:
"Data Binding Between Controls in Windows Forms"
http://msdn.microsoft.com/msdnmag/issues/02/02/cutting/default.aspx

=============================================================
Thank you for your patience and cooperation. If you have any questions or
concerns, please feel free to post it in the group. I am standing by to be
of assistance.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Click to expand...
 
Hi Alfredo,

Thanks very much for your feedback.

Based on your statement, I think you want to display all the rows in a
datagrid without master-details relations. That is: if master table's one
record mapping to 3 records in child tables, then you want to join the
master table with child table, and display just in one table. Then there
will be some redundant master table data in the result joined table. If I
misunderstand you, please feel free to point out.

Just as you said, doing this, we should use SQL statement to join this 2
tables into a result table, then fill into one datatable, and bind to
DataGrid.

There is a big updating synchronization issue about this way. If we
modified one record's master table column in the result datatable, the
other records' master column should be updated either. To keep the updating
synchronization, we should hook CurrentcyManager.ItemChanged event, update
all the related records with the same key column.

Anyway, I think this is not a good design for displaying master-details
database table, it has both data redundant and updating synchronization
issue. Can you show me why you want to show in this way? Thanks

=========================================
Thank you for your patience and cooperation. If you have any questions or
concerns, please feel free to post it in the group. I am standing by to be
of assistance.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Jeffrey,

Right now I am just working out possible scenarios for a project, so I have
probably not been as specific as I should have been. I think that you have
answered my question. You cannot use a DataRelation between two tables in a
DataSet in this manner. That's fine, now I don't have to figure out how to
do it...

As to the SQL. I would just have one table (the join) in the DataSet and
update the database from the data in that table. There would not be any
redundant data and I would have to write some code for the update.

To be honest, while the default master-details display is kind of neat, I
find it totally useless. It is not the way a typical user is used to
viewing data. In this case I just want to show a useful subset of the data,
which is a very typical thing to do. I do not want to show all the data in
the tables.

It would be great if I could create two tables and a relation, then display
and update data in a flat display using DataGrid. Since the DataGrid uses a
table DataMember for each display, it seems that I have to use a join to get
the fields of interest to show together.

Thanks for your help.

Alfredo



"Jeffrey Tan[MSFT]" said:
Hi Alfredo,

Thanks very much for your feedback.

Based on your statement, I think you want to display all the rows in a
datagrid without master-details relations. That is: if master table's one
record mapping to 3 records in child tables, then you want to join the
master table with child table, and display just in one table. Then there
will be some redundant master table data in the result joined table. If I
misunderstand you, please feel free to point out.

Just as you said, doing this, we should use SQL statement to join this 2
tables into a result table, then fill into one datatable, and bind to
DataGrid.

There is a big updating synchronization issue about this way. If we
modified one record's master table column in the result datatable, the
other records' master column should be updated either. To keep the updating
synchronization, we should hook CurrentcyManager.ItemChanged event, update
all the related records with the same key column.

Anyway, I think this is not a good design for displaying master-details
database table, it has both data redundant and updating synchronization
issue. Can you show me why you want to show in this way? Thanks

=========================================
Thank you for your patience and cooperation. If you have any questions or
concerns, please feel free to post it in the group. I am standing by to be
of assistance.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Click to expand...
 
Hi Alfredo,

Thanks very much for your feedback.

Yes, for your requirement of displaying both the parent and child rows in
one table, there is no build-in support for the updating synchroniztion, we
have to do it in code manually.

Maybe we may refer to another design to workaround this issue:

I think your concern is displaying the all the child table's rows. We may
just select the parent and child tables in 2 datatables in one dataset, add
the datarelation. Then we may only display the child table in the main
DataGrid, then use the datarelation to display the selected child row's
parent row in another datagrid(or in several TextBox, as you like). Because
several child rows share the one parent row, this will be enough for the
customer to view them. Also, the only one parent row will eliminate the
parent row updating synchronization issue.

Also, we need to hook main DataGrid's currentcell change event to change
the parent datagrid's dispaly, keep the display synchronization. Also, we
may select all the child rows in the code to enable the user to view the
parent/child relation, sample code like this:

DataSet ds=null;
private void Form3_Load(object sender, System.EventArgs e)
{
SqlDataAdapter adapter=new SqlDataAdapter("select * from Orders",
"server=localhost;database=northwind;uid=sa;pwd=test");
ds=new DataSet();
adapter.Fill(ds, "Order");

adapter=new SqlDataAdapter("select * from [Order Details]",
"server=localhost;database=northwind;uid=sa;pwd=test");
adapter.Fill(ds, "Order Details");

ds.Relations.Add(new DataRelation("orderdetails",
ds.Tables["Order"].Columns["OrderID"], ds.Tables["Order
Details"].Columns["OrderID"]));

this.dataGrid1.DataSource=ds.Tables["Order Details"];

Bind_parentGrid();
}

private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e)
{
Bind_parentGrid();
}

private void Bind_parentGrid()
{
CurrencyManager cm=this.BindingContext[ds.Tables["Order Details"]] as
CurrencyManager;
DataView dv=cm.List as DataView;
DataRow
dr=dv[this.dataGrid1.CurrentCell.RowNumber].Row.GetParentRow("orderdetails")
;


ds.Tables["Order"].DefaultView.RowFilter="OrderID="+dr["OrderID"].ToString()
;
this.dataGrid2.DataSource=ds.Tables["Order"].DefaultView;

if(dr!=null)
{
DataRow[] child_rows=dr.GetChildRows("orderdetails");



for(int i=0;i<dv.Count;i++)
{
foreach(DataRow row in child_rows)
{
if(dv.Row.Equals(row))
{
this.dataGrid1.Select(i);
}
}
}
}
}
It works well on my side.

Anyway, this is only a design option for your information.

Thank you for your patience and cooperation. If you have any questions or
concerns, please feel free to post it in the group. I am standing by to be
of assistance.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Jeffrey,

Thanks for bearing with me.

My question was about using one DataGrid and displaying data from more than
one related table. I believe I have the answer to that now.

Your example is basically how I would do it if I were using two DataGrids.
That would be a good solution in many circumstances. In the app that I am
thinking about, all the necessary fields would easily fit in one grid
display without the user having to scroll horizontally. In this case,
showing everything in one grid is a much cleaner UI.

Thanks again.

Alfredo



"Jeffrey Tan[MSFT]" said:
Hi Alfredo,

Thanks very much for your feedback.

Yes, for your requirement of displaying both the parent and child rows in
one table, there is no build-in support for the updating synchroniztion, we
have to do it in code manually.

Maybe we may refer to another design to workaround this issue:

I think your concern is displaying the all the child table's rows. We may
just select the parent and child tables in 2 datatables in one dataset, add
the datarelation. Then we may only display the child table in the main
DataGrid, then use the datarelation to display the selected child row's
parent row in another datagrid(or in several TextBox, as you like). Because
several child rows share the one parent row, this will be enough for the
customer to view them. Also, the only one parent row will eliminate the
parent row updating synchronization issue.

Also, we need to hook main DataGrid's currentcell change event to change
the parent datagrid's dispaly, keep the display synchronization. Also, we
may select all the child rows in the code to enable the user to view the
parent/child relation, sample code like this:

DataSet ds=null;
private void Form3_Load(object sender, System.EventArgs e)
{
SqlDataAdapter adapter=new SqlDataAdapter("select * from Orders",
"server=localhost;database=northwind;uid=sa;pwd=test");
ds=new DataSet();
adapter.Fill(ds, "Order");

adapter=new SqlDataAdapter("select * from [Order Details]",
"server=localhost;database=northwind;uid=sa;pwd=test");
adapter.Fill(ds, "Order Details");

ds.Relations.Add(new DataRelation("orderdetails",
ds.Tables["Order"].Columns["OrderID"], ds.Tables["Order
Details"].Columns["OrderID"]));

this.dataGrid1.DataSource=ds.Tables["Order Details"];

Bind_parentGrid();
}

private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e)
{
Bind_parentGrid();
}

private void Bind_parentGrid()
{
CurrencyManager cm=this.BindingContext[ds.Tables["Order Details"]] as
CurrencyManager;
DataView dv=cm.List as DataView;
DataRow
dr=dv[this.dataGrid1.CurrentCell.RowNumber].Row.GetParentRow("orderdetails")
;
Click to expand...
ds.Tables["Order"].DefaultView.RowFilter="OrderID="+dr["OrderID"].ToString()
;
this.dataGrid2.DataSource=ds.Tables["Order"].DefaultView;

if(dr!=null)
{
DataRow[] child_rows=dr.GetChildRows("orderdetails");



for(int i=0;i<dv.Count;i++)
{
foreach(DataRow row in child_rows)
{
if(dv.Row.Equals(row))
{
this.dataGrid1.Select(i);
}
}
}
}
}
It works well on my side.

Anyway, this is only a design option for your information.

Thank you for your patience and cooperation. If you have any questions or
concerns, please feel free to post it in the group. I am standing by to be
of assistance.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Click to expand...
 
Hi Alfredo,

Thanks very much for your feedback.

I think this is totally a design issue, and we have several choice, and we
have to make decision. Anyway, I am glad you have got what you want, if you
need further help, please feel free to tell me, Thanks.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
I had the same question that 'aualias' asked but it appears that it was never
answered in this thread. So, how would you use one datagrid to display
'Table1' below but instead of showing the 'TagTypeID' column from 'Table1' I
want show the 'TagTypeName' column from 'Table2' instead?

Here are the 2 tables:
Table1 --> TagID, TagName, TagTypeID
Table2 --> TagTypeID, TagTypeName
 
Back
Top