How to manage variable/multiple ConnectionString (Dev and Prod DbContext) in WPF

Dmtr_Grms 331 Reputation points
2023-10-05T16:20:19.16+00:00

I need to manage in a flexible way my ConnectionString, one for DEV and the other one for PROD in order to provide the connection in all my application when needed (create and modify Tables structure, Add/Modify/Delete Record/Reporting.... Today I'm using that ConnectionString in my DbContext definition:

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Data Source=xxx\SQLEXPRESS;Initial Catalog=XXXdb;Integrated Security=true;MultipleActiveResultSets=true;");
        }


What should be the best way to manage it correctly and safely? Thanks in advance for your answers. I tried with App.config but connectionstring was not provided for my context.

Developer technologies | .NET | Entity Framework Core
Developer technologies | Windows Presentation Foundation
Developer technologies | .NET | Other
{count} votes

Accepted answer
  1. Hui Liu-MSFT 48,681 Reputation points Microsoft External Staff
    2023-10-06T10:00:50.9033333+00:00

    Hi,@Dimitri Garmaise .Welcome Microsoft Q&A. Not sure about your coding environment and the method you are using. You could try to refer to the following code. If you have any questions please let me know.

    App Configuration:

    You can store your connection strings in your WPF application's configuration file (App.config or AppSettings.json). Create separate configurations for development and production environments. Here's an example of how you might structure your App.config file:

      <connectionStrings>
        <add name="DevDbContext" connectionString=..." providerName="System.Data.SqlClient" />
        <add name="ProdDbContext" connectionString="..." providerName="System.Data.SqlClient" />
      </connectionStrings>
    
    

    Create a Factory:

    Implement a factory or service to retrieve the appropriate connection string based on your application's environment. You can use conditional compilation symbols or app settings to determine the environment. Here's an example of a simple factory:

    
    
    public class DbContextFactory
    {
        public static YourDbContext CreateDbContext()
        {
            string connectionString;
    
            #if DEBUG
                connectionString = ConfigurationManager.ConnectionStrings["DevDbContext"].ConnectionString;
            #else
                connectionString = ConfigurationManager.ConnectionStrings["ProdDbContext"].ConnectionString;
            #endif
    
            var optionsBuilder = new DbContextOptionsBuilder<YourDbContext>();
            optionsBuilder.UseSqlServer(connectionString);
    
            return new YourDbContext(optionsBuilder.Options);
        }
    }
    

    Usage:

    Whenever you need to create an instance of your DbContext, use the factory method:

    using (var dbContext = DbContextFactory.CreateDbContext())
    {
        // Use dbContext for database operations
    }
    
    
    

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Bruce (SqlWork.com) 78,236 Reputation points Volunteer Moderator
    2023-10-06T22:27:52.74+00:00

    you just need the logic to get the connection string where you use it.

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
            string connectionString;
    
            #if DEBUG
                connectionString = ConfigurationManager.ConnectionStrings["DevDbContext"].ConnectionString;
            #else
                connectionString = ConfigurationManager.ConnectionStrings["ProdDbContext"].ConnectionString;
            #endif
    
            optionsBuilder.UseSqlServer(connectionString);
    }
    

    its more common to inject a dbcontext.

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.