Entity Framework change connection at runtime
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?
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.
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( "..." );
Jim Tollan's answer works great, but I got the Error: Keyword not supported 'data source'. To solve this problem I had to change this part of his code:
// add a reference to System.Configuration
var entityCnxStringBuilder = new EntityConnectionStringBuilder
(System.Configuration.ConfigurationManager
.ConnectionStrings[configNameEf].ConnectionString);
to this:
// add a reference to System.Configuration
var entityCnxStringBuilder = new EntityConnectionStringBuilder
{
ProviderConnectionString = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager
.ConnectionStrings[configNameEf].ConnectionString).ConnectionString
};
I'm really sorry. I know that I should't use answers to respond to other answers, but my answer is too long for a comment :(
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