locked
How to use multiple connection string in entity framework 6.0 RRS feed

  • Question

  • I have created a Web API using .net framework and using entity framework 6.0. Code based Migration is enabled.

    I have multiple connection strings (suppose 2 connection strings are present in web.config) so that if one server is down then Entity Framework should connect to another server using second connection string.

    Please suggest how can I achieve this?

    public class DatabaseBootstrapper : IDbContextFactory<TestDBContext> { public TestDBContext Create() { // I want some logic to test server is up or not using first connection string if server is not up then use second connection string. TestDBContext dbcontext = null; dbcontext = new TestDBContext(@"Data Source=.\sqlexpress3;Initial Catalog=TestDB;User ID=sa;Password=Password$;Trusted_Connection=False;"); return dbcontext; }

    }

      public class TestDBContext : DbContext
        {
            public DbSet<Nodes> Nodes { get; set; }
            public DbSet<Graphs> Graphs { get; set; }
           
          
            public TestDBContext(string connectionString)
                : base(connectionString)
            {
                           Database.SetInitializer(new MigrateDatabaseToLatestVersion<TestDBContext, Migrations.Configuration>());
            }
    
            protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
            {
                 base.OnModelCreating(modelBuilder);
            }
        }



    Monday, January 16, 2017 10:59 AM

All replies

  • Hi agrawalamit3101,

    We could create a AppSettings to achieve it. we could change the appsettings value to connect different connectionstring. like this:

      <appSettings>
        <add key="Connstr" value="Model2" />
      </appSettings>
    
      <connectionStrings>
        <add name="Model1" connectionString="data source=(localdb)\MSSQLLocalDB;initial catalog=EF20161102;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
        <add name="Model2" connectionString="data source=(localdb)\MSSQLLocalDB;initial catalog=Blogging;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
      </connectionStrings>

    #Rleated DbContext.

    public partial class Model1 : DbContext
        {
            public Model1()
                : base("name="+ ConfigurationManager.AppSettings["Connstr"].ToString() + "")
            {
            }
    
            public virtual DbSet<Blog> Blogs { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
            }
        }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 6:19 AM
  • You can first use general SqlAdapter Code, execute simple test query to test each connection string's connection is success or not in try catch

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

    if any connection success , then pass the success connection string to the constructor of entity framework 

    TestDBContext dbContext = new TestDBContext(theSuccessCOnnString);


    • Edited by www3000 Tuesday, August 29, 2017 7:58 AM
    Tuesday, August 29, 2017 7:58 AM