This content originally appeared on DEV Community and was authored by Farrukh Rehman
Series: From Code to Cloud: Building a Production-Ready .NET Application
By: Farrukh Rehman - Senior .NET Full Stack Developer / Team Lead
LinkedIn: https://linkedin.com/in/farrukh-rehman
GitHub: https://github.com/farrukh1212cs
Source Code Backend : https://github.com/farrukh1212cs/ECommerce-Backend.git
Source Code Frontend : https://github.com/farrukh1212cs/ECommerce-Frontend.git
Introduction
In Lecture 3 we added MySQL (Docker + EF Core) and wired it into our Clean Architecture solution.
In Lecture 4 we’ll add SQL Server as a second provider and make the app switchable at runtime by configuration. You’ll learn:
- how to pull and run SQL Server in Docker,
- how to configure appsettings.json to hold both providers,
- how to implement a single DependencyInjection extension that picks the provider at startup, and
- how to run EF Core migrations for SQL Server (and switch back to MySQL later).
Pull & run SQL Server from Docker Hub
docker pull mcr.microsoft.com/mssql/server:2022-latest
Run the container (works in PowerShell / Bash)
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Admin123!" -p 1433:1433 --name ecommerce-sqlserver -d mcr.microsoft.com/mssql/server:2022-latest
Notes:
- Default admin user: sa
- Use a strong password (example above): Admin123!
- Maps container port 1433 → host 1433
Lets Connect Server using Azure Data Studio
Add / update connection strings in appsettings.json
{
"DatabaseProvider": "SqlServer", // "MySql" or "SqlServer"
"ConnectionStrings": {
"MySQL": "Server=localhost;Port=3306;Database=ECommerceDb;User=root;Password=Admin123!;",
"SqlServer": "Server=localhost,1433;Database=ECommerceDb;User Id=sa;Password=Admin123!;TrustServerCertificate=True;"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*"
}
Now Install SQL Server Package
dotnet add ECommerce.Infrastructure package Microsoft.EntityFrameworkCore.SqlServer --version 8.0.21
Create New MySqlDbContext
Path : "ECommerce.Infrastructure/Data/MySqlDbContext.cs"
using Microsoft.EntityFrameworkCore;
namespace ECommerce.Infrastructure.Data;
public class MySqlDbContext : AppDbContext
{
public MySqlDbContext(DbContextOptions<MySqlDbContext> options)
: base(options)
{
}
}
Create New DBContext for SQL Server
Path : ECommerce.Infrastructure/Data/SqlServerDbContext.cs
using Microsoft.EntityFrameworkCore;
namespace ECommerce.Infrastructure.Data;
public class SqlServerDbContext : AppDbContext
{
public SqlServerDbContext(DbContextOptions<SqlServerDbContext> options)
: base(options)
{
}
}
RENAME and UPDATE DesignTimeDbContextFactory
Path : ECommerce.Infrastructure/Data/DesignTimeDbContextFactory.cs
TO ECommerce.Infrastructure/Data/MySqlDbContextFactory.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
namespace ECommerce.Infrastructure.Data;
public class MySqlDbContextFactory : IDesignTimeDbContextFactory<MySqlDbContext>
{
public MySqlDbContext CreateDbContext(string[] args)
{
// Locate the API project's appsettings.json
var basePath = Path.Combine(Directory.GetCurrentDirectory(), "../ECommerce.API");
var configuration = new ConfigurationBuilder()
.SetBasePath(basePath)
.AddJsonFile("appsettings.json", optional: false)
.AddJsonFile("appsettings.Development.json", optional: true)
.Build();
var connectionString = configuration.GetConnectionString("MySQL")
?? throw new InvalidOperationException("MySQL connection string not found in appsettings.json");
var optionsBuilder = new DbContextOptionsBuilder<MySqlDbContext>();
// Use a fixed version instead of ServerVersion.AutoDetect to avoid connection attempts during design-time
optionsBuilder.UseMySql(
connectionString,
new MySqlServerVersion(new Version(8, 0, 36))
);
return new MySqlDbContext(optionsBuilder.Options);
}
}
Create Data Factory for SQL Server
Path : ECommerce.Infrastructure/Data/SqlServerDbContextFactory.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
namespace ECommerce.Infrastructure.Data;
public class SqlServerDbContextFactory : IDesignTimeDbContextFactory<SqlServerDbContext>
{
public SqlServerDbContext CreateDbContext(string[] args)
{
var basePath = Path.Combine(Directory.GetCurrentDirectory(), "../ECommerce.API");
var configuration = new ConfigurationBuilder()
.SetBasePath(basePath)
.AddJsonFile("appsettings.json", optional: false)
.AddJsonFile("appsettings.Development.json", optional: true)
.Build();
var connectionString = configuration.GetConnectionString("SqlServer")
?? throw new InvalidOperationException("SqlServer connection string not found in appsettings.json");
var optionsBuilder = new DbContextOptionsBuilder<SqlServerDbContext>();
optionsBuilder.UseSqlServer(connectionString);
return new SqlServerDbContext(optionsBuilder.Options);
}
}
Implement multi-provider DependencyInjection (Infrastructure)
Create ECommerce.Infrastructure/DependencyInjection.cs. This extension reads DatabaseProvider and registers ApplicationDbContext (or ECommerceDbContext) with the appropriate provider.
using ECommerce.Infrastructure.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
namespace ECommerce.Infrastructure;
public static class DependencyInjection
{
public static IServiceCollection AddInfrastructure(
this IServiceCollection services,
IConfiguration configuration)
{
var provider = configuration["DatabaseProvider"] ?? "MySQL";
if (string.Equals(provider, "SqlServer", StringComparison.OrdinalIgnoreCase))
{
var conn = configuration.GetConnectionString("SqlServer");
services.AddDbContext<AppDbContext, SqlServerDbContext>(options =>
options.UseSqlServer(conn));
}
else if (string.Equals(provider, "MySQL", StringComparison.OrdinalIgnoreCase))
{
var conn = configuration.GetConnectionString("MySQL");
services.AddDbContext<AppDbContext, MySqlDbContext>(options =>
options.UseMySql(conn, ServerVersion.AutoDetect(conn)));
}
else
{
throw new InvalidOperationException($"Unsupported provider: {provider}");
}
return services;
}
}
Update Program file
using ECommerce.Application.Services.Implementations;
using ECommerce.Application.Services.Interfaces;
using ECommerce.Domain.Repositories;
using ECommerce.Infrastructure.Data;
using ECommerce.Infrastructure.Repositories;
using Microsoft.EntityFrameworkCore;
using ECommerce.Infrastructure;
var builder = WebApplication.CreateBuilder(args);
// ------------------------------------------------------
// Add Controllers
// ------------------------------------------------------
builder.Services.AddControllers();
// add infrastructure (DbContext + provider selection)
builder.Services.AddInfrastructure(builder.Configuration);
// ------------------------------------------------------
// Repository Registrations
// ------------------------------------------------------
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<ICustomerRepository, CustomerRepository>();
builder.Services.AddScoped<IOrderRepository, OrderRepository>();
builder.Services.AddScoped<IOrderItemRepository, OrderItemRepository>();
// ------------------------------------------------------
// Service Registrations (Application Layer)
// ------------------------------------------------------
builder.Services.AddScoped<IProductService, ProductService>();
builder.Services.AddScoped<ICustomerService, CustomerService>();
builder.Services.AddScoped<IOrderService, OrderService>();
builder.Services.AddScoped<IOrderItemService, OrderItemService>();
// ------------------------------------------------------
// Swagger
// ------------------------------------------------------
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();
// ------------------------------------------------------
// Middleware Pipeline
// ------------------------------------------------------
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();
*Now Move Old (MYSQL) migrations to MySQL folder *
mkdir Migrations\MySQL
move Migrations\*.cs Migrations\MySQL\
Lets Create Migrations for SQL Server
dotnet ef migrations add InitSqlServer -p ECommerce.Infrastructure -s ECommerce.API --context SqlServerDbContext --output-dir "Migrations/SqlServer"
Lets Test with SQL Server using swagger
Next Lecture Preview
Lecture 5 : Working with PostgreSQL
Configuring Entity Framework Core for multiple providers and supporting PostgresSQL alongside MySQL and SQL Server
This content originally appeared on DEV Community and was authored by Farrukh Rehman
Farrukh Rehman | Sciencx (2025-10-28T17:51:54+00:00) 🧱 Lesson 4 – Adding SQL Server Support (Multi-Database Setup). Retrieved from https://www.scien.cx/2025/10/28/%f0%9f%a7%b1-lesson-4-adding-sql-server-support-multi-database-setup/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.