SQL Server  

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

Previous Lession: 🧱 Lesson 3  - Integrating MySQL (First Database)

🚀 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
1

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
2

3

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

4

5

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": "*"
}
6

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)
    {
    }
}
7

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)
    {
    }
}
8

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);
    }
}
9

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);
    }
}
10

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;
    }
}
11

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\

12

Lets Create Migrations for SQL Server

dotnet ef migrations add InitSqlServer -p ECommerce.Infrastructure -s ECommerce.API --context SqlServerDbContext --output-dir "Migrations/SqlServer"
13

14

Lets Test with SQL Server using swagger

15

16

17

Next Lecture Preview
Lecture 5 : Working with PostgreSQL

Configuring Entity Framework Core for multiple providers and supporting PostgresSQL alongside MySQL and SQL Server.

Membership not found