Connect C# to PostgreSQL

This is a repost of my original article published at my blog: Unit Coding go and check it out! ✨

Hello programmer folks, yet another day I’m here with you to happily bring you some useful knowledge. Today continuing with my database topics I will show you how you can connect your c# application to postgre SQL and you might wonder why will you ever need or want to use postgre as your database if you have SQL server which is the most common database among C# developers and applications and I will give you some good reasons to use it.

Why Postgre? ❓

Well as I told you one second ago the most commonly used database among C#/.NET developers is SQL server as this is the microsoft duo and is widely used by big enterprises to build complex systems for all sort of applications. But the main drawback of SQL server is that out of the box you can only host it on windows servers which are expensive and if you want to deploy that means you would have to pay for that. Whereas Postgre can be hosted on linux server which are cheaper than windows and in many cases you can host your application with a postgre database for free on some platforms and that is the main reason for knowing how to connect your c# apps to a postgre database so you can store your apps paying last than you would using SQL server or in many cases not paying at all. So having said this let’s get our hands dirty doing some code! 💪🏾

Prerequisites 📃

In order to follow this tutorial you need to have installed any version of Postgre and pgAdmin which we can say is the gui to view all the database data and tables. If you would like me to write a post on installing Postgre and pgAdmin please let me know it in the comments, I would be more than happy to do it.

Also you need to install the Entity Framework Core tools with our dotnet cli, to install it write the following command on your terminal.

dotnet tool install --global dotnet-ef

After this write this other command on your terminal dotnet ef and you should see an output like the one below.

                                         _/\__
               ---==/    \\
         ___  ___   |.    \|\
        | __|| __|  |  )   \\\
        | _| | _|   \_/ |  //|\\
        |___||_|       /   \\\/\\

Entity Framework Core .NET Command-line Tools 2.1.3-rtm-32065

<Usage documentation follows, not shown.>

After this you are ready and set to go!

Create a new web Project 🕸️

For this example I will use an asp net core web api project but you can use this code for MVC, Razor or minimal apis projects as the structure of them are pretty much the same. So create a new web application using the dotnet cli.

dotnet new webapi -o [Name of your project here]

Code First approach 👨🏾‍💻

I know I’m writing this article for very beginners but in this example I make use of this approach to ensure that we can see data being insert into the database so I will try to explain briefly what is the code first approach.

The code first approach is one of the three approaches that Entity Framework provides to work with data. What this approach allows us is to work with C# objects instead of working directly with the database or any other library like ADO.NET this is pretty useful for us developers because we can keep working with code when trying to communicate to Postgre.

Add a Person model 🙍🏾

We will create a Person model which in place will be our table in SQL server and we will insert our object’s data into that sql table, therefore the code will end up looking like this.

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
}

This class is pretty simple we only add the classic fields for a Person object being this the Id, name, last name and the age of the person. This would be all for the person class.

Install Entity Framework nuget packages 📦

As I mentioned earlier we will use Entity Framework to communicate to the SQL database and to map our person object to the person table in SQL. We will install three EF packages, you can look for them in the nuget package manager on visual studio, use the package manager console or like in our case install the using the dotnet cli.

dotnet add package Microsoft.EntityFrameworkCore.Core

dotnet add package Microsoft.EntityFrameworkCore.Tools

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Now that you have installed the EF core package into your project we will proceed to create the AppDbContext.cs class.

Create the AppDbContext.cs class

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Person>().HasData(
            new Person {Id = 1, Name = "Oscar", LastName = "Montenegro", Age = 28},
            new Person {Id = 2, Name = "Yolanda", LastName = "Montenegro", Age = 27}
        );

        base.OnModelCreating(builder);
    }

    public DbSet<Person> People { get; set; }
}

First, we must create the AppDbContext class and make it inherit from the DbContext class and create an empty constructor calling the base constructor from the parent class. Then we are overriding the OnModelCreating function and using the model builder to tell entity framework that our collection should have data on the moment of being created on SQL and we are passing two objects inside the HasData function. Last but not least we create a DbSet property called people which is the plural for Persons as is a good practice that when you create a table for a specific entity to use the plural for that entity.

Add the connection string 🧵

Go to your appsettings.json file (if you have one if not create it) and add the connection string to your sql server instance as you can see below but without the square brackets:

"ConnectionStrings": {
    "PgDbConnection" : "Server=localhost;Database=MyFirstDb;Port=5432;User Id =YourUserId;Password=YourPassword;"
  }

Server: We will use for this example the localhost but you can use whatever server you desire to use.

Database: This is the name of your data base but as this is a code first approach there ir no existing database so you can give this database any name you want and it will be created with this name.

Port: Postgre default port is typically 5432 if this is not available on your machine you can choose the next available port for example 5433 and so on.

*******User Id:******* The default super user and the one that I’m using for this example is “Postgres” and you can use it too or you can use your own user Id if you have any.

***************Password:*************** For this one you have to use the password you created for your postgre user.

Execute EF commands 🪖

Lastly we need to execute some ef commands to create the database from our code.

dotnet ef migrations add "Initial Migration"

dotnet ef database update

After executing this two commands you should be able to go into pgAdmin and see the database created and inside the schemas there should be a table called People and in turn inside this table you soul have two records which are the records we entered into our context class.

Conclusion 🌇

And that is it now you can start adding more tables creating C# classes and adding the to the AppDbContext to make your application more complete and complex. I know this post was almost identical to my last where I teached you how to do the same with SQL Server but I did not wanted to keep telling you “If you want to know how to do tihs or that check my last post it’s almost identical”. I wanted this to be it’s own post about integrating Postgre into your c# application and for my next post I promise I will talk about something toatlly diferent 🤞🏾.

Thanks for your time and for deciding reading my post, stay tuned for my next releases here ir on my youtube channel Unit Coding 🎥 see you on my next post!