Connecting PostgreSQL from C#

PostgreSQL is probably one of the most popular and advanced databases presently available today. The best part is it is open source and has been in development for over 20 years. To the unversed, PostgreSQL is an object-oriented database managed by a thriving and well-organized open-source community.

Since it is highly flexible, you can add new features and capabilities as your needs evolve. In this post, we will connect to this database from the popular programming language C#.

Table of contents

1. Connecting PostgreSQL from C#
2. Conclusion

Connecting PostgreSQL from C#

PostgreSQL is known for its reliability, robustness, and performance. It supports both relational (SQL) and JSON querying. It can store text, images, audio, and video and can be connected to most languages, including C, C++, Java, Perl, Python, C#, etc.

To connect to PostgreSQL from C#, we need the following tools and framework to be installed on the local machine (we are considering our machine as the database server) -

• Visual Studio 2022
• .Net 6
• PostgreSQL Server and PgAdmin

In this guide, we are going to follow the database-first approach, where we will first create the database and tables that are required for the project. We will then try to utilize them with the help of our code and perform some operations on them.

To install PostgreSQL, visit the following link and install the required version - Download

Once installed, the PostgreSQL server can be accessed through PgAdmin. It provides an interface like the one below -

The next step is to create a database for our mini-project. To do that, right-click on the databases option and choose to Create -> Database. And provide a valid name for the database.

On saving, a new database will be created. Let us name it ‘PARTECH’.

The next step is to create tables in the database. Right-click the newly created database (that got listed), and select the Query Tool option.

Run the below command in the window.

 CREATE TABLE UserData (
           	UserName varchar(100) primary key NOT NULL,
           	Technology varchar(100) NOT NULL
)

This will create a UserData table in the PARTECH database. Here, we can add records using the insert command.

The required database part for this mini-project is set up and ready now. Let us jump into the .Net part of it.

To connect PostgreSQL from .Net, we are going to create an ASP .Net Web API project and choose the .Net version as 6.

Once the project is created, the folder structure of the project will look like the one below.

Next, we are going to add the required NuGet packages to access the PostgreSQL DB as ORM from the code.

Install Npgsql.EntityFrameworkCore.PostgreSQL and Microsoft.EntityFrameworkCore.Tools packages from the NuGet Package Manager.

Now, we have the packages to access PostgreSQL from C# code and the database is all set. Let us bring in the context of the database and tables in it as classes. We are going to achieve this by a process called DB scaffolding. To do that, open the Package Manager Console.

In the PM console, run the below command.

Scaffold-DbContext “Host=localhost;Database=database_name;Username=user_name;Password=password” Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir Models

On running the above command, a new Folder gets created in the solution explorer called Models. And it will have the database context file and the tables as classes in it.

When we open the PARTECHContext.cs file, we will find that under the onConfiguring method, the connection string required to connect to the database is hard-coded. We can move this config to appsettings.json and access it from there using the program.cs file. For simplicity, we are leaving it as it is.

Now that we have the context ready in our code, the next step is to make the context injected so that it is available throughout the code. To achieve it, open the Program.cs file and add the below line of code before the builder.Build() command.

builder.Services.AddDbContext<PARTECHContext>();

The next step is to create a new Controller to access the User data. Do this by right-clicking the Controller folder and adding an empty controller. Name it as UserController.cs.

Copy-paste the below set of code into the UserController.cs file

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using PARTECH_Postgresql.Models;

namespace PARTECH_Postgresql.Controllers
{
    [Route("api/[controller]")]
    public class UserController : Controller
    {
        public PARTECHContext _context;
        public UserController(PARTECHContext context)
        {
            _context = context;
        }

        [HttpGet("GetDetails")]
        public async Task<List<Userdatum>> GetDetails()
        {
            return await _context.Userdata.ToListAsync();
        }
    }
}

The above code indicates that the Controller can be accessed through the route baseurl/api/user and it has one constructor which takes up the DB context injection. It also has an endpoint which is of the type HttpGet. It can be accessed through the URL - baseurl/api/user/getdetails.

On running the above URL from any browser/postman/swagger, the code control will come to the GetDetails method. Inside the GetDetails method, code is written in such a way that it returns all the data present in the userdata table.

On running the project from the Visual Studio, it opens up the swagger that comes pre-built with the WebAPI template. The below screen appears on running the project. It shows up all the available endpoints in the solution.

For our scenario, we can go ahead and execute the Get endpoint below the User section. On clicking ‘Try out’ and then ‘Execute’, the below output gets displayed, which is the exact data that is present in the database. ## Conclusion And that’s pretty much everything about connecting PostgreSQL from C#. Remember, PostgreSQL is a powerful and robust database and accessing it from WebAPI ( a . Net-based application) enhances its scope. It also serves as an alternative for MS-SQL. So go ahead and design your next project based on these systems.