Summary: in this tutorial, you will learn how to perform a transaction in PostgreSQL using C#.

Creating a new table

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

psql -U ed -d elearning

Second, create a new table called invoices:

CREATE TABLE invoices (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    amount DEC(19,2) NOT NULL,
    tax DEC(5,2) NOT NULL,
    invoice_date DATE NOT NULL,
    FOREIGN KEY(student_id) REFERENCES students (id) ON DELETE CASCADE,
    FOREIGN KEY(course_id) REFERENCES courses (id) ON DELETE CASCADE
);

Third, insert five rows into the courses table:

INSERT INTO courses ( name, duration)
VALUES
   ('Introduction to PostgreSQL', '10 hours'),
   ('Advanced PostgreSQL Performance Tuning', '15 hours'),
   ('C# Fundamentals', '12 hours' ),
   ('Building Web Applications with C# and ASP.NET', '20 hours'),
   ('PostgreSQL for Application Developers', '18 hours')
RETURNING id, name, duration;

Output:

id |                     name                      | duration
----+-----------------------------------------------+----------
  1 | Introduction to PostgreSQL                    | 10:00:00
  2 | Advanced PostgreSQL Performance Tuning        | 15:00:00
  3 | C# Fundamentals                               | 12:00:00
  4 | Building Web Applications with C# and ASP.NET | 20:00:00
  5 | PostgreSQL for Application Developers         | 18:00:00
(5 rows)

Finally, exit the psql program:

exit

Perform a transaction

The following program illustrates how to enroll a student in a course and create an invoice for the enrollment within a transaction:

using Npgsql;

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

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

try
{
    // Create a new database source
    await using var dataSource = NpgsqlDataSource.Create(connectionString);


    // Open a connection to the PostgreSQL server
    await using var conn = await dataSource.OpenConnectionAsync();

    // Start the transaction
    await using var tx = await conn.BeginTransactionAsync();

    try
    {
        // Enroll student id 2 with the course id 1
        var sql = "INSERT INTO enrollments (student_id, course_id, enrolled_date) " +
            "VALUES (@student_id,@course_id, @enrolled_date)";

        await using var cmd1 = new NpgsqlCommand(sql, conn,tx)
        {
            Parameters =
            {
                new("@student_id", studentId),
                new("@course_id", courseId),
                new("@enrolled_date", invoiceDate),
            }
        };
        await cmd1.ExecuteNonQueryAsync();

        // Create a new invoice
        sql = "INSERT INTO invoices(student_id, course_id, amount, tax, invoice_date) " +
            "VALUES(@student_id, @course_id, @amount, @tax, @invoice_date)";

        await using var cmd2 = new NpgsqlCommand(sql, conn, tx)
        {
            Parameters =
            {
                new("@student_id", studentId),
                new("@course_id", courseId),
                new("@amount", amount),
                new("@tax", tax),
                new("@invoice_date", invoiceDate),
            }
        };
        await cmd2.ExecuteNonQueryAsync();

        // Commit the transaction
        await tx.CommitAsync();
    }
    catch (NpgsqlException ex)
    {
        Console.WriteLine($"Error: {ex.Message}");

        // Roll back the transaction
        await tx.RollbackAsync();
    }

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

Verify the transaction

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.

Second, retrieve data from the enrollments table:

SELECT * FROM enrollments;

Output:

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

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
(1 row)

Summary

  • Call the BeginTransactionAsync() method of the NpgsqlConnection object to start a transaction.
  • Call the CommitAsync() method of the NpgsqlTransaction object to apply the changes since the transaction started to the database permanently.
  • Call the RollbackAsync() method of the NpgsqlTransaction object to roll back the changes.