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.