How to connect to two databases in one connection string in C#?

Question

Normally, when I need to connect to a database using C#, I would use the following command routines below:
- define a mysql connection.
- open a mysql connection.
- define a sql statement / query.
- use MySqlCommand to execute the query.

Sample codes:

string con1 = "server=<db1 IP>;User Id=user;password=password;Persist Security Info=True;database=db1";
string con2 = "server=<db2 IP>;User Id=user;password=password;Persist Security Info=True;database=db2";
MySqlConnection cn1 = new MySqlConnection(con1);
MySqlConnection cn2 = new MySqlConnection(con2);
MySqlCommand com

cn1.Open();
string sql = "some query";
com = new MySqlCommand(sql, cn1);
com.executeNonQuery();  
cn1.Close();

My problem above is on the part where I use the MySqlCommand command because it is where a database connection is indicated so that it will now which database to query to like

MySqlCommand com = new MySqlCommand(sql, con);

where sql is a sql statement and con is a connection to be used for the query.

How do I query two databases in one sql statement?
Consider the following: (I'm using MySQL)

- I have two databases, db1 and db2.
- db1 is located in City A
- db1 is located in City B
- Both databases have one table (tbl) and they both have the same structure.
- Table structure for tbl:
    +-------------+--------------+------+-----+---------+-------+
    | Field       | Type         | Null | Key | Default | Extra |
    +-------------+--------------+------+-----+---------+-------+
    | id          | int(9)       | NO   | PRI |         |       |
    | ref_no      | int(9)       | NO   |     |         |       |
    | name        | varchar(10)  | YES  |     | NULL    |       |
    +-------------+--------------+------+-----+---------+-------+
- I want to run a query on db1.tbl against db2.tbl
- Example query: "select ref_no from db1.tbl where ref_no not in (select ref_no from db2.tbl)"

Or is there another way for this kind of problem?...

This is something that has to be set up at the database server level, not within C#, I believe. Linked Servers is one way to do it. Alternatively, you could get the results from both servers (via two connection strings) and then manipulate the data as desired in your program. I'm sure there are other options as well, these are just two that came to mind immediately. — Tim, May 02 '13 at 07:05
Use two connections and join using Linq. See http://stackoverflow.com/questions/4278993/is-it-possible-to-perform-joins-across-different-databases-using-linq — Morten, May 02 '13 at 07:11
This can be an important trade-off: **using two separate connections** (best separation), vs. **connecting to a single server that holds both databases** (allowing cross-database joins and transactions, but preventing you from moving one database to a different server in the future if you *do* use those). — Timo, Jun 07 '18 at 13:22

mjb · Answer 1 · 2018-01-12T01:34:13.707

9

string con = "server=localhost;user=root;pwd=1234;";

using (MySqlConnection cn1 = new MySqlConnection(con))
{
    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = cn1;
    cn1.Open();

    cmd.CommandText = sql;
    MySqlDataAdapter da = new MySqlDataAdapter();
    ....
}

sql statement:

select a.ref_no from db1.tbl a where a.ref_no not in (select b.ref_no from db2.tbl b)

You can query multiple database at a time.

Update

I think the only option is create 2 connections at the same time and pass the data between the 2 server through C#.

edited Jan 12 '18 at 01:34

answered May 02 '13 at 12:28

mjb

7,649
8
44
60

2

I tried your code and it worked but it is only applicable if your trying to query multiple databases on the same server indicated on the connection string... What if db1 and db2 are on different location? (e.g. db1's server=
and db2's server=
– chad May 03 '13 at 00:34
I think the only option is create 2 connections at the same time and manually pass the data between them through C# – mjb Jan 12 '18 at 01:30

score 0 · Answer 2 · answered Jul 10 '18 at 09:15

What you are looking for is the Federated Storage Enginge

On one of the server (or even both), you can create a placeholder table that is transparently routed by the MySQL server to the other MySQL server.

It allows you to run your query on just one server and your server will contact the other server for the data it needs.

How to connect to two databases in one connection string in C#?

2 Answers2