2

My ASP.Net MVC application has to connect to multiple databases at run time. I can overload my class to accept the connection string at run time as shown below

class MyClassDBContext:DbContext
{
  public MyClassDBContext(string str) : base(str)
  {
    this.Database.Connection.ConnectionString = str;
  }
}

Currently, I am retrieving this connection string from a database table. My workflow is as follows

  1. Website connects to default database using credentials stored in web.config
  2. Website queries default database to get connection strings for other databases.
  3. Websites connects to other databases by supplying the connection string at run time

The problem I facing right now is in keeping my code clean. Every time I need the connection string for database number 2, I have to look it up in the default database. Is there any cleaner way of doing this? I considered storing the connection string in the profile data but I am not sure if this is a good idea. Every user of my website will need to connect to at most 2-3 different databases depending on their credentials.

Improve this question
4
  • 4
    Why not define all connect strings in your web.config? Commented Jul 23, 2012 at 18:39
  • 1
    @m0s - Different clients connect to different databases. I am thinking ahead and felt that the web.config would get cluttered as my clients grow in number. Commented Jul 23, 2012 at 18:53
  • Make sure you're encypting the connection strings stored in the default database... BTW, I'm doing the exact same thing with a desktop app of mine and went through the same phase of wondering if there is a better way but could not come up with one so I'd love to see if anyone can post a better solution... Commented Jul 23, 2012 at 19:03
  • @ Dean K - Yes, I have already encrypted my connection strings. Thanks for adding that as a comment since it is a very valuable step that many developers skip Commented Jul 23, 2012 at 19:21

2 Answers 2

Reset to default
2

I would personally put all connection strings in your App.Config file and use a simple IOC implementation.

Actually the ninject package off Nuget might be perfect for your needs.

Here's what I mean though. Hopefully this makes your code clean. I used this exact same pattern for a previous project and it worked out well.

You could take it a step further and make a Service Locator and register services in your global.asax. Let me know if that interests you. Also check out ninject.

public interface IService() 
{ 
  string GetConnectionString(); 
  void DoStuff(); 
}

public class DBServiceOne : DbContext, IService
{
  protected string GetConnectionString() 
  {
    return ConfigurationManager.AppSettings["DBServiceOneConnectionString"]
  }

  public DBServiceOne(string str) : base(str)
  {
     this.Database.Connection.ConnectionString = GetConnectionString()
  }

   public void DoStuff() { //logic goes here }
}

public class DBServiceTwo : DbContext, IService
{

    public DBServiceTwo(string str) : base(str)
    {
      this.Database.Connection.ConnectionString = GetConnectionString();
    }


    protected string GetConnectionString() 
    {
      return ConfigurationManager.AppSettings["DBServiceTwoConnectionString"]
    }

    public void DoStuff() { //logic goes here }
}

public class DBServiceThree : DbContext, IService
{

   public DBServiceThree(string str) : base(str)
   {
     this.Database.Connection.ConnectionString = GetConnectionString();
   }

   protected string GetConnectionString() 
   {
     return ConfigurationManager.AppSettings["DBServiceThreeConnectionString"]
   }

   public void DoStuff() { //logic goes here }
}

Now for the implementation -- Use Constructor Injection on your controllers

//This could be in your home controller

public class HomeController : AsyncController
{
    private IService DBOneService;
    private IService DBTwoService;
    private IService DBThreeService;

   public HomeController(IService one, IService two, IService three)
   {
      DBOneService= one;
      DBTwoService = two;
      DBThreeService = three;
   }

  public HomeController() : this(new DBServiceOne(), new DBServiceTwo(), new DBServiceThree()) {}

public ActionResult Index() {
   DBOneService.DoStuff(); //here you'd want to return a list of data and serialize down with json or populate your razor template with it. Hope this helps!

}
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for the detailed code. I am not sure if this reduces my workflow since I still have to lookup the default database each time to check which secondary database the user has to be connected to. Maybe I can create a hybrid using your code along with mine. I can store the database name in the user profile and then lookup the connection string from the config file. I will wait to see if anyone else has a better idea. If not, I will accept your solution as the answer.
Why are the db connection strings in the database in the first place? Can you join all db's into one database?
So what happens when you need to change the connection string? Simple update of the app.config will not be sufficient...
@David Johnson - Unfortunately, the decision is not mine to make. The business rules for the app I am working on needs each customer to have their own database. These databases can also be in different locations depending on the customer's preference for hosting. That is why I am stuck with connection strings stored in a table. I should also mention that all these databases have the same tables in them. The only difference is in the stored data
Having all the databases have the same exact tables is not necessarily a bad thing for this pattern. The queries can be the exact same. If you don't like the implementation or want to take it to one level of abstraction make an abstract base class and have it implement the interface. Make all the DBService classes inherit that and then override them. Actually, you could make it an abstract class with virtual methods too. So this way you could provide a default implementation, that's the route I'd go. If you want me to show you just let me know.
0

I had a slightly different problem. The DB I connect to depends on the state of a product import. During the import databases get attached and detached. The currently available db is stored in a "default database".

The main problem I had was that I had to switch off connection pooling, otherwise invalid connection states existed after detaching databases and attaching them again.

This is might not be a problem for you.

Apart from that I store the current Connectionstring in the application state. Only after each 60 seconds I query the "default database" again. You have to watch out for multithreading issues by using locking.

Improve this answer

Comments