LinkedIn
GitHub profile
Проекты
Advertisement
RSS
.NET Framework .NET C# VB.NET LINQ ASP.NET Web API REST SignalR Windows Forms WPF WCF RabbitMQ PHP SQL Server MySQL PostgreSQL MariaDB SQLite MongoDB ADO.NET ORM Entity Framework Dapper XML JSON HTML5 CSS3 Bootstrap JavaScript jQuery Angular React TypeScript NPM Blazor UI/UX Responsive Web Design Redis Elasticsearch GraphQL Grafana Agile Scrum Kanban Windows Server IIS PowerShell Active Directory TFS Azure Automation Software Reverse Engineering Performance Optimization Git Jira/Confluence CI/CD TeamCity SOLID KISS DRY YAGNI
Articles
Always will be ready notify the world about expectations as easy as possible: job change page

Database Setup with DbUp + PostgreSQL + Dapper in ASP.Net Core

Created: Jan 11, 2023
Author: Nitesh Singhal
Source: source
Views: 316

Database Setup with DbUp + Postgresql + Dapper in ASP.Net Core

In this tutorial, we are going to explore how we can setup our database on startup when using Dapper for accessing database.

When using Dapper, one of the key learning I came to know is that we have to have database and tables already created in advance in order to read/write data.

So we have to write some kind of migration logic in our code before we access database.

Now we could do that our own, but what if there is any library which could this for you.

Or if you have your SQL scripts already written and you want to reuse them for your database creation.

DbUp can help us in achieving all of it.

Let’s understand with example.

Example

Create a ASP.Net core web API in Visual Studio Or VSCode.

as mention I am using Postgresql for database, so will add the required nuget package.

Install-Package Dapper
Install-Package Npgsql

and Create a database and Create Table manually using PgAdmin tool.

Database creation

Table creation

Let’s define the connection string in appsetting.json

Connection string

Repository

Interface for Repository

One sample example using Dapper

Controller

Sample controller

We can run our app and test if we are able to read/write data.

Using DbUp

This was good till now, but there was one problem, I had to create database and tables manually.

Now this is ok for development but for production scenario specially automated deployment scenarios this will not be OK.

DbUp can help us integrate sql script and setup database on startup.

First cleanup the database by dropping previously created database and tables.

Add the required nuget package

Install-Package dbup-postgresql

Let’s add those script to project.

Project structure with script folder

SQL Script for creating table

Embedding the script as a project resource

and Create a extension class for adding Migration code using DbUp.

public static class DatabaseExtension
{
    public static IHost MigrateDatabase<TContext>(this IHost host)
    {
        using (var scope = host.Services.CreateScope())
        {
            var services = scope.ServiceProvider;
            var configuration = services.GetRequiredService<IConfiguration>();
            var logger = services.GetRequiredService<ILogger<TContext>>();

            logger.LogInformation("Migrating postresql database.");

            string connection = configuration.GetValue<string>("DatabaseSettings:ConnectionString");

            EnsureDatabase.For.PostgresqlDatabase(connection);

            var upgrader = DeployChanges.To
                .PostgresqlDatabase(connection)
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                .LogToConsole()
                .Build();

            var result = upgrader.PerformUpgrade();

            if (!result.Successful)
            {
                logger.LogError(result.Error, "An error occurred while migrating the postresql database");
                return host;
            }

            logger.LogInformation("Migrated postresql database.");
        }

        return host;
    }
}

Program.cs

Changes in Program.cs

Our changes are ready. Let’s run the app and see the output window

Output window

As we can in the output window logs, it executed the SQL scripts and created the required database and tables.

Now we can call the API and query for data.

You can find full source code at GitHub.

Summary

Using DbUp we can easily integrate our existing SQL Scripts and make the database ready before any operation.

It is supported for different databases.

https://dbup.readthedocs.io/en/latest/supported-databases/

Hope it is helpful.

Similar
Jan 1, 2023
Author: Daniel Kreider
Here’s the simple step-by-step guide that will teach you how to build and code a generic repository.There are oodles of design patterns.Some of these design patterns are floating about on antique blogs full of mad logic. They’re ridiculous enough to...
Jul 16, 2021
Author: Edward Pollack
Monitoring SQL Server for performance is a critical part of a DBA’s job. In this article, Edward Pollack explains some of the SQL Server performance monitor data and how to collect it.Windows collects a wide array of useful metrics that...
Dec 19, 2020
SQLite and PostgreSQL are among the most widely used relational database management systems (RDMS). They are both open-source and free, but they have some major differences that should be considered when choosing a database to use for your business.1. Principle...
Mar 28, 2023
Author: Anupam Maiti
IntroductionThis article demonstrates Middleware concepts in ASP.NET Core. At the end of this article, you will have clear understanding on below points: What is Middleware? Why Middleware ordering is important? Understanding...
Send message
Email
Your name
*Message


© 1999–2023 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET | .NET Core | C# | ASP.NET | Windows Forms | WPF | Windows Phone | HTML5 | CSS3 | jQuery | AJAX | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban