Injecting multiple connection strings in constructor c#

Question

I have a use case where I will need multiple connection strings in my data access layer and will use anyone depending on the input.

Currently, I have 2 connection strings which I have added in JSON and then I am injecting both.

Is there any other solution to inject all the connection strings at once because in future with the introduction of any new DB I have to add one more connection string in JSON and then again inject it?

StartUp class:

private static void Main(string[] args)
{
    ServiceCollection serviceCollection = new ServiceCollection();
    ConfigureServices(serviceCollection);

    IServiceProvider serviceProvider = 
    serviceCollection.BuildServiceProvider();
    serviceProvider.GetService<StudentApp>().Start();
}

private static void ConfigureServices(IServiceCollection 
serviceCollection)
{
    IConfigurationRoot configuration = GetConfiguration();
    Database database1 = new SqlDatabase(configuration.GetSection("Configuration:ConnectionString1").Value;
    Database database2 = new SqlDatabase(configuration.GetSection("Configuration:ConnectionString2").Value;

    // Here I am doing Multiple injections
    serviceCollection.AddSingleton(database1);
    serviceCollection.AddSingleton(database2);

    serviceCollection.AddOptions();
    serviceCollection.Configure<AppSettings(configuration.GetSection("Configuration"));
    serviceCollection.AddSingleton(configuration);

    serviceCollection.AddTransient<IStudentDataAccess,StudentDataAccess>();
    serviceCollection.AddTransient<StudentApp>();
}

private static IConfigurationRoot GetConfiguration()
{
    return new ConfigurationBuilder()
       .AddJsonFile("appsettings.json", optional: true)
       .Build();
}

StudentApp Class:

private readonly IStudentDataAccess _dataAccess;
private readonly AppSettings _config;
private readonly Database _database1;
private readonly Database _database2;

public StudentApp(IStudentDataAccess dataAccess,IOptions<AppSettings> 
config, Database database1, Database database2)
{
     _dataAccess= dataAccess;
     _config = config.Value;
     _database1 = database1;
     _database2 = database2;
}

public void Start()
{
    int count= _dataAccess.GetStudentCount(deptId);
}

DataAccess classes:

public interface IStudentDataAccess 
{
    int GetStudentCount(int deptId);
}

public class StudentDataAccess : IStudentDataAccess 
{
    private readonly AppSettings _config;
    private readonly Database _database1;
    private readonly Database _database2;
    public StudentDataAccess (IOptions<AppSettings> config, Database 
    database1,Database database2)
    {
         _config = config.Value;
         _database1 = database1;
         _database2 = database2;
    }

    public int GetStudentCount(int deptId)
    {
        // Execute queries either by Database1 or 2.
    }
 }

Database class used is from Microsoft.Practices.EnterpriseLibrary.Data. How can I avoid creating multiple Singleton classes for different connection strings?

Any help?

Your database class could have a constructor with a IEnumerable<string> connectionStrings parameter and internally handle which connection each query should be launched against. — Cleptus
– Cleptus, Commented Apr 4, 2019 at 14:21
@bradbury9: This Database class is not created by me it is from Microsoft.Practices.EnterpriseLibrary.Data library. — Ritesh
– Ritesh, Commented Apr 4, 2019 at 14:24

Marko Papic · Accepted Answer · 2019-04-04 18:00:48Z

1

You can keep your connection strings as an array in your appsettings.json:

{
  ...
  "ConnectionStrings": [
    {
      "Name": "ConnectionString1",
      "Value":  "some value"
    },
    {
      "Name": "ConnectionString1",
      "Value": "some value"
    }
  ]
}

and map them to some class using Options pattern:

public class ConnectionStringOptions
{
    public ConnectionString[] ConnectionStrings { get; set; }
}

public class ConnectionString
{
    public string Name { get; set; }
    public string Value { get; set; }
}

And then, you can have an interface like this one:

public interface IDatabaseProvider
{
    IEnumerable<Database> GetDatabases();
    Database GetDatabase(string name);
}

with the implementation like this

public class DatabaseProvider : IDatabaseProvider
{
    private readonly ConnectionStringOptions _options;

    public DatabaseProvider(IOptions<ConnectionStringOptions> optionsAccessor)
    {
        this._options = optionsAccessor.Value;
    }

    public IEnumerable<Database> GetDatabases()
    {
        foreach (ConnectionString connectionString in this._options.ConnectionStrings)
            yield return new SqlDatabase(connectionString.Value);
    }

    public Database GetDatabase(string name)
    {
        string connectionString = this._options.ConnectionStrings.SingleOrDefault(x => x.Name == name).Value;
        return new SqlDatabase(connectionString);
    }
}

Now you just register the IDatabaseProvider:

serviceCollection.AddTransient<IDatabaseProvider, DatabaseProvider>()

and inject it in your services as needed. E.g:

public class StudentApp
{
    private readonly IEnumerable<Database> _databases;

    public StudentApp(IStudentDataAccess dataAccess, IDatabaseProvider databasesProvider)
    {
        //Or get just the one you want by name
        this._databases = databasesProvider.GetDatabases();

        // ...
    }

    // ...
}

Update: Code snippets for Options pattern:

serviceCollection.Configure<ConnectionStringOptions>(configuration.GetSection("ConnectionStrings”));

edited Apr 4, 2019 at 18:00

answered Apr 4, 2019 at 15:13

Marko Papic

1,95514 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ritesh Over a year ago

You haven't configured it yet in the StartUp class it will pass null to the options value in the constructor.

Marko Papic Over a year ago

@Ritesh I mentioned that it needs to be configured using the Options Pattern. I don't know the exact structure of your appsettings.json file, so I couldn't provide the code snipped for that part.

Collectives™ on Stack Overflow

Injecting multiple connection strings in constructor c#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related