How to change initial catalog at runtime when using a connection string and dapper

Question

I'm writing an MVC C# application. I use dapper as a lightweight ORM. My connection strings are defined with server and initial catalog, and currently if I need to access a different database I define another connection string, and use Ninject bindings to use a particular connection string based on the manager i'm injecting it into, like so:

public class NinjectBindings : NinjectModule
{
    public override void Load()
    {
        Bind<IDbConnection>().To<SqlConnection>()
           .WhenInjectedInto<DashboardManager>()
           .InRequestScope()
           .Named("myDashboard")
           .WithConstructorArgument("connectionString", ConfigurationManager.ConnectionStrings["dbDashboard"].ConnectionString);

        Bind<IDbConnection>().To<SqlConnection>()
           .WhenInjectedInto<ScoreboardManager>()
           .InRequestScope()
           .Named("myScoreboard")
           .WithConstructorArgument("connectionString", ConfigurationManager.ConnectionStrings["dbScoreboard"].ConnectionString);

    }
}

Unfortunately this doesn't work if I have code in the same Manager that needs to call stored procedures that are on different databases than the initially specified catalog.

Question is: Can I just define one connection string, lose all the ninject binding stuff above, and simply change the Initial Catalog to a point to a different database on the fly?

Hooman Bahreini · Accepted Answer · 2020-04-23T22:25:53.127

Do you need both Named and WhenInjectedInto constraints for your bindings?

I believe you have a class that requires both connectionstrings, this could be achieved using Named binding:

Bind<IDbConnection>().To<SqlConnection>()
   .InRequestScope()
   .Named("myDashboard")
   .WithConstructorArgument("connectionString", ConfigurationManager.ConnectionStrings["dbDashboard"].ConnectionString);

Bind<IDbConnection>().To<SqlConnection>()
   .InRequestScope()
   .Named("myScoreboard")
   .WithConstructorArgument("connectionString", ConfigurationManager.ConnectionStrings["dbScoreboard"].ConnectionString);

And your class can get both connections:

public class ClassWith2DbDependency // <-- I would question this class for SRP violation
{
    private readonly IDbConnection _dashboardConnection;
    private readonly IDbConnection _scoreboardConnection;

    public ClassWith2DBDependency(
        [Named("myDashboard")] IDbConnection dashboardConnection
        [Named("myScoreboard")] IDbConnection scoreboardConnection)
    {
        _dashboardConnection = dashboardConnection;
        _scoreboardConnection = scoreboardConnection;
    }

    public void WriteTo2Dbs()
    {
        // execute dashboard DB procedure
        // execute scoreboard DB procedure
    }
}

Can I just define one connection string, lose all the ninject binding stuff above, and simply change the Initial Catalog to a point to a different database on the fly?

Changing Initial Catalog doesn't affect an existing SqlConnection. It is possible to manage the dependencies yourself, but you still need 2 connectionstrings:

public class ClassWith2DbDependency
{
    public void WriteTo2Dbs()
    {
        var dashboardCon = ConfigurationManager.ConnectionStrings["dbDashboard"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(dashboardCon))
        {
            // execute dashboard DB procedure
        }

        var scoreboardCon = ConfigurationManager.ConnectionStrings["dbScoreboard"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(scoreboardCon))
        {
            // execute scoreboard DB procedure
        }
    }
}

However, I do NOT recommend this approach, the above class violates DI principle, by having Opaque Dependencies.

I haven't seen your code, but it doesn't sound like you are using Repository Pattern? This could be a good option...

Thank you, I used your first solution. Any ideas on how to make the named string generic so I can pass it in somehow before it is DI'd into the manager? [Named("thisStringMightChangeOnTheFly")] IDbConnection dashboardConnection — erikrunia, Apr 27 '20 at 13:40
You can implement something like [this](https://stackoverflow.com/questions/54835484/how-to-change-connectionstrings-at-runtime-for-a-web-api/54836942#54836942), but keep in mind that changing a `connectionString` does not affect an existing connection. — Hooman Bahreini, Apr 27 '20 at 22:31

How to change initial catalog at runtime when using a connection string and dapper

1 Answers1