In this post, we will see how to manage the PostgreSQL database created in the post “Python – Management of a PostgreSQL database”, using C#.
We will define a Minimal API for managing the database using C# and Dapper, combined with the Repository Pattern. Finally, we will add the Unit Test to check that all methods of the Repository Pattern work fine.
First of all, we run a Docker container for PostgreSQL then, we create the database called TestDB and finally, we create a table called User:
docker run --name db_postgres -e POSTGRES_USER=Admin -e POSTGRES_PASSWORD=Pass123 -p 5432:5432 -d postgres
Now, we create an ASP.NET Core Web API project called TestPostgreSQL where, we will install all necessary packages:
dotnet add package Dapper
dotnet add package Npgsql
dotnet add package Microsoft.Extensions.DependencyInjection
dotnet add package Moq
dotnet add package xUnit
Then, we will continue by defining Model, Repository and Service:
[USER.CS] – Model
namespace TestPostgreSQL.Model;
public class User
{
public int Id { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public string Email { get; set; }
}
[IUSERREPOSITORY.CS] – Repository interface
using TestPostgreSQL.Model;
namespace TestPostgreSQL.Repository;
public interface IUserRepository
{
Task<IEnumerable<User>> GetAllAsync();
Task<User> GetByIdAsync(int id);
Task AddAsync(User user);
Task UpdateAsync(User user);
Task DeleteAsync(int id);
}
[USERREPOSITORY.CS] – Repository implementation using Dapper
using Dapper;
using Npgsql;
using TestPostgreSQL.Model;
namespace TestPostgreSQL.Repository;
public class UserRepository: IUserRepository
{
private readonly string _connectionString;
public UserRepository(string connectionString)
{
_connectionString = connectionString;
}
public async Task<IEnumerable<User>> GetAllAsync()
{
using var connection = new NpgsqlConnection(_connectionString);
return await connection.QueryAsync<User>("SELECT * FROM public.\"User\"");
}
public async Task<User> GetByIdAsync(int id)
{
using var connection = new NpgsqlConnection(_connectionString);
return await connection.QueryFirstOrDefaultAsync<User>("SELECT * FROM public.\"User\" WHERE \"Id\" = @Id", new { Id = id });
}
public async Task AddAsync(User user)
{
using var connection = new NpgsqlConnection(_connectionString);
try
{
await connection.OpenAsync();
// Define the SQL query with parameter placeholders
var sql = @"
INSERT INTO public.""User"" (""Username"", ""Password"", ""Email"")
VALUES (@Username, @Password, @Email)
RETURNING ""Id"";";
// Execute the query and retrieve the generated Id
user.Id = await connection.ExecuteScalarAsync<int>(sql, new
{
user.Username,
user.Password,
user.Email
});
}
catch (NpgsqlException ex)
{
// Log the error and rethrow
Console.WriteLine($"Error: {ex.Message}");
throw new Exception("An error occurred while inserting the user into the database.", ex);
}
}
public async Task UpdateAsync(User user)
{
using var connection = new NpgsqlConnection(_connectionString);
try
{
await connection.OpenAsync();
var sql = @"
UPDATE public.""User""
SET ""Username"" = @Username,
""Password"" = @Password,
""Email"" = @Email
WHERE ""Id"" = @Id";
await connection.ExecuteAsync(sql, new
{
user.Username,
user.Password,
user.Email,
user.Id
});
}
catch (NpgsqlException ex)
{
// Log and handle the error
Console.WriteLine($"Error: {ex.Message}");
throw new Exception("An error occurred while updating the user in the database.", ex);
}
}
public async Task DeleteAsync(int id)
{
using var connection = new NpgsqlConnection(_connectionString);
await connection.ExecuteAsync("DELETE FROM public.\"User\" WHERE \"Id\" = @Id", new { Id = id });
}
}
[USERSERVICE.CS] – Service layer that encapsulates business logic
using TestPostgreSQL.Model;
using TestPostgreSQL.Repository;
namespace TestPostgreSQL.Services;
public class UserService
{
private readonly IUserRepository _userRepository;
public UserService(IUserRepository userRepository)
{
_userRepository = userRepository;
}
public Task<IEnumerable<User>> GetUsersAsync()
{
return _userRepository.GetAllAsync();
}
public Task<User> GetUserAsync(int id)
{
return _userRepository.GetByIdAsync(id);
}
public Task AddUserAsync(User user)
{
return _userRepository.AddAsync(user);
}
public Task UpdateUserAsync(User user)
{
return _userRepository.UpdateAsync(user);
}
public Task DeleteUserAsync(int id)
{
return _userRepository.DeleteAsync(id);
}
}
Now, we will define the configuration file and the Unit Test for the repository:
[APPSETTINGS.JSON] – Configuration file
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"ConnectionStrings": {
"DefaultConnection": "Host=localhost;Username=Admin;Password=Pass123;Database=TestDB"
},
"AllowedHosts": "*"
}
[USERREPOSITORYTEST.CS] – Unit Test for all methods in UserRepository
using Moq;
using TestPostgreSQL.Model;
using TestPostgreSQL.Repository;
using TestPostgreSQL.Services;
using Xunit;
namespace TestPostgreSQL.UnitTest;
public class UserRepositoryTests
{
private readonly Mock<IUserRepository> _userRepositoryMock;
private readonly UserService _userService;
public UserRepositoryTests()
{
_userRepositoryMock = new Mock<IUserRepository>();
_userService = new UserService(_userRepositoryMock.Object);
}
[Fact]
public async Task GetAllAsync_ShouldReturnAllUsers()
{
// Arrange
var users = new List<User>
{
new User { Id = 1, Username = "user1", Password = "pass1", Email = "user1@example.com" },
new User { Id = 2, Username = "user2", Password = "pass2", Email = "user2@example.com" }
};
_userRepositoryMock.Setup(repo => repo.GetAllAsync()).ReturnsAsync(users);
// Act
var result = await _userService.GetUsersAsync();
// Assert
Assert.Equal(2, result.Count());
Assert.Equal("user1", result.First().Username);
}
[Fact]
public async Task GetByIdAsync_ShouldReturnCorrectUser()
{
// Arrange
var user = new User { Id = 1, Username = "user1", Password = "pass1", Email = "user1@example.com" };
_userRepositoryMock.Setup(repo => repo.GetByIdAsync(1)).ReturnsAsync(user);
// Act
var result = await _userService.GetUserAsync(1);
// Assert
Assert.Equal("user1", result.Username);
Assert.Equal("pass1", result.Password);
}
[Fact]
public async Task GetByIdAsync_ShouldReturnNullIfUserNotFound()
{
// Arrange
_userRepositoryMock.Setup(repo => repo.GetByIdAsync(It.IsAny<int>())).ReturnsAsync((User)null);
// Act
var result = await _userService.GetUserAsync(1);
// Assert
Assert.Null(result);
}
[Fact]
public async Task AddAsync_ShouldCallRepositoryAdd()
{
// Arrange
var user = new User { Username = "user1", Password = "pass1", Email = "user1@example.com" };
// Act
await _userService.AddUserAsync(user);
// Assert
_userRepositoryMock.Verify(repo => repo.AddAsync(user), Times.Once);
}
[Fact]
public async Task UpdateAsync_ShouldCallRepositoryUpdate()
{
// Arrange
var user = new User { Id = 1, Username = "user1", Password = "newpass", Email = "newemail@example.com" };
// Act
await _userService.UpdateUserAsync(user);
// Assert
_userRepositoryMock.Verify(repo => repo.UpdateAsync(user), Times.Once);
}
[Fact]
public async Task DeleteAsync_ShouldCallRepositoryDelete()
{
// Arrange
var userId = 1;
// Act
await _userService.DeleteUserAsync(userId);
// Assert
_userRepositoryMock.Verify(repo => repo.DeleteAsync(userId), Times.Once);
}
[Fact]
public async Task DeleteAsync_ShouldNotThrowIfUserNotFound()
{
// Arrange
_userRepositoryMock.Setup(repo => repo.DeleteAsync(It.IsAny<int>())).Returns(Task.CompletedTask);
// Act
var exception = await Record.ExceptionAsync(() => _userService.DeleteUserAsync(1));
// Assert
Assert.Null(exception);
}
}
If we run the Unit Test, this will be the result:
Finally, we will modify the file Program.cs to set up the Minimal API:
[PROGRAM.CS]
using TestPostgreSQL.Model;
using TestPostgreSQL.Repository;
using TestPostgreSQL.Services;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
// Add services to the container.
builder.Services.AddTransient<IUserRepository>(provider =>
new UserRepository(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddTransient<UserService>();
var app = builder.Build();
app.UseHttpsRedirection();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
// Configure the HTTP request pipeline.
app.MapGet("/users", async (UserService userService) => await userService.GetUsersAsync());
app.MapGet("/users/{id:int}", async (int id, UserService userService) =>
{
var user = await userService.GetUserAsync(id);
return user is not null ? Results.Ok(user) : Results.NotFound();
});
app.MapPost("/users", async (User user, UserService userService) =>
{
await userService.AddUserAsync(user);
return Results.Created($"/users/{user.Id}", user);
});
app.MapPut("/users/{id:int}", async (int id, User user, UserService userService) =>
{
var existingUser = await userService.GetUserAsync(id);
if (existingUser is null)
return Results.NotFound();
user.Id = id;
await userService.UpdateUserAsync(user);
return Results.Ok(user);
});
app.MapDelete("/users/{id:int}", async (int id, UserService userService) =>
{
var user = await userService.GetUserAsync(id);
if (user is null)
return Results.NotFound();
await userService.DeleteUserAsync(id);
return Results.NoContent();
});
app.Run();
Now, using a Rest API client like Postman, we will chek that everything works fine:
INSERT
LIST
UPDATE
GET BY ID
DELETE
