Dockerize your SQL Server and use it in ASP.NET Core with Entity Framework Core

Time to read: 5 minutes

September 20, 2022
Written by
Bryan Hogan
Contributor
Opinions expressed by Twilio contributors are their own
Reviewed by
Niels Swimberghe
Twilion

I love learning and writing blogs about .NET and related technologies. One of my favorites over the past 10 years or so has been Entity Framework (EF) and Entity Framework Core (EF Core). I have used it professionally with MS SQL Server, Postgres, and MySQL. But the thing I don’t like is installing these databases on my computer, they are large, they add services that run on startup, and consume lots of resources.

Instead, when I want to write an application using EF and one of those databases, I download a docker image for the database, start up the container, and connect to that instance from my .NET application. When I’m done for the day, I shut down the container, and when I’m done with the application, I delete the container, and image, and my system is clean of the database.

The other great thing about using a database in Docker, is that it works on any operating system! I can start my work on Windows, then switch to Linux and change nothing about how I work or write my code.

This workflow greatly improves the productivity of developers and teams, so let's learn how to achieve this.

Prerequisites

You will need the following things in this tutorial:

Though this tutorial uses .NET 6, you can adapt it to work with newer and older versions of .NET.

Run SQL Server in a Docker Container

You'll be using Docker to quickly spin up and teardown SQL Server containers, but first, verify that Docker is running. Open a terminal and run:

docker info

If it is not running, you will get an error that looks like this:

Server:
ERROR: error during connect: This error may indicate that the docker daemon is not running.: Get "http://%2F%2F.%2Fpipe%2Fdocker_engine/v1.24/info": open //./pipe/docker_engine: The system cannot find the file specified.
errors pretty printing info

Note the message “This error may indicate that the docker daemon is not running”. Follow the instructions for starting Docker on your operating system.

Download and Run the SQL Server Docker Image

You can download a Docker image, create a container for that image, and start the container using a single command. Run this command to download the SQL Server image and run it as a container:

docker run -it \
    -e "ACCEPT_EULA=Y" \
    -e "SA_PASSWORD=A&VeryComplex123Password" \
    -p 1433:1433 \
    --name sql-server-2022 \
    mcr.microsoft.com/mssql/server:2022-latest

