Using Multiple servers and dbs in a query

thomlamb

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

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");