Click here to Skip to main content
15,867,771 members
Articles / Programming Languages / C#
Tip/Trick

Changing Databases at Run-time using Entity Framework

Rate me:
Please Sign up or sign in to vote.
5.00/5 (15 votes)
18 Jul 2014CPOL3 min read 61.2K   2K   22   5
How to change databases at run-time in a user-friendly manner.

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:

C#
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:

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

C#
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:

C#
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:

C#
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:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
Matthew Givens has been a software developer since 1988, and has worked for numerous companies over the years. He currently works in the healthcare industry, developing with C# .NET and Sql-Server.

Comments and Discussions

 
Questionhow use in generic repository and unit of work Pin
Member 1484214724-May-20 5:12
Member 1484214724-May-20 5:12 
Questionthanks Pin
Mohammed Davari7-Jan-15 20:07
Mohammed Davari7-Jan-15 20:07 
QuestionSomething that might interest you. Pin
Motor Viper20-Jul-14 23:09
professionalMotor Viper20-Jul-14 23:09 
AnswerRe: Something that might interest you. Pin
Matthew Givens21-Jul-14 11:41
Matthew Givens21-Jul-14 11:41 
GeneralRe: Something that might interest you. Pin
Pragmateek22-Jul-14 0:52
professionalPragmateek22-Jul-14 0:52 
Yes but what Motor Viper means is that the inner connection string "connString" could be built using the SqlConnectionStringBuilder instead of using raw string concatenation.
It works the same way as the EntityConnectionStringBuilder.

By the way excellent work, thanks for sharing. Smile | :)

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.