DEV Community

Cover image for C# (C Sharp) CRUD Rest API using .NET 7, ASP.NET, Entity Framework, Postgres, Docker and Docker Compose
Francesco Ciulla
Francesco Ciulla

Posted on • Updated on

C# (C Sharp) CRUD Rest API using .NET 7, ASP.NET, Entity Framework, Postgres, Docker and Docker Compose

Let's create a CRUD Rest API in C# (or C sharp), using:

  • .NET 7
  • ASP.NET (Framework for building web apps)
  • Entity Framework (ORM)
  • Postgres (Database)
  • Docker (Containerization)
  • Docker Compose (To run the database and the application)

Video version:

All the code is available in the GitHub repository (link in the video description): https://youtube.com/live/PY6uD1fgzZA


🏁 Intro

Here is a schema of the architecture of the application we are going to create:

crud, read, update, delete, to a ASP.NET (C# logo) and Postgres service, connected with Docker compose. Postman and Tableplus to test it

We will create 5 endpoints for basic CRUD operations:

  • Create
  • Read all
  • Read one
  • Update
  • Delete

Here are the steps we are going through:

  1. Define the requirements
  2. Create a new C# project
  3. Install the dependencies
  4. Configure the database connection
  5. Create the logic for the CRUD operations
  6. Update the project
  7. Dockerfile
  8. docker-compose.yml
  9. Run the Postgres database and the application
  10. Update the database schema
  11. Test the application

We will go with a step-by-step guide, so you can follow along.


📋 Requirements:

  • .NET 7 installed and running
  • dotnet CLI
  • (Optional): C# Extension Pack for VS Code ___ ## 🚀 Create a new C# project

There are many ways to create a new C# project, but I will use the dotnet CLI

Open a terminal and run the following command:



dotnet new webapi -n csharp-crud-api


Enter fullscreen mode Exit fullscreen mode

Now step into the directory:



cd csharp-crud-api


Enter fullscreen mode Exit fullscreen mode

📦 Install the dependencies

We will use the following dependencies:

  • Entity Framework
  • Npgsql (Postgres driver)

To install them, be sure to be in the csharp-crud-api directory and run the following commands:



dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL


Enter fullscreen mode Exit fullscreen mode

🔍 Test the base application

Before we proceed, let's see if the project is configured correctly.

Open the folder in VS Code (or your favorite IDE).



code .


Enter fullscreen mode Exit fullscreen mode

You should see the following structure:

Structure of the C# project

Now run the following command to start the application:



dotnet run --urls=http://localhost:5000


Enter fullscreen mode Exit fullscreen mode

You should see the following output:

Output of the dotnet run command

To test the application, open a browser and go to the following URL: http://localhost:5000/weatherforecast

You should see the following output:

Get response with weather

Now it's time to start coding the application.


📝 Code the application

There are three steps to code the application:

  • Configure the database connection
  • Create the logic for the CRUD operations
  • Update the Program.cs file

🗄️ Configure the database connection

open the appsettings.json file and add the following lines:



{
  "ConnectionStrings": {
    "DefaultConnection": "Host=db;Port=5432;Database=postgres;Username=postgres;Password=postgres"
  }
}


Enter fullscreen mode Exit fullscreen mode

Your appsettings.json file should look like this:



{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Host=db;Port=5432;Database=postgres;Username=postgres;Password=postgres"
  }
}


Enter fullscreen mode Exit fullscreen mode

✏️ Create the logic for the CRUD operations

Create a new folder called Models and a new file called User.cs inside it.

Structure of the C# project

Populate User.cs with the following code:



using System.ComponentModel.DataAnnotations.Schema;

namespace Models
{
    [Table("users")]
    public class User
    {
        [Column("id")]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }

        [Column("email")]
        public string Email { get; set; }
    }
}


Enter fullscreen mode Exit fullscreen mode

Now create a new folder called Data and a new file called UserContext.cs inside it.

Populate the UserContext.cs file with the following code:



using Models;
using Microsoft.EntityFrameworkCore;

namespace Data
{
    public class UserContext : DbContext
    {
        public UserContext(DbContextOptions<UserContext> options) : base(options)
        {
        }

        public DbSet<User> Users { get; set; }
    }
}


Enter fullscreen mode Exit fullscreen mode

