How to create a join across two foxpro databases using the MS Ole DB provider?

Question

Question:

I am working with existing commercial MS Visual Foxpro application, and need to extract data from the database directly using a c# application. Unfortunately, not all of the tables are stored in the same database, some of the records are stored in a database broken down by year. How do I create a query against these two databases using a single join?

I am using Microsoft's OLE DB Provider for Visual FoxPro 9.0 (SP2)

More Detail:

Essentially, the customer information is stored in one database, and the customer purchase history is stored a database broken down by year. So I am trying to create a simple query to print out customers and their most recent purchase from this year.

In graphical form, the file structure of the db looks like this:

Data\
 +-2009\
 |  +-MyDB.dbc
 |  +-Sales.dbf
 +-2010\
 |  +-MyDB.dbc
 |  +-Sales.dbf
 +-MyDB.dbc
 +-Customers.dbf

Currently I can connect to each DB individually, and query them:

// This works to connect to the customer DB
string connectionPath1 = @"Provider=vfpoledb.1;Data Source=E:\Data\MyDB.dbc";
OleDbConnection conn1 = new OleDbConnection(connectionPath1);
OleDbCommand command1 = new OleDbCommand(@"SELECT * FROM Customers", conn1);
OleDbDataReader reader1 = command1.ExecuteReader();

// This works to connect to the annual sales record DB
string connectionPath2 = @"Provider=vfpoledb.1;Data Source=E:\Data\2010\MyDB.dbc";
OleDbConnection conn2 = new OleDbConnection(connectionPath2);
OleDbCommand command2 = new OleDbCommand(@"SELECT * FROM Sales", conn2);
OleDbDataReader reader2 = command2.ExecuteReader();

What I can't do is execute my join statement:

//How do I do this?
OleDbConnection connMagic = new OleDbConnection(connectionPath1, connectionPath2); //non-valid code
OleDbCommand commandIWant = new OleDbCommand(@"SELECT Customers.Name, Sales.Item, Sales.Date FROM Customers LEFT JOIN Sales ON (Customers.ID=Sales.CustomerID)", connMagic);
OleDbDataReader reader3 = commandIWant.ExecuteReader();

Letterpress

Answer 1

If the directory structure is as you indicate... where the different years are all UNDER the parent common, you can query to all of them directly just by including the relative path...

select 
      a1.Whatever,
      b1.Sales1
   from
      Customers a1,
      2009\Sales b1
   where 
      a1.CustomerID = b1.CustomerID
union all
select 
      a1.Whatever,
      b1.Sales1
   from
      Customers a1,
      2010\Sales b1
   where 
      a1.CustomerID = b1.CustomerID
union ... 

you dont even have to qualify the actual DBC either, the OleDB should auto-detect it, but having it included wouldn't hurt as your original samples indicate...

Profusion

Answer 2

Add both FoxPro tables to a DataSet either with seperate OleDbConnection objects or by reusing one OleDbConnection object. Then add a DataRelation between the 2 DataTables.

You could also try adding the DBC name to your SQL SELECT:

"SELECT c.Name, s.Item, s.Date FROM MyDB!Customers c LEFT JOIN 2009\MyDB!Sales s ON (c.ID=s.CustomerID)"

Bazil