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

Advertisements

About Clivant

Clivant a.k.a Chai Heng enjoys composing software and building systems to serve people. He owns techcoil.com and hopes that whatever he had written and built so far had benefited people.

12 Comments

  • conta
    January 19, 2012 at 2:46 pm

    so do you recommend opening and closing connection everytime [select, update, insert..]?

    is also: using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())
    {
    ……
    sqlTransaction.Commit();
    }

    needed?

    i found another examples without that, eg:

    try
    {
    this.ExecuteNonQuery(String.Format(“insert into {0}({1}) values({2});”, tableName, columns, values));
    }

    Thanks!

    • clivant
      January 19, 2012 at 11:33 pm

      Initially, I tried to synchronize database access via a single database connection for the database operations needed by my program. However, that resulted in the error message “System.Data.SQLite.SQLiteException: Unable to open the database file unable to open database file” that arbitrarily occurs after the program ran for some time.

      After I create and a new connection instance for each database access, the problem was solved.

      Although the using statement will take care of closing the database connection, I would made it a habit to explicitly close the database connections. This is because there are other languages that I may have to use which does not have such automatic database closing feature.

      For the sqlite library that I described in the post, the commit is necessary.

      Hope this helps!

      🙂

  • Dorababu
    April 10, 2012 at 9:14 pm

    Hi can you tell why the session object is NULL after Inserting or Updating the data to the table. I did a sample application where I insert records on first button click before that in page load I assigned my Session as follows

    protected void Page_Load(object sender, EventArgs e)
    {

    if (!IsPostBack)
    {
    Session[“x”] = “session value”; // assigning Session
    }
    }

    My code to insert data to the Database is as follows

    protected void Button1_Click(object sender, EventArgs e)
    {
    string path = Server.MapPath(“bin/sampldb.db”);
    SQLiteConnection conn = new SQLiteConnection(“Data Source=” + path + “”);
    try
    {
    conn.Open();
    SQLiteCommand cmd = new SQLiteCommand();
    cmd.Connection = conn;
    string txt = “insert into stu values(” + TextBox1.Text + “,'” + TextBox2.Text + “‘)”;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = txt;
    cmd.ExecuteNonQuery(); // If I comment this session is available
    conn.Close();

    }
    catch (Exception ex)
    {
    Label1.Visible = true;
    Label1.Text = “Error:” + ex.Message;
    }
    }

    My code to test whether Session exists or not after Inserting data is as follows

    protected void Button2_Click(object sender, EventArgs e)
    {
    if (Session[“x”] != null) // Here after Inserting data and clicking on the next button available my Session value is getting Null
    {
    Label1.Visible = true;
    Label1.Text = Session[“x”].ToString();
    }
    }

    • clivant
      April 15, 2012 at 4:31 pm

      Hi Dorababu,

      I am not familiar with aspx lifecyle, thus I am not confident on how aspx deals with sessions.

      From your code, I guess that the session is created when the page is rendered for the first time.

      From your description, I suspect that there may be some exception being thrown at cmd.ExecuteNonQuery(), and that could have prevented the codes at Page_Load from running, which will in turn set the session.

      An sql exception could have occurred when you try to insert the data.

      Are there anything captured in the logs?

  • jose Alexander
    May 17, 2012 at 11:14 am

    muchas gracias… ha sido de gran ayuda

    • clivant
      May 17, 2012 at 11:42 am

      You are welcomed! 🙂

  • Ganesan
    July 26, 2012 at 11:27 pm

    is possible to open SQLite db file in C# window apps?

    • Clivant
      July 27, 2012 at 1:47 am

      Yes of course!

  • Ryan
    October 21, 2012 at 5:46 am

    How did you create the database file?

    • Clivant
      October 22, 2012 at 12:55 am

      Hi Ryan,

      The database file will be created when you initialize a database connection to a non existent file via the System.data.sqlite.SQLiteConnection class.

  • Samantha
    June 19, 2013 at 7:19 pm

    I just want to say “Thanks a lot”.
    Your article was very useful for me and I managed to use SQLite without spending a lot of time on fixing exceptions and errors.

    • Clivant
      June 19, 2013 at 7:33 pm

      You are welcomed, Samantha. 🙂

Advertisements
Advertisements