Using Multiple Database Types in an ASP.NET Core App

December 4, 2019 at 2:53 pm Leave a comment

Sometimes, you might want to use one type of database, like MS SQL Server LocalDB, for development and a different type, like MySQL, on a production server. I’ll walk you through writing the code to use different database providers for Entity Framework (EF) depending on your environment: development or production.

Modifying Startup.cs

The database context (a class derived for DBContext) for your app is typically created by a service that is initialized in the Setup class. You probably have some code like this:

public void ConfigureServices(IServiceCollection services) {
   // Some code omitted for brevity 
   services.AddMvc();
   services.AddDbContext<AppDbContext>(options => options.UseSqlServer(
      Configuration["ConnectionStrings:MsSqlConnection"]));

}

The AddDbContext method above is called to configuring the AppDbContext to use an MS SQL Server database provider. We want to change this so that the database provider above is only loaded when the app is running on a development machine, and a MySQL provider is loaded when running on a production server. We do this by changing the code as shown below. The changes are shown in bold.

public class Startup {
   private IHostingEnvironment environment;
   public IConfiguration Configuration { get; }

   // constructor
   public Startup(IConfiguration configuration, IHostingEnvironment env) {
      Configuration = configuration;
      environment = env;
   }

   public void ConfigureServices(IServiceCollection services) {
      services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
      // Some code omitted for brevity
       if (environment.IsDevelopment()) {
          services.AddDbContext<AppDbContext>(options => options.UseSqlServer(
             Configuration["ConnectionStrings:MsSqlConnection"]));
      } else if (environment.IsProduction()) {
          services.AddDbContext<AppDbContext>(options => options.UseMySql(
              Configuration["ConnectionStrings:MySqlConnection"]));
     }
}

The first thing I did was add a parameter for IHostingEnvironment to the Startup constructor. I also made IHostingEnvironment environment a field of the class. This is so that I can determine whether the operating system has the ASPCORENET_ENVIRONMENT variable set to “Production” or “Development”. (To see how to set the environment variable, read “Use Multiple Environments in ASP.NET Core, Set the Environment

Next, I added conditional statements to selectively call services.AddDbContext for whichever database provider is required for the current environment. In order to use options.UseMySql, I had to load the Nuget package for Pomelo.EntityFrameworkCore.MySql

Setting up the MySQL Database

Now you need to set up the MySQL database on your production server. You can do this in the usual way and apply your migrations as you normally would, but there is one caveat. For some reason the Auto Increment property doesn’t get set on the primary key columns in the the MySQL tables, so you will need to use a database manager, like PHP MyAdmin, to set the A_I property of each primary key manually as shown in the image below:

Entry filed under: Programming, C#, Web Development, ASP.NET Core. Tags: , , .

Hosting Multiple ASP.NET Core Sub-Sites on a Single Site Hosting Account

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Bird’s Bits

Computers, software & the Internet

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 40 other followers


%d bloggers like this: