Posts tagged ‘sqllite’

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.