Using SQLite with .Net

What Is SQLite?

 

SQLite is a free, public domain database engine. Unlike SQL Server, Oracle, or other commercial databases, it's not a stand-alone program. It doesn't have a server. It's a very small code library written in C which other applications (yours) can call.

 

An SQLite database is a single file. The tables and data, triggers, foreign keys, and constraints are all stored in this file. Your application reads and writes to the database by calling SQLite. When you do a SELECT or UPDATE, SQLite reads and writes to the file. If you do your updates inside a SQL transaction, SQLite makes sure you have exclusive access to the file. You can back up your database by making a copy of the database file. In short, you can use SQLite just like you would use any other database, but behind the scenes, you are calling a small code library that is storing your data in a file.

 

SQLite code is public domain. There's not even an open-source license. It's in the public domain, which means "anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means." (Cite) There are still many volunteer developers working on SQLite, but the code is released to the public with no restrictions.

 

SQLite supports most of the standard SQL language, with some limitations. The most significant missing pieces are GRANT and REVOKE. SQLite writes to a file on a drive. The only permissions available are the read and write permissions for the file itself. The complete list of missing functionality can be found here.

 

SQLite transactions are ACID (Atomic, Consistent, Isolated, and Durable). That means that a transaction in SQLite will occur completely, or not at all. Transactions are enforced even when there are application errors, power outages, and system failures. (Cite)

 

SQLite may be the most commonly used database in the world. It's in every copy of Firefox. It's on the Apple iPhone and in many other cellphones and mp3 players. It's in Skype and Google Gears. It comes bundled with PHP and Python. You can use it too.

 

The SQLite home page can be found here.

 

What Is SQLite Good For?

 

SQLite works very well as a small, embedded database. The entire SQLite library with all options enabled is around 300 kb. With optional features disabled, it's around 180 kb. It was designed for small, portable applications, especially applications that need to work offline. The data is stored in a local file, which means the database works without an internet connection. It also means the data remains even when the computer is turned off.

 

In recent times, portable applications are becoming more and more popular. Many of these programs can be run from a portable flash drive. An important aspect of these programs is that they don't store their settings on the host computer. They don't store values in the registry. SQLite can help. Instead of storing your application settings and preferences in the registry, you can store them in an SQLite database.

 

How To Use SQLite

 

SQLite has an API written in C. In .Net terms, this makes SQLite unmanaged code. Fortunately, SQLite also provides a good open-source ADO.NET data provider here.  Using the data provider, you can access an SQLite database just like you would SQL Server or other commercial databases.

 

First, download the data provider and run the installer. The installation files include several.dlls, including System.Data.SQLite.dll and System.Data.SQLite.Linq.dll. System.Data.SQLite contains the entire SQLite code library plus the ADO.net wrapper. You should add a reference to this .dll to your project.

 

Now you have the code library, but you need to make a database. There are many free and commercial SQLite management programs out there. These programs provide a GUI to create and update an SQLite database. There is a list of SQLite management tools at http://www.sqlite.org/cvstrac/wiki?p=ManagementTools. One of the better ones is SQLite Administrator:

 

 

Now you have the SQLite library and you have a database. You're ready to go. For example:

using System.Data.SQLite;
conn.Close();

SQLiteConnection conn = new SQLiteConnection(@ "Data Source=C:MySQLiteDB.s3db");
conn.Open();

SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select * from Customer";

SQLiteDataReader reader = cmd.ExecuteReader();

while (reader.Read()) {
     // do something
}

reader.Close();

cmd.CommandText = "delete from Customer where CustomerID = 33";
cmd.ExecuteScalar();

Have fun.