Switch Databases Using EF: Part Deux

Consider you are a company where you want to keep each of your customer’s data in its own database on the same server.  Each of the databases has the identical schema.  For example:

image

In the Data Access Layer, you are using Entity Framework and you want to move between databases at run time.  I blogged about one possible solution to this problem here, but I was thinking about this some more and I have a different (better?) idea. 

The first thing to realize is that EF does not make this easy.  There is not a Contxext.ChangeDatabase(String) method and the constructor does not allow to pass in a connection string.  This tight-coupling smells of poor design, but fortunately this is not insurmountable.

The first step is to create a Model (NorthwindModel in this case).  When you inspect the context, you can see that Microsoft made the entities a partial class

image

So you can add a class to your project like so:

  1. public partial class NorthwindEntities
  2. {
  3.     public NorthwindEntities(String connectionString)
  4.     {
  5.         base.Database.Connection.ConnectionString = connectionString;
  6.     }
  7.  
  8. }

 

Then, in your consuming class, you can create a method like this:

  1. internal static String CreateConnectionStringWithCustomDatabase(String baseConnectionString, String database)
  2. {
  3.     SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(baseConnectionString);
  4.     connectionStringBuilder.InitialCatalog = database;
  5.     return connectionStringBuilder.ToString();
  6. }

And then you can switch databases.  Just pass in the default connection string and the new database name like so:

  1. static void Main(string[] args)
  2. {
  3.     Console.WriteLine("Start");
  4.     String defaultConnectionString = "data source=DIXON12;initial catalog=TBD;integrated security=True;MultipleActiveResultSets=True";
  5.     String actualConnectionString = CreateConnectionStringWithCustomDatabase(defaultConnectionString, "Northwind");
  6.  
  7.     using (NorthwindEntities entities = new NorthwindEntities(actualConnectionString))
  8.     {
  9.         foreach (var product in entities.Products)
  10.         {
  11.             Console.WriteLine(product.ProductName);
  12.         }
  13.     }
  14.  
  15.     Console.WriteLine("-End-");
  16.     Console.ReadKey();
  17.  
  18. }

 

And you can change databases on the fly:

image

The ’gotcha’ to realize is that Entity Framework requires that you have a default value in the app/web.config file.  If not, you will get this exception:

image

This smells like too much coupling, but that is a common theme with EF.  So make sure you have a default EF connection string in the .config – even if you don’t even use it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: