如何创建多个使用MS OLE DB提供2的FoxPro数据库的加入？ [英] How to create a join across two foxpro databases using the MS Ole DB provider?

问题描述

问：

我与现有的商业MS Visual FoxPro应用程序的工作，并需要直接用C＃应用程序从数据库中提取数据。不幸的是，不是所有的表都存储在相同的数据库中，有些的记录存储在逐年细分的数据库。如何创建使用一个连接针对这两个数据库查询？

我使用微软的 OLE DB提供程序的Visual FoxPro 9.0（SP2）

更多详情：

实质上，客户信息存储在一个数据库中，而客户购买历史被存储逐年细分的数据库。所以，我想创建一个简单的查询，打印出来的客户和他们最近从今年购买。

在图形的形式，该数据库的文件结构看起来像这样

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

目前我可以连接到每一个单独的DB，并且对它们进行查询：

  //这个工程来连接到客户数据库
串connectionPath1 = @供应商= vfpoledb.1;数据源= E：\Data\MyDB.dbc 
 OleDbConnection的CONN1 =新的OleDbConnection（connectionPath1）; 
 OleDbCommand的命令1 =新的OleDbCommand（@SELECT * FROM客户，CONN1）; 
 OleDbDataReader reader1 = command1.ExecuteReader（）; 
 
 //这个工程连接到年度销售记录DB 
串connectionPath2 = @供应商= vfpoledb.1;数据源= E：\Data\2010\MyDB。 DBC; 
 OleDbConnection的CONN2 =新的OleDbConnection（connectionPath2）; 
 OleDbCommand的命令2 =新的OleDbCommand（@SELECT * FROM销售，CONN2）; 
 OleDbDataReader reader2 = command2.ExecuteReader（）; 
  

我不能做的是执行我的连接语句：

  //我该怎么办呢？ 
 OleDbConnection的connMagic =新的OleDbConnection（connectionPath1，connectionPath2）; //非有效代码
 OleDbCommand的commandIWant =新的OleDbCommand（@SELECT Customers.Name，Sales.Item，Sales.Date客户LEFT JOIN销量（Customers.ID = Sales.CustomerID），connMagic）; 
 OleDbDataReader reader3 = commandIWant.ExecuteReader（）; 
  

解决方案

如果目录结构是您指示...其中，不同年份都是父母共同作用下，可以直接，只需要包含相对路径...

 <$ C $查询到所有的人从
客户A1，
 2009\Sales B1 
 C>选择
 a1.Whatever，
 b1.Sales1 
，其中
 A1。客户id = b1.CustomerID 
 UNION ALL 
选择
 a1.Whatever，
 b1.Sales1 $ b $从
客户A1 b，
 2010\销售B1 
，其中
 a1.CustomerID = b1.CustomerID 
工会... 
  

您甚至不具备资格的实际DBC或者，在OLEDB应自动检测到它，而拥有它包括不会伤害你原来样本显示...

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();

解决方案

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...

这篇关于如何创建多个使用MS OLE DB提供2的FoxPro数据库的加入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！