PostgreSQL C#: Call a Stored Procedure

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;