When building applications in ASP.NET Core, it is essential to have a robust database system in place. One of the challenges of working with databases is managing database schema changes, and this is where DbUp comes in handy. DbUp is a popular library that helps you deploy database changes in a structured and consistent manner. In this article, we will go through how to set up a database using DbUp with Postgresql and Dapper in an ASP.NET Core application.
Databases are an essential part of most applications, providing a reliable and scalable way to store and retrieve data. In this article, we will go through setting up a PostgreSQL database in an ASP.Net Core application using DbUp and Dapper.
DbUp is a popular open-source library used for database schema migration, while Dapper is an Object-Relational Mapping (ORM) tool that simplifies database access. We will use these tools to create a database and tables, insert and retrieve data, and upgrade the database schema.
Prerequisites:
Before we begin, make sure that you have the following software installed on your machine:
Visual Studio or Visual Studio Code
.Net Core SDK
PostgreSQL
Creating a new ASP.Net Core Application:
To get started, let's create a new ASP.Net Core application in Visual Studio. Open Visual Studio and select "Create a new project." Select "ASP.Net Core Web Application" from the list of templates and click "Next."
Give your project a name and click "Create." Select the "Web Application" template and click "Create" again.
Setting up the Database:
To set up the PostgreSQL database, we will use the Npgsql library, which is the .Net Core driver for PostgreSQL. To install it, open the NuGet Package Manager Console in Visual Studio and type the following command:
Install-Package Npgsql.EntityFrameworkCore.PostgreSQLNext, let's create a new PostgreSQL database. Open pgAdmin, the PostgreSQL administration tool, and connect to your PostgreSQL server. Right-click on the "Databases" folder and select "New Database." Give your database a name and click "Save."
Now, let's create a table in the database. Open the SQL Query tool in pgAdmin and run the following script:
CREATE TABLE Users (
Id SERIAL PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Email VARCHAR(50) NOT NULL UNIQUE,
Password VARCHAR(50) NOT NULL
);This script creates a table called "Users" with four columns: "Id", "Name", "Email", and "Password." The "Id" column is set to auto-increment, while the "Email" column is set to unique.
Next, let's create a DbUp migration script to create this table automatically.
Setting up DbUp:
DbUp is a library that makes it easy to manage database schema changes through code. To install DbUp, open the NuGet Package Manager Console in Visual Studio and type the following command:
Install-Package DbUpNext, let's create a new class called "DatabaseMigrator" that will handle running the migration scripts. Here's the code:
using System;
using System.Reflection;
using DbUp;
using Microsoft.Extensions.Configuration;
namespace MyApp.Database
{
public class DatabaseMigrator
{
private readonly string connectionString;
public DatabaseMigrator(IConfiguration configuration)
{
connectionString = configuration.GetConnectionString("DefaultConnection");
}
public void MigrateDatabase()
{
EnsureDatabase.For.PostgresqlDatabase(connectionString);
var upgrader =
DeployChanges.To
.PostgresqlDatabase(connectionString)
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.WithTransaction()
.LogToConsole()
.Build();
var result = upgrader.PerformUpgrade();
if (!result.Successful)
{
throw new Exception("Failed to upgrade database", result.Error);
}
}
}
}
This class takes an IConfiguration parameter in its constructor, which will provide access to the connection string from the appsettings.json file.
The MigrateDatabase method ensures that the database exists and runs any migration scripts that need to be executed. The DeployChanges.To method sets up the migration, pointing to the Postgresql database using the connection string. We use the WithScriptsEmbeddedInAssembly method to look for any migration scripts embedded in the assembly and apply them in the correct order. We also enable transactions, log to the console, and build the upgrader.
Finally, we call PerformUpgrade to run the migration scripts. If any errors occur, an exception is thrown.
Running the Migration:
To run the migration, we need to call the MigrateDatabase method from our Startup class. Open the Startup.cs file and add the following code to the ConfigureServices method:
services.AddTransient<DatabaseMigrator>();This code registers the DatabaseMigrator class with the ASP.Net Core Dependency Injection container. Next, add the following code to the Configure method:
using (var scope = app.ApplicationServices.CreateScope())
{
var migrator = scope.ServiceProvider.GetService<DatabaseMigrator>();
migrator.MigrateDatabase();
}
This code creates a scope for the Dependency Injection container, retrieves an instance of the DatabaseMigrator class, and calls the MigrateDatabase method to run the migration scripts.
Now, when you run your ASP.Net Core application, the migration scripts will be executed automatically, creating the Users table in the database.
Accessing the Database with Dapper:
Now that we have a database set up, let's access it using Dapper. To install Dapper, open the NuGet Package Manager Console in Visual Studio and type the following command:
Install-Package DapperNext, let's create a UserRepository class that will handle all the database operations related to users. Here's the code:
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using Npgsql;
namespace MyApp.Database
{
public class UserRepository
{
private readonly string connectionString;
public UserRepository(string connectionString)
{
this.connectionString = connectionString;
}
public IEnumerable<User> GetUsers()
{
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return connection.Query<User>("SELECT * FROM Users");
}
}
public User GetUserById(int id)
{
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
return connection.Query<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = id }).FirstOrDefault();
}
}
public void CreateUser(User user)
{
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
connection.Execute("INSERT INTO Users (Name, Email, Password) VALUES (@Name, @Email, @Password)", user);
}
}
public void UpdateUser(User user)
{
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
connection.Execute("UPDATE Users SET Name = @Name, Email = @Email, Password = @Password WHERE Id = @Id", user);
}
}
public void DeleteUser(int id)
{
using (var connection = new NpgsqlConnection(connectionString))
{
connection.Open();
connection.Execute("DELETE FROM Users WHERE Id = @Id", new { Id = id });
}
}
}
}
This class takes the connection string in its constructor and provides methods to retrieve, create, update, and delete users from the database using Dapper.
In the GetUsers method, we use the Query method to retrieve all the users from the Users table. In the GetUserById method, we use the Query method again, this time with a parameter to retrieve a single user by ID. In the CreateUser method, we use the Execute method to insert a new user into the table. In the Update method, we use the Execute method to update an existing user in the table. And in the DeleteUser method, we use the Execute method to delete a user from the table by ID.
To use this repository in our application, we need to register it with the ASP.Net Core Dependency Injection container. Open the Startup.cs file and add the following code to the ConfigureServices method:
services.AddTransient<UserRepository>(provider => new UserRepository(Configuration.GetConnectionString("Default")));This code registers the UserRepository class with the Dependency Injection container, passing in the connection string from the configuration file.
Next, let's create a UsersController class that will handle the HTTP requests for user operations. Here's the code:
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using MyApp.Database;
namespace MyApp.Controllers
{
[ApiController]
[Route("[controller]")]
public class UsersController : ControllerBase
{
private readonly UserRepository userRepository;
public UsersController(UserRepository userRepository)
{
this.userRepository = userRepository;
}
[HttpGet]
public IEnumerable<User> Get()
{
return userRepository.GetUsers();
}
[HttpGet("{id}")]
public User Get(int id)
{
return userRepository.GetUserById(id);
}
[HttpPost]
public void Post([FromBody] User user)
{
userRepository.CreateUser(user);
}
[HttpPut("{id}")]
public void Put(int id, [FromBody] User user)
{
user.Id = id;
userRepository.UpdateUser(user);
}
[HttpDelete("{id}")]
public void Delete(int id)
{
userRepository.DeleteUser(id);
}
}
}
This class uses the UserRepository to handle the HTTP requests for retrieving, creating, updating, and deleting users.
In the Get method, we call the GetUsers method of the UserRepository to retrieve all the users from the database. In the Get method with an ID parameter, we call the GetUserById method of the UserRepository to retrieve a single user by ID. In the Post method, we deserialize the request body into a User object and call the CreateUser method of the UserRepository to insert a new user into the database. In the Put method, we deserialize the request body into a User object and set its ID to the ID parameter. Then we call the UpdateUser method of the UserRepository to update the user in the database. And in the Delete method, we call the DeleteUser method of the UserRepository to delete a user from the database by ID.
Finally, we need to add the UsersController to the ASP.Net Core pipeline. Open the Startup.cs file and add the following code to the Configure method:
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
This code maps the UsersController to the HTTP endpoints in the ASP.Net Core pipeline.
Conclusion:
In this article, we learned how to set up a Postgresql database with DbUp and access it using Dapper in an ASP.Net Core application. We used DbUp to run migration scripts that created a Users table in the database and used Dapper to access the table through a UserRepository class. We also created a UsersController class to handle the HTTP requests for user operations and registered it with the ASP.Net Core Dependency Injection container. Finally, we added the UsersController to the ASP.Net Core pipeline to handle the HTTP requests. With these techniques, you can easily set up a database and access it in your ASP.Net Core application.