4

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 ?

Dalorzo
  • 19,834
  • 7
  • 55
  • 102
Atzi
  • 457
  • 1
  • 6
  • 16
  • I don't think there is anyway to do that and I feel that would be going against any sort of best practice to not separate connections even if you could. – Evan Frisch Aug 11 '14 at 22:40
  • If these databases are on the same server, then either connection string will do. You just need to connect to the server. It would be a performance issue to seperate connections as the join would not occur on the database server. – Nick.Mc Aug 11 '14 at 22:52

4 Answers4

6

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

petelids
  • 12,305
  • 3
  • 47
  • 57
6

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.

Greg
  • 11,302
  • 2
  • 48
  • 79
  • This answer doesn't address what to do if one connection doesn't have permissions to both databases. This comes up in certain deployment scenarios where applications are using 2 different connections with 2 different users for 2 different DBs. What do you do in this case? – Ian Aug 01 '17 at 16:22
  • @Ian Well, if the OP required that it would be in his question. However, if an application is going to be utilizing multiple databases in the application, then a specific user should be created that has access to both. Restricted to the bare minimum the application requirements may require. – Greg Aug 02 '17 at 14:35
3

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.

Randy R
  • 323
  • 3
  • 13
  • Your answer is a nice approach, you should add more detail / example to help future visitors. As it can be tricky to set that up. – Greg Aug 12 '14 at 16:29
1

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.

keven1894
  • 31
  • 2