My experience with System.Data.SQLite in C#

It was time when simple file IO like object serialization to file, was not enough to manage data persistence in the .NET application that I had been building. Prior to encountering this situation, I had always wanted to gain practical experience with SQLite. With some googling, I found a .NET wrapper around the SQLite library that I could use in my application. This post documents my experience with using the System.Data.SQLite wrapper.

Including the System.Data.SQLite into your project

The creator of System.Data.SQLite made it very easy for developers to use SQLite in their .NET projects: the whole SQLite database is contained within a single dll. As I was developing a .NET 2.0 project, I chose to download the 1.0.66.0 version. Just add a reference to one of the three flavors of System.Data.SQLite.dll (Win32, Itanium and x64) and you can start using SQLite in your project.

Initializing a connection to SQLite

As with most sql databases, the first action to perform on the database is to connect to it. Connecting to the database is performed via the System.Data.SQLite.SQLiteConnection class. The following code segment attempts to connect to the database file "sample.s3db" in the same working directory as the project executable.

string dbConnectionString = @"Data Source=sample.s3db";
SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);
sqliteCon.Open();

If the database file does not exist, a new one will be created. If there exists a file with the same name, the code will read the file as a SQLite database file and an SQLiteException will be thrown if the file is not a valid one.

Defining a data structure to store my application data

Suppose that I need to store the name and username of users of my application, I will define the table with the SQL CREATE syntax:

CREATE TABLE [AppUser] (
    [name] TEXT NULL,
    [username] TEXT NULL
)

Then, I will use the following .NET code to implement the table structure into my SQLite database instance:

// Open connection to database
string dbConnectionString = @"Data Source=sample.s3db";
SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);
sqliteCon.Open();

// Define the SQL Create table statement
string createAppUserTableSQL = "CREATE TABLE [AppUser] (" +
    "[name] TEXT NULL," +
    "[username] TEXT  NULL" +
    ")";

using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())
{
    // Create the table
    SQLiteCommand createCommand = new SQLiteCommand(createAppUserTableSQL
                                                          , sqliteCon);
    createCommand.ExecuteNonQuery();
    createCommand.Dispose();

    // Commit the changes into the database
    sqlTransaction.Commit();
} // end using

// Close the database connection
sqliteCon.Close();

Inserting, updating and deleting records from a data structure

Inserting, updating and deleting records can be performed via SQL INSERT, UPDATE and DELETE statements:

-- Insert example
INSERT INTO AppUser(name, username) VALUES('Tommy', 'Tommy_83')

--Update example
UPDATE AppUser SET name =  'Tom' WHERE username = 'Tommy_83'

--Delete example
DELETE FROM AppUser WHERE name='Tom' AND username='Tommy_83'

The coding to perform these three kind of statements are of the same form:

// Performs an insert, change contents of sqlStatement to perform
// update or delete.
string sqlStatement = "INSERT INTO AppUser(name, username) VALUES('Tommy', 'Tommy_83')";
string dbConnectionString = @"Data Source=sample.s3db";
SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);
sqliteCon.Open();
using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())
{
    SQLiteCommand command = new SQLiteCommand(sqlStatement , sqliteCon);
    command .ExecuteNonQuery();
    sqlTransaction.Commit();
}

Performing database queries

Database queries can be performed via the SQL SELECT statement:

--Select the name and username column of all users
SELECT user, username FROM AppUser

The following code segment retrieves the user and username columns for every record in the AppUser table and print them on the Console.

// Connect to database
string dbConnectionString = @"Data Source=sample.s3db";
SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);
sqliteCon.Open();

// Execute query on database
string selectSQL = "SELECT user, username FROM AppUser";
SQLiteCommand selectCommand = new SQLiteCommand(selectSQL, sqliteCon);
SQLiteDataReader dataReader = selectCommand.ExecuteReader();

// Iterate every record in the AppUser table
while (!dataReader.Read())
{
   Console.WriteLine("User: " + dataReader.GetString(0)
        + " Username: " + dataReader.GetString(1));
}
dataReader.Close();
sqliteCon.Close();

Note: Unlike other database api, the GetXXX methods of the SQLiteDataReader in the wrapper that I used do not accept a column name as input for retrieval of data from a particular column. Instead, the GetXXX method accepts a index input, which is the position of the column data as specified in the SQL SELECT statement. In this case, because I had specified the user column followed by the username column in my SQL SELECT statement, the index 0 will represent the data from the 'user' column and the index 1 will represent the data from the 'username' column.

