One of the most used databases these days is PostgreSQL (aka Postgres). Its accomplishments include being the most popular DB [among professional developers] according to Stack Overflow survey of 2022, the database in all of the fastest TechEmpower benchmarks, and being the most advanced open source database (self-proclaimed).
We’ll talk today about combining Postgres with my favorite language and runtime: C# and .NET. Just because Postgres isn’t a first-party Microsoft database, like SQL Server, doesn’t mean you don’t have full support for it in C#. You absolutely do, and in this article, you’ll see modern ways to do just that. You’ll see how to interact between Postgres and C# using the standard data provider library Npgsql, use it with Dapper, and even with Entity Framework. We’ll go over the basic operations like creating a table, inserting rows, deleting, and updating records using all of these frameworks.
Installing Postgres and pgAdmin
To get started, go to postgresql.org/download and get the latest PostgreSQL version.
During the installation, you’ll be able to install pgAdmin, which is the management tool for Postgres. You can also use a different management tool like the excellent DataGrip (which is paid unfortunately).
Once installed, you can run pgAdmin and interact with the service. The first order of business is to create a server if it wasn’t created already.
Once a server is running, go ahead and create your first database.
As soon as you have your server and a database, you can start using it with C#.
Using Postgres in C# with the standard data provider (Npgsql)
The official package to use Postgres is Npgsql. Add it to your project with dotnet add package Npgsql.
Once installed, you can use this code to create your first table.
var con = new NpgsqlConnection(
connectionString: "Server=localhost;Port=5432;User Id=postgres;Password=passw0rd;Database=testdb;");
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"DROP TABLE IF EXISTS teachers");
await cmd.ExecuteNonQueryAsync();
cmd.CommandText= "CREATE TABLE teachers (id SERIAL PRIMARY KEY," +
"first_name VARCHAR(255)," +
"last_name VARCHAR(255)," +
"subject VARCHAR(255)," +
"salary INT)");
await cmd.ExecuteNonQueryAsync();
Looking at the connection string, you can see that my database’s name is testdb, the user name is postgres and the password is passw0rd. Port 5432 is the default port number for Postgres. You can see these settings in pgAdmin by right-clicking on the server -> Properties.
Let’s see some basic operations.
Adding records with Npgsql
// using the same 'cmd' (NpgsqlCommand) object from before
cmd.CommandText = $"INSERT INTO teachers (first_name, last_name, subject, salary) VALUES ('Severus', 'Snape', 'Potions', 10000)";
await cmd.ExecuteNonQueryAsync();
The above query can also be written as a parameterized query, this way:
cmd.CommandText = $"INSERT INTO teachers (first_name, last_name, subject, salary) VALUES (@firstName, @lastName, @subject, @salary)";
cmd.Parameters.AddWithValue("firstName", "Severus");
cmd.Parameters.AddWithValue("lastName", "Snape");
cmd.Parameters.AddWithValue("subject", "Potions");
cmd.Parameters.AddWithValue("salary", 10000);
await cmd.ExecuteNonQueryAsync();
Updating records with Npgsql
cmd.CommandText = $"UPDATE teachers" +
$" SET first_name='{newValues.FirstName}'," +
$"last_name='{newValues.LastName}'," +
$"subject='{newValues.Subject}'," +
$"salary={newValues.Salary}" +
$" WHERE id = {id}";
await cmd.ExecuteNonQueryAsync();
Deleting records with Npgsql
cmd.CommandText = $"DELETE FROM teachers WHERE id = {id}";
await cmd.ExecuteNonQueryAsync();
Reading records with Npgsql
public async Task<IEnumerable<Teacher>> GetBySubject(string subject)
{
cmd.CommandText = $"SELECT * FROM teachers WHERE subject='{subject}'";
NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();
var result = new List<Teacher>();
while (await reader.ReadAsync())
{
result.Add(new Teacher(
id: (int)reader["id"],
first_name: reader[1] as string, // column index can be used
last_name: reader.GetString(2), // another syntax option
subject: reader["subject"] as string,
salary: (int)reader["salary"]));
}
return result;
}
I left reading for last to compare to the way the same looks using Dapper.
Dapper
If you’re not familiar with Dapper, it’s a simple object mapper that allows you to query databases with regular SQL and map them to C# objects. It’s somewhere halfway between ADO.NET where you parse responses yourself to a full-blown ORM like Entity Framework.
To use Dapper with Postgres, install the NuGet package Dapper with dotnet add package Dapper.
Dapper adds extension methods like QueryAsync on the connection object NpgsqlConnection, so you don’t need to initialize anything extra.
The main advantage of Dapper is the auto-mapping in read operations. Instead of using NpgsqlDataReader, you can create a constructor with parameters matching column names and the code becomes much nicer. Here’s an example of implementing GetBySubject with Dapper:
public async Task<IEnumerable<Teacher>> GetBySubject(string subject)
{
return await con.QueryAsync<Teacher>(
$"SELECT * FROM {Teacher.TableName} WHERE subject='{subject}'");
}
For the above code to work, the class Teacher should have a constructor accepting all the table columns as parameters, like so:
public Teacher(int id, string first_name, string last_name, string subject, int salary)
{
Id = id;
FirstName = first_name;
LastName = last_name;
Subject = subject;
Salary = salary;
}
In addition to reading records, you can use Dapper to execute any SQL command, like CREATE, UPDATE, or DELETE. Here are a couple of examples:
public async Task DeleteById(int id)
{
await _connection.ExecuteAsync($"DELETE FROM teachers WHERE id = {id}");
}
public async Task Insert(Teacher teacher)
{
string sqlCommand = $"INSERT INTO {Teacher.TableName} (first_name, last_name, subject, salary) VALUES (@firstName, @lastName, @Subject, @salary)";
var queryArguments = new
{
firstName = teacher.FirstName,
lastName = teacher.LastName,
subject = teacher.Subject,
salary = teacher.Salary,
};
await _connection.ExecuteAsync(sqlCommand, queryArguments);
}
ExecuteAsync is another Dapper extension method.
As you can see, Dapper has a slightly nicer syntax than Npgsql, especially for parameterized commands.
Entity Framework
Entity Framework is a big full-featured object-relational mapping framework (ORM) by Microsoft. Although it’s usually associated with SQL Server, EF supports PostgreSQL with the NuGet package Npgsql.EntityFrameworkCore.PostgreSQL.
To initialize EF for your Postgres database, you’ll have to create a DbContext class for the database and tables as follows:
public class SchoolContext : DbContext
{
public DbSet<Teacher> Teachers { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql(connectionString:
"Server=localhost;Port=5432;User Id=postgres;Password=passw0rd;Database=testdb;");
base.OnConfiguring(optionsBuilder);
}
}
[Table("teachers")]
public class Teacher : DbContext
{
[System.ComponentModel.DataAnnotations.Key]
[Column("id")]
public int Id { get; internal set; }
[Column("first_name")]
public string FirstName { get; internal set; }
[Column("last_name")]
public string LastName { get; internal set; }
[Column("subject")]
public string Subject { get; internal set; }
[Column("salary")]
public int Salary { get; internal set; }
}
Note that I’m using the attribute System.ComponentModel.DataAnnotations.Schema.Table to declare the name of the table and System.ComponentModel.DataAnnotations.Schema.Column to declare the names of the columns as they are set in the database. This allows me to have different field names in my class, ones that use the C# convention and start with an uppercase letter. Alternatively, you can name the properties the same as the columns are named in PostgreSQL, or you can modify these names by overriding the OnModelCreating method as follows:
// -------- Alternative to using [Column] and [Table] attributes
public class SchoolContext : DbContext
{
public DbSet<Teacher> Teachers { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql(
connectionString: "Server=localhost;Port=5432;User Id=postgres;Password=passw0rd;Database=testdb;");
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Teacher>(e => e.ToTable("teachers"));
modelBuilder.Entity<Teacher>(entity =>
{
entity.Property(e => e.Id)
.HasColumnName("id")
.HasDefaultValueSql("nextval('account.item_id_seq'::regclass)");
entity.Property(e => e.FirstName).IsRequired().HasColumnName("first_name");
entity.Property(e => e.LastName).IsRequired().HasColumnName("last_name");
entity.Property(e => e.Subject).IsRequired().HasColumnName("subject");
entity.Property(e => e.Salary).HasColumnName("salary");
});
base.OnModelCreating(modelBuilder);
}
}
With the above configuration, you could have removed the [Column] attribute from the field names in Teacher class, which can be nice if you want separation of concerns or if your model is in external code that you can’t modify.
If you’re using ASP.NET Core, you can inject the DbContext the following way, instead of overriding OnConfiguring.
// ----------- Alternative to overriding OnConfiguring
services.AddDbContext<SchoolContext>(
optionsBuilder => optionsBuilder.UseNpgsql(connectionString)
);
Reading records with Entity Framework
Here are a couple of ways to read rows in the database.
public async Task<Teacher> GetById(int id)
{
using (var db = new SchoolContext())
{
return await db.Teachers.FindAsync(id);
}
}
public async Task<List<Teacher>> GetBySubject(string subject)
{
using (var db = new SchoolContext())
{
return await db.Teachers.Where(t => t.Subject == subject).ToListAsync();
}
}
Inserting records with Entity Framework
Don’t forget to call SaveChangesAsync at the end of inserts and updates.
public async Task Insert(Teacher teacher)
{
using var db = new SchoolContext();
await db.Teachers.AddAsync(teacher);
await db.SaveChangesAsync();
}
Updating records with Entity Framework
public async Task UpdateSalary(int id, int newSalary)
{
using (var db = new SchoolContext())
{
var t = await db.Teachers.FindAsync(id);
t.Salary = newSalary;
await db.SaveChangesAsync();
}
}
public async Task UpdateAllValues(int id, Teacher teacher)
{
using (var db = new SchoolContext())
{
var t = await db.Teachers.FindAsync(id);
teacher.Id = t.Id; // you might need to return to original value
db.Entry(t).CurrentValues.SetValues(teacher);
await db.SaveChangesAsync();
}
}
Once everything is set up, Entity Framework is really nice to work with. You can stay in the comfort zone of C# code, treating your database tables like any other .NET collection. Well, mostly. On the other hand, EF has its downsides. That additional wrapper can lead to performance issues, trouble in the initial setup, difficulties with schema changes, an incomplete feature set, and downright wrong behavior if there are bugs in the EF implementation.
Finishing up
As you can see, PostgreSQL is fully supported by C# and you can interact with it in different ways, including Dapper and Entity Framework. Everything is mostly easy and straightforward, which describes well the overall experience with PostgreSQL.
You might be interested in another article I wrote about Postgres performance: Performance benchmarks of PostgreSQL in .NET: Npgsql vs Dapper vs Entity Framework