CRUD Operations in ASP.NET Core and SQL Server with Docker

CRUD Operations in ASP.NET Core and SQL Server with Docker

In this tutorial I will perform CRUD Operations in as ASP.NET Core app that uses SQL Server as a backend. I will use Docker Compose for this so there will be 2 containers one running the ASP.NET Core app and other SQL Server 2019.

Note that this setup is very useful for people who don’t want to install SQL Server on their PC and can save the licence money. The SQL Server 2019 image will be run from docker container and is just around 350 mb in size. You can even connect to this SQL Server container from SQL Server Management Studio and do any type of work like creating database, creating table, running queries and so on.

Pull and run the SQL Server 2019 Image

Open the command prompt and run the following command which will pull the SQL Server 2019 image from Microsoft Container Registry(mcr).

docker pull mcr.microsoft.com/mssql/server:2019-latest

The command will start the download of the sql server image which is around 350 mb in size.

This tutorial is a part of ASP.NET Core apps on Docker series.

docker pull sql server

Next, run a Docker Container with the SQL Server’s downloaded image. The command is given below:

docker run -e "ACCEPT_EULA=Y" -e "[email protected]" -p 1440:1433 --name mysqlserver -h mysqlserver -d mcr.microsoft.com/mssql/server:2019-latest
docker container sql server

The above command does the following things:

  • 1. Creates a container by the name of mysqlserver which will run this image of SQL Server.
  • 2. The SQL Server password is set to [email protected]. Note that the password you set should be strong and at least 8 character in length. It should have a combination of letters, number, capital letters and special characters.
  • 3. The container’s port is exposed for the host. See -p 1440:1433, Here 1433 port of the container (second value) is exposed to 1440 (first value) port on the host.

Now check your docker desktop which will show this container up and running, see the below image containing the screenshot of my docker desktop.

sql server container docker desktop

Alternately, you can also run the docker ps on the command prompt to see this container in running mode.

Connect to SQL Server on the container with SSMS

Now you can connect with the SQL Server running in the docker container. So, in SQL Server Management Studio (SSMS), enter the following:

  • 1. localhost,1440 for the server name, recall 1440 was the port we set earlier. Instead of localhost you can also write your machine ip which you can get from running ipconfig command.
  • 2. SA for login.
  • 3. [email protected] for password.

Click the Connect button to connect with this SQL Server running in the container.

ssms connect docker container

Once you are connected, you can create databases, tables, stored procedures and do any other stuff which you normally do in the database.

Changing the Database Password

You can also change the SQL Server’s password by running the following given command:

docker exec -it mysqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "[email protected]" -Q "ALTER LOGIN SA WITH PASSWORD='[email protected]'"

So, after running this command the new password becomes [email protected].

Creating CRUD operations in ASP.NET Core app

Let us now Create CRUD operations by an ASP.NET Core app which uses this SQL Server running on the docker container. So create a new ASP.NET Core Web Application in visual studio.

ASP.NET Core Web Application

Next, give your app a name, I have named it as DockerCrud, and un-check the option Place solution and project in the same directory.

configure project

Do not select Enable Docker Support.

Next, select the template called ASP.NET Core Web App (Model-View-Controller) for creating a basic ASP.NET Core MVC app.

ASP.NET Core MVC app
Movie Database and Entity Framework Core

I will be creating a Movie Database called MovieDB in the SQL Server running on the docker container. There will be one table called as Movie, in this table Create, Read, Update and Delete operations will be performed from ASP.NET Core MVC app. The database creating and CRUD operations will be performed by Entity Framework Core (EF Core).

Note that here the ASP.NET Core MVC app will be accessing the SQL Server running on the container. Check the below image which describes this architecture.

sql server running in docker container

So, first add the necessary packages of EF Core on the ASP.NET Core MVC app from NuGet. These packages are:

  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Tools

Next, inside the Models folder create a new class called Movie.cs.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace DockerCrud.Models
{
    public class Movie
    {
        [Required]
        public int Id { get; set; }

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

        [Required]
        public string Actors { get; set; }
    }
}

Add another class called MovieContext.cs to the Models folder. It will serve as a Database Context for EF Core. The code is given below:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace DockerCrud.Models
{
    public class MovieContext : DbContext
    {
        public MovieContext(DbContextOptions<MovieContext> options) : base(options)
        {
        }

        public DbSet<Movie> Movie { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
    }
}
Controller Setup

Now update the HomeController.cs file inside the Controllers folder, to include action method that will be performing CRUD operations.

using DockerCrud.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;

namespace DockerCrud.Controllers
{
    public class HomeController : Controller
    {
        private MovieContext context;
        public HomeController(MovieContext mc)
        {
            context = mc;
        }

        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        public async Task<IActionResult> Create(Movie m)
        {
            if (ModelState.IsValid)
            {
                context.Add(m);
                await context.SaveChangesAsync();

                return RedirectToAction("Index");
            }
            else
                return View();
        }

        public IActionResult Index()
        {
            var m = context.Movie.ToList();
            return View(m);
        }

        public IActionResult Update(int id)
        {
            var pc = context.Movie.Where(a => a.Id == id).FirstOrDefault();
            return View(pc);
        }

        [HttpPost]
        public async Task<IActionResult> Update(Movie m)
        {
            if (ModelState.IsValid)
            {
                context.Update(m);
                await context.SaveChangesAsync();

                return RedirectToAction("Index");
            }
            else
                return View(m);
        }

        [HttpPost]
        public async Task<IActionResult> Delete(int id)
        {
            var pc = context.Movie.Where(a => a.Id == id).FirstOrDefault();
            context.Remove(pc);
            await context.SaveChangesAsync();

            return RedirectToAction("Index");
        }
    }
}
Views for UI

After that create 3 Views (Index.cshtml, Create.cshtml, Update.cshtml) inside the Views >> Home controller. These views will form the UI for the CRUD operations.

1. Index.cshtml

@model List<Movie>

@{
    ViewData["Title"] = "Movies";
}

<h1 class="bg-info text-white">Movies</h1>
<a asp-action="Create" class="btn btn-secondary">Create a Movie</a>

<table class="table table-sm table-bordered">
    <tr>
        <th>Id</th>
        <th>Name</th>
        <th>Actors</th>
        <th></th>
        <th></th>
    </tr>
    @foreach (Movie m in Model)
    {
        <tr>
            <td>@m.Id</td>
            <td>@m.Name</td>
            <td>@m.Actors</td>
            <td>
                <a class="btn btn-sm btn-primary" asp-action="Update" asp-route-id="@m.Id">
                    Update
                </a>
            </td>
            <td>
                <form asp-action="Delete" asp-route-id="@m.Id" method="post">
                    <button type="submit" class="btn btn-sm btn-danger">
                        Delete
                    </button>
                </form>
            </td>
        </tr>
    }
</table>

2. Create.cshtml

@model Movie

@{
    ViewData["Title"] = "Create a Movie";
}

<h1 class="bg-info text-white">Create a Movie</h1>
<a asp-action="Index" class="btn btn-secondary">View all Movies</a>

<div asp-validation-summary="All" class="text-danger"></div>

<form method="post" enctype="multipart/form-data">
    <div class="form-group">
        <label asp-for="Name"></label>
        <input type="text" asp-for="Name" class="form-control" />
        <span asp-validation-for="Name" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Actors"></label>
        <input type="text" asp-for="Actors" class="form-control" />
        <span asp-validation-for="Actors" class="text-danger"></span>
    </div>
    <button type="submit" class="btn btn-primary">Create</button>
</form>

3. Update.cshtml

@model Movie

@{
    ViewData["Title"] = "Update a Movie";
}

<h1 class="bg-info text-white">Update a Movie</h1>
<a asp-action="Index" class="btn btn-secondary">View all Movies</a>

<div asp-validation-summary="All" class="text-danger"></div>

<form method="post" enctype="multipart/form-data">
    <div class="form-group">
        <label asp-for="Id"></label>
        <input type="text" asp-for="Id" readonly class="form-control" />
    </div>
    <div class="form-group">
        <label asp-for="Name"></label>
        <input type="text" asp-for="Name" class="form-control" />
        <span asp-validation-for="Name" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Actors"></label>
        <input type="text" asp-for="Actors" class="form-control" />
        <span asp-validation-for="Actors" class="text-danger"></span>
    </div>
    <button type="submit" class="btn btn-primary">Update</button>
</form>
Entity Framework Core Migration

Open the appsettings.json file and create the connection string to the SQL Server database. The Data Source should be the localhost, 1440 and Initial Catalog is the name of the database. You can name the database anything you like.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=localhost,1440;Initial Catalog=MovieDB;Persist Security Info=True;User ID=SA;[email protected]"
  }
}

Instead of localhost you can write your pc’s IP address. The IP address can be seen by running ipconfig command on the command prompt. My pc’s ip address is 192.168.1.100. So, I can use it on my connection string as shown below.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=192.168.1.100,1440;Initial Catalog=MovieDB;Persist Security Info=True;User ID=SA;[email protected]"
  }
}

Next, go to the Startup.cs file and add the Database Context as a service. Check the highlighted code below.

using DockerCrud.Models;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace DockerCrud
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<MovieContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddControllersWithViews();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            …
        }
    }
}

Finally, you need to run EF Core migration commands. So open Package Manager Console window in VS and go to the directory of the Startup.cs class and then run the following 2 commands.

dotnet ef migrations add Migration1
dotnet ef database update

This will create the database called MovieDB on the SQL Server running in the container, now you can perform CRUD operation with the ASP.NET Core MVC app.

Testing CRUD Operations

Now it’s time to test the CRUD operations working. Run your ASP.NET Core MVC app in vs and go to Create Movie section, enter a movie name and it’s actors (separated by comma), and then click the Create button. The movie record will be inserted to the database. See the below image where I have shown this thing.

create movie screenshot

Next, click the update link and enter new values to the text box. Then click the update button to update the record. Check the below screenshot where I have shown the update procedure.

update movie screenshot

