“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:
- 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.
- [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.
- 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.