🧱 Lesson 4 - Adding SQL Server Support (Multi-Database Setup)

#database #sqlserver #docker #dotnet

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"