Query Multiple databases with single ado.net query

To talk to different databases / connections, you'll need a distributed transaction via TransactionScope; fortunately, this is actually easier than db-transactions (although you need a reference to System.Transactions.dll):

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

Additionally, TransactionScope nest naturally, and SqlConnection enlists automatically, making it really easy to use.

Use TransactionScope.

If you open connections to different servers within the scope, the transaction will be escelated to a distributed transaction.

Example:

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

You can't do what you're after with a single query unless you're willing to insert an intermediary of some kind, such as a SQL Express instance, that would mediate with the other servers, perhaps using SQL CLR. But that's messy.

It would be much easier to simply issue a bunch of async requests, and then merge the responses into a single DataTable (or equivalent) when they arrive. By using native ADO.NET style async calls, all queries can happen in parallel. You will of course need to use a lock while reading the data into a single DataTable.

The best solution here is to use a Virtual DBMS to blend your multiple back-ends into a single apparent backend -- so your query goes to the Virtual DBMS which then relays it appropriately to the actual data stores.

OpenLink Virtuoso is one option. Virtuoso opens connections to any ODBC-accessible (including JDBC-accessible, via an ODBC-to-JDBC Bridge) data source.

Your data consuming applications can connect to Virtuoso via ODBC, JDBC, OLE-DB, or ADO.NET as needed. All remote linked objects (Tables, Views, Stored Procedures, etc.) are available through all data access mechanisms.

While you can achieve similar results using the other techniques outlined here, those require the end user to know all about the back-end data structures, and to optimize queries themselves. With Virtuoso, a built-in Cost-based Optimizer will re-write queries to deliver the fastest possible results, with the least possible network traffic, based on the Virtual Schema constructed when you link in the remote objects.

Disclaimer: I work for OpenLink Software, but do not directly benefit from anyone choosing to use our products.

If you do not have access or privileges to make a Linked server and generate a View with consolidate JOINed Query with all SQL Servers, fill results from the same query statement at all SQL Server instances and make and make a union of results. Looping through all databases connections and add collected data to a consolidated collection data structure, for this example I choose a DataTable:

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

Using this method example to query any SQL Server Database based on ADO.NET:

/// <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;
}

With this solution, only per attention to replicated data, needs after to make a distinct on consolidated result.