Join tables from different databases (ADO.NET)

Question

I have two databases and a UDL connection string to each one. I'd like to construct a query that joins tables from both databases.

I'm doing a query from a single database like this.

string query = "...";

using (OleDbConnection connection = new OleDbConnection(ConnectionString))
using (OleDbCommand command = new OleDbCommand(query, connection))
{
    int count = command.ExecuteNonQuery();
}

Can someone show me an example of doing the same thing with a query that joins tables from two databases?

is this for sql-server? if so - have a look at linked servers feature. — Daniel A. White, Sep 27 '18 at 19:01
The underlying database is SQL Server, yes. But my question is really about doing this in ADO.NET and C#. And, to be honest, I'm really not sure what you are referring to with *Linked server features*. — Jonathan Wood, Sep 27 '18 at 19:03
i'd do it w/ the db itself, that way you wouldn't baloon your process's memory — Daniel A. White, Sep 27 '18 at 19:05
Right. But that's not an option since the code needs to do other things besides run this query. — Jonathan Wood, Sep 27 '18 at 19:05
If your data is not too large to make it impractical to bring down to the client you can get the data from each database and then join in memory with Linq. I would caution against this especially if there is any chance your data will grow over time. — Crowcoder, Sep 27 '18 at 19:06
define other things. you could write a sproc that returns the values. — Daniel A. White, Sep 27 '18 at 19:07
@Crowcoder: The data could be quite large, and the query is a little complex, so it could be problematic bringing all the data from one table that could meet my final criteria. — Jonathan Wood, Sep 27 '18 at 19:13
@DanielA.White: I need to be able to deploy this on existing systems. It's not an option to also modify the database. — Jonathan Wood, Sep 27 '18 at 19:14
so your only real option is to pull all the data into your application and join it manually. — Daniel A. White, Sep 27 '18 at 19:15
@DanielA.White: Are you saying it's not possible to do with ADO.NET? — Jonathan Wood, Sep 27 '18 at 19:16
i'm pretty sure that's the case. what do you expect? ado.net to tell the databases to work together to come up with some result? — Daniel A. White, Sep 27 '18 at 19:17
Why the downvote? I'm just asking if it's something that can be done. — Jonathan Wood, Sep 27 '18 at 19:17

Jonathan Wood · Accepted Answer · 2018-09-28T18:43:15.510

So, the thing that was throwing me off was the assumption I'd need two connection strings. There doesn't appear to be any way to construct a single query in ADO.NET that involves two connections.

In my case, both databases are on the same server. So only one connection string is required. Then, the query can include database prefixes to identify which database you are referring to.

SELECT [DB1].[dbo].Field1, [DB2].[dbo].Field2 FROM ...

score 0 · Answer 2 · answered Sep 27 '18 at 21:41

You can select both in two DataTables and then join them using Linq.

var results = from par in dt1.AsEnumerable()
                join chi in dt2.AsEnumerable()
                  on (int)par["ID"] equals (int)chi["ParentID"]
                select new //Here you can leave it that way or use your own object.
                           // select new MyResultObject(){prop1 = x, prop2 = y ...}
                {
                    ParentID = (int)par["ParentID"],
                    ChildID = (int)par["ChildID"],
                    ColA = (string)par["ColA"],
                    ColB = (int)par["ColB"],
                    ColC = (double)chi["ColC"],
                    ColD = (date)chi["ColD"]
                };

Hope it helps :)

Thanks, but this requires loading way too much data across the connection and into memory. — Jonathan Wood, Sep 30 '18 at 16:07

Join tables from different databases (ADO.NET)

2 Answers2