Now create a new folder called Controllers and a new file called UsersController.cs inside it.

Populate the UserscController.cs file with the following code:



using Data;
using Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
namespace csharp_crud_api.Controllers;


[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
  private readonly UserContext _context;

  public UsersController(UserContext context)
  {
    _context = context;
  }

  // GET: api/users
  [HttpGet]
  public async Task<ActionResult<IEnumerable<User>>> GetUsers()
  {
    return await _context.Users.ToListAsync();
  }

  // GET: api/users/5
  [HttpGet("{id}")]
  public async Task<ActionResult<User>> GetUser(int id)
  {
    var user = await _context.Users.FindAsync(id);

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

    return user;
  }

  // POST: api/users
  [HttpPost]
  public async Task<ActionResult<User>> PostUser(User user)
  {
    _context.Users.Add(user);
    await _context.SaveChangesAsync();

    return CreatedAtAction(nameof(GetUser), new { id = user.Id }, user);
  }

  // PUT: api/users/5
  [HttpPut("{id}")]
  public async Task<IActionResult> PutUser(int id, User user)
  {
    if (id != user.Id)
    {
      return BadRequest();
    }

    _context.Entry(user).State = EntityState.Modified;

    try
    {
      await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
      if (!UserExists(id))
      {
        return NotFound();
      }
      else
      {
        throw;
      }
    }

    return NoContent();
  }

  // DELETE: api/users/5
  [HttpDelete("{id}")]
  public async Task<IActionResult> DeleteUser(int id)
  {
    var user = await _context.Users.FindAsync(id);
    if (user == null)
    {
      return NotFound();
    }

    _context.Users.Remove(user);
    await _context.SaveChangesAsync();

    return NoContent();
  }

  private bool UserExists(int id)
  {
    return _context.Users.Any(e => e.Id == id);
  }

  // dummy method to test the connection
  [HttpGet("hello")]
  public string Test()
  {
    return "Hello World!";
  }
}


Enter fullscreen mode Exit fullscreen mode

🖋️ Update the Program.cs file

We are almost done, now we just need to update the Program.cs file.

add these 2 imports at the top of the file:



using Data;
using Microsoft.EntityFrameworkCore;


Enter fullscreen mode Exit fullscreen mode

And these lines lines above the var app = builder.Build(); line:



// Added configuration for PostgreSQL
var configuration = builder.Configuration;
builder.Services.AddDbContext<UserContext>(options =>
    options.UseNpgsql(configuration.GetConnectionString("DefaultConnection")));


Enter fullscreen mode Exit fullscreen mode

Or you can replace the whole Program.cs file with the following code:



using Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// Added configuration for PostgreSQL
var configuration = builder.Configuration;
builder.Services.AddDbContext<UserContext>(options =>
    options.UseNpgsql(configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();


Enter fullscreen mode Exit fullscreen mode

We are done with the application logic, now it's time to Dockerize it.


🐳 Dockerize the application

We will Dockerize the application in creating and populating two files:

  • Dockerfile
  • docker-compose.yml

🐋 Dockerfile

At the root of the project create a new file called Dockerfile and populate it with the following code:



FROM mcr.microsoft.com/dotnet/sdk:7.0 AS build
WORKDIR /app

COPY *.csproj ./
RUN dotnet restore

COPY . ./
RUN dotnet publish -c Release -o out

FROM mcr.microsoft.com/dotnet/aspnet:7.0 AS runtime
WORKDIR /app
COPY --from=build /app/out ./

ENTRYPOINT ["dotnet", "csharp-crud-api.dll"]


Enter fullscreen mode Exit fullscreen mode

Explanation:

We have 2 stages in the Dockerfile, build and runtime.

Build stage:

  • FROM mcr.microsoft.com/dotnet/sdk:7.0 AS build: This line tells Docker to use the mcr.microsoft.com/dotnet/sdk:7.0 image as the base image for the build stage.
  • WORKDIR /app: This line tells Docker to set the working directory to /app.
  • COPY *.csproj ./: This line tells Docker to copy all the .csproj files to the working directory.
  • RUN dotnet restore: This line tells Docker to run the dotnet restore command.
  • COPY . ./: This line tells Docker to copy all the files to the working directory.
  • RUN dotnet publish -c Release -o out: This line tells Docker to run the dotnet publish command.

Runtime stage:
This stage is very similar to the build stage, the only difference is that we are using the mcr.microsoft.com/dotnet/aspnet:7.0 image as the base image.

  • FROM mcr.microsoft.com/dotnet/aspnet:7.0 AS runtime: This line tells Docker to use the mcr.microsoft.com/dotnet/aspnet:7.0 image as the base image for the runtime stage.
  • WORKDIR /app: This line tells Docker to set the working directory to /app.
  • COPY --from=build /app/out ./: This line tells Docker to copy the out folder from the build stage to the working directory.
  • ENTRYPOINT ["dotnet", "csharp-crud-api.dll"]: This line tells Docker to run the dotnet csharp-crud-api.dll command.

🐙 docker-compose.yml

At the root of the project create a new file called docker-compose.yml and populate it with the following code:



version: '3'
services:
  csharp_app:
    container_name: csharp_app
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8080:80"
    depends_on:
      - db
    environment:
      ConnectionStrings__DefaultConnection: "Host=db;Database=postgres;Username=postgres;Password=postgres"
  db:
    container_name: db
    image: postgres:12
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata: {}


Enter fullscreen mode Exit fullscreen mode

Explanation:

Explanation:

  • We define 2 services: csharp_app and db. Csharp_app:
  • container_name: csharp_app: This line tells Docker to name the container csharp_app.
  • build:: This line tells Docker to build the image from the Dockerfile.
  • ports:: This line tells Docker to expose the port 8080 of the container to the port 80 of the host.
  • depends_on:: This line tells Docker that the csharp_app container depends on the db container.
  • environment:: This line tells Docker to set the environment variables for ConnectionStrings__DefaultConnection.

Db:

  • container_name: db: This line tells Docker to name the container db.
  • image: postgres:12: This line tells Docker to use the postgres:12 image as the base image for the container.
  • environment:: This line tells Docker to set the environment variables for POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB.
  • ports:: This line tells Docker to expose the port 5432 of the container to the port 5432 of the host.
  • volumes:: This line tells Docker to mount the pgdata volume to the /var/lib/postgresql/data directory of the container.

We also need to define the pgdata volume, used by the db container.

We are done. Now we can run the Postgres and the app containers.


🏃‍♂️ Run the application

We will run the containers (services) defined in the docker-compose.yml file.

🐘 Run the Postgres container



docker compose up -d db


Enter fullscreen mode Exit fullscreen mode

📜 Create the table in the database

We can create the table in different ways, but let me show you how you can do it directly from the app container.

First, be sure that the Postgres container is running.

Open a different terminal and run the following command:



docker exec -it db psql -U postgres


Enter fullscreen mode Exit fullscreen mode

And we are inside of the Postgres Container now. We can create the table with the following command:



CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(50) NOT NULL,
  "email" VARCHAR(255) NOT NULL
);


Enter fullscreen mode Exit fullscreen mode

🏗️ Build and run the app container

Build the Docker image:



docker compose build


Enter fullscreen mode Exit fullscreen mode

Run the app container:



docker compose up csharp_app


Enter fullscreen mode Exit fullscreen mode

🧪 Test the application

Let's test the application with Postman

Now we can test the project. We will use Postman, but you can use any other tool.

📝 Create a user

To create a new user, make a POST request to localhost:8080/api/users.

The body of the request should be like that:



{
    "name": "aaa",
    "email": "aaa@mail"
}


Enter fullscreen mode Exit fullscreen mode

The output should be something like that:

create user

Let's create two more users, make a POST request to localhost:8080/api/users.



{
    "name": "bbb",
    "email": "bbb@mail"
}


Enter fullscreen mode Exit fullscreen mode


{
    "name": "ccc",
    "email": "ccc@mail"
}


Enter fullscreen mode Exit fullscreen mode

📝 Get all users

To get all users, make a GET request to localhost:8000/api/users.

The output should be something like that:

get all users

📝 Get a user

To get a user, make a GET request to localhost:8000/api/users/{id}.

For example GET request to localhost:8000/api/users/1.

The output should be something like that:

get a user

📝 Update a user

To update a user, make a PUT request to localhost:8000/api/users/{id}.

For example PUT request to localhost:8000/api/users/2.

The body of the request should be like that:



{
    "name": "Francesco",
    "email": "francesco@mail"
}


Enter fullscreen mode Exit fullscreen mode

The output should be something like that:

update a user

📝 Delete a user

To delete a user, make a DELETE request to localhost:8000/api/users/{id}.

For example DELETE request to localhost:8000/api/users/1.

On Postman you should see something like that:

delete a user

Final test

As a final test, we can check the database using TablePlus.

tableplus


🏁Conclusion

We made it! we built a CRUD Rest API project in C# (or C sharp), using:

  • .NET 7
  • ASP.NET (Framework for building web apps)
  • Entity Framework (ORM)
  • Postgres (Database)
  • Docker (Containerization)
  • Docker Compose (To run the database and the application)

Video version:

All the code is available in the GitHub repository (link in the video description): https://youtube.com/live/PY6uD1fgzZA

That's all.

If you have any question, drop a comment below.

Francesco

Top comments (22)

Collapse
 
vladissvostok2000 profile image
VladisS-Vostok2000
// POST: api/users
  [HttpPost]
  public async Task<ActionResult<User>> PostUser(User user)
  {
    _context.Users.Add(user);
    await _context.SaveChangesAsync();

    return CreatedAtAction(nameof(GetUser), new { id = user.Id }, user);
  }
Enter fullscreen mode Exit fullscreen mode

Is there expected PostUser?
return CreatedAtAction(nameof(PostUser), new { id = user.Id }, user);

Collapse
 
cawoodm profile image
Marc

Check your code formatting. On iOS Chrome at least it’s garbled.

Collapse
 
francescoxx profile image
Francesco Ciulla

do you mean the brackts?

Collapse
 
cawoodm profile image
Marc

We see:

Let's create two more users, make a POST request to localhost:8080/api/users.

`json
{
"name": "bbb",
"email": "bbb@mail"
}
`
Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
francescoxx profile image
Francesco Ciulla

oh there was a trailing backtick. solved thanks.

Collapse
 
siknik profile image
SikNik

Thanks for the cool tutorial, also watched your video thanks very much!

Not sure if anyone else picked this up from the walkthrough guide on this page above, at the section where we get users? Its pointing to port 8000 which is invalid and the users only return on port 8080, which is correct in accordance with our configs...

The guide here on this page just says otherwise...

📝 Get all users
To get all users, make a GET request to localhost:8000/api/users.

The output should be something like that:

Collapse
 
francescoxx profile image
Francesco Ciulla

oh I will check that, thank you! maybe I was using a different port on that specific part!

Collapse
 
galezovsky profile image
Bartek

You could also add validation for User type to prevent incorrect data and reject request on start, not during writing to database. Obviously that will give you ability to check other things such as e.g. format of e-mail addresses.

Collapse
 
francescoxx profile image
Francesco Ciulla

that would probably be a good idea

Collapse
 
bringablazer profile image
BringABlazer

Very nice - just a question of if it is on purpose that you skipped the explanation of your docker files?

Collapse
 
francescoxx profile image
Francesco Ciulla

fixed, thanks

Collapse
 
vladissvostok2000 profile image
VladisS-Vostok2000

I repeated all steps this awesome guide, but face the problem with creating a user:
MessageText: relation "users" does not exist
I fixed it renaming all colums and table to lower case, and it works.

Collapse
 
francescoxx profile image
Francesco Ciulla

you are correct. I adapted the c# code for a better readability but left the old SQL file. nice catch.

Collapse
 
joetogee profile image
Joe

Super helpful, really helped launch my app. Thank you!

Collapse
 
francescoxx profile image
Francesco Ciulla

you are welcome Joe

Collapse
 
1chris_alex profile image
Christian Alexsander

Can you do a part 2 using entity migrations?

Collapse
 
francescoxx profile image
Francesco Ciulla

that's actually a good idea

Collapse
 
softwaresennin profile image
Lionel♾️☁️

Awesome article @francescoxx I really enjoyed the read.

Please keep it up.

Collapse
 
francescoxx profile image
Francesco Ciulla

you are welcome Lionel!

Collapse
 
nayeemgithub profile image
Nayeem

👍

Collapse
 
francescoxx profile image
Francesco Ciulla

🔥🔥