Next click the delete button against a record to delete it. Check the below screenshot:

delete movie screenshot

We have successfully tested the CRUD operations which are working perfectly.

Docker Compose for ASP.NET Core app and SQL Server 2019

Now I will run ASP.NET Core MVC app we just built and SQL Server 2019 in multi-containers with Docker Compose.

Right click the Project name in Solution Explorer and select container orchestrator support.

Container Orchestrator Support

Next, on the new window that opens up select “Docker Compose”.

Add Container Orchestrator Support

Next, select the Target OS as Linux and click the OK button.

Docker Support Options

This will add a new project called docker-compose on the solution explorer.

Open the docker-compose.yml file which you will find inside the docker-compose project and add new service and name it sqldata.

docker compose yml

Specify the image to be sql server 2019, password and ports. See the highlighted code given below.

version: '3.4'

services:
  dockercrud:
    image: ${DOCKER_REGISTRY-}dockercrud
    build:
      context: .
      dockerfile: DockerCrud/Dockerfile
  
  sqldata:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      - [email protected]
      - ACCEPT_EULA=Y
    ports:
      - "1450:1433"

Notice the ports “1450:1433”, the first port is the host’s port and the second is the port of the container running sql server. I used a different port 1450 and not the previous 1440 port, which is already having the sql server running on it. To be more clear, here the SQL Server used is different that the previous one. So, we have to create the database from Entity Framework Core Migration commands ones more. I will do it in just a moment.

As soon you save the yml file, docker will create 2 containers – one will run the ASP.NET Core MVC app while the other one will run the SQL Server. The below image explains this thing.

multi container docker compose sql server

You can see these multi containers up and running in your docker desktop.

multi container sql server docker desktop

Next, change the connection string in this appsettings.json file to this new SQL Server by changing the port to 1450.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=sqldata;Initial Catalog=MovieDB;Persist Security Info=True;User ID=SA;[email protected]"
  }
}

Note that here I have provided Data Source=sqldata which is the service name for the SQL Server in docker-compose.yml file. This is done because containers in docker compose can communicate with one another by their name.

This is a great relief since we don’t have to put the IP address now.

Running the EF Core Migrations

The EF Core migrations will create the database on the SQL Server. There is a catch when performing migrations, you have to change the database string to use localhost, 1450 instead of sqldata because EF core should know to which database the migrations need to be performed. Therefore the updated connection string should be:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=localhost,1450;Initial Catalog=MovieDB;Persist Security Info=True;User ID=SA;[email protected]"
  }
}

Now in the package manager console window run the following 2 command from the directory of Startup.cs file.

dotnet ef migrations add Migration2
dotnet ef database update

Once the migrations are completed revert the connection string to:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=sqldata;Initial Catalog=MovieDB;Persist Security Info=True;User ID=SA;[email protected]"
  }
}
One thing to also note that the database container should be in running mode else migrations will not run. If they are not running then press F6 to build the app and this will run the database container, and then your migrations will run successfully.

Running the APP

Now you can run your app in visual studio and start doing the CRUD operations.

I have created a small 15 seconds video which shows the working. Check it.

If you want to run the app directly on the browser without taking the aid of Visual Studio then you will need to expose the ports and generate HTTPS development certificate. Then need to map this certificate to the container using Docker Volume. I have done these things in my previous tutorial on Docker. If you want to know these steps then follow Exposing ports and configuring Environment variables for HTTPS.

Download the source codes:

Download

Conclusion

In this tutorial you learned how to create a docker container with SQL Server 2019 image. This procedure is helpful if you don’t want to install sql server on your system. You also learned how to run database driven ASP.NET Core MVC app with SQL Server 2019 container. For this we used the Docker Compose.

If you love reading this tutorial makes sure to do a bit of promotion by sharing it on your facebook and twitter accounts.

Share this article -

yogihosting

ABOUT THE AUTHOR

I hope you enjoyed reading this tutorial. If it helped you then consider buying a cup of coffee for me. This will help me in writing more such good tutorials for the readers. Thank you. Buy Me A Coffee

Comments

  1. Ani says:

    Can we set sql data location in drive. So once image or container destroyed we still have access to data, so that we can restore in another server or deploy on production.

    1. yogihosting says:

      Yes you can do it with Docker Volumes. How to use volumes is given on my post – Exposing ports and configuring Environment variables for HTTPS.

      We have to map the directory /var/opt/mssql/data for volume to your host machine. See the below command which uses -v tag for volume and specify it as – -v sqlvolume:/var/opt/mssql/data

      docker run -e "ACCEPT_EULA=Y" -e "[email protected]" -p 1440:1433 --name mysqlserver -h mysqlserver -v sqlvolume:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2019-latest

  2. Kevin says:

    Thanks Yogi!
    This is another great post in this Docker series – one of the most comprehensive Docker tutorial for .Net developers.

    1. yogihosting says:

      Thank you Kevin for the kind words. I am also starting Kubernetes series for .NET developers. First article will come in the next 3 to 5 days. Kubernetes will automate docker containers.

Leave a Reply

Your email address will not be published. Required fields are marked *