Connect C# to PostgreSQL
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 is pretty much the same. So let's 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 inserted 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 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 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 database 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 Postgres user.
Register your AppDbContext in the Program file
Now you can go and register your AppDbContext into the program file and pass the connection string to be able to open the communication with Postgre SQL.
builder.Services.AddEntityFrameworkNpgsql().AddDbContext<AppDbContext>(opt =>
{
opt.UseNpgsql(configuration.GetConnectionString("PgDbConnection"));
});Add this line of code below the other services registration and above the app building line.
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 these 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 should 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 them to the AppDbContext to make your application more complete and complex. I know this post was almost identical to my last where I teach you how to do the same with SQL Server but I did not want to keep telling you “If you want to know how to do this or that check my last post it’s almost identical”. I wanted this to be its own post about integrating Postgre into your c# application and for my next post I promise I will talk about something totally different 🤞🏾.
Thanks for your time and for deciding to read my post, stay tuned for my next releases here ir on my youtube channel Unit Coding 🎥 see you on my next post!