Sql query containing 2 databases

Question

In C# I want to execute a query that use 2 different databases (One is Access for local, and other is distant and is MySQL)

I'm able to do it in VBA Access, but how I can make the same thing in C# ??

This is how I made it in Access:

Link my 2 differents table/databases in Table

In VBA:

sSQL = "INSERT INTO DB1tblClient SELECT * FROM DB2tblClient"  
CurrentDb.Execute sSQL  

How I can execute this SQL in C# ? (What object to use, etc... Example code if you can)

Thanks !

Answer 1

There are two ways to do this. One is to set up linked tables on Access and run a single query. The other is to run both queries from c# and join them with linq.

The first way is better. If you really have to do it with linq, here is some sample code:

dWConnection.Open();
dWDataAdaptor.SelectCommand = dWCommand1;
dWDataAdaptor.Fill(queryResults1);
dWDataAdaptor.SelectCommand = dWCommand2;
dWDataAdaptor.Fill(queryResults2);
dWConnection.Close();

IEnumerable<DataRow> results1 = (from events in queryResults1.AsEnumerable()
                       where events.Field<string>("event_code").ToString() == "A01"
                       ||  events.Field<string>("event_code").ToString() == "ST"
                       select events ) as IEnumerable<DataRow>;


var results2 = from events1 in queryResults1.AsEnumerable()
    join events2 in queryResults2.AsEnumerable()
    on (string)events1["event_code"] equals (string)events2["event_code"]

    select new
            {
                  f1 = (string)events1["event_code"],
                  f2 = (string)events2["event_name"]
            };

DataTable newDataTable = new DataTable();
newDataTable = results1.CopyToDataTable<DataRow>();

See why I said linked tables is better?