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?

Answer 1

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