WWW Yariv Hammer's Code Site

Saturday, January 07, 2006

Using JoinView to Show Queries From Multiple Tables

In MS-Access there is the possibility to show queries from two tables. Take for example the case of Order and Product tables in the Northwind database. Say we want to show in a grid the amount of items purchased (per Order) with the appropriate product name and price (from the Product table). We are able to do so if there is a relation between the tables, and by using SQL Join clause.
This effect is not available in ADO.NET. Once the data is filled into the DataSet, there is no built-in way to produce this kind of query. Sure, we can show a Master-Detail view of the data. But in order to show data from several tables in one DataGrid at once we will need to work hard. DataGrid can only show data from one DataTable at a time, and DataView can only reflect data of one table.
This is a very big problem. One way of solving it is getting the data into a DataSet from the database itself using a Select Command with Join clauses. The DataSet should have a DataTable with the same schema as the resulting Join query. While this is possible in some cases, other cases require some kind of mechanism to have a joined view without accessing the database.
For example, say I have a server and clients. The server does all the access to the database. The client gets the DataSets from the server. If I want to show the data in a DataGrid in the client, I cannot afford to approach the server again. Not only this is expensive, but it can also cause concurrency problems. I have all the data I need in memory, so why do I need to access the database again?

Microsoft comes to our aid. Obviously someone understood the problem, so he or she developed a class called JoinView which acts like a DataView class, but handles data with multiple DataTables with DataRelations set between them.
The code for the class (VB.NET), with examples of usage can be found here:
C# source code can be found here:

This class is the ONLY convenient way I could find to show the data from few related tables in one table without getting the data in this structure in the first place.

Unfortunately, the JoinView class is not as convenient as the DataView class. Here are few of the drawbacks I could find when using this class:
1. You cannot edit the related columns - only the main table columns. The rest are read-only and I couldn't get passed that.
2. You cannot dynamically filter the rows!!! you will need to create a new JoinView whenever the filter is changed. This is a huge drawback, as instanciating a JoinView is probably expensive.
3. Apparently the AllowNew property works well if you you set it BEFORE you assign the JoinView as a DataSource to the grid. If you set the AllowNew property to False after you assign the DataSource, the result is unexpected (I got the Add New Row displayed, and when the user pressed on this line, an exception was thrown, saying: "AddNew is not allowed").
4. It works with column styles, but i needed to make some little changes.

Basically it was a good experience working with JoinView, although it is not perfect. As I said before, it is currently the only solution i know of to work with Join without getting the data from the database in that structure.

Some Thoughts
Why has Microsoft not provided us a proper way of doing a Join on the DataSet objects in-the-box? We have DataTables and DataRelations in .NET so the JoinView class seems to be a required piece. More importantly - someone realized the need, otherwise the JoinView class would not have been published - so why it is not provided in .NET 2005?

Why can't we use Command objects on DataSets? By allowing us to use SQL commands on DataSets we will have more strength in using DataSets.


At 5:42 AM, September 11, 2012, Anonymous jrivam said...

And still the same in 2010...and 2012? is there yet a better way to do this? thank you.


Post a Comment

<< Home

Feel free to use everything here. Add links to my site if you wish.

Do not copy anything to other sites without adding link to here.

All the contents of the site belong to Yariv Hammer.