"How do I join two tables from dataset and display the result?"

This is a question that often appears in ado.net newsgroup. Actually very often. And there isn't a good solution to this problem out there. (There is Link over DataSet comming but it is years away from general adoption). Out of my head these are the possible solutions:

  1. Add columns and use DataColumn.Expression property.
    This is a limited solution and works only in simple cases, there were problems when updating to database such tables in .net 1.1 (not sure about .net 2.0 since I am avoiding this approach when doing updates) and it is intrusive.
  2. [MS] approach: HOW TO: Implement a DataSet JOIN helper class in Visual C# .NET(http://support.microsoft.com/default.aspx/kb/32608)
    Here a new DataTable is created and populated based on input parameters. This is somewhat better but still, it is very unflexible.
  3. Create a new DataTable (or a list of objects) manually and populate it using self made code.
    This is a flexible solution but requires some coding and looping and as a sight effect it might blur the code clarity.

But hey, there is another, much better solution. It is called QueryADataset. I saw it advertised from time to time but only recently decided to try it out (I wonder why I didn't try it before). Why is it good? Because it combines simplicity, flexibility and a SQL language (BTW, if you don't know what SQL is then forget doing database applications until you learn the basics).

Let's look at an example. Let's say I have a dataset instance named northwind1 containing Categories and Products tables from the database you all love - Northwind. Let's say I want to show CategoryName and count of related products in a table. Here is the code required to get a DataView with required fields:

DataView view = DsCommand.Execute(@" SELECT CategoryName, Count(*) As ProductName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID GROUP BY CategoryName ORDER BY CategoryName ", northwind1);

And here is the result:

Can it be simplier? And this isn't exactly a trivial select - it contains a join, name aliasing, grouping and sorting.

But hey, you are not limited with such simple statements. You have a very substantial subset of SQL power in your hands. Let's say you want to filter products, too. Here is revisited query:

DataView view = DsCommand.Execute(@" SELECT CategoryName, Count(*) As ProductName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE Products.UnitPrice > 20 GROUP BY CategoryName HAVING Count(*) > 4 ORDER BY CategoryName ", northwind1);

Here I additionaly filter by Products.UnitPrice and select only those categories having more than 4 such products. I hope you get the idea of the power behind QueryADataset.

And you are not limited to selects. Oh no, you can do INSERT/UPDATE/DELETE commands. But the fun doesn't stop here - even CREATE TABLE, DROP TABLE, CREATE INDEX and DROP INDEX commands are supported to certain extent. Read more here.

To sum it up, as it says on web site - it is a great addition to ADO.NET that lets you do SQL statements on the top of the DataSet - nothing gets executed on the database. I think I'll use this stuff from now on in all of my projects where I deal with datasets.

I am missing a couple of features though (suggestions):

  • I would like to pass an empty (strong typed) DataTable to Excute method, so my table is filled instead of new one created. You can call me strong typing maniac, but I really do believe in strong typing.
  • I would like to fill a list of classes instead a datatable sometimes. Because it is a lot easier to add complex additional properties to a class.

I guess I'll have a better knowledge of the product once I start to use it for real. BTW, the thing works on both .net 1.1 and 2.0.

Comments (1) -

  • Giuseppe

    22.2.2007 12:26:07 | Reply

    Hi Miha, thanks for the great hint!

Pingbacks and trackbacks (1)+