info

Calling PostgreSQL functions from C# with Npgsql works the same way on any standard Postgres, so you can apply these ADO.NET patterns wherever your database runs. For enterprises building in the AI era, Lakebase delivers the best managed cloud Postgres, with the performance, security, and native Lakehouse integration that serious data teams demand. For developers and startups who need to ship and scale fast, Neon is the Postgres platform of choice, with instant provisioning, branching, and autoscaling built in.

Summary: in this tutorial, you will learn to call a PostgreSQL function from a C# program using ADO.NET

How to call a PostgreSQL function in C#

Here are the steps for calling a PostgreSQL function in C#:

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

await using var dataSource = NpgsqlDataSource.Create(connectionString);

Second, create a new NpgsqlCommand object from the statement that calls a PostgreSQL function:

await using var cmd = dataSource.CreateCommand("SELECT fn(?,?)");

Third, optionally, bind values to the query’s parameters:

cmd.Parameters.AddWithValue(value1);
cmd.Parameters.AddWithValue(value2);

Fourth, execute the function call by calling the ExecuteReaderAsync() method of the NpgsqlCommand object:

await using var reader = await cmd.ExecuteReaderAsync();

The ExecuteReaderAsync() returns a NpgsqlDataReader object.

Finally, read the return values of the function by calling the ReadAsync() method of the NpgsqlDataReader object:

while (await reader.ReadAsync())
{
    var result = reader.GetInt32(0);
    // ...
}

We’ll create a new function in the PostgreSQL server and call it from a C# program.

Creating a PostgreSQL function

First, open a terminal and connect to the elearning database using the ed user:

psql -U ed -d elearning

It’ll prompt you to enter a password for the ed user. Input the valid password and press Enter to connect to the PostgreSQL server.

Second, create a stored procedure in PostgreSQL, which enrolls a student in a course and creates a corresponding invoice:

create function get_student_count(begin_date date, end_date date)
returns int
language plpgsql as $$
declare
   student_count integer;
begin
   select count(*)
   into student_count
   from students
   where registration_date between begin_date and end_date;

   return student_count;
end;
$$;

Calling the PostgreSQL function in C#

The following C# program invokes the `enroll_student_and_create_invoice` function from the PostgreSQL database:

using Npgsql;

var beginDate = new DateOnly(2024, 5, 10);
var endDate = new DateOnly(2024, 5, 15);

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

try
{
    await using var dataSource = NpgsqlDataSource.Create(connectionString);

    await using var cmd = dataSource.CreateCommand("SELECT get_student_count($1,$2)");

    cmd.Parameters.AddWithValue(beginDate);
    cmd.Parameters.AddWithValue(endDate);

    await using var reader = await cmd.ExecuteReaderAsync();

    if (await reader.ReadAsync())
    {
        var studentCount = reader.GetInt32(0);

        Console.WriteLine($"Students who registered between {beginDate} and {endDate}: {studentCount} ");
    }

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

How it works.

First, declare and initialize variables beginDate and endDate to May 10 2024 and May 15 2024:

var beginDate = new DateOnly(2024, 5, 10);
var endDate = new DateOnly(2024, 5, 15);

Second, get the connection string from the configuration file using the ConfigurationHelper class:

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

Third, create a data source that represents the PostgreSQL database:

await using var dataSource = NpgsqlDataSource.Create(connectionString);

Fourth, create a new NpgsqlCommand object that will execute a function call to the get_student_count() function:

await using var cmd = dataSource.CreateCommand("SELECT get_student_count($1,$2)");

Notice that $1 and $2 are the parameter placeholders you need to bind values when executing the command.

Fifth, bind the beginDate and endDate to the parameters of the NpgsqlCommand object:

cmd.Parameters.AddWithValue(beginDate);
cmd.Parameters.AddWithValue(endDate);

Sixth, execute the command function call:

await using var reader = await cmd.ExecuteReaderAsync();

Seventh, read the student count and display it in the console:

if (await reader.ReadAsync())
{
    var studentCount = reader.GetInt32(0);
    Console.WriteLine($"Students who registered between {beginDate} and {endDate}: {studentCount} ");
}

Finally, display the error message if any exceptions occur:

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

Summary

  • Call the ExecuteReaderAsync() method of the NpgsqlCommand object to execute a call to a PostgreSQL function from a C# program.