>The Merge method looks kind of cool, but it appears to just be a UNION where only records by distinct key are kept.

Nope, it all depends on the parameters used in the Merge and whether or not you have PK's defined in your DataSets, etc. Take a look at it, play with it, I think it will probably do at least some of what you need. You won't have SQL-like commands available ... that's not coming until the LINQ project is ready, whenever that will be. But you can at least get your data into one DataSet the way you want to.

So, if your VFP data has a personID, and your SQL data has a personID ... all you need is:

First, a PK defined in each DataTable (there are two different ways for defining PKs ... I'll show both ways):

MyVFPDataSet.Tables["MyPersonTable"].PrimaryKey = new DataColumn[1] {MyVFPDataSet.Tables["MyPersonTable"].Columns["personID"]};
MySqlDataSet.Tables["MyPersonTable"].Constraints.Add("PersonPK", MySqlDataSet.Tables["MyPersonTable"].Columns["personID"], true);

Then, if you use the Merge method without any extra parms, it will add any extra columns that might be different. You can either merge from one of the DataSets, or create a whole new DataSet to hold the merged data:

MyVFPDataSet.Merge(MySqlDataSet);

Like I said, play with it ... I think it will be a step in the right direction anyway.

~~Bonnie







>Bonnie,
>
>The Merge method looks kind of cool, but it appears to just be a UNION where only records by distinct key are kept.
>
>Sadly, that does not accomplish what my example is asking about. Let me summarize what I want (need) to be able to do with ADO.NET in order to make is feasible for my firm right now:
>
>- Grab data from varied data sources (without having to move or replicate the data onto SQL Server first).
>- Combine that data by SQL or SQL-like joins. Not just merge all the data...if you look at my example, I am doing an inner join based on personID. I need that type of functionality -- that's the whole point of these joins -- to join linked data being stored in two (or more) separate ways. SQL is sooooooo powerful (JOINs, GROUP BY, ORDER BY, WHERE claues, powerful field-list functions, etc.). I need that power, and cannot always do what I need to do on the first data grab (as in the case with varied data sources).
>
>Surely I am not the only person trying to do this? Or the only person with concerns about it? At every job I have had for the past 10 years (3 different companies) there has always been the need to combine data from very disparate sources into one common cursor. And VFP has always been able to do it. Over the years I have accessed MSAccess, Paradox tables, DBFs, AS/400 DB2, SQL Server, MySQL, Excel, and text files (sometimes 3 or 4 of those at once!) via native VFP code, ODBC, OLEDB, and SQL passthrough. And it always ends up as one beautiful cursor that I can send to a report. Just last week I combined Internet monitoring data off a prorietary SQL Server database with our DBF userlist table so that I could sift out activities that were allowed because the user was accessing the Internet before work, during lunch, or after work. I was able to do it in one day (it only took that long because I made a flexible interface and pretty report for it) even though I had never seen the SQL
> Server data before.
>
>How would I do that in ADO.NET? How do I get everything into a similar cursor format where the cursors can then be joined, grouped, and ordered together using set-based functions?
>
>Thanks,
>JoeK