(转)QueryADataset

"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. Microsoft 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.