Entity Framework changes connection at runtime

Question

I have a web API project that references my model and DAL assemblies. The user gets 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 change the data context connection?

And secondly, since this is a web API project, is the connection string (set when I logged in above) constant during user interaction, or should it be passed each time to my data context?

Answer 1

A bit late in this answer, but I think there is a potential way to do this using a neat extension method. We can use the EF configuration convention and a few small infrastructure calls.

In any case, the code with comments and examples:

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 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 } } } 

base use:

 // 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 that you already have some basic features, but thought it would add a bit of variety.

Answer 2

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

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

Then just pass the name of the configured connection string or the connection string itself when you instantiate the DbContext

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

Answer 3

The answer to Jim Tollan works fine, but I got an error: the keyword is not supported by the "data source". To solve this problem, I had to change this part of its code:

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

:

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

I'm sorry. I know that I should use the answers to respond to other answers, but my answer is too long for a comment :(

Answer 4

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 

therefore, you only need to create a partial native class file for the original automatically generated class (with the same class name!) and add a new constructor with the connection string parameter, for example, before Moho answers.

After that, you can use the parameterized constructor against the original. :-)

Example:

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

Answer 5

Add some connection strings to your web.config or app.config file.

Then you can get them as a string, for example:

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

Then use the line to install:

 Provider Metadata ProviderConnectionString 

Here's a better explanation:

Read connection string from web.config

Answer 6

 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 web.config and simply set this in the EntityConnectionStringBuilder constructor and use EntityConnectionStringBuilder as an argument in the context constructor.

Download the connection string by username. A simple example using several common cache add / retrieve processing methods.

 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 7

In my case, I am using an ObjectContext and not a DbContext, so I configured the code in the accepted answer for this 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 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 } } } 

Answer 8

I wanted to have several data sources in the application configuration. Therefore, after setting up the section in app.config, I replaced the data source and then passed it to 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; 

It took me a bit to figure out. Hope this helps someone. I did it too hard. before.

Answer 9

I have two extension methods for converting a regular connection string to the Entity Framework format. This version works well with class library projects without copying the connection strings from the app.config file to the main project. This is VB.Net, but easy to convert to C #.

 Public Module Extensions <Extension> Public Function ToEntityConnectionString(ByRef sqlClientConnStr As String, ByVal modelFileName As String, Optional ByVal multipleActiceResultSet As Boolean = True) Dim sqlb As New SqlConnectionStringBuilder(sqlClientConnStr) Return ToEntityConnectionString(sqlb, modelFileName, multipleActiceResultSet) End Function <Extension> Public Function ToEntityConnectionString(ByRef sqlClientConnStrBldr As SqlConnectionStringBuilder, ByVal modelFileName As String, Optional ByVal multipleActiceResultSet As Boolean = True) sqlClientConnStrBldr.MultipleActiveResultSets = multipleActiceResultSet sqlClientConnStrBldr.ApplicationName = "EntityFramework" Dim metaData As String = "metadata=res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl;provider=System.Data.SqlClient;provider connection string='{1}'" Return String.Format(metaData, modelFileName, sqlClientConnStrBldr.ConnectionString) End Function End Module 

After that, I create an incomplete class for the DbContext:

 Partial Public Class DlmsDataContext Public Shared Property ModelFileName As String = "AvrEntities" ' (AvrEntities.edmx) Public Sub New(ByVal avrConnectionString As String) MyBase.New(CStr(avrConnectionString.ToEntityConnectionString(ModelFileName, True))) End Sub End Class 

Request creation:

 Dim newConnectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DB;Persist Security Info=True;User ID=sa;Password=pass" Using ctx As New DlmsDataContext(newConnectionString) ' ... ctx.SaveChanges() End Using 

Answer 10

 Linq2SQLDataClassesDataContext db = new Linq2SQLDataClassesDataContext(); var query = from p in db.SyncAudits orderby p.SyncTime descending select p; Console.WriteLine(query.ToString()); 

try this code ...