The backslash () is used to nicely format the command arguments on separate lines for bash-like shells. For CMD you can use the caret symbol (^) instead of (/), and for PowerShell you can use the backtick symbol (`) instead.

Here's what the arguments do:

  • -e "ACCEPT_EULA=Y", sets an environment variable read by SQL Server stating that you accept the end user license agreement
  • -e "SA_PASSWORD=A&VeryComplex123Password", sets an environment variable for the system administrator password
  • -p 1433:1433, forwards the SQL Server port from inside the container to outside the container, without this you could not access SQL Server from your host computer
  • --name sql-server-2022, give the container instance a name
  • mcr.microsoft.com/mssql/server:2022-latest, the image to download from Docker hub

When using docker run as above, the Docker CLI executes docker pull to download the image, docker create to create a container for the image, and finally docker run to run the container.

That’s it!

You now have SQL Server running on your computer. Any application that can connect to a traditional SQL Server can connect to this one. To connect to the SQL Server, use localhost for the hostname, and SA for the username, and “A&VeryComplex123Password” for the password.

Press CTRL + C to stop the container.
You can start the container again using this command:

docker start -i sql-server-2022

sql-server-2022 is the name of the previously created container.

If you want to use a different version of SQL Server, go to the Microsoft SQL Server image on Docker Hub and scroll down to see the available tags. Choose the version that suits you and replace <TAG> with your chosen tag in the below command, and replace <CONTAINER_NAME> with a name of your choice:

docker run -it -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=A&VeryComplex123Password" -p 1433:1433 --name <CONTAINER_NAME> mcr.microsoft.com/mssql/server:<TAG>

That’s it, you now have a fully working SQL Server up and running in a container. You can use any tool you are familiar with to connect to it, just like any SQL Server you have used in the past.

If your SQL Server container isn't running, start it now using this command:

docker container start -i sql-server-2022

Leave the container running and open a new terminal for the upcoming commands.

Create a Web API that consumes the SQL Server

This part will be familiar to you if you have worked with .NET, SQL Server, and EF. If you haven’t, don’t worry, I will walk you through it step by step.


You are going to create a Web API application using top level statements, but not Minimal API. Optionally, you will turn off HTTPS redirection and Swagger documentation to keep the Program.cs file smaller for this tutorial.-

dotnet new webapi -n SalesApi --no-https true --no-openapi true

Navigate to the SalesApi directory.

cd SalesApi

Add these two NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package AutoFixture

You'll use EF Core to manage the data in the SQL Server, and you are going to use AutoFixture to generate sample data for the database.

Optionally, you can delete the files WeatherForecast.cs and Controllers/WeatherForecastController.cs.

Create a new directory named Data, and add the following four new files to it.

Product.cs, a basic representation of a product:

namespace SalesApi.Data
{
   public class Product
   {
       public int ProductId { get; set; }
       public string? Name { get; set; }
       public ProductCategory ProductCategory { get; set; }
       public string? Description { get; set; }
       public decimal Price { get; set; }
       public string? SKU { get; set; }
       public string? Code { get; set; }
   }
}

ProductCategory.cs, some sample product categories:

namespace SalesApi.Data
{
   public enum ProductCategory
   {
       Clothing = 1,
       Footware = 2,
       Electronics = 3,
       Household = 4
   }
}

SalesContext.cs, your EF Core database context, which will be used to add and retrieve data from the database:

using Microsoft.EntityFrameworkCore;

namespace SalesApi.Data
{
   public class SalesContext : DbContext
   {
       public SalesContext(DbContextOptions<SalesContext> options) : base(options)
       {
       }

       public DbSet<Product>? Products { get; set; }
   }
}

Seeder.cs, this seeds the database with 100 rows of product data generated by AutoFixture:

using AutoFixture;

namespace SalesApi.Data
{
   public static class Seeder
   {
       public static void Seed(this SalesContext salesContext)
       {
           if (!salesContext.Products.Any())
           {
               Fixture fixture = new Fixture();
               fixture.Customize<Product>(product => product.Without(p => p.ProductId));
               //--- The next two lines add 100 rows to your database
               List<Product> products = fixture.CreateMany<Product>(100).ToList();
               salesContext.AddRange(products);
               salesContext.SaveChanges();
          }
       }
   }
}

Seeding your database with dummy data can be helpful for development and demos. Instead of manually entering data before you can test your app, the app can seed the data beforehand, saving you and testers time. However, be cautious when using this technique in production to not delete or pollute production data.

Open the appsettings.Development.json file and add the ConnectionStrings property with the SalesDb connection string to it:

{
  …,
  "ConnectionStrings": {
        "SalesDb": "Server=localhost;Database=SalesDb;User Id=SA;Password=A!VeryComplex123Password;MultipleActiveResultSets=true"
  }
}

You can store the connection string in plain text like the appsettings.json file because this database is running locally on your machine only for local development. If the connection string contains a username and password, or other sensitive secrets, then you should use the Secrets Manager (for local development only), Environment Variables, or a vault service.

Now it is time to integrate the data code you just added. In the Program.cs file, you'll add the SalesContext to the dependency injection container, and seed the database the first time you run the application

Open the Program.cs file. At the top of the file, add two using statement:

using Microsoft.EntityFrameworkCore;
using SalesApi.Data;

Right below the builder.Services.AddControllers() line, add the following lines:

builder.Services.AddDbContext<SalesContext>(options =>
        options.UseSqlServer(builder.Configuration.GetConnectionString("SalesDb")));

These lines get the connection string from configuration which includes the appsettings.json file, and adds the SalesContext to the service collection.

One more change needed in Program.cs, seed the database.

Below the line var app = builder.Build();, add the following code:

var app = builder.Build();

if(app.Environment.IsDevelopment())
{
    using(var scope = app.Services.CreateScope())
    {
        var salesContext = scope.ServiceProvider.GetRequiredService<SalesContext>();
        salesContext.Database.EnsureCreated();
        salesContext.Seed();
    }
}

In a development environment, this code retrieves an instance of the SalesContext from the service collection, creates the database if it doesn’t exist, and seeds it if it is empty. It is also common to use EnsureDeleted before EnsureCreated to start from an empty database each time.

Keep in mind that this will create the database based upon the entities configured in the SalesContext and ignore any EF Core migration scripts you have. If you're using EF Core migrations, you should use the Migrate method instead, which will execute pending migrations.

Automatically generating your database can speed up development and testing, however, it is recommended you don't do this in production to avoid accidental data loss. For production, you should use SQL scripts or the EF CLI tools in addition to doing backups of your database. That's why the code above only runs when in a development environment.

There, that is all the wiring up you need to do.

Create a new controller in the Controllers directory, name it ProductsController.

Replace the content of the ProductsController.cs file with the following code:

using Microsoft.AspNetCore.Mvc;
using SalesApi.Data;

namespace SalesApi.Controllers;

[ApiController]
[Route("[controller]")]
public class ProductsController : ControllerBase
{
   private readonly SalesContext _salesContext;

   public ProductsController (SalesContext salesContext)
   {
       _salesContext = salesContext;
   }

   [HttpGet]
   public ActionResult Get(int take = 10, int skip = 0)
   {
       return Ok(_salesContext.Products.OrderBy(p => p.ProductId).Skip(skip).Take(take));
   }
}

This constructor will have a SalesContext passed to it via dependency injection.

The Get method takes optional take and skip parameters, allowing you to paginate through the database table.

One last change, open the Properties/launchsettings.json file, look for the profile named SalesApi or http, and change the launchUrl to products.

That’s it. Now, start your application using your IDE or using dotnet run.

The application will start, connect to the SQL Server running in a Docker container, check if a database named “SalesDb” exists, creates and seeds the database if necessary, and then opens a page in your web browser with the first 10 entries from the Products table.

Browser displaying sample data from database

Conclusion

Traditionally, installing Microsoft SQL Server is a complicated, long task that takes a lot of disk space. But with Docker containers, you can spin up a SQL server in minutes and tear it down to reclaim disk space. In this tutorial, you learned how to run a database locally with Docker. Then, you created a .NET 6 application that uses that containerized database. The application is in no way different because of the way the database is running.

As a next step, consider running other services in Docker containers, you might want to try Redis. You could also try running your .NET application in a Docker container.

You can find the source code for this sample on GitHub.

Bryan Hogan is a blogger, podcaster, Microsoft MVP, and Pluralsight author. He has been working on .NET for almost 20 years. You can reach him on Twitter @bryanjhogan.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.