Connecting to Multiple Databases in C# using DataContext

Instead of scattering connection logic throughout the codebase, encapsulating your database connections using a DataContext class helps organize your connections and makes it easier to manage different databases in a clean, scalable way.

Define Connection Strings in appsettings.json

Add the connection strings for both databases in the appsettings.json file:

{
  "ConnectionStrings": {
    "DatabaseA": "************",
    "DatabaseB": "************"
  }
}

Create Two Separate DataContext Classes

Define two classes one for each database connection string.

Here is a sample for DatabaseA and DatabaseB.

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

namespace MyApp
{
    public class DatabaseADataContext
    {
        private readonly string _connectionString;

        public DatabaseADataContext(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DatabaseA");
        }

        public SqlConnection CreateConnection() => new SqlConnection(_connectionString);
    }

    public class DatabaseBDataContext
    {
        private readonly string _connectionString;

        public DatabaseBDataContext(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DatabaseB");
        }

        public SqlConnection CreateConnection() => new SqlConnection(_connectionString);
    }
}

In this example, we’ve defined two classes, DatabaseADataContext and DatabaseBDataContext. They follow the same pattern but use different connection strings.

Register DataContext Classes in Program.cs

In Program.cs, register the two DataContext classes as singletons. This makes them available throughout the application via dependency injection.

public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
        .ConfigureServices((hostContext, services) =>
        {
            services.AddSingleton<DatabaseADataContext>();
            services.AddSingleton<DatabaseBDataContext>();
        });

Use DataContext in Repositories

Now, you can inject the DataContext classes into your repositories and use them to query the respective databases.

using Dapper;
using Microsoft.Data.SqlClient;

namespace MyApp
{
    public class MyRepository
    {
        private readonly DatabaseADataContext _databaseADataContext;
        private readonly DatabaseBDataContext _databaseBDataContext;

        public MyRepository(DatabaseADataContext databaseADataContext, DatabaseBDataContext databaseBDataContext)
        {
            _databaseADataContext = databaseADataContext;
            _databaseBDataContext = databaseBDataContext;
        }

        public async Task<IEnumerable<MyData>> GetDataFromDatabaseA()
        {
            using var connection = _databaseADataContext.CreateConnection();
            var data = await connection.QueryAsync<MyData>("SELECT * FROM TableA");
            return data;
        }

        public async Task<IEnumerable<MyData>> GetDataFromDatabaseB()
        {
            using var connection = _databaseBDataContext.CreateConnection();
            var data = await connection.QueryAsync<MyData>("SELECT * FROM TableB");
            return data;
        }
    }
}

Here, the repository uses both DatabaseADataContext and DatabaseBDataContext to interact with DatabaseA and DatabaseB. By following this pattern, the connection logic remains organized and maintainable.

Additional Features You Can Add

If you want to use Azure Managed Identity to connect to one of your databases, simply modify the connection logic in the DataContext classes to include Azure authentication.

You can add table names directly inside the DataContext class for further abstraction:

namespace MyApp
{
    public class DatabaseADataContext
    {
        private readonly string _connectionString;
        public string TableA = "TableA";

        public DatabaseADataContext(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DatabaseA");
        }

        public SqlConnection CreateConnection() => new SqlConnection(_connectionString);
    }
}

This allows you to reference table names dynamically within your queries:

public async Task<IEnumerable<MyData>> GetDataFromDatabaseA()
{
    using var connection = _databaseADataContext.CreateConnection();
    var data = await connection.QueryAsync<MyData>($"SELECT * FROM {_databaseADataContext.TableA}");
    return data;
}

You can add a boolean flag, such as Enabled, to control whether certain operations are executed. This is useful for dry runs or toggling between production and testing environments.

public class DatabaseADataContext
{
    public bool Enabled { get; private set; }

    public DatabaseADataContext(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DatabaseA");
        Enabled = configuration.GetValue<bool>("DatabaseAEnabled");
    }

    public SqlConnection CreateConnection() => new SqlConnection(_connectionString);
}

Then use the Enabled flag within your repositories:

public async Task<IEnumerable<MyData>> GetDataFromDatabaseA()
{
    if (!_databaseADataContext.Enabled)
        return Enumerable.Empty<MyData>();

    using var connection = _databaseADataContext.CreateConnection();
    var data = await connection.QueryAsync<MyData>("SELECT * FROM TableA");
    return data;
}

Using a DataContext class is a powerful way to manage multiple database connections in C#. By abstracting connection logic and separating database-specific details into their own classes, you can scale your application efficiently and improve maintainability. You can also easily integrate additional features like Azure Managed Identity or feature flags, making your application more flexible and adaptable.