How to create a connection through two foxpro databases using the MS Ole DB provider?

Question

Question:

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

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

Additional Information:

In fact, customer information is stored in one database, and the customer’s purchase history stores a database broken down by year. Therefore, I am trying to create a simple request for listing customers and their last purchase from this year.

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

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

Currently, I can connect to each database separately and request 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 cannot 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(); 

Answer 1

If the directory structure is specified as you specify ... where different years are UNDER parent common, you can query them all directly 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 don’t even need to qualify the actual DBC, OleDB should automatically detect it, but turning it on will not hurt, as your original samples indicate ...

Answer 2

Add both FoxPro tables to the DataSet, either with separate OleDbConnection objects, or by reusing a single OleDbConnection object. Then add a DataRelation between the two data tables.

You can 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)"