{"id":106,"date":"2011-07-22T11:24:53","date_gmt":"2011-07-22T03:24:53","guid":{"rendered":"https:\/\/www.techcoil.com\/blog\/?p=106"},"modified":"2018-09-02T22:51:25","modified_gmt":"2018-09-02T14:51:25","slug":"my-experience-with-system-data-sqlite-in-c","status":"publish","type":"post","link":"https:\/\/www.techcoil.com\/blog\/my-experience-with-system-data-sqlite-in-c\/","title":{"rendered":"My experience with System.Data.SQLite in C#"},"content":{"rendered":"

It was time when simple file IO like object serialization to file<\/a>, 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<\/a>. With some googling, I found a .NET wrapper around the SQLite library<\/a> that I could use in my application. This post documents my experience with using the System.Data.SQLite wrapper.<\/p>\n

Including the System.Data.SQLite into your project<\/h3>\n

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<\/a>. Just add a reference to one of the three flavors of System.Data.SQLite.dll<\/strong> (Win32, Itanium and x64) and you can start using SQLite in your project.<\/p>\n

Initializing a connection to SQLite<\/h3>\n

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.<\/p>\n

\r\nstring dbConnectionString = @"Data Source=sample.s3db";\r\nSQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);\r\nsqliteCon.Open();\r\n<\/pre>\n
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.<\/p>\n
Defining a data structure to store my application data<\/h3>\n
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:<\/p>\n
\r\nCREATE TABLE [AppUser] (\r\n    [name] TEXT NULL,\r\n    [username] TEXT NULL\r\n)\r\n<\/pre>\n
Then, I will use the following .NET code to implement the table structure into my SQLite database instance:<\/p>\n
\r\n\/\/ Open connection to database\r\nstring dbConnectionString = @"Data Source=sample.s3db";\r\nSQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);\r\nsqliteCon.Open();\r\n\r\n\/\/ Define the SQL Create table statement\r\nstring createAppUserTableSQL = "CREATE TABLE [AppUser] (" +\r\n    "[name] TEXT NULL," +\r\n    "[username] TEXT  NULL" +\r\n    ")";\r\n\r\nusing (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())\r\n{\r\n    \/\/ Create the table\r\n    SQLiteCommand createCommand = new SQLiteCommand(createAppUserTableSQL\r\n                                                          , sqliteCon);\r\n    createCommand.ExecuteNonQuery();\r\n    createCommand.Dispose();\r\n\r\n    \/\/ Commit the changes into the database\r\n    sqlTransaction.Commit();\r\n} \/\/ end using\r\n\r\n\/\/ Close the database connection\r\nsqliteCon.Close();\r\n<\/pre>\n
Inserting, updating and deleting records from a data structure<\/h3>\n
Inserting, updating and deleting records can be performed via SQL INSERT, UPDATE and DELETE statements:<\/p>\n
\r\n-- Insert example\r\nINSERT INTO AppUser(name, username) VALUES('Tommy', 'Tommy_83')\r\n\r\n--Update example\r\nUPDATE AppUser SET name =  'Tom' WHERE username = 'Tommy_83'\r\n\r\n--Delete example\r\nDELETE FROM AppUser WHERE name='Tom' AND username='Tommy_83'\r\n<\/pre>\n
The coding to perform these three kind of statements are of the same form:<\/p>\n
\r\n\/\/ Performs an insert, change contents of sqlStatement to perform\r\n\/\/ update or delete.\r\nstring sqlStatement = "INSERT INTO AppUser(name, username) VALUES('Tommy', 'Tommy_83')";\r\nstring dbConnectionString = @"Data Source=sample.s3db";\r\nSQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);\r\nsqliteCon.Open();\r\nusing (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())\r\n{\r\n    SQLiteCommand command = new SQLiteCommand(sqlStatement , sqliteCon);\r\n    command .ExecuteNonQuery();\r\n    sqlTransaction.Commit();\r\n}\r\n<\/pre>\n
Performing database queries<\/h3>\n
Database queries can be performed via the SQL SELECT statement:<\/p>\n
\r\n--Select the name and username column of all users\r\nSELECT user, username FROM AppUser\r\n<\/pre>\n
The following code segment retrieves the user and username columns for every record in the AppUser table and print them on the Console.<\/p>\n
\r\n\/\/ Connect to database\r\nstring dbConnectionString = @"Data Source=sample.s3db";\r\nSQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);\r\nsqliteCon.Open();\r\n\r\n\/\/ Execute query on database\r\nstring selectSQL = "SELECT user, username FROM AppUser";\r\nSQLiteCommand selectCommand = new SQLiteCommand(selectSQL, sqliteCon);\r\nSQLiteDataReader dataReader = selectCommand.ExecuteReader();\r\n\r\n\/\/ Iterate every record in the AppUser table\r\nwhile (!dataReader.Read())\r\n{\r\n   Console.WriteLine("User: " + dataReader.GetString(0)\r\n        + " Username: " + dataReader.GetString(1));\r\n}\r\ndataReader.Close();\r\nsqliteCon.Close();\r\n<\/pre>\n
Note:<\/strong> 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.<\/p>\n
Some problems that took me some time to solve<\/h3>\n
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.<\/p>\n
System.Data.SQLite.SQLiteException: The database file is locked cannot rollback transaction - SQL statements in progress<\/h4>\n
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:<\/p>\n
\r\n\/\/ Connect to database\r\nstring dbConnectionString = @"Data Source=sample.s3db";\r\nSQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);\r\n\r\n\/\/ Search the table for user Tommy\r\nstring selectSQL = "SELECT user FROM AppUser WHERE user = 'Tommy'";\r\nSQLiteCommand selectCommand = new SQLiteCommand(selectSQL, sqliteCon);\r\nSQLiteDataReader dataReader = selectCommand.ExecuteReader();\r\n\r\n\/\/ If Tommy is in the table\r\nif (dataReader.Read())\r\n{\r\n    \/\/ Update his username\r\n    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())\r\n    {\r\n        \/\/ Update the expiry date of the application\r\n        string updateSQL = "UPDATE AppUser SET username = 'Tommy_85'" +\r\n                                   " WHERE user = 'Tommy'";\r\n        SQLiteCommand updateCommand = new SQLiteCommand(updateSQL, sqliteCon);\r\n        updateCommand.ExecuteNonQuery();\r\n        sqlTransaction.Commit();\r\n    }\r\n}\r\nelse\r\n{\r\n    \/\/ Insert Tommy as a new user\r\n    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())\r\n    {\r\n        string insertSQL = "INSERT INTO AppUser(user, username)" +\r\n                                 " VALUES ('Tommy', 'Tommy_86')";\r\n        SQLiteCommand insertCommand = new SQLiteCommand(insertSQL, sqliteCon);\r\n        insertCommand.ExecuteNonQuery();\r\n        sqlTransaction.Commit();\r\n    }\r\n}\r\ndataReader.Close();\r\nsqliteCon.Close();\r\n<\/pre>\n
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.<\/p>\n
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.<\/p>\n
\r\n\/\/ Open connection to database\r\nSQLiteConnection sqliteCon = new SQLiteConnection(this._dbConnectionString);\r\nsqliteCon.Open();\r\n\r\n\/\/ Search the table for user Tommy\r\nstring selectSQL = "SELECT user FROM AppUser" +\r\n                          " WHERE user = 'Tommy'";\r\nSQLiteCommand selectCommand = new SQLiteCommand(selectSQL\r\n                                                   , sqliteCon);\r\nSQLiteDataReader dataReader = selectCommand.ExecuteReader();\r\n\r\n\/\/ Use a variable to store the result of the search\r\nbool tommyExists = dataReader.Read();\r\ndataReader.Close();\r\n\r\n\/\/ If Tommy is in the table\r\nif (tommyExists)\r\n{\r\n    \/\/ Update his username\r\n    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())\r\n    {\r\n        \/\/ Update the expiry date of the application\r\n        string updateSQL = "UPDATE AppUser SET username = 'Tommy_85'" +\r\n                                   " WHERE user = 'Tommy'";\r\n        SQLiteCommand updateCommand = new SQLiteCommand(updateSQL\r\n                                                            , sqliteCon);\r\n        updateCommand.ExecuteNonQuery();\r\n        sqlTransaction.Commit();\r\n    }\r\n}\r\nelse\r\n{\r\n    \/\/ Insert Tommy as a new user\r\n    using (SQLiteTransaction sqlTransaction = sqliteCon.BeginTransaction())\r\n    {\r\n        string insertSQL = "INSERT INTO AppUser(user, username)" +\r\n                                 " VALUES ('Tommy', 'Tommy_86')";\r\n        SQLiteCommand insertCommand = new SQLiteCommand(insertSQL, sqliteCon);\r\n        insertCommand.ExecuteNonQuery();\r\n        sqlTransaction.Commit();\r\n    }\r\n}\r\nsqliteCon.Close();\r\n<\/pre>\n
System.Data.SQLite.SQLiteException: Unable to open the database file unable to open database file<\/h4>\n
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.<\/p>\n
Other topics that may interest you<\/h4>\n