> This page location: PostgreSQL C# > Call a Stored Procedure using C# > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL C#: Call a Stored Procedure **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](https://www.databricks.com/product/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](https://neon.com) 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: ```cs await using var dataSource = NpgsqlDataSource.Create(connectionString); ``` Second, create a new `NpgsqlCommand` object from the statement that calls a stored procedure: ```cs 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: ```cs cmd.Parameters.AddWithValue(value1); cmd.Parameters.AddWithValue(value2); ``` Fourth, execute the stored procedure call by calling the `ExecuteNonQueryAsync()` method of the `NpgsqlCommand` object: ```cs 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: ```bash 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: ```sql 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: ```cs 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`: ```cs 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: ```cs string connectionString = ConfigurationHelper.GetConnectionString("DefaultConnection"); ``` Third, create a data source that represents the PostgreSQL database: ```cs await using var dataSource = NpgsqlDataSource.Create(connectionString); ``` Fourth, create a new `NpgsqlCommand` object that will execute a call to the `enroll_student` stored procedure: ```cs 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: ```cs 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: ```cs await cmd.ExecuteNonQueryAsync(); ``` Finally, show the error message if any exceptions occur: ```cs // ... } catch (NpgsqlException ex) { Console.WriteLine($"Error: {ex.Message}"); } ``` ## Verify the stored procedure call First, connect to the `elearning` database using the `ed` user: ```bash psql -U ed -d elearning ``` Second, retrieve data from the enrollments table: ```sql SELECT * FROM enrollments; ``` Output: ```text 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: ```sql 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. --- ## Related docs (PostgreSQL C#) - [Connect to Database Using C#](https://neon.com/postgresql/csharp/csharp-connect) - [Call PostgreSQL Functions using C#](https://neon.com/postgresql/csharp/csharp-call-postgresql-function) - [Connect to PostgreSQL using C#](https://neon.com/postgresql/csharp/csharp-connect) - [Create Tables using C#](https://neon.com/postgresql/csharp/csharp-create-table) - [Delete Data from a Table using C#](https://neon.com/postgresql/csharp/csharp-delete) - [Import Data from CSV using C#](https://neon.com/postgresql/csharp/csharp-import-csv-file) - [Insert Data Into Tables using C#](https://neon.com/postgresql/csharp/csharp-insert) - [Select Data in a Table using C#](https://neon.com/postgresql/csharp/csharp-select) - [PostgreSQL Transactions in C#](https://neon.com/postgresql/csharp/csharp-transaction) - [Update Data in a Table in C#](https://neon.com/postgresql/csharp/csharp-update)