This is a repost from my original article on my blog: Connect C# to PostgreSQL (unitcoding.com) go 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 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.
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!