C# PostgreSQL
last modified October 18, 2026
In this article we show how to program PostgreSQL in C#. It covers the basics of PostgreSQL programming with C#.
PostgreSQL
PostgreSQL is a powerful, open source, object-relational database system. It is a multi-user database management system. It runs on multiple platforms, including Linux, FreeBSD, Microsoft Windows, and macOS. PostgreSQL is developed by the PostgreSQL Global Development Group.
PostgreSQL has sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting.
ADO.NET and Npgsql
ADO.NET is an essential part of .NET. It is a specification that unifies access to relational databases, XML files and other application data. Npgsql is an implementation of the ADO.NET specification for the PostgreSQL database. It is a driver written in C# and is available for all .NET languages. The examples use Npgsql 10 and PostgreSQL 18.
$ dotnet add package Npgsql
We add the package to our project.
The NpgsqlDataSource, NpgsqlConnection,
NpgsqlCommand, and NpgsqlDataReader are the core
elements of the provider. The NpgsqlDataSource represents a
database and manages a pool of connections to it; it is the recommended
starting point since Npgsql 7. The NpgsqlConnection is a
connection to the database. The NpgsqlCommand object executes an
SQL statement. The NpgsqlDataReader reads the rows of a query
result.
For offline work with data, ADO.NET provides the DataSet class,
which is filled by an NpgsqlDataAdapter. If we only need to read
the results of a query, the NpgsqlDataReader is the better
choice.
C# PostgreSQL version
In the following program we check the version of the PostgreSQL server.
using Npgsql;
var cs = "Host=localhost;Username=postgres;Password=s$cret;Database=testdb";
await using var dataSource = NpgsqlDataSource.Create(cs);
await using var con = await dataSource.OpenConnectionAsync();
Console.WriteLine($"Server version: {con.ServerVersion}");
await using var cmd = new NpgsqlCommand("SELECT version()", con);
var version = await cmd.ExecuteScalarAsync();
Console.WriteLine(version);
We connect to the database and get some info about the PostgreSQL server.
var cs = "Host=localhost;Username=postgres;Password=s$cret;Database=testdb";
This is the connection string. It is used by the data provider to establish a connection to the database. We specify the host name, user name, password and a database name. In a real application, the connection string should not be hard-coded in the source code; it is usually read from a configuration file or an environment variable.
await using var dataSource = NpgsqlDataSource.Create(cs); await using var con = await dataSource.OpenConnectionAsync();
We create a data source from the connection string and open a connection. The data source is usually created once and shared by the whole application. Disposing a connection returns it to the pool.
Console.WriteLine($"Server version: {con.ServerVersion}");
The ServerVersion property of the connection contains the
version of the server.
await using var cmd = new NpgsqlCommand("SELECT version()", con);
var version = await cmd.ExecuteScalarAsync();
The version function returns a detailed description of the
server. There are queries which return only a scalar value; the
ExecuteScalarAsync method is used in such situations.
$ dotnet run Server version: 18.6 (Debian 18.6-1.pgdg13+2) PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
C# PostgreSQL create table
In the following example, we create a database table and fill it with data.
using Npgsql;
var cs = "Host=localhost;Username=postgres;Password=s$cret;Database=testdb";
await using var dataSource = NpgsqlDataSource.Create(cs);
await using (var cmd = dataSource.CreateCommand("DROP TABLE IF EXISTS cars"))
{
await cmd.ExecuteNonQueryAsync();
}
await using (var cmd = dataSource.CreateCommand("""
CREATE TABLE cars(id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(255) NOT NULL, price INT NOT NULL)
"""))
{
await cmd.ExecuteNonQueryAsync();
}
await using (var cmd = dataSource.CreateCommand("""
INSERT INTO cars(name, price) VALUES
('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000),
('Volvo', 29000), ('Bentley', 350000), ('Citroen', 21000),
('Hummer', 41400), ('Volkswagen', 21600)
"""))
{
int n = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"Table cars created with {n} rows");
}
In the example, we create a cars table with eight rows.
await using (var cmd = dataSource.CreateCommand("DROP TABLE IF EXISTS cars"))
{
await cmd.ExecuteNonQueryAsync();
}
The CreateCommand method of the data source creates a command
that takes a connection from the pool when it is executed. First we drop the
table if it already exists. We use the ExecuteNonQueryAsync
method if we do not want a result set, for example for DROP,
INSERT, or DELETE statements.
CREATE TABLE cars(id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(255) NOT NULL, price INT NOT NULL)
The cars table is created. The identity column gets its values
automatically. Identity columns are the standard SQL way of auto-incremented
columns and are preferred over the older PostgreSQL SERIAL type.
INSERT INTO cars(name, price) VALUES
('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000),
('Volvo', 29000), ('Bentley', 350000), ('Citroen', 21000),
('Hummer', 41400), ('Volkswagen', 21600)
We insert eight rows with a single multi-row INSERT statement.
The ExecuteNonQueryAsync method returns the number of affected
rows.
$ dotnet run Table cars created with 8 rows
We run the program.
$ psql -U postgres testdb -c "SELECT * FROM cars" id | name | price ----+------------+-------- 1 | Audi | 52642 2 | Mercedes | 57127 3 | Skoda | 9000 4 | Volvo | 29000 5 | Bentley | 350000 6 | Citroen | 21000 7 | Hummer | 41400 8 | Volkswagen | 21600 (8 rows)
We verify the data with the psql tool.
C# PostgreSQL parameterized query
Parameterized queries increase security and performance. We use placeholders instead of directly writing the values into the statements. The values are sent to the server separately from the SQL text, so they can never change the structure of the statement. This guards against SQL injection attacks. Never build SQL statements by concatenating or interpolating user input.
using Npgsql;
var cs = "Host=localhost;Username=postgres;Password=s$cret;Database=testdb";
await using var dataSource = NpgsqlDataSource.Create(cs);
await using var cmd = dataSource.CreateCommand(
"INSERT INTO cars(name, price) VALUES($1, $2) RETURNING id");
cmd.Parameters.Add(new() { Value = "BMW" });
cmd.Parameters.Add(new() { Value = 36600 });
var id = await cmd.ExecuteScalarAsync();
Console.WriteLine($"row inserted, id: {id}");
We add a new car to the cars table.
await using var cmd = dataSource.CreateCommand(
"INSERT INTO cars(name, price) VALUES($1, $2) RETURNING id");
cmd.Parameters.Add(new() { Value = "BMW" });
cmd.Parameters.Add(new() { Value = 36600 });
The $1 and $2 are positional placeholders, which is
the native PostgreSQL syntax. They are filled with the parameters in the order
in which they are added.
var id = await cmd.ExecuteScalarAsync();
The RETURNING clause makes the INSERT statement
return the generated id, which we get with ExecuteScalarAsync.
$ dotnet run row inserted, id: 9
Npgsql also supports named placeholders such as @price. In the
following example, we use them in a query.
using Npgsql;
var cs = "Host=localhost;Username=postgres;Password=s$cret;Database=testdb";
await using var dataSource = NpgsqlDataSource.Create(cs);
await using var cmd = dataSource.CreateCommand(
"SELECT name, price FROM cars WHERE price BETWEEN @min AND @max ORDER BY price");
cmd.Parameters.AddWithValue("min", 20000);
cmd.Parameters.AddWithValue("max", 40000);
await using var rdr = await cmd.ExecuteReaderAsync();
while (await rdr.ReadAsync())
{
string name = rdr.GetFieldValue<string>(0);
int price = rdr.GetFieldValue<int>(1);
Console.WriteLine($"{name}: {price}");
}
We select the cars whose price is between two values.
cmd.Parameters.AddWithValue("min", 20000);
cmd.Parameters.AddWithValue("max", 40000);
The parameter names are given without the @ character.
string name = rdr.GetFieldValue<string>(0); int price = rdr.GetFieldValue<int>(1);
The generic GetFieldValue method reads a column value as the
given type.
$ dotnet run Citroen: 21000 Volkswagen: 21600 Volvo: 29000 BMW: 36600
C# NpgsqlDataReader
The NpgsqlDataReader is an object used to retrieve data from the
database. It provides fast, forward-only, read-only access to query results. It
is the most efficient way to retrieve data from tables.
using Npgsql;
var cs = "Host=localhost;Username=postgres;Password=s$cret;Database=testdb";
await using var dataSource = NpgsqlDataSource.Create(cs);
await using var cmd = dataSource.CreateCommand("SELECT id, name, price FROM cars ORDER BY id");
await using var rdr = await cmd.ExecuteReaderAsync();
Console.WriteLine($"{rdr.GetName(0),-4} {rdr.GetName(1),-10} {rdr.GetName(2),10}");
while (await rdr.ReadAsync())
{
Console.WriteLine($"{rdr.GetInt32(0),-4} {rdr.GetString(1),-10} {rdr.GetInt32(2),10}");
}
We get all rows from the cars table and print them to the console
together with the column names.
await using var rdr = await cmd.ExecuteReaderAsync();
To create a data reader, we call the ExecuteReaderAsync method of
the command.
Console.WriteLine($"{rdr.GetName(0),-4} {rdr.GetName(1),-10} {rdr.GetName(2),10}");
We get the names of the columns with the GetName method of the
reader.
while (await rdr.ReadAsync())
{
Console.WriteLine($"{rdr.GetInt32(0),-4} {rdr.GetString(1),-10} {rdr.GetInt32(2),10}");
}
The ReadAsync method advances the data reader to the next record.
It returns true if there are more rows; otherwise
false. Typed methods such as GetInt32 access the
column values in their native data types.
$ dotnet run id name price 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 9 BMW 36600
C# PostgreSQL transaction
A transaction is an atomic unit of database operations. Either all of the statements in a transaction are committed to the database, or none of them are.
using Npgsql;
var cs = "Host=localhost;Username=postgres;Password=s$cret;Database=testdb";
await using var dataSource = NpgsqlDataSource.Create(cs);
await using var con = await dataSource.OpenConnectionAsync();
await using var tx = await con.BeginTransactionAsync();
try
{
await using var cmd = new NpgsqlCommand("UPDATE cars SET price = 10000 WHERE name = 'Skoda'", con, tx);
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = "UPDATE carz SET price = 30000 WHERE name = 'Volvo'";
await cmd.ExecuteNonQueryAsync();
await tx.CommitAsync();
Console.WriteLine("transaction committed");
}
catch (PostgresException e)
{
await tx.RollbackAsync();
Console.WriteLine($"transaction rolled back: {e.MessageText}");
}
await using var cmd2 = new NpgsqlCommand("SELECT price FROM cars WHERE name = 'Skoda'", con);
Console.WriteLine($"Skoda price: {await cmd2.ExecuteScalarAsync()}");
We update two rows in a transaction. The second statement contains a typo in the table name, so it fails and the whole transaction is rolled back.
await using var tx = await con.BeginTransactionAsync();
A transaction belongs to a connection, so we open a connection explicitly.
await using var cmd = new NpgsqlCommand("UPDATE cars SET price = 10000 WHERE name = 'Skoda'", con, tx);
The command is created for the connection and the transaction.
catch (PostgresException e)
{
await tx.RollbackAsync();
Console.WriteLine($"transaction rolled back: {e.MessageText}");
}
Errors reported by the server are thrown as PostgresException.
Its MessageText property contains the error message, and the
SqlState property the standard error code. We roll back the
transaction, so the first update is undone.
$ dotnet run transaction rolled back: relation "carz" does not exist Skoda price: 9000
The price of the Skoda car was not changed.
Source
In this article we have shown how to program PostgreSQL databases in C#.
Author
List all C# tutorials.