Displaying n:m relation without showing the in-between table.

G

Guest

Hi,

I want to display an n:m relation in Windows without showing the in-between
table.
As an example, I used the Orders, Order Details and Products tables from the
Northwind database.
I use a ComboBox for the parent and want the details in a DataGrid

I have set up a form and added some SqlDataAdapters and a DataSet, ComboBox
and DataGrid.
(using Visual Studio 2003).
Here is the code I added:

System.Data.DataColumn masterCol;
System.Data.DataColumn detailCol;
DataRelation dr;

// Let the DataAdapters fill the DataSet
DA_Orders.Fill(dataSet11);
DA_Products.Fill(dataSet11);
DA_OrderDetails.Fill(dataSet11);

//add relation Orders - Order Details
masterCol = dataSet11.Tables["Orders"].Columns["OrderID"];
detailCol = dataSet11.Tables["Order Details"].Columns["OrderID"];

dr = new DataRelation("OrdersToOrderDetails",masterCol,detailCol);

dataSet11.Relations.Add(dr);

//add relation Order Details - Products
masterCol = dataSet11.Tables["Order Details"].Columns["ProductID"];
detailCol = dataSet11.Tables["Products"].Columns["ProductID"];

dr = new DataRelation("OrderDetailsToProducts",masterCol,detailCol,false);

dataSet11.Relations.Add(dr);

// Let the ComboBox display the OrderID
comboBox1.DataSource = dataSet11;
comboBox1.DisplayMember = "Orders.OrderID";

// Let the DataGrid display the OrderDetails
dataGrid1.DataSource = dataSet11;
dataGrid1.DataMember = "Orders.OrdersToOrderDetails";

When the DataGrid has "AllowNavigation" set to true, everything works fine.
I get 3 Order details records, and by pressing "+" I can go to the Products
information.
!!!! However, I don't want to see the in-between table Order Details !!!!!

So I tried setting
dataGrid1.DataMember = "Orders.OrdersToOrderDetails.OrderDetailsToProducts",
in an attempt to leave out the in-between table, and directly show the
Products.
But now I don't see 3 Products anymore, I only see the first one.

What is the best way to hide the Order Details?

Thanks in advance,

Alexander van Buitenen.
 
B

Bart Mermuys

Hi,

Alexander van Buitenen said:
Hi,

I want to display an n:m relation in Windows without showing the
in-between
table.
As an example, I used the Orders, Order Details and Products tables from
the
Northwind database.
I use a ComboBox for the parent and want the details in a DataGrid

I have set up a form and added some SqlDataAdapters and a DataSet,
ComboBox
and DataGrid.
(using Visual Studio 2003).
Here is the code I added:

System.Data.DataColumn masterCol;
System.Data.DataColumn detailCol;
DataRelation dr;

// Let the DataAdapters fill the DataSet
DA_Orders.Fill(dataSet11);
DA_Products.Fill(dataSet11);
DA_OrderDetails.Fill(dataSet11);

//add relation Orders - Order Details
masterCol = dataSet11.Tables["Orders"].Columns["OrderID"];
detailCol = dataSet11.Tables["Order Details"].Columns["OrderID"];

dr = new DataRelation("OrdersToOrderDetails",masterCol,detailCol);

dataSet11.Relations.Add(dr);

//add relation Order Details - Products
masterCol = dataSet11.Tables["Order Details"].Columns["ProductID"];
detailCol = dataSet11.Tables["Products"].Columns["ProductID"];

dr = new DataRelation("OrderDetailsToProducts",masterCol,detailCol,false);

dataSet11.Relations.Add(dr);

// Let the ComboBox display the OrderID
comboBox1.DataSource = dataSet11;
comboBox1.DisplayMember = "Orders.OrderID";

// Let the DataGrid display the OrderDetails
dataGrid1.DataSource = dataSet11;
dataGrid1.DataMember = "Orders.OrdersToOrderDetails";

