EF6 DBContext Dynamic Connection String
Solution 1
For SQL Server connection, override the entity container class: (works in EF6, SQL server 2012 express, VS2013)
public partial class PxxxxEntities
{
private PxxxxEntities(string connectionString)
: base(connectionString)
{
}
public static PxxxxEntities ConnectToSqlServer(string host, string catalog, string user, string pass, bool winAuth)
{
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder
{
DataSource = host,
InitialCatalog = catalog,
PersistSecurityInfo = true,
IntegratedSecurity = winAuth,
MultipleActiveResultSets = true,
UserID = user,
Password = pass,
};
// assumes a connectionString name in .config of MyDbEntities
var entityConnectionStringBuilder = new EntityConnectionStringBuilder
{
Provider = "System.Data.SqlClient",
ProviderConnectionString = sqlBuilder.ConnectionString,
Metadata = "res://*/DbModel.csdl|res://*/DbModel.ssdl|res://*/DbModel.msl",
};
return new PxxxxEntities(entityConnectionStringBuilder.ConnectionString);
}
}
Solution 2
You should pass an ordinary connection string into the the DbContext
constructor, not an entity connection string. So try changing your code as follows:
public ProcessContext()
: base(ConnectionString())
{
}
private static string ConnectionString()
{
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
sqlBuilder.DataSource = "XXX";
sqlBuilder.InitialCatalog = "YYY";
sqlBuilder.PersistSecurityInfo = true;
sqlBuilder.IntegratedSecurity = true;
sqlBuilder.MultipleActiveResultSets = true;
return sqlBuilder.ToString();
}
Solution 3
You have to change Web.config
<connectionStrings>
<add name="DefaultConnection" connectionString="data source=mydb;initial catalog=mydatabase;persist security info=True;user id=sa;password=password;multipleactiveresultsets=True;application name=EntityFramework" providerName="System.Data.SqlClient" />
<add name="DataContext" connectionString="data source=mydb;initial catalog=mydatabase;persist security info=True;user id=sa;password=password;multipleactiveresultsets=True;application name=EntityFramework" providerName="System.Data.SqlClient" />
<!-- Here Add multiple database connection string -->
after you have to modify ProcessContext.cs file
one constructor to create default connection
public ProcessContext()
:base("name=DefaultConnection"){
//here code
}
other parametrise constructor dynamic connection to database
public ProcessContext(string DynamicConnectionString)
:base(DynamicConnectionString){
// herer code }
here to default connection is "DefaultConnection" but you have chnage connection string given code any controller
ProcessContext db=new ProcessContext();//this is default connection
ProcessContext db=new ProcessContext("DataContext");//dynamic change connection string
try this code
Solution 4
This method worked well for me. I just added another named connection string to my app.config and then passed the name into the below GetDbContext() static method as shown below.
Example of usage:
var dbAlternate = PxxxxEntities.GetDbContext("PxxxxEntitiesAlternate")
Add the following to your project:
public partial class PxxxxEntities
{
private PxxxxEntities(string name)
: base("name=" + name)
{
}
public static PxxxxEntities GetDbContext(string name)
{
return new PxxxxEntities(name);
}
}
Solution 5
Try to use an app.config file. Then call the desired connection string by its name:
using System.Configuration;
public ProcessContext()
: base(ConnectionString("foo"))
{
}
private static string ConnectionString(string connKey)
{
var conn = ConfigurationManager.ConnectionStrings[connKey].ConnectionString;
return conn;
}
Related videos on Youtube
Gautham Nayak
Updated on January 13, 2020Comments
-
Gautham Nayak over 4 years
public partial class ProcessContext : DbContext { static ProcessContext() { Database.SetInitializer<ProcessContext>(null); } public ProcessContext() : base("Name=ProcessCS") //Comes from Config File { } --DBSets protected override void OnModelCreating(DbModelBuilder modelBuilder) { --Code } }
This is a Multi Tenent DB where we have 3 Different DB's. Centralized DB is in common location and would not be changed. This is where rest of the DB details will be stored. I need to create the Connection string @ runtime where the details will be coming from this centralized DB. Can some one please let me know how to go about it?
I tried with the following code, but it is not working. This Method will be called here
public ProcessContext() : base(nameOrConnectionString: ConnectionString()) { } private static string ConnectionString() { SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(); sqlBuilder.DataSource = "XXX"; sqlBuilder.InitialCatalog = "YYY"; sqlBuilder.PersistSecurityInfo = true; sqlBuilder.IntegratedSecurity = true; sqlBuilder.MultipleActiveResultSets = true; EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(); entityBuilder.ProviderConnectionString = sqlBuilder.ToString(); entityBuilder.Metadata = "res://*/"; entityBuilder.Provider = "System.Data.SqlClient"; return entityBuilder.ToString(); }
-
Steven V over 10 years"But it is not working" - what doesn't work about it? Do you get an error message?
-
phil soady about 9 yearssolution on how to use dynamic contexts stackoverflow.com/a/16133150/1347784
-
Sarthak Shah over 6 years@StevenV When I tried above code I am getting an error saying "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication". What could be the possible solution for it?
-
Sarthak Shah over 6 yearsThanks a lot, It is working fine. I found a little bit mistake in my code.
-
-
Gautham Nayak over 10 years@luskan can you please help me out with this question as well.. stackoverflow.com/questions/21181253/…
-
Tim Butterfield about 9 yearsThe above certainly worked for me and saved me a lot of pain.
-
Harvey Darvey almost 8 yearsThis is a nice solution. However, just wondering, the "MultipleActiveResultSets = true" part, is it advisable for EF6? msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx
-
E. Zeytinci over 4 yearsWhile this code may provide a solution to OP's problem, it is highly recommended that you provide additional context regarding why and/or how this code answers the question. Code only answers typically become useless in the long-run because future viewers experiencing similar problems cannot understand the reasoning behind the solution.