跨越多个数据库的交叉引用

Question

我有两个数据库，一个是MS Access文件，另一个是SQL Server数据库。我需要创建一个SELECT命令，根据Access数据库中的数据过滤SQL Server数据库中的数据。如何使用ADO.NET实现最佳方法？

我可以从每个数据库中提取所需数据到两个新表中。将它们放入单个数据集中。然后在数据集上执行另一个SELECT命令以组合数据吗？

附加信息： Access数据库不是永久的。用户在运行时设置要使用的Access文件。

这里有一些背景信息来解释为什么有两个数据库。我们公司使用CAD程序设计建筑物。该程序将CAD模型中使用的材料存储在Access数据库中。每个模型都有一个文件。我正在编写一个程序，将为每个模型生成成本信息。这基于存储在SQL Server数据库中的当前材料价格。

我的解决方案： 最终，我只是将Access数据库中的数据导入到SQL Server数据库中的临时表中。执行所有必要的处理，然后删除临时表。这不是一个完美的解决方案，但它起作用了。

- Dave Turvey

Answer 1

您不必要拉取两个数据集，如果没有必要的话。另外，您在实现Tomalak的解决方案时可能会遇到麻烦，因为文件位置可能会发生变化，甚至可能无法被服务器本身轻松获取。

我猜测您的用户使用Access数据库设置了他们感兴趣的人员/产品或其他内容，这就是为什么您需要跨两个数据库进行选择的原因。如果是这种情况，Access表格可能比SQL Server表格小。您最好拉取Access数据，然后使用它来生成一个过滤查询到SQL Server，以便您可以尽量减少通过网络发送的数据量。

所以，最重要的事情是：

1. 在服务器上过滤数据，以便您可以最小化网络流量，并且因为数据库在过滤方面比ADO.NET更快
2. 如果您必须选择一个数据集拉入应用程序中，请拉入较小的数据集，然后使用它来过滤另一个表格。

- Tom H.

---

同意..由于每个客户端上都有一个Access数据库，设置那么多“临时”链接服务器似乎不合理。 - neonski

每个Access数据库都有不同的数据吗？如果是这样，那么谁是设计这种设置的白痴？ - David-W-Fenton

实际上这并不罕见。有一个公司数据库记录了所有销售，但东海岸区域经理拥有一个只包含东海岸客户的访问数据库，用于报告。或者统计团队已经缩小了他们关注的范围等。 - Tom H

顺便说一句，上面只是可能的例子。我不知道原帖的具体情况，只是做了一个有根据的猜测。 - Tom H

Answer 2

假设 Sql Server 可以访问 Access 数据库，您可以在它们之间构建一个 OPENROWSET 查询。

 SELECT a.* 
 FROM SqlTable 
 JOIN OPENROWSET(
     'Microsoft.Jet.OLEDB.4.0', 
     'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
     Orders
 ) as b ON
     a.Id = b.Id

您只需在运行时更改访问数据库的路径即可访问不同的MDB文件。

- Mark Brackett

Answer 3

首先，您需要在服务器上执行一些操作 - 将访问数据库作为“链接服务器”引用。

然后，您将能够从SQL服务器内查询它，以任何方式提取或填充数据。此网页提供了如何执行此操作的详细概述。

http://blogs.meetandplay.com/WTilton/archive/2005/04/22/318.aspx

- Tomalak

Answer 4

你尝试过通过ODBC从Access前端链接到SQL Server，并将你的SQL写成两个表都是本地的形式，然后进行基准测试吗？然后你可以在服务器上进行跟踪，以查看Jet发送到服务器的确切内容。你可能会惊讶于Jet在这种情况下的效率有多高。如果你在关键字段（例如ID字段，无论来自SQL Server还是其他地方）上进行链接，那么Jet很可能会发送一个ID列表。或者你可以编写SQL以这种方式执行（在WHERE子句中使用IN SELECT...）。

基本上，事物的效率取决于WHERE子句将在哪里执行。例如，如果你正在将本地Jet表与链接的SQL Server表连接在单个字段上，并根据本地表中的值过滤结果，则很可能非常高效，因为Jet只会向服务器发送必要的内容以过滤SQL Server表。

不过，这完全取决于你要做什么（即，你要筛选哪些字段）。但是给Jet一个机会，看看它是否聪明，而不是一开始就假设Jet会搞砸它。也许需要一些微调才能使Jet高效地工作，但如果你可以将所有逻辑保留在客户端，那么你比试图从服务器跟踪所有Access数据库更好。

- David W. Fenton

Answer 5

如果我理解问题正确，您并没有尝试跨多个数据库进行交叉引用。

您只需要引用有关特定文件的详细信息，该文件可能包含：

主键、父文件校验和（如果是修改）、文件校验和、最后已知作者、修订号、上次更改日期...

然后，在使用您的程序分析该文件时，将该主键添加到获取的信息中。

如果您确实需要分布式数据库，也许您会更喜欢使用非关系型数据库，如LDAP。

如果您无法使用LDAP，但必须使用关系型数据库，则可以考虑使用GUID来确保您的主键有效。

- Arafangion

Answer 6

由于您没有提供足够的信息，我不得不做一些假设。

假设：

1. SQL Server 和 Access 数据库不在同一台计算机上
2. SQL Server 无法通过文件共享看到 Access 数据库，或者这样做太困难了。
3. 您不需要在访问数据库和 SQL Server 之间进行连接，只需将访问数据库中的数据用作 where 子句的查找元素即可。

如果以上假设是正确的，那么您可以简单地使用 ADO 打开 Access 数据库并检索所需的数据，可能是在数据集或数据表中。然后提取所需的数据，并将其馈送到不同的 ADO 查询中，以便在动态 Where 子句、预处理语句或通过参数传递给存储过程的方式下发到 SQL Server。

其他人提供的解决方案都假定您需要在数据上执行连接或执行包括两个数据库的 SQL。要做到这一点，您必须使用链接数据库，否则将数据导入表中（可能是临时的）。

- Mystere Man