SQLite in C#

As one of my readers pointed out, SQLite is a small database fit for programs that need only minimal database activity looking for small footprint database. It’s used by Firefox / google chrome browser among others.

First let’s take a look at creating a small test SQLite database.  From the command prompt start up SQLite command line tool.

>sqlite3 test.db
sqlite>CREATE TABLE cars ( id INTEGER PRIMARY KEY AUTOINCREMENT, model text, year integer );
sqlite>insert into cars( model, year ) values( “Ford 350”, 2007 );
sqlite>insert into cars( model, year ) values( “Buick Skylark”, 1953 );
sqlite>insert into cars( model, year ) values( “Honda Civic”, 2002 );
sqlite>Select * from cars;
1|Ford 350|2007
2|Buick Skylark|1953
3|Honda Civic|2002
sqlite>.quit

You can activate the help by issuing .help
sqlite>.help

With the database created lets access it from C#. First you need some kind of a .net SQLite wrapper library. I choose to use system.data.SQLite it pretty much wraps the whole SQLite engine, it also works under Mono. All you need is their dll, add it to your project and reference the SQLite namespace. When you ship just include the System.Data.SQLite.DLL and your all set.

using System.Data.SQLite;

// Connection to the database
using( DbConnection conn = new SQLiteConnection(“Data Source=test.db”))
using( DbCommand cmd = conn.CreateCommand())
{
    // Open the database
    conn.Open();
    // Insert to the table
    cmd.CommandText = “insert into cars( model, year ) values( ‘Hummer’, 2000 )”;
    // exec
    cmd.ExecuteNonQuery();

    // Select from the table
    cmd.CommandText = “SELECT * FROM cars”;
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while( reader.Read() )
        {
            Console.WriteLine(“id=” + reader[0] +
                   “, model=” + reader[1] + “, year=” + reader[2]);
        }
    }
}

The SQLite dll is about 820k that’s the only software you need when you deploy. Along with the database, unless you want to create the database on the fly. The upside is that now you have a neat little database at your disposal. It’s fast and has a small footprint, which is what we were looking for.

But… yes there is always one of those. It turns out the Mono version is totally another distro, so don’t expect to fold this in your C# progam and expect it to run under Mono also. See further details on that here.

One Comment

  1. anelson says:

    SQLite is one of my all-time favorite open-source projects. It’s public domain (none of that commie GPL crap), performant, small, highly readable, and very portable. Whenever I start a project that needs a datastore, I default to SQLite and only go up to MySQL or MSDE if there’s a clear win.

    You also didn’t mention SQLite does in-memory databases as well, by creating a database called (iirc) “#memory”. We’re using this in one of my projects to replace klunky in-memory STL data structures will a rich schema queryable with normal SQL. Awesome.

Leave a Reply