SQLite in .Net

After my previous post about SQL Server Everywhere I received a comment recommending SQLite as simple filebased database store (in a similar manner to Microsoft Access).

There are a couple of versions of .Net wrappers for SQLite around, so depending on which version of .Net you are using the following should be a quick guide:

##SQLite Version 2 and Version 3
There are currently 2 ‘inuse’ versions of SQLite. There are two seperate DLLs for these versions, sqlite.dll and sqlite3.dll. You can get the latest compiled version from the SQLite web site. Ideally you should use version 3 although some of the wrappers below default to using version 2 format.

The database itself is stored in a simple file, which can be created when you try to access the database. The file, once created is complety cross-platform, and can be used by any application that reads SQLite files of that version.

##Finisar.SQLite

This is a .Net 1.1 ADO.Net wrapper for the SQLite.dll (or SQLite3.dll). A simple example of the code used can be found here. It is distributed with a copy of both SQLite.dll and SQLite3.dll, but I’d recommend getting the latest versions from the SQLite web site (specifically the ones distributed do not support the AUTOINCREMENT constraint on a field when creating a table).

As the SQLite DLL is require you will also need to add this to your project and make sure it is copied to the bin directory when compiling your project.

This project does not appear to have been updated since October 2005, and they even recommend themselves using the next wrapper instead.

##ADO.Net 2.0 Provider for SQLite

This is a ADO.Net 2.0 Provider for SQLite. Unlike the Finisar one above it does not require a copy of the SQLite dll to operate as the SQLite 3 source code is compiled directly into the managed assembly, which makes it much easier to include in your projects. Its only drawback really is that its .Net 2.0 only, but if you’re using .Net 2.0 then I can only recommend you go for this version.

This version also contains full integration with Visual Studio 2005 so you can use the server explorer to browse and manage your SQLite databases.

##Others

There are other C# wrappers out there but most are not written as ADO.Net providers. A (possibly) complete list is available here.

##Some Sample Code

using System.Data.SQLite;
SQLiteConnection conn = new SQLiteConnection();
conn.ConnectionString = "Data Source=testdb.db;Version=3;New=False;Compress=True";
conn.Open();
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "CREATE TABLE Test (id integer primary key AUTOINCREMENT, text varchar(100))";
command.ExecuteNonQuery();

command = conn.CreateCommand();
command.CommandText = "INSERT INTO Test (text) VALUES (@1)";
SQLiteParameter parameter = command.CreateParameter();
parameter.ParameterName = "@1";
parameter.Value = "lkhkjhkjh";
command.Parameters.Add(parameter);
command.ExecuteNonQuery();

As you can see the code looks no different from any other database code. The .Net 2.0 provider has full support for DBFactory as well. Named parameters can use either the @ symbol, compatible with SQL Server, or the SQLite standards of : or $.

I have to say I’m very impressed, and its moved right to the top of my list of embedded database engines to use in .Net applications from now on.

[tags]sqlite, ado.net, .net, c#, programming, databases, database[/tags]

1 thought on “SQLite in .Net”

Comments are closed.