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

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.