Some problems that took me some time to solve

My attempt to integrate SQLite into my .NET application wasn't as smooth as I had wanted it to be. The following are some exceptions that I solved after I had integrated SQLite into my application.

System.Data.SQLite.SQLiteException: The database file is locked cannot rollback transaction - SQL statements in progress

This is the first bizarre exception that I encountered in my application log. The source of the exception came from the code segment of the following form:

// Connect to database
string dbConnectionString = @"Data Source=sample.s3db";
SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);

// Search the table for user Tommy
string selectSQL = "SELECT user FROM AppUser WHERE user = 'Tommy'";
SQLiteCommand selectCommand = new SQLiteCommand(selectSQL, sqliteCon);
SQLiteDataReader dataReader = selectCommand.ExecuteReader();

// If Tommy is in the table
if (dataReader.Read())
{
    // Update his username
    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())
    {
        // Update the expiry date of the application
        string updateSQL = "UPDATE AppUser SET username = 'Tommy_85'" +
                                   " WHERE user = 'Tommy'";
        SQLiteCommand updateCommand = new SQLiteCommand(updateSQL, sqliteCon);
        updateCommand.ExecuteNonQuery();
        sqlTransaction.Commit();
    }
}
else
{
    // Insert Tommy as a new user
    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())
    {
        string insertSQL = "INSERT INTO AppUser(user, username)" +
                                 " VALUES ('Tommy', 'Tommy_86')";
        SQLiteCommand insertCommand = new SQLiteCommand(insertSQL, sqliteCon);
        insertCommand.ExecuteNonQuery();
        sqlTransaction.Commit();
    }
}
dataReader.Close();
sqliteCon.Close();

While the following code will work well in some other database system, SQLite does not like this way of data manipulation. The reason is that SQLite will lock the table for every data manipulation action (SELECT, INSERT, UPDATE, DELETE). In the above code segment, when Tommy is in the database table, the call to dataReader.Read() will lock the AppUser table. And when subsequent code attempts to update to the AppUser table, the code could not proceed and the exception would be thrown.

I was able to solve the exception by rewriting the code to close the SQLiteDataReader instance before subsequent code attempts to perform an update or insert on the AppUser table.

// Open connection to database
SQLiteConnection sqliteCon = new SQLiteConnection(this._dbConnectionString);
sqliteCon.Open();

// Search the table for user Tommy
string selectSQL = "SELECT user FROM AppUser" +
                          " WHERE user = 'Tommy'";
SQLiteCommand selectCommand = new SQLiteCommand(selectSQL
                                                   , sqliteCon);
SQLiteDataReader dataReader = selectCommand.ExecuteReader();

// Use a variable to store the result of the search
bool tommyExists = dataReader.Read();
dataReader.Close();

// If Tommy is in the table
if (tommyExists)
{
    // Update his username
    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())
    {
        // Update the expiry date of the application
        string updateSQL = "UPDATE AppUser SET username = 'Tommy_85'" +
                                   " WHERE user = 'Tommy'";
        SQLiteCommand updateCommand = new SQLiteCommand(updateSQL
                                                            , sqliteCon);
        updateCommand.ExecuteNonQuery();
        sqlTransaction.Commit();
    }
}
else
{
    // Insert Tommy as a new user
    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())
    {
        string insertSQL = "INSERT INTO AppUser(user, username)" +
                                 " VALUES ('Tommy', 'Tommy_86')";
        SQLiteCommand insertCommand = new SQLiteCommand(insertSQL, sqliteCon);
        insertCommand.ExecuteNonQuery();
        sqlTransaction.Commit();
    }
}
sqliteCon.Close();

System.Data.SQLite.SQLiteException: Unable to open the database file unable to open database file

This exception was non deterministic and I took longer to attempt solve it. There are many people in the web who had encountered the same exception but their solutions does not apply to my problem. Among the many suggestions, I thought that permission issues with the database file and the folder in which the database file resides in appeared to be the main culprit. However for my case, I was able to perform some database operations before I encounter the exception, even those operations that threw the exception. In the end, it turned out that it was my attempt in synchronizing access to a single SQLiteConnection instance that caused the exception. After I modified my code to create instances of the SQLiteConnection object for every database operation, the exception disappears from my log.

Other topics that may interest you

• How to build a web server layer in your C# program.
• How to upload large HTTP multipart request in C#
• How to download file from a HTTP server in C#
• How to implement serialization over TCP/IP in C#
• How to save and load objects to and from file in C#

• Share
• Tweet
• Share
• Save