CODEWITHSUNDEEP
c#sqldatabaseado.net
user1017413
user1017413

Reputation: 2183

Combining queries to two databases in ADO.NET

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.

Upvotes: 3

Views: 1926

Answers (2)

Jacob
Jacob

Reputation: 78920

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
    }
}

Upvotes: 1

Joshua Honig
Joshua Honig

Reputation: 13235

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.

Upvotes: 1

Related Questions