How to Create a CRUD in ASP.NET Core Web API Using PostgreSQL: A Step-by-Step Guide

In this blog post, I will guide you through the process of creating a Web API using PostgreSQL as the database. This tutorial is based on the .NET 6 framework and follows the Database-First approach, which involves designing the database schema first and then generating models from the existing database. In this tutorial, we will create a table (entity) named "Users" with four columns: userid, username, email, and password. I'm providing a GitHub repository link for this project, allowing you to access the code as a point of reference.

Create a New Web API Project:

• Open Visual Studio.
• Click on "Create a new project."
• In the project creation dialog, search for "ASP.NET Core Web Application."
• Select this template and click "Next."

Configure the Project:

• Specify the project name and location.
• Choose the .NET 6.0 framework from the dropdown.
• Click "Create" to create the project.

Install the Required Packages:

• Once the project is created, right-click on the project in the Solution Explorer.
• Select "Manage NuGet Packages" from the context menu.

In the NuGet Package Manager, search for and install the following packages:

• "Npgsql.EntityFrameworkCore.PostgreSQL": This package allows you to use Entity Framework Core with PostgreSQL as your database engine.
• "Microsoft.EntityFrameworkCore": This is the core package for Entity Framework (EF) Core, providing essential functionality and APIs for working with databases using EF Core.

Create a Models Folder and UserModel.cs File:

• Create a "Models" folder in your project.
• Inside the "Models" folder, create a new C# class file named "UserModel.cs."
• In the "UserModel.cs" file, define the properties that represent the fields of the "Users" table.

Create a DAL (Data Access Layer) Folder and UserRepository.cs File:

• Create a "DAL" folder within your project to house your data access layer components.
• Inside the "DAL" folder, create a C# class file named "UserRepository.cs."
• The primary purpose of this class is to encapsulate data access logic, providing an organized and structured way to perform database operations on the "Users" table.

Define Database Configuration in appsettings.json:

• Open the "appsettings.json" file in your project.
• Add a "ConnectionStrings" object to hold the database configuration settings.
• Within the "ConnectionStrings" object, specify the connection details for your database, including the server, port, database name, user ID, and password.

  

Configure Npgsql Service in Program.cs:

• In the "Program.cs" file of your ASP.NET Core Web API project, configure the Npgsql service to handle database connections.
• This service will use the database connection string defined in the "appsettings.json" file for database connectivity.

Create UserController in the Controllers Folder:

• Within the "Controllers" folder of your project, add a "UserController.cs" file.
• In this file, define the HTTP methods for the CRUD (Create, Read, Update, Delete) operations related to the "Users" table.
• The code in this controller handles interactions with the "Users" table, such as retrieving user data, adding new users, updating user information, and deleting user records.

Build and Run the Project:

• Once you have implemented the necessary controllers and configurations, build and run your ASP.NET Core Web API project.
• This will launch the project, and you can access the Swagger UI through a web browser. Swagger provides a user-friendly interface for testing your API endpoints and exploring the available functionality.

Test Each API Method Using Swagger in browser:

• In Swagger, you'll see a list of Get, Post, Put and Delete endpoints.
• Click on an endpoint to expand it.
• Click on a method (e.g., "GET") to test it.
• For Post, Put and Delete Endpoits, provide parameters or request body data.
• Click "Execute" to test the method.
• You can able to View the response to see if the operation was successful.