The OLE DB documentation documents a hierarchial rowset feature, with SQL examples, but doesn't indicate what DB providers support it. Do JET and SQL Server support this feature? And do they use the same sort of syntax as in the documentation, or their own extended syntax?
A:
It's not a direct answer to your question, so if you must use hierarchical rowsets just ignore it. I remember reading about this feature some 9 years ago but have not seen it being used since. I think it's proven to be wrong way to go. On the other hand, I did use common table expressions (and recursive ones in particular) a lot and found them of great value (http://msdn.microsoft.com/en-us/library/ms186243.aspx). I think what was supposed to be done by hierarchical rowsets is object-relational mapping and those rowsets do not make good ORM anyway (NHibernate is much better).
vaso
2010-08-23 00:45:53
Minor clarification: common table expressions are specific to SQL Server, while the question is for both SQL Server and Jet/ACE.
David-W-Fenton
2010-08-23 20:00:08
David, you are right.But considering overall technology choices, I personally would still avoid using hierarchical rowsets, I guess NHibernate will happily work with Access. It's a bit tricky to start working with, but it pays off a couple of weeks later. I have seen many times how the use of plain OLEDB, ADO.NET etc instead of some sort of mapper (initially done to keep things simple) leads to a mess in code. I used lean and mean BLToolkit for some time, it had a nice MapResultSets function which implemented this sort of mapping in a declarative and object-oriented way.
vaso
2010-08-24 00:22:15
+1
A:
Yes, it is supported. I've used it quite a bit in the past. This functionality was available using the Data Shaping Service that shipped with MDAC (in a file named Msadds.dll). The OLE DB provider is named MSDataShape and you would use the SHAPE command to produce hierarchical results. The connection string syntax resembled the following:
Jet:
Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PathToMyDatabase.mdb;
SQL Server:
Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=ServerNameOrAddress;Initial Catalog=DatabaseName;User ID=;Password=
A few things worth noting:
The SHAPE syntax can get very unwieldy (and awkward) as your queries become more complex, so keep this in mind.
If you are using .NET, the same functionality can be achieved with
DataSetandDataRelationclasses.More importantly, the documentation clearly states that:
This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.
Garett
2010-08-23 08:16:55
I delved into the guts of Access subdatasheets back in the day (A2000), and quickly got deep into the weeds with the MSDataShape provider. Given that Access still supports subdatasheets in current versions, it seems there's still some support for it in UI components if not in code. Also keep in mind that Jet was once part of the MDAC, but was removed some time after Jet became a component of Windows (i.e., Win2000). Given that A2000 onward seem to have some dependence on MSDataShape, I wonder if the support is there if you have Access installed.
David-W-Fenton
2010-08-23 20:02:57