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

User · Answer

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

User · Answer

I have two extension methods to convert the normal connection string to the Entity Framework format  This version working well with class library projects without copying the connection strings from app config file to the primary project  This is VB Net but easy to convert to C    Public Module Extensions       lt Extension gt      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       lt Extension gt      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 a partial class for 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   Creating a query   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For both SQL Server and SQLite Databases  use   sqlServerDBsContext   new SqlServerDBsContext new DbContextOptionsBuilder lt SqlServerDBsContext gt    UseSqlServer  quot Connection String to SQL DB quot   Options     For SQLite  make sure Microsoft EntityFrameworkCore Sqlite is installed  then the connection string is simply  quot  DataSource    the file name quot     sqliteDBsContext   new SqliteDBsContext new DbContextOptionsBuilder lt SqliteDBsContext gt    UseSqlite  quot Connection String to SQLite DB quot   Options

User · Answer

string  connString    metadata res     Model csdl res     Model ssdl res     Model msl provider System Data SqlClient provider connection string  amp quot data source localhost initial catalog DATABASE persist security info True user id sa password YourPassword multipleactiveresultsets True App EntityFramework amp quot     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 lt string gt  username  value       get from cache   string value   GetFromCache lt string gt  username    if  value    null             got item  do something with it      else           item does not exist in cache       public void AddToCache lt T gt  string token  T item                cache Add token  item  DateTime Now AddMinutes 1           public T GetFromCache lt T gt  string cacheKey  where T   class               try                       return  T cache cacheKey                     catch                       return null

User · Answer

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

User · Answer

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

User · Answer

Linq2SQLDataClassesDataContext db   new Linq2SQLDataClassesDataContext     var query   from p in db SyncAudits orderby p SyncTime descending select p  Console WriteLine query ToString       try this code

User · Answer

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

[c#] Entity Framework change connection at runtime

Examples related to c#

Examples related to entity-framework

Examples related to asp.net-web-api

Examples related to connection-string