Hey all,
For a long time, I have loved a certain aspect of VFP that I have been unable to duplicate in ADO. At first, I think ADO just didn't have what I needed. Now, with ADO.NET, I am thinking (and hoping) that the operation is supported -- I just don't know how to do what I want.
I am talking about joining data from hetereogenous data sources. We're talking joining data that comes partly from SQL Server and partly from native DBF tables.
The firm I work for is in the unenviable (but not altogether uncommon) position of having two transitions looming: 1) converting all data from DBF to SQL Server, and 2) migrating the code base to .NET. At this point, we have some data in SQL Server (stuff that was easy to segment with clear join points back to the DBF data) and some in DBF tables. We regularly access DBF data (sometimes with SELECT statements, sometimes with SEEKs) and then assemble related SQL Server data using SQL Passthrough (SPT).
I have always loved VFP's ability to do multiple queries into local cursors and then further refine or combine those local cursors. In some cases, I have combined data from DBFs, text files, Excel, and SQL Server all in one module.
Basically, I want to know how to do that in ADO.NET.
Can I, in ADO.NET, generate one dataset from DBFs (I know how to do that) and one from SQL Server (I know how to do that, too) and then combine them into one (this is what I don't know)? I know I can combine them with an iterative approach (I consider that the brain-dead, brute-force method), but that seems silly. I have also seen some articles mentioning ADO "relations" to combine smaller queries, but could not find a lot of detail. I guess the bottom line is that I would like someone to tell me how one would do the following in ADO.NET:
USE person.dbf IN 0 SHARED
SELECT ID, name FROM person WHERE <some condition> INTO CURSOR _temp1
lnConn = SQLSTRINGCONNECT(<connection string>)
lcSQL = "SELECT personID, orderdesc, orderID FROM orders WHERE <condition>"
lnResult = SQLEXEC(lnConn, lcSQL, "_temp2")
IF lnResult > 0 AND USED("_temp2")
SELECT _temp1.*, _temp2.orderdesc, _temp2.orderID ;
FROM _temp1 INNER JOIN _temp2 ON _temp1.ID = _temp2.personID ;
INTO CURSOR _finaldata
ELSE
ENDIF
This is child's play in VFP. Compact, intuitive, and fast. Like I said, I could even add in data from a text file, a different SQL Server database, or other DBF tables. It's a cake walk.
How do I do this in the .NET world. Can I join two ADO datasets using SQL or SQL-like syntax (i.e. set-based operations as opposed to looping through data)? What kind of performance can I expect in comparison to VFP's local-cursor methodology?
Thanks for reading, and thanks to one and all for any help you can offer!
Thanks,
JoeK