PostgreSQL C#: Selecting Data

Summary: in this tutorial, you will learn how to retrieve data from a PostgreSQL table from the C# program.

This tutorial begins where Deleting data from PostgreSQL using C# is left off.

How to query data from PostgreSQL using C#

To query data from PostgreSQL using C# ADO.NET, you follow these steps:

First, create a data source that represents the PostgreSQL database.

Second, create a command object NpgsqlCommand with a SELECT statement from the data source.

Third, execute the SELECT statement by calling one of the following methods of the NpgsqlCommand object:

ExecuteReaderAsync() – executes a query that returns a result set. The method returns a NpgsqlDataReader that can be used to read rows from the query's result set.
ExecuteScalarAsync() – executes a query that returns a scalar value such as a query that uses an aggregate function to return a count, sum, maximum, minimum, and average value.

Finally, iterate over the result set and use the Get* methods of the NpgsqlDataReader object to retrieve the values of columns in each row.

Querying all rows from a table

The following program queries all rows from the students table in the elearning database:

using Npgsql;

var sql = @"SELECT
 id,
 first_name,
 last_name,
 email,
 registration_date
 FROM
 students";

string connectionString = ConfigurationHelper.GetConnectionString("DefaultConnection");

try
{
 // Open a connection
 await using var dataSource = NpgsqlDataSource.Create(connectionString);

 // Create a Command
 await using var cmd = dataSource.CreateCommand(sql);

 // Create a new data reader
 using var reader = await cmd.ExecuteReaderAsync();

 // Read data from the table
 while (await reader.ReadAsync())
 {
 var id = reader.GetInt32(0);
 var firstName = reader.GetString(1);
 var lastName = reader.GetString(2);
 var email = reader.GetString(3);
 var registrationDate = reader.GetDateTime(4);

 // Display the student details
 Console.WriteLine($"{id}\t{firstName}\t{lastName}\t{email}\t{registrationDate.ToShortDateString()}");
 }
}
catch (NpgsqlException ex)
{
 Console.WriteLine($"Error: {ex.Message}");
}

Output:

2 Emma Smith emma.smith@example.com 5/20/2024
3 Liam Johnson liam.johnson@example.com 5/20/2024
4 Olivia Williams olivia.williams@example.com 5/20/2024
5 Noah Brown noah.brown@example.com 5/15/2024
6 Ava Jones ava.jones@example.com 5/15/2024
7 William Garcia william.garcia@example.com 5/15/2024
8 Sophia Miller sophia.miller@example.com 5/10/2024
9 James Davis james.davis@example.com 5/10/2024
10 Isabella Rodriguez isabella.rodriguez@example.com 5/10/2024
11 Benjamin Martinez benjamin.martinez@example.com 5/10/2024

Querying data with parameters

The following program shows how to retrieve the students who registered on 2024年05月10日 from the students table:

using Npgsql;

var sql = @"SELECT
 first_name,
 registration_date
 FROM
 students
 WHERE
 registration_date=@registration_date";

string connectionString = ConfigurationHelper.GetConnectionString("DefaultConnection");

try
{
 // Open a connection
 await using var dataSource = NpgsqlDataSource.Create(connectionString);


 // Create a Command
 await using var cmd = dataSource.CreateCommand(sql);

 cmd.Parameters.AddWithValue(
 "@registration_date",
 new DateOnly(2024, 5, 10)
 );

 // Create a new data reader
 using var reader = await cmd.ExecuteReaderAsync();

 // Read data from the table
 while (await reader.ReadAsync())
 {
 var firstName = reader.GetString(0);
 var registrationDate = reader.GetDateTime(1);

 Console.WriteLine($"{firstName}\t{registrationDate.ToShortDateString()}");
 }
}
catch (NpgsqlException ex)
{
 Console.WriteLine($"Error: {ex.Message}");
}

Output:

Sophia 5/10/2024
James 5/10/2024
Isabella 5/10/2024
Benjamin 5/10/2024

Querying a scalar value

The following program shows how to select the total number of students from the students table using the count(*) aggregate function:

using Npgsql;

var sql = @"SELECT count(*) FROM students";

string connectionString = ConfigurationHelper.GetConnectionString("DefaultConnection");

try
{
 // Open a connection
 await using var dataSource = NpgsqlDataSource.Create(connectionString);

 // Create a Command
 await using var cmd = dataSource.CreateCommand(sql);

 // Return the total number of students
 var studentCount = await cmd.ExecuteScalarAsync();

 Console.WriteLine($"Student count: {studentCount}");
}
catch (NpgsqlException ex)
{
 Console.WriteLine($"Error: {ex.Message}");
}

Output:

Student count: 10

Summary

Call the ExecuteReaderAsync() method of a NpgsqlCommand object to execute a query that returns a result set.
Call the ExecuteScalarAsync() method of the NpgsqlCommand object to execute a query that returns a scalar value.

PostgreSQL C#: Selecting Data

How to query data from PostgreSQL using C#

Querying all rows from a table

Querying data with parameters

Querying a scalar value

Summary

On this page