Hello,

Am I correct in thinking that it is currently not possible to perform an SQL 
JOIN across multiple DataTables in a DataSet in .NET? I have a typed DataSet 
with three relational tables which need to be placed into one flat table, 
e.g.:

   Table A: PK, Table A Col 1, Table A Col 2...
   Table B: PK, FK 1 (Table A PK), FK 2 (Table C PK)
   Table C: PK, Table C Col 1, Table C Col 2...

   =>   PK, Table A Col 1, Table A Col 2, Table C Col 1, Table C Col 2

Currently the only way to resolve this involves embedded for-loops going 
down each of the typed relations to get the required data. Obviously 
performing one SELECT statement would be far more efficient, but is this 
possible here?

I only have access to this DataSet and so am unable to perform the join in 
the database before the DataSet is Filled.

Thanks,

Marc

Right. There is (currently) no SQL engine in ADO.NET. That's what's coming 
in Orcas.

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Marc Woolfson"  wrote in message 
news:67933F09-2D22-4BA2-A6D5-1F2ECC4441CC@microsoft.com...

> Hello,
>
> Am I correct in thinking that it is currently not possible to perform an 
> SQL
> JOIN across multiple DataTables in a DataSet in .NET? I have a typed 
> DataSet
> with three relational tables which need to be placed into one flat table,
> e.g.:
>
>   Table A: PK, Table A Col 1, Table A Col 2...
>   Table B: PK, FK 1 (Table A PK), FK 2 (Table C PK)
>   Table C: PK, Table C Col 1, Table C Col 2...
>
>   =>   PK, Table A Col 1, Table A Col 2, Table C Col 1, Table C Col 2
>
> Currently the only way to resolve this involves embedded for-loops going
> down each of the typed relations to get the required data. Obviously
> performing one SELECT statement would be far more efficient, but is this
> possible here?
>
> I only have access to this DataSet and so am unable to perform the join in
> the database before the DataSet is Filled.
>
> Thanks,
>
> Marc 

Excellent, thanks.

"William (Bill) Vaughn" wrote:


> Right. There is (currently) no SQL engine in ADO.NET. That's what's coming 
> in Orcas.

http://www.queryadataset.com

Now supports joining across mulitple datasets and information_schema for 
querying dataset metadata such as tables, columns and indexes.

Hope this helps
Ad.

"Marc Woolfson"  wrote in message 
news:67933F09-2D22-4BA2-A6D5-1F2ECC4441CC@microsoft.com...

> Hello,
>
> Am I correct in thinking that it is currently not possible to perform an 
> SQL
> JOIN across multiple DataTables in a DataSet in .NET? I have a typed 
> DataSet
> with three relational tables which need to be placed into one flat table,
> e.g.:
>
>   Table A: PK, Table A Col 1, Table A Col 2...
>   Table B: PK, FK 1 (Table A PK), FK 2 (Table C PK)
>   Table C: PK, Table C Col 1, Table C Col 2...
>
>   =>   PK, Table A Col 1, Table A Col 2, Table C Col 1, Table C Col 2
>
> Currently the only way to resolve this involves embedded for-loops going
> down each of the typed relations to get the required data. Obviously
> performing one SELECT statement would be far more efficient, but is this
> possible here?
>
> I only have access to this DataSet and so am unable to perform the join in
> the database before the DataSet is Filled.
>
> Thanks,
>
> Marc 

Bill,

Orcas does not provide ad-hoc SQL statements to be executed against the 
data.  It is also limited to the data in the local process unless you 
implement a data provider.  Finally, it currently does not support indexing 
but I've heard this will be possible by RTM.

The SQL engine which I provide at http://www.queryadataset.com does not have 
these limitation when querying datasets or XML documents and it available 
today.  If you are interested in evaluating it, please download the trial 
and try it.  It provides a robust, high-performance implemenation which is 
in use by over 50 companies today.  My ultimate intention is to evolve this 
into a soft, real-time, peer-to-peer networked database.

Thanks
Adrian Moore

Microsoft MVP - Windows Networking



"William (Bill) Vaughn"  wrote in message 
news:eK0tVZKxHHA.1184@TK2MSFTNGP04.phx.gbl...

> Right. There is (currently) no SQL engine in ADO.NET. That's what's coming 
> in Orcas.
>
> -- 
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no 
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Marc Woolfson"  wrote in message 
> news:67933F09-2D22-4BA2-A6D5-1F2ECC4441CC@microsoft.com...
>> Hello,
>>
>> Am I correct in thinking that it is currently not possible to perform an 
>> SQL
>> JOIN across multiple DataTables in a DataSet in .NET? I have a typed 
>> DataSet
>> with three relational tables which need to be placed into one flat table,
>> e.g.:
>>
>>   Table A: PK, Table A Col 1, Table A Col 2...
>>   Table B: PK, FK 1 (Table A PK), FK 2 (Table C PK)
>>   Table C: PK, Table C Col 1, Table C Col 2...
>>
>>   =>   PK, Table A Col 1, Table A Col 2, Table C Col 1, Table C Col 2
>>
>> Currently the only way to resolve this involves embedded for-loops going
>> down each of the typed relations to get the required data. Obviously
>> performing one SELECT statement would be far more efficient, but is this
>> possible here?
>>
>> I only have access to this DataSet and so am unable to perform the join 
>> in
>> the database before the DataSet is Filled.
>>
>> Thanks,
>>
>> Marc
>
>