SQLite C#: Transactions

Summary: in this tutorial, you will learn how to perform database transactions in the SQLite database using ADO.NET in a C# program

Introduction to database transactions

In SQLite, a transaction consists of multiple SQL statements that execute in an all-or-nothing manner:

• If any statement fails, changes made to the database are rolled back.
• If all statements succeed, the changes are committed to the database permanently.

To perform a transaction in a C# program using ADO.NET, you follow these steps:

• First, open a new database connection.
• Next, start a new transaction by calling the BeginTransaction() method of the SqliteConnection objects. It returns a SqliteTransaction object.
• Then, execute multiple SQL statements.
• After that, commit the changes to the database by calling the Commit() method of the SqliteTransaction object.
• Finally, roll back the changes since the transaction started if an error occurs by calling the Rollback() method of the SqliteTransaction object.

Transaction examples

Let’s take an example of performing a transaction in the SQLite database using C# ADO.NET.

1) Creating new tables

First, open the Terminal on Unix-like systems or Command Prompt on Windows.

Second, connect to the C:\db\pub.db using the sqlite3 client program:

sqlite3 c:\db\pub.dbCode language: C# (cs)

Third, create a new table called books to store the book data

CREATE TABLE books(
    id INTEGER PRIMARY KEY, 
    title TEXT NOT NULL,
    isbn TEXT NOT NULL,
    published_date DATE NOT NULL
);Code language: C# (cs)

Fourth, create a new table called book_authors that stores the relationship between books and authors:

CREATE TABLE book_authors(
    book_id INT,
    author_id INT,
    PRIMARY KEY(book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);Code language: C# (cs)

Fifth, display all the tables in the pub database:

.tablesCode language: C# (cs)

Output:

authors       book_authors  booksCode language: C# (cs)

Sixth, exit the sqlite3 program by running the following command and pressing the Enter (or Return) key:

.quitCode language: C# (cs)

2) Performing a transaction

The following program inserts a new book into the books table and also inserts a new row into the book_authors table within the same transaction:

using Microsoft.Data.Sqlite;

// Insert a new book
var title = "Harry Potter and the Philosopher's Stone";
var isbn = "9781408855652";
var publishedDate = new DateOnly(1997, 6, 26);


try
{
    using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
    connection.Open();
    
    using var transaction = connection.BeginTransaction();
    try
    {
        var sql = "INSERT INTO books(title, isbn, published_date) VALUES(@title, @isbn, @published_date)";
        using var cmdInsertBook = new SqliteCommand(sql, connection, transaction);

        cmdInsertBook.Parameters.AddWithValue("@title", title);
        cmdInsertBook.Parameters.AddWithValue("@isbn", isbn);
        cmdInsertBook.Parameters.AddWithValue("@published_date", publishedDate);

        cmdInsertBook.ExecuteNonQuery();

        // Get the last inserted book id
        sql = "SELECT last_insert_rowid()";
        using var cmdGetId= new SqliteCommand(sql, connection, transaction);
        var bookId = cmdGetId.ExecuteScalar();
        Console.WriteLine($"Book id {bookId}");


        // Assign book to author
        var authorId = 1;
        sql = "INSERT INTO book_authors(book_id, author_id) VALUES(@book_id, @author_id)";
        using var cmdInsertBookAuthor  = new SqliteCommand(sql, connection, transaction);

        cmdInsertBookAuthor.Parameters.AddWithValue("@book_id", bookId);
        cmdInsertBookAuthor.Parameters.AddWithValue("@author_id", authorId);

        cmdInsertBookAuthor.ExecuteNonQuery();

        // Commit the transaction
        transaction.Commit();
    }
    catch (SqliteException e) {
        // Roll back the transaction
        transaction.Rollback();
        Console.WriteLine(e.Message);
    }
}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}Code language: C# (cs)

How it works.

First, open a connection to the SQLite database file:

using var connection = new SqliteConnection(@"Data Source=C:\db\pub.db");
connection.Open();Code language: C# (cs)

Second, begin a transaction by calling the BeginTransaction() method:

using var transaction = connection.BeginTransaction();Code language: C# (cs)

Third, insert a new book into the books table:

var sql = "INSERT INTO books(title, isbn, published_date) VALUES(@title, @isbn, @published_date)";
using var cmdInsertBook = new SqliteCommand(sql, connection, transaction);

cmdInsertBook.Parameters.AddWithValue("@title", title);
cmdInsertBook.Parameters.AddWithValue("@isbn", isbn);
cmdInsertBook.Parameters.AddWithValue("@published_date", publishedDate);

cmdInsertBook.ExecuteNonQuery();Code language: C# (cs)

Fourth, get the inserted book Id for inserting into the book_authors table:

// Get the last inserted book id
sql = "SELECT last_insert_rowid()";
using var cmdGetId = new SqliteCommand(sql, connection, transaction);
var bookId = cmdGetId.ExecuteScalar();
Console.WriteLine($"Book id {bookId}");Code language: C# (cs)

Fifth, insert a new row into the book_authors table:

// Assign book to author
var authorId = 1;
sql = "INSERT INTO book_authors(book_id, author_id) VALUES(@book_id, @author_id)";
using var cmdInsertBookAuthor = new SqliteCommand(sql, connection, transaction);

cmdInsertBookAuthor.Parameters.AddWithValue("@book_id", bookId);
cmdInsertBookAuthor.Parameters.AddWithValue("@author_id", authorId);

cmdInsertBookAuthor.ExecuteNonQuery();Code language: C# (cs)

Sixth, commit the transaction:

// Commit the transaction
transaction.Commit();Code language: C# (cs)

Finally, roll back the transaction if any exception occurs.

transaction.Rollback();Code language: C# (cs)

3) Verifying the transaction

First, open the Terminal on Unix-like systems or Command Prompt on Windows.

Second, connect to the C:\db\pub.db using the sqlite3 client program:

sqlite3 c:\db\pub.dbCode language: C# (cs)

Third, retrieve data from the books table:

SELECT * FROM books;Code language: C# (cs)

Output:

id  title                                     isbn           published_date
--  ----------------------------------------  -------------  --------------
1   Harry Potter and the Philosopher's Stone  9781408855652  1997-06-26Code language: C# (cs)

Finally, retrieve data from the book_authors table:

SELECT * FROM book_authors;Code language: C# (cs)

Output:

book_id  author_id
-------  ---------
1        1Code language: C# (cs)

Summary

• Call the BeginTransaction() method of the SqliteConnection object to start a transaction.
• Call the Commit() method to commit the transaction.
• Call the Rollback() method to roll back the transaction.