When the DataGrid has "AllowNavigation" set to true, everything works
fine.
I get 3 Order details records, and by pressing "+" I can go to the
Products
information.
!!!! However, I don't want to see the in-between table Order Details !!!!!

So I tried setting
dataGrid1.DataMember =
"Orders.OrdersToOrderDetails.OrderDetailsToProducts",
in an attempt to leave out the in-between table, and directly show the
Products.
But now I don't see 3 Products anymore, I only see the first one.

What is the best way to hide the Order Details?
Click to expand...

Order 1<--->oo OrderDetail oo<-->1 Product

So, Product isn't a child table of OrderDetails it's a lookup table. The
second relation you add is really wrong and that's why you must disable
constraints. Instead Product should be the parent, eg. there can be
multiple OrderDetails for each Product.

//add relation Product - Order Details
masterCol = dataSet11.Tables["Products"].Columns["ProductID"];
detailCol = dataSet11.Tables["Order Details"].Columns["ProductID"];
dr = new DataRelation("ProductsToOrderDetails", masterCol, detailCol);

There is no direct support for showing n:m relations without using the
inbetween table, so what you can do is show the inbetween table
(OrderDetails) and add expression columns to it that get the fields from the
parent Product; eg:

dataSet11.Tables["Order Details"].Columns.Add(
"ProductName", typeof(String),
"Parent(ProductsToOrderDetails).ProductName");
.....

And you could then hide the Order Details columns you don't want to see, eg:
dataSet11.Tables["Order Details"].Columns["ProductID"].ColumnMapping =
MappingType.Hidden;
....

I know there is an article about this somewhere but i can't remember where.


HTH
Greetings
 
G

Guest

Hi Bart,

Using the inbetween table as the display table was a real eye-opener.
Adding a column with the string expression
"Parent(ProductsToOrderDetails).ProductName" was new for me.

Your suggestion to hide columns this way:

dataSet11.Tables["Order Details"].Columns["ProductID"].ColumnMapping =
MappingType.Hidden;

did't work.

MSDN Help say's:
"The ColumnMapping property determines how a DataColumn is mapped when a
DataSet is saved as an XML document using the WriteXml method."

So it does not influence the way it is displayed in the DataGrid.

Now I delete GridColumnStyles to hide a column:

// Create a DataGridTableStyle
DataGridTableStyle dataGridTableStyle = new DataGridTableStyle();

// Map it to Order Details
dataGridTableStyle.MappingName = "Order Details";

// When you add an empty DataGridTableStyle to the collection,
// DataGridColumnStyle objects are automatically generated for you.
dataGrid1.TableStyles.Add(dataGridTableStyle);

// Now remove the GridColumnStyle for ProductID
DataGridColumnStyle style =
dataGrid1.TableStyles[0].GridColumnStyles["ProductID"];
dataGrid1.TableStyles[0].GridColumnStyles.Remove(style);

Now everything works as I wanted it!

Thank you very much for your help,

Alexander van Buitenen.



Bart Mermuys said:
Hi,

Alexander van Buitenen said:
Hi,

I want to display an n:m relation in Windows without showing the
in-between
table.
As an example, I used the Orders, Order Details and Products tables from
the
Northwind database.
I use a ComboBox for the parent and want the details in a DataGrid

I have set up a form and added some SqlDataAdapters and a DataSet,
ComboBox
and DataGrid.
(using Visual Studio 2003).
Here is the code I added:

System.Data.DataColumn masterCol;
System.Data.DataColumn detailCol;
DataRelation dr;

// Let the DataAdapters fill the DataSet
DA_Orders.Fill(dataSet11);
DA_Products.Fill(dataSet11);
DA_OrderDetails.Fill(dataSet11);

//add relation Orders - Order Details
masterCol = dataSet11.Tables["Orders"].Columns["OrderID"];
detailCol = dataSet11.Tables["Order Details"].Columns["OrderID"];

dr = new DataRelation("OrdersToOrderDetails",masterCol,detailCol);

dataSet11.Relations.Add(dr);

