CODEWITHSUNDEEP
c#sqlsql-serverinner-joinsqlcommand
Atzi
Atzi

Reputation: 515

one sql command with two connection string

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 ?

Upvotes: 4

Views: 8234

Answers (4)

Randy R
Randy R

Reputation: 322

If the databases are on different servers you can create a "Linked Server" in Management Studio.

  1. From within your main database goto Server Objects -> Linked Servers.
  2. Right-click on Linked Servers then hit "New Linked Server"
  3. On the General page select the Type of Server you'd like to link to and enter credentials if needed.
  4. Add the local user that will have access to the linked database and enter the "remote" user that you want to be and press Ok.
  5. Done.

Then in front of the database name you need to specify which server. An example:

     select * from [2.2.2.2].[Test].[dbo].[MyTable] 
         join [1.1.1.1].[OtherDb].[dbo].[OtherTable] on ...etc

The great part about Linked Servers is that you map the user so that the same user doesn't have to exist on both machines. The database with the link then automatically logs you in the the other one.

Upvotes: 3

Greg
Greg

Reputation: 11480

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.

Upvotes: 6

petelids
petelids

Reputation: 12815

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).

Upvotes: 6

keven1894
keven1894

Reputation: 31

Your query is database specified "[FirstDataBase].[dbo].[table1]". So you just to connect to a database (even can be a database other that [FirstDataBase] or [SecondDataBase]). Your code should works well.

Upvotes: 1

Related Questions