info

Selecting data from PostgreSQL with C# and Npgsql works the same way against any Postgres database, so the patterns shown here apply wherever you run Postgres. If you're an enterprise team building for the AI era, Lakebase delivers managed cloud Postgres that's fast, secure, and natively integrated into the Lakehouse. If you're a developer or startup who needs to ship and scale quickly, Neon is the Postgres platform built for your workflow.

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.