Multiple Connection String

Question

I am not sure if this is feasible in ADO.NET, but i still want to run by you gurus.

I have a following sql query:

select a.name, b.id 
from datasource1_table a, datasource2_table b
where a.id=b.id

1) datasource1_table - SQL Server "SQLServer1" connection

2) datasource2_table - SQL Server "SQLServer2" connection

So i have connection string that has login for both the server. I would like execute the above sql in one shot.

Is it possible?

Let me know if you need more information or clarification.

Thanks a lot in advance.

Are the tables in databases on the same database server? Or are they on completely different servers? If they are on different servers, you'll find this very difficult to do without selecting data from both databases separately, and joining in memory via DataTables, for example. — dash
– dash, Commented Aug 7, 2012 at 23:46
server are on different location and can be of any dbtype - oracle, sql server or mysql — Murali Bala
– Murali Bala, Commented Aug 7, 2012 at 23:55

Abe Miessler · Accepted Answer · 2012-08-07 23:56:14Z

4

Not exactly. You can only associate one SqlConnection with a SqlCommand. What you can do is is create a linked server on SQLServer1 that points to SQLServer2. Then you can reference it like so:

select a.name, b.id 
from datasource1_table a, myLinkedServer.myDb.dbo.datasource2_table b
where a.id=b.id

On a side note I would change your current syntax to use actual joins.

If linked servers are not an option you will probably have to execute two queries and process the results.

edited Aug 7, 2012 at 23:56

answered Aug 7, 2012 at 23:46

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dash Over a year ago

Yes, indeed, although linked/remote servers can be more complex to maintain (but not unduly so). I've worked in many places where the DBA's will not set them up.

Murali Bala Over a year ago

Thanks Abe. Linked Server is not an option. Also connection can be from any db type - mysql, sql server or oracle.

Abe Miessler Over a year ago

Without linked servers I think you will need to execute two separate queries and then process the results.

Murali Bala Over a year ago

Yeah. That's what i figured. Just wanted to take stackoverflow's input.

dash · Accepted Answer · 2012-08-08 00:07:36Z

1

Given that you cannot use a Linked Server, you may wish to investigate the DataRelation class

In your example, perform two SELECT's:

DataSet domain = new DataSet();

DataTable dataFromA = null;
DataTable dataFromB = null;

using(SqlConnection conn1 = new SqlConnection("FirstConnectionString"))
{
  DataTable dataFromA = //result from SELECT id, name FROM TableA
}


using(SqlConnection conn2 = new SqlConnection("SecondConnectionString"))
{
  DataTable dataFromB = //result from SELECT id, name FROM TableB
}

domain.Tables.Add(dataFromA);
domain.Tables.Add(dataFromB);

I have paraphrased the process here.

Once you have both data tables in memory, you can create a DataRelation between them:

DataRelation idRelationship = new DataRelation("ABLink", dataFromA.Columns["Id"], dataFromB.Columns["Id"]);

You can then explot this relationship anyway you wish:

foreach(DataRow aRow in dataFromA.Rows)
{
  //matching B Rows:
  DataRow[] bRows = aRow.GetChildRows(idRelationship);
}

It's effectively an in-memory index between the two tables.

edited Aug 8, 2012 at 0:07

answered Aug 8, 2012 at 0:01

dash

91.8k4 gold badges54 silver badges72 bronze badges

2 Comments

Murali Bala Over a year ago

dash - Thanks for the sample.

dash Over a year ago

@MuraliBala I already do exactly the same thing because we aren't allowed to use Linked Servers. Only thing to be aware of is that with large tables, memory will be an issue. In that instance, it's possible to use two DataReaders, one looping through the table on the connection to the first server, and one looping through the table on the connection to the second server.

brainboost · Accepted Answer · 2012-08-07 23:53:06Z

0

You can add one server as a linked server to the other one with sp_linkedserver. Then, use the plain ado.net connection and queries:

SELECT * FROM OPENQUERY (LinkedServerName,'SELECT * FROM myDatabase.Customer')

Don't you?

answered Aug 7, 2012 at 23:53

brainboost

3792 silver badges14 bronze badges

2 Comments

brainboost Over a year ago

or use Nhibernate with two connections. See the article mikehadlow.blogspot.com/2008/10/… for detals

Murali Bala Over a year ago

Sounds good. But my process can connect to multiple datasource in different servers. "NHibernate to do joins across databases so long as they are on the same server"

VRK · Accepted Answer · 2012-08-08 01:48:35Z

0

I was in a same situation as you are now. DBA's did not allow me to use link servers.

So as a solution I implemented WCF service that connects to two different database servers and get record set in to the memory. After data from two databases are in the memory we can play with it.

Please note that you have to take into the consideration how much data we are dealing with as we are grabbing all in to the memory. In order to minimize the data we can retrieve only required data(i.e. avoid using select * from table)

answered Aug 8, 2012 at 1:48

VRK

4002 silver badges5 bronze badges

Collectives™ on Stack Overflow

Multiple Connection String

4 Answers 4

4 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related