Entity Framework change connection at runtime

Question

I have a web API project which references my model and DAL assemblies. The user is presented with a login screen, where he can select different databases.

I build the connection string as follows:

    public void Connect(Database database)
    {
        //Build an SQL connection string
        SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
        {
            DataSource = database.Server,
            InitialCatalog = database.Catalog,
            UserID = database.Username,
            Password = database.Password,
        };

        //Build an entity framework connection string
        EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
        {
            Provider = database.Provider,
            Metadata = Settings.Default.Metadata,
            ProviderConnectionString = sqlString.ToString()
        };
    }

First of all, how do I actually change the connection of the data context?

And secondly, as this is a web API project, is the connection string (set at login per above) persistent throughout the user's interaction or should it be passed every time to my data context?

Answer 1

string _connString = "metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string="data source=localhost;initial catalog=DATABASE;persist security info=True;user id=sa;password=YourPassword;multipleactiveresultsets=True;App=EntityFramework"";

EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder(_connString);
ctx = new Entities(_connString);

You can get the connection string from the web.config, and just set that in the EntityConnectionStringBuilder constructor, and use the EntityConnectionStringBuilder as an argument in the constructor for the context.

Cache the connection string by username. Simple example using a couple of generic methods to handle adding/retrieving from cache.

private static readonly ObjectCache cache = MemoryCache.Default;

// add to cache
AddToCache<string>(username, value);

// get from cache

 string value = GetFromCache<string>(username);
 if (value != null)
 {
     // got item, do something with it.
 }
 else
 {
    // item does not exist in cache.
 }


public void AddToCache<T>(string token, T item)
    {
        cache.Add(token, item, DateTime.Now.AddMinutes(1));
    }

public T GetFromCache<T>(string cacheKey) where T : class
    {
        try
        {
            return (T)cache[cacheKey];
        }
        catch
        {
            return null;
        }
    }

Answer 2

DbContext has a constructor overload that accepts the name of a connection string or a connection string itself. Implement your own version and pass it to the base constructor:

public class MyDbContext : DbContext
{
    public MyDbContext( string nameOrConnectionString ) 
        : base( nameOrConnectionString )
    {
    }
}

Then simply pass the name of a configured connection string or a connection string itself when you instantiate your DbContext

var context = new MyDbContext( "..." );

Answer 3

The created class is 'partial'!

public partial class Database1Entities1 : DbContext
{
    public Database1Entities1()
        : base("name=Database1Entities1")
    {
    }

... and you call it like this:

using (var ctx = new Database1Entities1())
      {
        #if DEBUG
        ctx.Database.Log = Console.Write;
        #endif

so, you need only create a partial own class file for original auto-generated class (with same class name!) and add a new constructor with connection string parameter, like Moho's answer before.

After it you able to use parametrized constructor against original. :-)

example:

using (var ctx = new Database1Entities1(myOwnConnectionString))
      {
        #if DEBUG
        ctx.Database.Log = Console.Write;
        #endif

Answer 4

Add multiple connection strings in your web.config or app.config.

Then you can get them as a string like :

System.Configuration.ConfigurationManager.
    ConnectionStrings["entityFrameworkConnection"].ConnectionString;

Then use the string to set :

Provider
Metadata
ProviderConnectionString

It is better explained here :

Read connection string from web.config

Answer 5

I wanted to have multiple datasources in the app config. So after setting up a section in the app.config i swaped out the datasource and then pass it into the dbcontext as the connection string.

//Get the key/value connection string from app config  
var sect = (NameValueCollection)ConfigurationManager.GetSection("section");  
var val = sect["New DataSource"].ToString();

//Get the original connection string with the full payload  
var entityCnxStringBuilder = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["OriginalStringBuiltByADO.Net"].ConnectionString);     

//Swap out the provider specific connection string  
entityCnxStringBuilder.ProviderConnectionString = val;

//Return the payload with the change in connection string.   
return entityCnxStringBuilder.ConnectionString;

This took me a bit to figure out. I hope it helps someone out. I was making it way too complicated. before this.

Answer 6

In my case I'm using the ObjectContext as opposed to the DbContext so I tweaked the code in the accepted answer for that purpose.

public static class ConnectionTools
{
    public static void ChangeDatabase(
        this ObjectContext source,
        string initialCatalog = "",
        string dataSource = "",
        string userId = "",
        string password = "",
        bool integratedSecuity = true,
        string configConnectionStringName = "")
    {
        try
        {
            // use the const name if it's not null, otherwise
            // using the convention of connection string = EF contextname
            // grab the type name and we're done
            var configNameEf = string.IsNullOrEmpty(configConnectionStringName)
                ? Source.GetType().Name
                : configConnectionStringName;

            // add a reference to System.Configuration
            var entityCnxStringBuilder = new EntityConnectionStringBuilder
                (System.Configuration.ConfigurationManager
                    .ConnectionStrings[configNameEf].ConnectionString);

            // init the sqlbuilder with the full EF connectionstring cargo
            var sqlCnxStringBuilder = new SqlConnectionStringBuilder
                (entityCnxStringBuilder.ProviderConnectionString);

            // only populate parameters with values if added
            if (!string.IsNullOrEmpty(initialCatalog))
                sqlCnxStringBuilder.InitialCatalog = initialCatalog;
            if (!string.IsNullOrEmpty(dataSource))
                sqlCnxStringBuilder.DataSource = dataSource;
            if (!string.IsNullOrEmpty(userId))
                sqlCnxStringBuilder.UserID = userId;
            if (!string.IsNullOrEmpty(password))
                sqlCnxStringBuilder.Password = password;

            // set the integrated security status
            sqlCnxStringBuilder.IntegratedSecurity = integratedSecuity;

            // now flip the properties that were changed
            source.Connection.ConnectionString
                = sqlCnxStringBuilder.ConnectionString;
        }
        catch (Exception ex)
        {
            // set log item if required
        }
    }
}