one sql command with two connection string

Question

I want to run this query in C#

SELECT *
FROM [FirstDataBase].[dbo].[table1]
INNER JOIN [SecondDataBase].[dbo].[table2]

and my code is :

SqlConnection cn = new SqlConnection(myConnectionString);
SqlCommand cmd = new SqlCommand(@"SELECT * FROM [FirstDataBase].[dbo].[table1]
    INNER JOIN [SecondDataBase].[dbo].[table2]");

cmd.Connection = cn; // here is my question !!!

cn.Open();
int x = (int)cmd.ExecuteScalar();

but my query needs two connection string ... one for [FirstDataBase] and second for [SecondDataBase]... How can I do this ? How can I insert two SqlConnection or ConnectionString to one SqlCommand ? or How can I do it in other ways ?

Answer 1

I've actually reread your question, you don't need two connection strings. Your query command can affect any database you wish, once you've connected. For instance:

string query = @"SELECT * FROM [FirstDataBase].[dbo].[table1]
    INNER JOIN [SecondDataBase].[dbo].[table2]";

using(SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionString[@"db"].ConnectionString))
     using(SqlCommand command = new SqlCommand(query, connection))
     {
         // Logic
     }

That would impact both databases should your query impact both of them. Your able to use the same hierarchy that is present in SQL Management Studio to perform your task.

Answer 2

You only need to connect to the first database. The query will then connect from there to the second database. Alternatively you can connect to just the second database if you prefer but the key point is that you only require one connection.

The same principle applies if you are using Management Studio, you would connect to the first database and then run the query joining across to the second.

Obviously this assumes that the first database can communicate with the second (as @Tim Medora pointed out in the comments).