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

Answer 1

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