//add relation Order Details - Products
masterCol = dataSet11.Tables["Order Details"].Columns["ProductID"];
detailCol = dataSet11.Tables["Products"].Columns["ProductID"];

dr = new DataRelation("OrderDetailsToProducts",masterCol,detailCol,false);

dataSet11.Relations.Add(dr);

// Let the ComboBox display the OrderID
comboBox1.DataSource = dataSet11;
comboBox1.DisplayMember = "Orders.OrderID";

// Let the DataGrid display the OrderDetails
dataGrid1.DataSource = dataSet11;
dataGrid1.DataMember = "Orders.OrdersToOrderDetails";

When the DataGrid has "AllowNavigation" set to true, everything works
fine.
I get 3 Order details records, and by pressing "+" I can go to the
Products
information.
!!!! However, I don't want to see the in-between table Order Details !!!!!

So I tried setting
dataGrid1.DataMember =
"Orders.OrdersToOrderDetails.OrderDetailsToProducts",
in an attempt to leave out the in-between table, and directly show the
Products.
But now I don't see 3 Products anymore, I only see the first one.

What is the best way to hide the Order Details?
Click to expand...

Order 1<--->oo OrderDetail oo<-->1 Product

So, Product isn't a child table of OrderDetails it's a lookup table. The
second relation you add is really wrong and that's why you must disable
constraints. Instead Product should be the parent, eg. there can be
multiple OrderDetails for each Product.

//add relation Product - Order Details
masterCol = dataSet11.Tables["Products"].Columns["ProductID"];
detailCol = dataSet11.Tables["Order Details"].Columns["ProductID"];
dr = new DataRelation("ProductsToOrderDetails", masterCol, detailCol);

There is no direct support for showing n:m relations without using the
inbetween table, so what you can do is show the inbetween table
(OrderDetails) and add expression columns to it that get the fields from the
parent Product; eg:

dataSet11.Tables["Order Details"].Columns.Add(
"ProductName", typeof(String),
"Parent(ProductsToOrderDetails).ProductName");
.....

And you could then hide the Order Details columns you don't want to see, eg:
dataSet11.Tables["Order Details"].Columns["ProductID"].ColumnMapping =
MappingType.Hidden;
....

I know there is an article about this somewhere but i can't remember where.


HTH
Greetings
Thanks in advance,

Alexander van Buitenen.
Click to expand...
Click to expand...
 
B

Bart Mermuys

Hi,

Alexander van Buitenen said:
Hi Bart,

Using the inbetween table as the display table was a real eye-opener.
Adding a column with the string expression
"Parent(ProductsToOrderDetails).ProductName" was new for me.

Your suggestion to hide columns this way:

dataSet11.Tables["Order Details"].Columns["ProductID"].ColumnMapping =
MappingType.Hidden;

did't work.

MSDN Help say's:
"The ColumnMapping property determines how a DataColumn is mapped when a
DataSet is saved as an XML document using the WriteXml method."
Click to expand...

Yes, but the following link shows both approaches: ColumnMapping and
DataGridColumnStyles.
http://support.microsoft.com/default.aspx?scid=kb;en-us;317951

Not sure why ColumnMapping didn't work for you. Anyway you got it to work
with DataGridColumnStyles which is just as good.


Greetings
So it does not influence the way it is displayed in the DataGrid.

Now I delete GridColumnStyles to hide a column:

// Create a DataGridTableStyle
DataGridTableStyle dataGridTableStyle = new DataGridTableStyle();

// Map it to Order Details
dataGridTableStyle.MappingName = "Order Details";

// When you add an empty DataGridTableStyle to the collection,
// DataGridColumnStyle objects are automatically generated for you.
dataGrid1.TableStyles.Add(dataGridTableStyle);

// Now remove the GridColumnStyle for ProductID
DataGridColumnStyle style =
dataGrid1.TableStyles[0].GridColumnStyles["ProductID"];
dataGrid1.TableStyles[0].GridColumnStyles.Remove(style);

Now everything works as I wanted it!

Thank you very much for your help,

Alexander van Buitenen.



Bart Mermuys said:
Hi,

"Alexander van Buitenen" <Alexander van
(e-mail address removed)>
wrote in message
Hi,

I want to display an n:m relation in Windows without showing the
in-between
table.
As an example, I used the Orders, Order Details and Products tables
from
the
Northwind database.
I use a ComboBox for the parent and want the details in a DataGrid

I have set up a form and added some SqlDataAdapters and a DataSet,
ComboBox
and DataGrid.
(using Visual Studio 2003).
Here is the code I added:

System.Data.DataColumn masterCol;
System.Data.DataColumn detailCol;
DataRelation dr;

// Let the DataAdapters fill the DataSet
DA_Orders.Fill(dataSet11);
DA_Products.Fill(dataSet11);
DA_OrderDetails.Fill(dataSet11);

//add relation Orders - Order Details
masterCol = dataSet11.Tables["Orders"].Columns["OrderID"];
detailCol = dataSet11.Tables["Order Details"].Columns["OrderID"];

dr = new DataRelation("OrdersToOrderDetails",masterCol,detailCol);

dataSet11.Relations.Add(dr);

//add relation Order Details - Products
masterCol = dataSet11.Tables["Order Details"].Columns["ProductID"];
detailCol = dataSet11.Tables["Products"].Columns["ProductID"];

dr = new
DataRelation("OrderDetailsToProducts",masterCol,detailCol,false);

dataSet11.Relations.Add(dr);

// Let the ComboBox display the OrderID
comboBox1.DataSource = dataSet11;
comboBox1.DisplayMember = "Orders.OrderID";

// Let the DataGrid display the OrderDetails
dataGrid1.DataSource = dataSet11;
dataGrid1.DataMember = "Orders.OrdersToOrderDetails";

When the DataGrid has "AllowNavigation" set to true, everything works
fine.
I get 3 Order details records, and by pressing "+" I can go to the
Products
information.
!!!! However, I don't want to see the in-between table Order Details
!!!!!

So I tried setting
dataGrid1.DataMember =
"Orders.OrdersToOrderDetails.OrderDetailsToProducts",
in an attempt to leave out the in-between table, and directly show the
Products.
But now I don't see 3 Products anymore, I only see the first one.

What is the best way to hide the Order Details?
Click to expand...

Order 1<--->oo OrderDetail oo<-->1 Product

So, Product isn't a child table of OrderDetails it's a lookup table. The
second relation you add is really wrong and that's why you must disable
constraints. Instead Product should be the parent, eg. there can be
multiple OrderDetails for each Product.

//add relation Product - Order Details
masterCol = dataSet11.Tables["Products"].Columns["ProductID"];
detailCol = dataSet11.Tables["Order Details"].Columns["ProductID"];
dr = new DataRelation("ProductsToOrderDetails", masterCol, detailCol);

There is no direct support for showing n:m relations without using the
inbetween table, so what you can do is show the inbetween table
(OrderDetails) and add expression columns to it that get the fields from
the
parent Product; eg:

dataSet11.Tables["Order Details"].Columns.Add(
"ProductName", typeof(String),
"Parent(ProductsToOrderDetails).ProductName");
.....

And you could then hide the Order Details columns you don't want to see,
eg:
dataSet11.Tables["Order Details"].Columns["ProductID"].ColumnMapping =
MappingType.Hidden;
....

I know there is an article about this somewhere but i can't remember
where.


HTH
Greetings
Thanks in advance,

Alexander van Buitenen.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

data binding to show 3 layer M/S tables in datagrid 2
Update Multiple Tables in Database using DataRelations 8
"These columns don't currently have unique values" 0
Getting AutoNum field from updated db table via data adapter 3
Updating hierarchical data 1
no matching record on the "one" side in one-to-many relationship 1
Filter Master Table based on Child Criteria 3
Cannot change DataGrid.RowHeaderWidth to less than 30 when there is a DataRelation on the datasource 2

Top