info

Calling PostgreSQL stored procedures from C# with Npgsql and ADO.NET works the same against any Postgres database, so the patterns here carry over wherever you run your workloads. If you're building an enterprise .NET application in the AI era, Lakebase gives you the best managed cloud Postgres, with the performance, security, and native Lakehouse integration large teams need. If you're a developer or startup who wants to ship and scale fast, Neon is the Postgres platform built for you.

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

How to call a PostgreSQL stored procedure in C#

The following are the steps for calling a PostgreSQL stored procedure 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 stored procedure:

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

The $1 and $2 are placeholders for binding parameters to the stored procedure.

Third, optionally, bind values to the command:

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

Fourth, execute the stored procedure call by calling the ExecuteNonQueryAsync() method of the NpgsqlCommand object:

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

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

Creating a PostgreSQL stored procedure

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 PostgreSQL stored procedure that enrolls a student and creates an invoice:

CREATE OR REPLACE PROCEDURE enroll_student(
    p_student_id INTEGER,
    p_course_id INTEGER,
    p_amount DOUBLE PRECISION,
    p_tax DOUBLE PRECISION,
    p_invoice_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN

    -- Enroll the student in the course
	INSERT INTO enrollments (student_id, course_id, enrolled_date)
    VALUES (p_student_id, p_course_id, p_invoice_date);

    -- Create a new invoice for the student
    INSERT INTO invoices (student_id, course_id, amount, tax, invoice_date)
    VALUES (p_student_id, p_course_id, p_amount, p_tax, p_invoice_date);

END;
$$;

Calling the PostgreSQL stored procedure in C#

The following C# program invokes the enroll_student stored procedure from the PostgreSQL database:

using Npgsql;

var studentId = 2;
var courseId = 2;
var amount = 49.99;
var tax = 0.05;
var invoiceDate = new DateOnly(2024, 05, 20);

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


try
{
    await using var dataSource = NpgsqlDataSource.Create(connectionString);
    await using var cmd = dataSource.CreateCommand("CALL enroll_student($1,$2,$3,$4,$5)");

    cmd.Parameters.AddWithValue(studentId);
    cmd.Parameters.AddWithValue(courseId);
    cmd.Parameters.AddWithValue(amount);
    cmd.Parameters.AddWithValue(tax);
    cmd.Parameters.AddWithValue(invoiceDate);


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

How it works.

First, declare and initialize variables for storing the enrollment details including studentId, courseId, amount, tax, and invoiceDate:

var studentId = 2;
var courseId = 2;
var amount = 49.99;
var tax = 0.05;
var invoiceDate = new DateOnly(2024, 05, 20);

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 call to the enroll_student stored procedure:

await using var cmd = dataSource.CreateCommand("CALL enroll_student($1,$2,$3,$4,$5)");

Note that $1, $2, $3, $4 and $5 are the parameter placeholders you need to bind values when executing the command.

Fifth, bind the variables to the parameters of the NpgsqlCommand object:

cmd.Parameters.AddWithValue(studentId);
cmd.Parameters.AddWithValue(courseId);
cmd.Parameters.AddWithValue(amount);
cmd.Parameters.AddWithValue(tax);
cmd.Parameters.AddWithValue(invoiceDate);

Sixth, execute the command that calls the PostgreSQL stored procedure:

await cmd.ExecuteNonQueryAsync();

Finally, show the error message if any exceptions occur:

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

Verify the stored procedure call

First, connect to the elearning database using the ed user:

psql -U ed -d elearning

Second, retrieve data from the enrollments table:

SELECT * FROM enrollments;

Output:

student_id | course_id | enrolled_date
------------+-----------+---------------
          2 |         1 | 2024-05-20
          2 |         2 | 2024-05-20
(2 rows)

Third, retrieve data from the invoices table:

SELECT * FROM invoices;

Output:

id | student_id | course_id | amount | tax  | invoice_date
----+------------+-----------+--------+------+--------------
  1 |          2 |         1 |  99.50 | 0.05 | 2024-05-20
  2 |          2 |         2 |  49.99 | 0.05 | 2024-05-20
(2 rows)

The output shows that the program successfully called the stored procedure enroll_student that inserts new rows into the enrollments and invoices tables;

Summary

  • Call the ExecuteNonQueryAsync() method of the NpgsqlCommand object to execute a call to a PostgreSQL stored procedure from a C# program.