Getting started with SQLite and .NET

2 Flares Twitter 0 Google+ 2 LinkedIn 0 Email -- 2 Flares ×

SQLite is a “self-contained, serverless, zero-configuration, transactional SQL database engine.” It is utilized in a few systems you may have heard of: Firefox, Skype, iPhone, and Andriod to name a few. The entire database is stored in a single file, which means your application or website can utilize a fully functional relational database without the constraints or costs of a traditional database server.

So when you don’t want to shell out the cash for SQL Server, your host doesn’t offer MySQL, and you’re tired of dancing around XML files as a database alternative, you can turn to SQLite!

System.Data.SQLite

You can install and use the core SQLite library on the official download page, but as a .NET developer your best bet is go with System.Data.SQLite – an ADO.NET provider for the SQLite engine. It also includes design-time support in Visual Studio 2005/2008!

I’m going to demonstrate the full process of setup using a simple C# .NET console application:

Install System.Data.SQLite from sqlite.phxsoftware.com

Include Visual Studio support for the version(s) desired.

sqlite ado.net provider installer sqlite designer installation

Create a new application, reference System.Data.SQLite

I’ve created a simple console application.

add reference to system.data.sqlite System.Data.SQLite referenced

Create a new database

Go to the Server Explorer and add a new Connection. Select SQLite as the data source, and on the following screen click “New…” to specify the name and location of the database you wish to create. Click OK and you’ve just established your connection!

Note: there is no specific naming convention for the file extension, and in this case I did not provide a file extension at all. Using something like .sqlite, .db, or .sdb all make sense to me. SQLite Administrator (a freeware GUI tool) seems to expect .sdb as the default file extension, whereas SQLite Manager (a Firefox addon) looks for .sqlite files by default.

SQLite change data source SQLite add connection

Right click on the new connection and view properties to examine the connection string. In my case it is: data source="C:\path\to\my\project\SQLiteFun\SQLiteDb"

Add a new table

Expand the database connection and right-click “Add New Table.” The table designer is still in development, so it’s important to note that you don’t have to use Visual Studio as the GUI tool for the database. There are a number of 3rd party tools you can use, and I’ll mention a couple below.

SQLite add new table SQLite add new table pt2

I’ve created a table named “Colors” and filled it with some data just for this demonstration.

SQLite table retrieve data

Write some traditional ADO.NET code

The System.Data.SQLite namespace contains all the classes used below (SQLiteConnection, SQLiteCommand, SQLiteDataReader). Here I’m using the typical ADO.NET way of creating a connection, a command, and executing a datareader. The name and hex database fields are outputted to the console one record at a time.

static void Main(string[] args)
{
    string connStr = @"data source=""C:\path-to-my-project\SQLiteFun\SQLiteDb""";
    string sql = "select * from Colors";

    using (SQLiteConnection conn = new SQLiteConnection(connStr))
    {
        SQLiteCommand cmd = new SQLiteCommand(sql, conn);
        SQLiteDataReader rdr = null;
        conn.Open();
        try
        {
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (rdr.Read())
            {
                Console.WriteLine(rdr["name"] + " " + rdr["hex"]);
            }
        }
        finally
        {
            rdr.Close();
        }
    }

    Console.Read();
}

And the output:

program output

That’s it!

That’s all there is to get started with SQLite in .NET using System.Data.SQLite!

GUI Tools

There are a number of third party GUI tools to interact with your SQLite database (thus, installing and using Visual Studio design-time support is purely optional). Below are examples of two of them in use.

SQlite Administrator

SQLite Administrator is a great little freeware tool supporting multiple languages and many features.

SQLite Administrator GUI

Firefox plugin

SQLite Manager is a Firefox addon (or see project hosted on google code: http://code.google.com/p/sqlite-manager/)

SQLite Manager GUI

Next up: SQLite with ASP.NET

In the beginning of this article I alluded to the fact that you may choose to use SQLite as a free database alternative to a paid SQL Server (or other) host, but I’ve completely ignored ASP.NET in this article.

I’m going to tinker with SQLite some more, using it with ASP.NET both locally and on a shared hosting provider. The results I will leave for a follow-up article.

2 Flares Twitter 0 Google+ 2 LinkedIn 0 Email -- 2 Flares ×