Using Multiple servers and dbs in a query

thomlamb · February 2020

On Sept 2016 Joe wrote:

This strategy will work if all the databases in question have the same schema.

You can retrieve the connection string from another query as follows:

Util.GetMyQueries().First (x => x.FilePath == "...").GetConnectionInfo().DatabaseInfo.GetCxString()

If I get the connection string from another query how do I use it in a new query for a specific block of code? Can I use
this.Connection.ConnectionString to flip my query to another connection and then back again?

nescafe · February 2020

If you are using LINQ to SQL, you can switch to the secondary database using this code:

Table1.Take(10).Dump(); // 10 rows from Table1 in Database1

var connectionString2 = new SqlConnectionStringBuilder(Connection.ConnectionString)
{
  InitialCatalog = "Database2",
  Password = "abcd", // Re-supply the password
}.ToString();

using (var context2 = new TypedDataContext(connectionString2))
{
  context2.Table1.Take(10).Dump(); // 10 rows from Table1 in Database2
}

Note that the following will also work if you are on the same server (but lacks object tracking):

var data = ExecuteQuery<Table1>("SELECT TOP 10 * FROM Database2..Table1");

Using Multiple servers and dbs in a query

Comments