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

A bit late on this answer but I think there's a potential way to do this with a neat little extension method. We can take advantage of the EF convention over configuration plus a few little framework calls.

Anyway, the commented code and example usage:

extension method class:

public static class ConnectionTools {     // all params are optional     public static void ChangeDatabase(         this DbContext source,         string initialCatalog = "",         string dataSource = "",         string userId = "",         string password = "",         bool integratedSecuity = true,         string configConnectionStringName = "")          /* this would be used if the         *  connectionString name varied from          *  the base EF class name */     {         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.Database.Connection.ConnectionString                  = sqlCnxStringBuilder.ConnectionString;         }         catch (Exception ex)         {             // set log item if required         }     } } 

basic usage:

// assumes a connectionString name in .config of MyDbEntities var selectedDb = new MyDbEntities(); // so only reference the changed properties // using the object parameters by name selectedDb.ChangeDatabase     (         initialCatalog: "name-of-another-initialcatalog",         userId: "jackthelady",         password: "nomoresecrets",         dataSource: @".\sqlexpress" // could be ip address 120.273.435.167 etc     ); 

I know you already have the basic functionality in place, but thought this would add a little diversity.