Entity Framework/Entity Framework Core dynamic connection strings (C#)
Introduction
Learn how to setup Entity Framework 6 and Entity Framework Core connections for desktop applications for different environments encrypted and unencrypted connection strings.
- All code presented is with SQL-Server. None of the connection methods are tied to SQL-Server so it will work with all Entity Framework providers.
- Before running code samples
- Run the following script to create and populate the database in Visual Studio or SSMS (SQL-Server Management Studio). Open the script and inspect the path where the database will be created as different version of SQL-Server will be install in different paths.
- With the Visual Studio solution open and viewing Solution Explorer right click on the top node and select restore NuGet packages if not using VS2019 which should auto restore packages.
- Code First from existing database is used for both flavors of Entity Framework.
Strategy
Since it is a bad idea to have a single database for development, staging and production there should be an environment for development, staging and production.
For a developer working on a project environments may be setup for one server and three mirrored databases. For example, SQL-Server Express edition is used with a database named Inventory, the development database would be named InventoryDev, staging InventoryStaging and production Inventory.
For a developer working on a project environments in a company usually have separate servers for each environment. This means the database will have the same name on each server while the database name is the same.
Dependent on which above case is used connection strings may be placed into a project’s application’s configuration file (app.config) then with some code and a conditional compilation symbol (under project properties build tab) access the proper connection string for where the application will be deployed.
The last step is to have instructions for deployment and changing environments, not simply for connection strings but any other configuration items and a rollback plan in the event the deployment fails or there is a bug in the application code.
Entity Framework 6
The connection string is stored in app.config under connectionStrings and accessed in the new constructor of the DbContext class.
using EntityFramework6Library.Models;
namespace EntityFramework6Library
{
using System.Data.Entity;
public partial class NorthWindContext : DbContext
{
public NorthWindContext() : base("name=NorthWindContext")
{
}
public virtual DbSet<Contact> Contacts { get; set; }
public virtual DbSet<ContactType> ContactTypes { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
}
}
}
For handling multiple environments modify the above class to have a conditional constructor as shown below. Next, add a connection string for each environment as shown in the following configuration file.
using EntityFramework6Library.Models;
namespace EntityFramework6Library
{
using System.Data.Entity;
public partial class NorthWindContext : DbContext
{
#if Dev
public NorthWindContext() : base("name=DevConnection")
#elif Staging
public NorthWindContext() : base("name=StagingConnection")
#else
public NorthWindContext() : base("name=ProductionConnection")
#endif
{ }
public virtual DbSet<Contact> Contacts { get; set; }
public virtual DbSet<ContactType> ContactTypes { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
}
}
}
Set a conditional compilation symbol (shown below is for the development environment).
Figure 1
Entity Framework Core 5 and higher
Using the following NuGet package EntityFrameworkCoreHelpers provides extension methods for connecting to a SQL-Server database using the following appsettings.json file.
{
"ConnectionsConfiguration": {
"ActiveEnvironment": "Production",
"Development": "Dev connection string goes here",
"Stage": "Stage connection string goes here",
"Production": "Prod connection string goes here"
}
}
There are three methods, the first to connect to a database, the second to write log information to Visual Studio output window and the third, write log information to a file.
public class DbContextConnections
{
/// <summary>
/// Simple configuration for setting the connection string
/// </summary>
/// <param name="optionsBuilder"></param>
public static void NoLogging(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConfigurationHelper.ConnectionString());
}
/// <summary>
/// Default logging to output window
/// </summary>
public static void StandardLogging(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConfigurationHelper.ConnectionString())
.EnableSensitiveDataLogging()
.LogTo(message => Debug.WriteLine(message));
}
/// <summary>
/// Writes/appends to a file
/// Make sure that the folder exists, as coded folder name is Logs under the app folder.
/// One way to ensure the folder exists is to use MsBuild task MakeDir as in
/// the test project ShadowPropertiesUnitTestProject.
/// </summary>
public static void CustomLogging(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConfigurationHelper.ConnectionString())
.EnableSensitiveDataLogging()
.LogTo(new DbContextLogger().Log)
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
/// <summary>
/// Writes/appends to a file
/// </summary>
/// <param name="optionsBuilder"></param>
/// <param name="fileName">File name to write log data too</param>
public static void CustomLogging(DbContextOptionsBuilder optionsBuilder, string fileName)
{
optionsBuilder.UseSqlServer(ConfigurationHelper.ConnectionString())
.EnableSensitiveDataLogging()
.LogTo(new DbContextLogger(fileName).Log)
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
}
Example for a simple connection in a DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
DbContextConnections.NoLogging(optionsBuilder);
}
}
Write to Visual Studio output window
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
DbContextConnections.StandardLogging(optionsBuilder);
}
}
Write to a log file
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
DbContextConnections.CustomLogging(optionsBuilder);
}
}
Entity Framework Core (version 3 and below)
By default the connection is setup as follows, hard code unlike Entity Framework Core
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
optionsBuilder.UseSqlServer("Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWind2020;Integrated Security=True");
Even with a different in how a connection is configured what has been done with EF6 (Entity Framework 6) can be applied to EF Core. Going with the standard connection string above the line is replaced with the following.
Notes:
- For those familiar with a DbContext class figure 2 is for EF Core
- Note the third using statement, this requires a reference to System.Configuration.
Figure 2
using Microsoft.EntityFrameworkCore;
using EntityFrameworkCoreLibrary.Models;
using static System.Configuration.ConfigurationManager;
namespace EntityFrameworkCoreLibrary.Contexts
{
public partial class NorthWindContext : DbContext
{
public NorthWindContext()
{
}
public NorthWindContext(DbContextOptions<NorthWindContext> options)
: base(options)
{
}
public virtual DbSet<ContactType> ContactType { get; set; }
public virtual DbSet<Contacts> Contacts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
var environment = "";
#if Dev
environment = "DevConnection";
#elif Staging
environment = "StagingConnection";
#else
environment = "ProductionConnection";
#endif
optionsBuilder.UseSqlServer(AppSettings[environment]);
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new ContactTypeConfiguration());
modelBuilder.ApplyConfiguration(new ContactsConfiguration());
OnModelCreatingPartial(modelBuilder);
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
}
The app.config file a section for appSetting is added with connection strings for each environment which can be seen here. Next set the environment as done in figure 1 for EF6.
Entity Framework Core with encryption
The exact same logic is used as the last example except the following class is used to encrypt and decrypt the connection string.Contains two methods, one to encrypt, one to decrypt a string used to secure a connection string for Entity Framework Core code first.
The level of encrypt/decryption used here was picked at random, there are less and more secure methods, what a developer selects should be dependent on if they believe someone will be able to hack the application or not.
See encrypted connection in the app.config file.
VB.NET coding
For those working with EF Core there is an example to follow which works the same as C# examples above.
Summary
Code has been presented to allow a developer to setup Entity Framework 6 and Entity Framework Core to work in development, staging and production along with securing connection strings from prying people poking around.
Note that even with encryption user names and password should not be stored, instead a better idea is to use active directory and/or creating users and roles in SQL-Server.
Don't care for conditional compiling for different environments, an alternative is to setup a static class with logic to deal with different environments which will be presented in a future article. To get an idea of the basics see ApplicationSettings class.
See also
- .NET Core desktop application configuration C# (for EF Core 4)
- Entity Framework Core 3.x database connection
- Entity Framework Core 3: projections
- Entity Framework Core/Windows Forms tips and tricks
Source code
The following GitHub repository contains all code for this article.