Changing Databases at Run-time using Entity Framework

• Download example - 2.9 MB

Introduction

When using Entity Framework, the database connection string is stored in the app.config file by default, and the entity object references that to connect to the database. If, for any reason, a user needs to change the database, he can do so simply by editing that file prior to running the program. Most people are satisfied with that, but based upon a recent experience, I found a different way to do it.

In my case, we changed servers and moved the database to a different location, requiring the application configs to be changed on each user's machine in order to get things working again. A few of the users, while editing the database connection string, messed it up by adding or deleting characters that shouldn't have been added or deleted. This led me to develop a user-friendly method of changing the database connection at run-time.

Background

This article assumes the reader understands how to set-up and use entity framework in their applications.

The example code uses the freely available AdventureWorks database, with the entity containing only the Product table.... make sure the AdventureWorks database is downloaded and installed in your instance of SQL-Server. If you don't use SQL-Server or can't get the AdventureWorks database for some reason, you can at least use the example code as a blueprint for doing the same thing on your own database.

Using the Code

So, create an application and set up an entity referencing the AdventureWorks database. At this point, open the Context.cs file, and you should see something like this:

namespace EF_Example
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    public partial class AdventureWorksEntities : DbContext
    {
        public AdventureWorksEntities()
            : base("name=AdventureWorksEntities")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public DbSet<Product> Product { get; set; }
    }
}

Notice the bolded line... that's how the code knows to utilize the connection string in the config file. We can't simply change that line, because this is a generated class and our changes could easily be overwritten. What we need to do is change the class to allow us to pass in a connection string, but to do so in a non-generated file. Note that the AdventureWorksEntities class is a partial, so we add the following to our example program in the program's NameSpace but outside the form class:

public partial class AdventureWorksEntities : DbContext
{
    public AdventureWorksEntities(String connString)
        : base(connString)
    {
    }
}

You'll also need to add the following using statements to the example program.

using System.Data.Entity;
using System.Data.EntityClient;
using System.Data.SqlClient;

At this point, we've modified the entity class to allow us to pass in a connection string for it to use. Now let's create a method to build a connection string:

private String BuildConnectionString(String DataSource, String Database)
{
    // Build the connection string from the provided datasource and database
    String connString = @"data source=" + DataSource + ";initial catalog=" + 
    Database + ";integrated security=True;MultipleActiveResultSets=True;App=EntityFramework;";

    // Build the MetaData... feel free to copy/paste it from the connection string in the config file.
    EntityConnectionStringBuilder esb = new EntityConnectionStringBuilder();
    esb.Metadata = "res://*/AW_Model.csdl|res://*/AW_Model.ssdl|res://*/AW_Model.msl";
    esb.Provider = "System.Data.SqlClient";
    esb.ProviderConnectionString = connString;

    // Generate the full string and return it
    return esb.ToString();
}

Note that the MetaData is specific to your project and can be extracted from the database connection in the config file. Now we simply pass the results of the BuildConnectionString to the entity constructor:

AdventureWorksEntities entities = new AdventureWorksEntities(BuildConnectionString
("<DataSource>", "<Database>"));

That's really all there is to it. How you store and/or enter the data source and database information is up to you. In the example program, I stored the data in application settings, and allowed the user to change them using a very simple pop-up form. You may choose a different method, and that's fine... choose what works for you.

The relevant portions of the main form code now looks like this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Entity;
using System.Data.EntityClient;

namespace EF_Example
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void btnFetchData_Click(object sender, EventArgs e)
        {
            // Instantiate the entity and retrieve the db connection data
            AdventureWorksEntities entities = new AdventureWorksEntities
            (BuildConnectionString(<DataSource>, "AdventureWorks"));
            lblResults.Text = entities.Database.Connection.DataSource + 
            "." + entities.Database.Connection.Database;

            // Query the product table to find how many products are in the inventory
            var prodCount = (from p in entities.Product select p).Count();
            lblResults.Text += ": " + prodCount.ToString() + " products.";
        }

        private String BuildConnectionString(String DataSource, String Database)
        {
            // Build the connection string from the provided datasource and database
            String connString = @"data source=" + DataSource + 
            ";initial catalog=" + Database + 
            ";integrated security=True;MultipleActiveResultSets=True;App=EntityFramework;";

            // Build the MetaData... feel free to copy/paste it from the connection string in the config file.
            EntityConnectionStringBuilder esb = new EntityConnectionStringBuilder();
            esb.Metadata = "res://*/AW_Model.csdl|res://*/AW_Model.ssdl|res://*/AW_Model.msl";
            esb.Provider = "System.Data.SqlClient";
            esb.ProviderConnectionString = connString;

            // Generate the full string and return it
            return esb.ToString();
        }
    }

    public partial class AdventureWorksEntities : DbContext
    {
        public AdventureWorksEntities(String connString)
            : base(connString)
        {
        }
    }
}

What we did here, basically, was to extend the entity class to include a constructor method that would pass a connection string direct instead of a pointer to a connection string in the config file, which is what it does automatically. This gives us an easy and user-friendly way to change the database at run-time, without having to worry about altering the config file in any way.