How to merge two DataView objects

[Guest]

Hi,

I have two DataView objects sharing the same DataTable. I want to merge these two. Ideally the DataView class would have a Merge(DataView dv) method, but it doesn't.

Does anyone know how to do this?

Here is what Im trying to accomplish. Say I am using the Northwind database and I want to display 2 DataGrids, one representing the Customer table and one for OrderDetails table. Everytime I select a customer in the Customers table I would like to show all the order details in the Orders table for all the orders for that customer. So say the DataRowView representing the customer is customerDataRowView, then I will get all the children in the Orders table for that customer by

ordersDataView = customerDataRowView.CreateChildView(CustomerToOrdersDataRelation);

Then I want to say:

orderDetailsDataView = new DataView();
foreach(DataRowView drv in ordersDataView.Rows)
{
DataView dv = drv.CreateChildView(OrdersToOrderDetailsDataRelation);
orderDetailsDataView.Merge(dv);
}

Thanks in advance for all your help.

Nima

 

[Jay B. Harlow [MVP - Outlook]]

Nima,
There is no Merge available for DataView, as Merge doesn't make sense per
se, what are you to do with the differing columns? Do you perhaps mean Join?

The only ways I can think of to resolve your "problem" is to denormalize the
data, use 4 grids, or possibly a JoinView...

I would favor the 3 grids...

You could denormalize the data into a single table, The caveat being updated
orders & order details...

The following sample using 3 grids is taken for a Northwind Sample:

customerAdapter.Fill(customerDataSet, "Customers")
orderAdapter.Fill(customerDataSet, "Orders")
orderDetailsAdapter.Fill(customerDataSet, "OrderDetails")

' Create relationships.
customerDataSet.Relations.Add("CustomerOrders", _
customerDataSet.Tables("Customers").Columns("CustomerID"), _
customerDataSet.Tables("Orders").Columns("CustomerID"))

customerDataSet.Relations.Add("OrderDetails", _
customerDataSet.Tables("Orders").Columns("OrderID"), _
customerDataSet.Tables("OrderDetails").Columns("OrderID"))

' Bind to a DataGrid.
Me.DataGrid1.SetDataBinding(customerDataSet, "Customers")
Me.DataGrid2.SetDataBinding(customerDataSet,
"Customers.CustomerOrders")
Me.DataGrid3.SetDataBinding(customerDataSet,
"Customers.CustomerOrders.OrderDetails")


There is a JoinView sample custom DaveView class for VB.NET that you might
be able to modify to do what you want...

See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325682

Basically you create a new JoinView object, set the properties for your
join, then use this JoinView object as the DataSource on your DataGrid.

Thinking about it, instead of three grids you may be able to use a grid, a
combo box, and a second grid. Where the first grid has the customers, the
combo box has the orders for a customer, and the second grid has the order
details for an order.

Hope this helps
Jay

Hi,

I have two DataView objects sharing the same DataTable. I want to merge

these two. Ideally the DataView class would have a Merge(DataView dv)
method, but it doesn't.

Does anyone know how to do this?

Here is what Im trying to accomplish. Say I am using the Northwind

database and I want to display 2 DataGrids, one representing the Customer
table and one for OrderDetails table. Everytime I select a customer in the
Customers table I would like to show all the order details in the Orders
table for all the orders for that customer. So say the DataRowView
representing the customer is customerDataRowView, then I will get all the
children in the Orders table for that customer by

 

[Guest]

Thanks Jay for your reply,

A well written Merge method would actually make sense. It would first check to make sure that the Table property of both DataViews is referring to the same object, and if that is not the case it would throw an exception.

With 3 grids the problem is solved as you noted. However, I simplified my real problem to make it more easily comprehendible. I have 40 tables and am writing a routine that takes any 2 of these tables and generate two datagrids, where you will choose one record in one and it will show all the related records in the other. Of course this only makes sense if there is a navigation path using DataRelation objects between these two tables. This might take several levels of navigation, and I don't want to confuse my user with extra DataGrids or ComboBoxes.

I tried to extend the DataView class and add a Merge method to it. But DataRowView class does not have a public constructor and its Row property is read only. In other words I cannot create an instance of a DataRowView and assign it an existing DataRow!

Thanks for the article you provided. Studying it and implementing it for my case seems to be my only hope. I will let you know how it goes. In the article they are writing both the JoinView class and the JoinViewRow class from scratch, therefore they have to implement all necessary functionality. I don't understand why Microsoft engineers did not design the DataRowView class in a way that it could be extended to save the poor developer from rewriting their classes.

I will let you know once I have it working.

Thanks,

Nima

 

[Jay B. Harlow [MVP - Outlook]]

Nima,

A well written Merge method would actually make sense.
It would first check to make sure that the Table property
of both DataViews is referring to the same object,
and if that is not the case it would throw an exception.

To use database terms you mean Union then, where you are restricting the
Union to the same table.

Rather the try to create a View object that will show the related objects, I
would seriously consider "denormalizing" the data so as to create the
relationship.

In the case of Customers, Orders, & OrderDetails. I would consider adding
the customer ID to OrderDetails, create a relationship between Customers &
OrderDetails, while keeping all three tables.

Note you can add the CustomerID to OrderDetails in the DataSet, with having
it in the Database itself! You can either fixup the CustomerID after you
fill the DataSet, or have the query that returns the data return the
CustomerID.

A variation of "denormalizing" would be to create a routine that dynamically
looked at the DataRelations and created new ones for parent to grand
children based on relations between grand parent & parent that were found.
However this would be slightly more advanced, but could be generalized to
any dataset.

If I went the UnionView route (what you are asking for) I would create a new
object that implemented the same interfaces as DataView, specifically
IBindingList, ITypedList, & ISupportInitializatize the other interfaces are
inherited by IBindingList. It would hold a collection (ArrayList) of
DataView objects, and returned to correct row out of the correct DataView...
(Note UnionView would not inherit from DataView!) I would consider which
properties of DataView that UnionView would need and delegate to the each
DataView object as needed... Actually UnionView sounds like a fun puzzle, I
may see what I can come up with...

Hope this helps
Jay

Thanks Jay for your reply,

A well written Merge method would actually make sense. It would first

check to make sure that the Table property of both DataViews is referring to
the same object, and if that is not the case it would throw an exception.

With 3 grids the problem is solved as you noted. However, I simplified my

real problem to make it more easily comprehendible. I have 40 tables and am
writing a routine that takes any 2 of these tables and generate two
datagrids, where you will choose one record in one and it will show all the
related records in the other. Of course this only makes sense if there is a
navigation path using DataRelation objects between these two tables. This
might take several levels of navigation, and I don't want to confuse my user
with extra DataGrids or ComboBoxes.

I tried to extend the DataView class and add a Merge method to it. But

DataRowView class does not have a public constructor and its Row property is
read only. In other words I cannot create an instance of a DataRowView and
assign it an existing DataRow!

Thanks for the article you provided. Studying it and implementing it for

my case seems to be my only hope. I will let you know how it goes. In the
article they are writing both the JoinView class and the JoinViewRow class
from scratch, therefore they have to implement all necessary functionality.
I don't understand why Microsoft engineers did not design the DataRowView
class in a way that it could be extended to save the poor developer from
rewriting their classes.