Combining queries to two databases in ADO.NET

Question

I'm working on a project in C# that utilizes ADO.NET to connect to a database. The queries aren't terribly complex, but due to the nature of the project, we have two databases with the same structure. One is fixed with information that will be updated from an outside source perhaps once a year. The other maintains the same structure, but it used for custom user entries. The thing is, this means the same queries get fired off to two databases. As I'd rather not duplicate a bunch of code, I was wondering if anyone knew of a relatively simple process to combine queries to two identically-structured databases and automatically combine the results.

You'll need to be more specific about what *combining* the queries means. — Jacob, Dec 30 '11 at 19:23
This post on merging datasets may help http://stackoverflow.com/questions/7441188/how-to-efficiently-merge-two-datasets — bryanmac, Dec 30 '11 at 19:23
What is the database engine? DB2? Oracle? Sql Server?... Also, do you mean two physically separate databases on different instances on different servers? Or two named database objects on the same server? — Joshua Honig, Dec 30 '11 at 19:32
It's two Access databases on the same server. The structure is identical, but rather than connect, query, and pull the results on each, it would be nice to combine that process for both into something more simple than doing the same thing twice. — user1017413, Dec 30 '11 at 20:20

score 1 · Answer 1 · edited Dec 30 '11 at 19:29

DataAdapter.Fill(DataTable) will append results to the table you pass to it. So assume cs1 and cs2 are the two connection strings:

DataTable dt = new DataTable();
string selectSql = "SELECT STUFF FROM SOMEWHERE";
foreach (String cs in new string[] { cs1, cs2 }) {
    using (OdbcDataAdapter adp = new OdbcDataAdapter(selectSql, cs)) {
        adp.Fill(dt);
    }
}

The first call initializes the DataTable and fills it with the results from database 1, the second call appends the data from database 2.

score 1 · Answer 2 · answered Dec 30 '11 at 19:30

One approach is to create a class that handles the logic of sending the query to the two databases and merging the results with whatever rules you determine. Here's a rough example:

public class OverlayDatabase
{
    private string masterDbConnStr;
    private string userDbConnStr;

    public OverlayDatabase(string masterDbConnStr, string userDbConnStr)
    {
        // ...
    }

    public DataSet ExecuteQuery(DbCommand command)
    {
        var dataSets = new List<DataSet>();
        foreach (var connStr in new[] { masterDbConnStr, userDbConnStr } )
        {
            // 1. Create connection
            // 2. Execute command with that connection
            // 3. Store dataset in dataSets
        }
        return MergeDataSets(dataSets);
    }

    private DataSet MergeDataSets(IEnumerable<DataSet> datasets)
    {
        // Merge logic here
    }
}

Combining queries to two databases in ADO.NET

2 Answers2