.NET Core and SQL Server in Docker - Part 1: Building the Service

Getting Started

First things first, we need to install our development tools. For this tutorial, we will be using a mix of text editor and the command line. For editing cross-platform C# projects, I highly recommend Microsoft's lightweight Visual Studio Code. For the command line, I will be assuming a Bash shell. Bash is the default shell for the MacOS terminal and is now also available on Windows starting with Windows 10.

For .NET Core, this tutorial assumes you are using a minimum version of 1.0.4.

Finally, you need to install Docker. Docker runs natively on Linux, but there are integrated VM solutions available for macOS and Windows (Windows 10 or later only, older versions of Windows should use a VM).

Creating The .NET Project

From the terminal, we are going to create a new project directory and initialize a new C# webapi project:

$ mkdir dotnet-docker-tutorial
$ cd dotnet-docker-tutorial
$ dotnet new webapi

Next, let's restore our NuGet dependencies and run our API:

$ dotnet restore
$ dotnet run

And finally, in a second terminal window, let's test out the API with curl:

$ curl http://localhost:5000/api/values

One change you will also want to make is to register the service to run on hostnames other than localhost. This is important later when we run our service inside of Docker. Open up Program.cs and modify the startup code:

var host = new WebHostBuilder()  
    .UseUrls("https://*:5000")
    .UseKestrel()
    // etc

Adding SQL Server

Now it's time to add a database. Thanks to Docker and SQL Server for Linux, it's super fast and easy to get this started. From the terminal, let's download and run a new instance of SQL Server as a Docker container.

$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Testing123' -p 1433:1433 --name sqlserver -d microsoft/mssql-server-linux

That's all that's needed to have a SQL Server development database server up and running. Note that if you are running Docker for Windows or Docker for Mac, you need to allocate at least 4GB of RAM to the VM or SQL Server will fail to run.

Next, let's add a new API controller to our application that interacts with the database. First we need to add Entity Framework to our csproj file, which should look like this:

<Project Sdk="Microsoft.NET.Sdk.Web">  
  <PropertyGroup>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <Folder Include="wwwroot\" />
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore" Version="1.1.1" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.2" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="1.1.1" />
  </ItemGroup

Next, we create a new DbContext. In our Models folder, create a file ProductsController.cs and edit as follows:

using Microsoft.EntityFrameworkCore;

namespace Kontena.Examples.Models  
{
    public class ApiContext : DbContext
    {
        public ApiContext(DbContextOptions<ApiContext> options)
            : base(options)
        {
            this.Database.EnsureCreated();
        }

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

Next is the model class. In the Models folder, create a file Product.cs, and create the Product model:

using System.ComponentModel.DataAnnotations;

namespace Kontena.Examples.Models  
{
    public class Product
    {
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}

And finally, let's create a new API Controller. In the Controllers folder, create a file ProductsController.cs and add the following code:

using System.Linq;  
using Microsoft.AspNetCore.Mvc;  
using Kontena.Examples.Models;

namespace Kontena.Examples.Controllers  
{
    [Route("api/[controller]")]
    public class ProductsController : Controller
    {
        private readonly ApiContext _context;

        public ProductsController(ApiContext context)
        {
            _context = context;
        }

        // GET api/values
        [HttpGet]
        public IActionResult Get()
        {
            var model = _context.Products.ToList();

            return Ok(new { Products = model });
        }

        [HttpPost]
        public IActionResult Create([FromBody]Product model)
        {
            if (!ModelState.IsValid)
                return BadRequest(ModelState);

            _context.Products.Add(model);
            _context.SaveChanges();

            return Ok(model);
        }

        [HttpPut("{id}")]
        public IActionResult Update(int id, [FromBody]Product model)
        {
            if (!ModelState.IsValid)
                return BadRequest(ModelState);

            var product = _context.Products.Find(id);

            if (product == null)
            {
                return NotFound();
            }

            product.Name = model.Name;
            product.Price = model.Price;

            _context.SaveChanges();

            return Ok(product);
        }

        [HttpDelete("{id}")]
        public IActionResult Delete(int id)
        {
            var product = _context.Products.Find(id);

            if (product == null)
            {
                return NotFound();
            }

            _context.Remove(product);
            _context.SaveChanges();

            return Ok(product);
        }
    }
}

This should be enough for us to provide a simple CRUD-style REST interface over our new Product model. The final step needed is to register our new database context with the ASP.NET dependency injection framework and fetch the SQL Server credentials. In the file Startup.cs, modify the ConfigureServices method:

public void ConfigureServices(IServiceCollection services)  
{
    // Add framework services.
    services.AddMvc();

    var hostname = Environment.GetEnvironmentVariable("SQLSERVER_HOST") ?? "localhost";
    var password = Environment.GetEnvironmentVariable("SQLSERVER_SA_PASSWORD") ?? "Testing123";
    var connString = $"Data Source={hostname};Initial Catalog=KontenaAspnetCore;User ID=sa;Password={password};";

    services.AddDbContext<ApiContext>(options => options.UseSqlServer(connString));
}            

Note that we are pulling our SQL Server credentials from environment variables, defaulting to the values we used above for setting up our SQL Server container. In a production application, you would probably use the more sophisticated Asp.Net core configuration framework and a SQL Server user other than "sa."

Testing Out Our API

Time to test out our new API. In your terminal window, restore and start up the API again:

$ dotnet restore && dotnet run

In another window, let's use curl to POST some data to our API:

$ curl -i -H "Content-Type: application/json" -X POST -d '{"name": "6-Pack Beer", "price": "5.99"}' http://localhost:5000/api/products

If all goes well, you should see a 200 status response, and our new Product returned as JSON (with a proper database generated id).

Next, let's modify our data with a PUT and change the price:

$ curl -i -H "Content-Type: application/json" -X PUT -d '{"name": "6-Pack Beer", "price": "7.99"}' http://localhost:5000/api/products/1

Of course, we can also GET our data:

$ curl -i http://localhost:5000/api/products

And finally we can DELETE it:

$ curl -i -X DELETE http://localhost:5000/api/products/1

Putting It in Docker

Now that we have our service, we need to get it in Docker. The first step is to create a new Dockerfile that tells Docker how to build our service. Create a file in the root folder called Dockerfile and add the following content:

FROM microsoft/dotnet:runtime

WORKDIR /dotnetapp  
COPY out .  
ENTRYPOINT ["dotnet", "dotnet-example.dll"]  

Next, we need to compile and "publish" our application, and use the output to build a Docker image with the tag dotnet-example:

$ dotnet publish -c Release -o out
$ docker build -t dotnet-example .

And finally, we can run our new container, linking it to our SQL Server container:

$ docker run -it --rm -p 5000:5000 --link sqlserver -e SQLSERVER_HOST=sqlserver dotnet-example

You should be able to access the API via curl the same as we did earlier.

Next Time

In our next installment, we will show you how to take your new API and run the whole thing inside Docker. Then we will move those containers into the cloud with Kontena.

Accompanying source code for this tutorial can be found at https://github.com/kontena/dotnet-example.