Categories
.NET Core

Connect .NET Core With MSSQL On A Docker Container

In this post, we will learn to setup a .NET core app to connect with MSSQL database on a docker container environment. We will run a SQL server on ubuntu container and attach volume on it. Then we will learn to run queries on the running sql server instance and persist changes on it.

We have also already covered a similar tutorial where we had used a PostgreSQL with .net core app instead of MSSQL.

Setup MSSQL Server On Docker Container

Docker Compose For MSSQL Server

Here we have a docker-compose.yaml file for creating an image for the Microsoft SQL Server (MSSQL).

version: "3"
services:
    db:
        image: "mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04"
        environment:
            SA_PASSWORD: "qwertY12@3"
            ACCEPT_EULA: "Y"
        ports: 
            - "1433:1433"
        volumes:
            - sql_data_volume:/var/opt/mssql
volumes: 
    sql_data_volume:

We want to specify that we are using the ubuntu container image for our MSSQL server.

Once the server is setup and starts running, a user SA is created by default. For the user, we can assign a password by using the SA_PASSWORD parameter. Since MSSQL enforces a strong password use, if you do not use a strong password here, the container creation can fail. So, make sure to use a strong password.

Both ports and volumes are optional parameters here. But we need to specify both as they are crucial when working with real world application.

For connecting .net core app with the container that has MSSQL server running, a port needs to be available. Similarly, to persist data in the database, we need to setup the volume. This allows the volume to be available even after the MSSQL server container is restarted.

The docker-compose.yaml file resides inside docker folder in the project root.

Script To Start Container

To run our MSSQL container image, we will create a new file start_local.sh.

This file resides inside scripts folder which also resides on the project root.

docker-compose --project-name=mssql_core --file=docker/docker-compose.yaml up -d

Now, whenever we need to run the MSSQL server container image, we can use the following command:

./scripts/start_local.sh

If everything went well, you should see a running container when you run docker ps command.

Script To Stop Running Container

Add another file inside the scripts folder titled stop_local.sh.

docker-compose --project-name=mssql_core --file=docker/docker-compose.yaml down

To stop a running container, use the command like this:

./scripts/stop_local.sh

Executing SQL Queries In Running Container

Once you have a running MSSQL server inside a container, we can run any SQL queries on it.

Enter A Running Container

To get access inside any running docker container, we can use the docker exec command. First, identify the container id for your MSSQL server by using the docker ps command.

Next, run the following command:

sudo docker exec -it [container_id] "bash"

Once inside the container, you should see a prompt like below:

mssql@6222a7d1229b:/$ 

Running Queries From Inside The Container

For running queries, we will use the default sqlcmd tool. To enter into query mode, use the command below:

/opt/mssql-tools/bin/sqlcmd -S localhost -U

Once you run the above command, it will ask for a password. Use the same password that you have set for SA user.

You should now have entered the sql command prompt mode.

Check Existing Databases

Once inside the sql command prompt, run:

select name from sys.databases
go

Each sql statement is executed only after a GO command is sent. The above sql query should give you the list of default database names:

name                                                                                                                            
-------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb

Create A New Database

To create a new database, run:

create database my_db
go

This will create a new database named my_db.

Since we have attached an external volume to the container, this new database will persist even when you restart the container.

You can test it out by stopping the container and starting it again and checking existing databases as above.

Connecting .NET Core App With MSSQL On Docker Container

Now that we have got a working MSSQL server running on docker container, it’s time to connect it with a .net core app.

Setup DatabaseContext

When working with .net core app and entity framework, we make use of DbContext class. If you are new to entity framework, you should first read Entity Framework Code First Approach With .NET Core.

A typical DbContext implementation looks like this:

using Microsoft.EntityFrameworkCore;

public class MyDbContext: DbContext {
    public MyDbContext (DbContextOptions options) : base (options) { }
    public DbSet<User> Users {get; set;}
}

Here, the User DbSet represents an entity for which there would be an equivalent table in the MSSQL database.

For demo, a simple User class looks like this:

public class User {
    public int Id { get; set; }
    public string Name { get; set; }
}

Add Package Reference For SqlServer

We will use the Microsoft.EntityFrameworkCore.SqlServer to enable communication between our SQL server and .net core app.

So, add reference to it:

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.0" /> 

Create Connection With MSSQL Server

Now, the last thing we need to do is setup the DbContext object to connect with the MSSQL server running on docker container.

For this, we create a connection string like this:

var connection = @"Server=127.0.0.1,1433;Database=my_db;User=sa;Password=qwertY12@3;";

Since, we had exposed the container via port 1433, we use the same port number here. The database name is same as the one we created above i.e. my_db.

Setup the database context object inside ConfigureServices method of Startup.cs file as below:

public void ConfigureServices(IServiceCollection services)
        {
            // Database connection string.
            var connection = @"Server=127.0.0.1,1433;Database=main_db;User=sa;Password=qwertY12@3;";
            
            services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connection));
...

Running Migrations On MSSQL Server

To check if things are working well, first create the entity framework migration to create the User table.

dotnet ef migrations add InitDb

Then execute the migration onto the database:

dotnet ef database update

If everything went well, you should now have the User table created inside the my_db which is running inside a container.

Conclusion

In this post, we went in detail about how to start a MSSQL server on a docker container and run queries on it. Finally, we learnt how to connect the container of MSSQL server with .net core application.

Hope you were able to follow it well and can now take it further for your project requirements. Good luck.