Recently I was experimenting with SQLite. SQLite is a popular, serverless database that is amazingly lightweight and amazingly free. It is the most widely deployed SQL database engine in the world and it's source code is in the public domain. It comes free with no strings attached - do with it what you will. On a whim, I decided to see how involved it would be to incorporate a SQLite database into a simple C# ASP.NET web application. Over the course of an hour or so, I was able to create a table in SQLite and get it's data displayed in a grid view control in a .NET web application.

After downloading and playing around with the SQLite executable for a while, I started looking for ways to hook it into a .NET application. It turns out that there is a handy ADO.NET adapter for SQLite called System.Data.SQLite. More information about the SQLite ADO.NET adapter can be found at System.Data.SQLite.org. The adapter can be downloaded from here.

There is a good GUI for SQLite called SQLite Administrator that can be found here. It helps with creating and editing database files. There is also an add-on for Firefox that is supposed to be good. It might be worth a try. I have not tried it yet because I normally use Chrome.

Download and install the System.Data.SQLite DLL

I downloaded the executable from the System.Data.SQLite.org site listed above. I ran the executable and several items were installed in a System.Data.SQLite folder under my Program Files folder. The DLL's of interest are in the bin folder under the System.Data.SQLite folder.

Create a Web Application

Create a Web Application in Visual Studio. Under the References section in the Solution Explorer add a reference to the System.Data.SQLite DLL. Note that I had to copy the System.Data.SQLite.dll and the SQLite.Interop.dll files into the bin folder of the new web application in order for this code to work. I had to reference the System.Data.SQLite.dll file from the web application bin folder. I am not sure if it matters or not, but I am running 64bit Windows 7 on a quad-core AMD machine.

Add a GridView to the web form

I added a standard GridView control called 'GridView1' to the default web form. The grid will display the data from my SQLite database.

Next I used the SQLite Administrator to create a test database called "testdb.s3db" in the App_Data folder of my project. I put one table in the database called "test1". I put two columns in the table. The first column was "ID". It was an auto-incrementing integer field. The second column was "Name". This was a VARCHAR field with a length of 100. Actually, if I am not mistaken, SQLite ignores the lengths of VARCHAR fields. It would happily take a 200 character name in this field.

Add to the code-behind to display data in the GridView

Finally we get to the code-behind file. I made a small function called PopulateGrid that gets called in the Page_Load event. This function displays data from the SQLite database in the GridView control on the web page. Below is the code for my code-behind page. It simply displays a table containing all ID's and Names in my database.

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
using System.Data;
using System.Data.SQLite;
 
 
namespace WebApplication1
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            PopulateGrid();
        }
 
        protected void PopulateGrid()
        {
            String connectionString = "Data Source=" + 
                        Server.MapPath(@"~\App_Data\testdb.s3db") + 
                        "; Version=3;";
            String selectCommand = "Select ID, Name from test1";
            SQLiteDataAdapter dataAdapter = 
                        new SQLiteDataAdapter(selectCommand, connectionString);
 
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);
 
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }
}