使用单个ado.net查询查询多个数据库

Question

我有一个分布式数据库架构，数据存储在多个SQL服务器中。

如何通过运行单个查询执行选择/更新/删除。例如，“select * from employees”应该返回我所有数据库的数据。

如何编写单个查询，可以跨多个SQL服务器运行，并向我的Web服务器获取单个合并视图。

注意：由于SQL服务器的数量可能随时更改，因此我正在寻找其他方法来管理链接查询以及缩放（升级或降级），因为这是一件很麻烦的事情。

- user72486

---

通过事务，我指的是通过运行单个查询来进行选择/更新/删除。例如，“select * from employees”应该从所有三个数据库中返回数据。 - user72486

Answer 1

要访问不同的数据库/连接，您需要通过TransactionScope进行分布式事务；幸运的是，这实际上比db-transactions更容易（尽管您需要引用System.Transactions.dll）：

using(TransactionScope tran = new TransactionScope()) {
    // lots of code talking to different databases / connections
    tran.Complete();
}

此外，TransactionScope 自然嵌套，SqlConnection 自动注册，使其非常容易使用。

- Marc Gravell

---

值得注意的是，如果要使用TransactionScope进行分布式事务处理，则需要使用Windows 2000或更高版本，并且必须运行Microsoft Distributed Transaction Controller（MSDTC）服务。 - Paul Turner

通过事务，我指的是通过运行单个查询来进行选择/更新/删除。例如，“select * from employees”应该从所有三个数据库中返回数据。 - user72486

Answer 2

使用 TransactionScope。

如果在范围内打开到不同服务器的连接，则事务将升级为分布式事务。

示例：

using (TransactionScope scope = new TransactionScope())
{
    conn1.Open(); //Open connection to db1
    conn2.Open(); //Open connection to db2

    // Don't forget to commit the transaction so it won't rollback
    scope.Complete()
}

- Oded

---

顺便说一下，它是Complete()而不是Commit()。 - Marc Gravell

通过事务，我指的是通过运行单个查询来进行选择/更新/删除。例如，“select * from employees”应该从所有三个数据库中返回数据。 - user72486

在SQL Server中，可以使用链接服务器来实现此操作。然后，您可以联合选择不同数据库中的内容（命名方式为dbname.schema.owner.table）。 - Oded

Answer 3

如果您没有访问或权限来创建联接服务器并生成具有所有Sql服务器的联合JOIN查询的视图，请使用相同的查询语句在所有Sql Server实例中填充结果，并对结果进行联合。循环遍历所有数据库连接并将收集到的数据添加到一个统一的数据结构集合中，在这个例子中我选择了DataTable：

DataTable consolidatedEmployees = new DataTable();
foreach(ConnectionStringSettings cs in ConfigurationManager.ConnectionStrings)
{
    consolidatedEmployees.Merge(
          SelectTransaction("select * from employees", cs.ConnectionString));
}

使用此示例方法基于 ADO.NET 查询任何 SQL Server 数据库：

/// <summary>
/// Method to execute SQL Query statements with
/// Transaction scope using isolation level to select read commited data
/// </summary>
/// <param name="query">SQL Query statement</param>
/// <param name="connString">Connections String</param>
internal DataTable SelectTransaction(string query, string connString)
{
    DataTable tableResult = null;
    SqlCommand cmd = null;
    SqlConnection conn = null;
    SqlDataAdapter adapter = null;
    TransactionOptions tranOpt = new TransactionOptions();
    tranOpt.IsolationLevel = IsolationLevel.ReadCommitted;
    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, tranOpt))
    {
        tableResult = new DataTable();
        try
        {
            conn = new SqlConnection(connString);
            conn.Open();
            cmd = new SqlCommand(query, conn);
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(tableResult);
            break;
        }
        catch (Exception ex)
        {
            scope.Dispose();
            throw new Exception("Erro durante a transação ao banco de Dados.", ex);
        }
        finally
        {
            if (null != adapter)
            {
                adapter.Dispose();
            }
            if (null != cmd)
            {
                cmd.Dispose();
            }
            if (null != conn)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        scope.Complete();
    }
    return tableResult;
}

使用这个方案，只需要关注复制的数据，并在合并的结果上进行去重处理即可。

- Antonio Leonardo

Answer 4

在这里最好的解决方案是使用虚拟数据库管理系统将多个后端混合成一个单一的后端，因此您的查询将发送到虚拟数据库管理系统，然后适当地中继到实际数据存储。

OpenLink Virtuoso 是其中一种选择。Virtuoso 可以打开任何支持 ODBC 访问（包括通过 ODBC-to-JDBC 桥接器支持 JDBC 访问）的数据源的连接。

您的数据消费应用程序可以根据需要通过 ODBC、JDBC、OLE-DB 或 ADO.NET 连接到 Virtuoso。所有远程链接对象（表、视图、存储过程等）都可以通过所有数据访问机制进行访问。

虽然您可以使用本文中概述的其他技术实现类似的结果，但这些技术要求最终用户了解所有后端数据结构，并自行优化查询。使用 Virtuoso，则内置的基于成本的优化器将重写查询，以便根据链接远程对象时构建的虚拟模式提供尽可能快速的结果和尽可能少的网络流量。

免责声明：我为 OpenLink Software 工作，但不会直接从任何人选择使用我们的产品中获益。

- TallTed

---

当您推荐由您的雇主生产的产品时，应该披露您的从属关系。 - nobody

Answer 5

如果你不想插入中间件，比如 SQL Express 实例，并使用 SQL CLR 与其他服务器进行中介，那么你不能通过单个查询来实现你想要的目标。但这样做会很麻烦。

更简单的方法是发出一堆异步请求，然后在它们到达时将响应合并到单个 DataTable（或等效项）中。通过使用本机 ADO.NET 异步调用风格，所有查询都可以并行进行。当然，在将数据读入单个 DataTable 时需要使用锁。

- RickNZ