Query Multiple databases with single ado.net query

Question

I have distributive DB architecture where data is stored in multiple SQL servers.

how can i do select/update/delete by running a single query. for example "select * from employees" should return data from all databases i have.

How can write single query which run across multiple SQL servers and gets a single consolidated view to my web server.

NOTE: Since the number of SQL servers may change at varied times so I am looking for something else than linked queries since managing the linked queries at scale( up or down) is a big pain

Answer 1

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

Answer 2

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.

Answer 3

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.

I hope this helps!

Ted

Answer 4

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.