My application has to connect to 22 mixed databases based on Users Location. Proper way to use with DI

Question

We have 22 locations, and each location has its own database or db schema. So, each location has 2 dbs, one oracle db and SQL db.

Now, the way I'm approaching this is to get the user location based on their AD profile when they login to my application and then I can pull the connection string like that.

on program.cs, add reference to my Data layer and add

builder.Services.AddScoped<IProductDataService,ProductDataService>();
builder.Services.AddScoped<IOracleDataAccess, OracleDataAccess>();
builder.Services.AddScoped<ISQLDataAccess, SQLDataAccess>();

ProductDataService.cs

 public async Task<List<Product>> GetProducts(string userLocationId)
    {
      
            //read query for product by location. 
        var productQuery= QueryReader.ReadQueryFile(QuerySubFolderName, "products.sql");

        var locationData = await _locationService.GetLocationById(userLocationId);
          
        //get data using dapper
        //pass the connectionstring  based on user AD location. 
        var data = await _oracleDb.LoadData<Product, dynamic>(productQuery, new { }, locationData.WMSDB);
        return data;
    }

on my DATA project.

  public async Task<List<T>> LoadData<T, U>(string query, U parameters, string connectionStringName)
    {

        var connectionString = _config.GetConnectionString(connectionStringName);
        using (IDbConnection connection = new OracleConnection(connectionString))
        {
            var items = await connection.QueryAsync<T>(query, parameters, commandType: CommandType.Text, commandTimeout: 240);
            return items.ToList();
        }
    }

While this works, I wonder if this is the right approach. All samples online are usually for using Singleton with one database but in my case, I have 22 databases to connect dynamically. What is the right way to do it?

Answer 1

First, I'll give my single-opinion on this code:

var connectionString = _config.GetConnectionString(connectionStringName);

This is very "Dot-Net-FRAMEWORK'ish", not "DotNet-CORE" thinking.

DotNetCore... the switchover is to "inject DbContexts", not (old FW thinking) of "go lookup a connection string". Some will disagree.

But there is a reason why "AddDbContext" exists. (Note for EF-CORE, it is a "since core-1.0" and has no "DotNet-FW" versions (if you follow the link below).

https://learn.microsoft.com/en-us/dotnet/api/microsoft.extensions.dependencyinjection.entityframeworkservicecollectionextensions.adddbcontext?view=efcore-6.0

So my answer is "AddDbContext" centric. Now "lookup connection string" centric. Please add a tag to your question to disambiguate between dotnet-FRAMEWORK vs dotnet-CORE.

Typically (with ONE database/database-context)..... you have something like this:

    public class DepartmentCommandEntityFrameworkDomainDataLayer : IDepartmentCommandDomainData
    {

        private readonly MyAppNameDbContext entityDbContext;


        public DepartmentCommandEntityFrameworkDomainDataLayer(
            MyAppNameDbContext context )
        {

            this.entityDbContext = context ?? throw new ArgumentNullException(
                "MyAppNameDbContext is null",
                (Exception)null);

        }

        public async Task<int> AddAsync(DepartmentEntity entity, CancellationToken token)
        {
            this.entityDbContext.Departments.Add(entity);
            int saveChangesAsyncValue = await this.entityDbContext.SaveChangesAsync(token);
            return saveChangesAsyncValue;
        }

And you inject a SINGLE MyAppNameDbContext into your DAL class.

But you have ~many MyAppNameDbContext's.

So you can go to a "Factory" way......and you inject your many MyAppNameDbContext's into your Factory.

public interface IDbContextFactory()
{
    public MyAppNameDbContext GetASingleMyAppNameDbContext(int-or-string factoryKey);
}

and

public class MyDbContextsFactoryConcrete : IDbContextsFactory
{

    private readonly IEnumerable<MyAppNameDbContext> allAvailableDbContexts;

    public MyDbContextsFactoryConcrete(IEnumerable<MyAppNameDbContext> allAvailableDbContexts)
    {
        /* check for null or !Any() here and throw an exception, aka, fail early */
        this.allAvailableDbContexts = allAvailableDbContexts;
    }

    public MyAppNameDbContext GetASingleMyAppNameDbContext(int-or-string factoryKey)
    {

        MyAppNameDbContext returnItem = this.allAvailableDbContexts.(whatever code to find the matching MyAppNameDbContext from the IEnumerable-allAvailableDbContexts);
        /* see https://stackoverflow.com/a/52435195/214977 for an idea here */

        return returnItem;

    }

}

and you will inject ALL available MyAppNameDbContext(s)" into this "Factory".

Now you will refactor your DAL class:

    public class DepartmentCommandEntityFrameworkDomainDataLayer : IDepartmentCommandDomainData
    {

        private readonly IDbContextsFactory entityDbContextFactory;


        public DepartmentCommandEntityFrameworkDomainDataLayer(
            IDbContextsFactory entityDbContextFactory
)        {

            this.entityDbContextFactory = entityDbContextFactory ?? throw new ArgumentNullException(
                "IDbContextsFactory is null",
                (Exception)null);

        }

        public async Task<int> AddAsync(
            int dbContextFactoryKey,
            DepartmentEntity entity, CancellationToken token)
        {
            MyAppNameDbContext foundContext = this.entityDbContextFactory.GetASingleMyAppNameDbContext(dbContextFactoryKey);
            foundContext.Departments.Add(entity);
            int saveChangesAsyncValue = await this.entityDbContext.SaveChangesAsync(token);
            return saveChangesAsyncValue;
        }


    }

Now, the above is the "idea".

The "match your factoryKey" to "what is in the IEnumerable-collection" needs to be determined.

Here is how I did it one time:

https://stackoverflow.com/a/52435195/214977

If you follow the link to the other SOF answer... you'll see this code.

private IShipper FindIShipper(String preferredShipperAbbreviation)
{

    IShipper foundShipper = this.shippers.FirstOrDefault(s => s.FriendlyNameInstance.Equals(preferredShipperAbbreviation, StringComparison.OrdinalIgnoreCase));

    if (null == foundShipper)
    {
        throw new ArgumentNullException(
            String.Format("ShipperInterface not found in shipperProviderMap. ('{0}')", preferredShipperAbbreviation));
    }

    return foundShipper;
}

In the other SOF answer, this code is "buried" inside the OrderProcessor.

The "Factory" guidelines I provide above is basically "encapsulate the 'private IShipper FindIShipper' functionality into its own class to provider better reuse.