Experimenting with SQLite and the SQLite.NET Provider
By Peter A. Bromberg, Ph.D.

Peter Bromberg

" There are two distinctive classes of people today, those who have personal computers, and those who have several thousand extra dollars apiece. " -- Dave Barry

SQLITE Database Engine and ADO.NET Provider

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine. Features include:

  • ACID (Atomic, Consistent, Isolated, Durable) transactions.
  • Zero-configuration - no setup or administration needed.
  • Implements most of SQL92.
  • A complete database is stored in a single disk file.
  • Database files can be freely shared between machines with different byte orders.
  • Supports databases up to 2 terabytes (2^41 bytes) in size.
  • Small memory footprint: less than 30K lines of C code, about 320KB code space (VS.NET C++)
  • Faster than other popular database engines for most common operations.
  • Simple, easy to use API .
  • An ADO.NET provider is available separately on SourceForge.net
  • Well-commented source code with over 90% test coverage.
  • Self-contained: no external dependencies.
  • Sources are in the public domain . Use for any purpose.
  • One of the developers of the ADO.NET provider also has a very nice Query Analyzer clone.
  • There is also an ODBC driver available, with source code. It works. I can also tell you it is SLOOOW!


Version 3.XX of SQLite, which is curently in Beta, makes additional improvements both in the datatypes and the Database engine / database file format. There is currently support working for the Compact Framework, I am not sure that it is complete, because I haven't tried it yet, but I am sure it will be available soon.

SQLite supports reasonably complex SQL queries including table aliasing, joins, Temp tables, and triggers. It is also very fast. The only thing missing is of course, stored procedures. In a database engine that is only about 300K, that's not a major concern to me since the ADO.NET provider class is only 48K, and my database file with some 8 or nine blog records already in it - is only about 6K! It also supports native NTFS compression, which helps keep the db size down as well.

Other projects have seen fit to make provision for SQLite. One good example is the Gentle.NET Object Persistence Framework, also available on Sourceforge.

Web Stress Test Results

I haven't had the opportunity to run serious web stress tests on SQLite with the SQLite.NET ADO.NET provider, but my early experiences show that somewhere in the neighborhood of 25 to 40 requests per second on a typical server box would be "expectable" without HTTP or other errors. This assumes that almost all of your database access is to read (not insert or update) data, which is of course, the typical usage scenario of a web -based application. This is using HOMER with from 10 to 20 simultaneous threads, and no delays between requests in the test suite. Server memory usage ran a bit higher than would be expected against compared to , for example, SQL Server, but the memory footprint remained flat throughout the test (e.g., no memory leaks). The only other issue I noticed in my tests is that the database file appears to be locked by IIS after its use. It's possible I just haven't learned all the little connection-string nuances necessary to handle this, but I don't think it will be a problem. There are API methods available that the current ADO.NET provider does not address. Previous builds combined the SQLite engine C project in with the ADO.NET provider project, but the most current iteration decouples the two - most probably because of versioning and other issues. Again, check with SourceForge.Net and especially, read the forum posts relating to the distribution for more details.

ASP.NET Blog Test Application

I put together a small "ASP.NET Blog" application to put SQLite and the ADO.NET provider to the test, and I am happy to report that I'm pleased with the results. Using the Provider is very easy because it conforms to the overall methodology of the SQLClient classes, and supports DataAdapters, DataReaders, and the creation of Update, Delete and Insert DataAdapter commands from the Select query. The SQLite database tables also have a built-in ROWID pseudocolumn which can be very useful.

I used a DataList for my display and coded up methods for the update, insert and delete to respond to ItemTemplate LinkButtons, as well as a Calendar control to select a specific date for which to view Blog entries, and an "Add Blog Entry" LinkButton in the DataList Footer which basically pops up a new empty records, allows you to fill it in, and then when you press the Update LinkButton, does the insert and rebinds the DataSource.

Here's a reduced screen shot of what the page looks like:

Creating a database and tables is very natural for SQLite. Using the ADO.NET provider:

SQLiteConnection Conn = new SQLiteConnection();
Conn.ConnectionString = "Data Source=diary.db;New=True;Compress=True;Synchronous=Off";
Conn.Open();
SQLiteCommand Cmd = new SQLiteCommand();
Cmd = Conn.CreateCommand();
Cmd.CommandText = "CREATE TABLE GOALS(GOALS_ID integer primary key , CATEGORY varchar (50), PRIORITY integer , SUBJECT varchar (150) , DESCRIPTION varchar (500),START_DATE datetime , COMPLETION_DATE datetime)" ;
Cmd.ExecuteNonQuery();
Cmd.CommandText="CREATE TABLE NOTES (NOTES_ID integer primary key ,NOTES_DATE datetime ,NOTES_TEXT varchar (8000) )";
Cmd.ExecuteNonQuery();
Cmd.CommandText =" CREATE TABLE REMINDERS (REMINDER_ID integer primary key ,REMINDER_DATE smalldatetime ,SUBJECT varchar (150) ,DESCRIPTION varchar (500) , ALARM1_DATE datetime ,ALARM2_DATE datetime ,ALARM3_DATE datetime ,EMAIL_ALARM bit )";
Cmd.ExecuteNonQuery();
Cmd.CommandText ="CREATE TABLE TODO ( TODO_ID integer primary key,CATEGORY varchar (20),PRIORITY int, PERCENT_COMPLETE float, START_DATE datetime ,END_DATE datetime , SUBJECT varchar (150) , DETAILS varchar (8000) ";
Cmd.ExecuteNonQuery();
Cmd.CommandText ="CREATE TABLE CATEGORIES (CATEGORY_ID INTEGER PRIMARY KEY,CATEGORY_NAME varchar (25))";
Cmd.ExecuteNonQuery();
Cmd.Dispose();
Conn.Close();

Likewise, performing updates and inserts will be familiar to most developers:

private void DataList1_UpdateCommand(object source , System.Web.UI.WebControls.DataListCommandEventArgs e)
{
bool isInsert=false;
SQLiteDataAdapter da = new SQLiteDataAdapter();
SQLiteConnection Conn = new SQLiteConnection();
string dbPath=Server.MapPath("Blog.db");
Conn.ConnectionString = "Data Source="+dbPath+";Compress=True;Synchronous=Off";
Conn.Open();
SQLiteCommand Cmd = new SQLiteCommand();
Cmd = Conn.CreateCommand();
// test if they pressed update button without edit--
if(e.Item.FindControl("Label6")==null) return;
if(((Label)e.Item.FindControl("Label6")).Text=="")
isInsert=true;
int blogId=0;
if(!isInsert)
blogId = Convert.ToInt32(((Label)e.Item.FindControl("Label6")).Text ) ;
string pubDate = Convert.ToDateTime( ((Label)e.Item.FindControl("Label5")).Text ).ToShortDateString();
string title = ((TextBox)e.Item.FindControl("Textbox1")).Text;
title=title.Replace("'","''");
string description= ((TextBox)e.Item.FindControl("Textbox2")).Text;
description=description.Replace("'","''");
string link = ((TextBox)e.Item.FindControl("Textbox3")).Text;
string strSQL="";
if(!isInsert)
{
strSQL="UPDATE BLOGITEMS SET pubDate='"+ pubDate +"',title='" +title+ "',description='"+description+"',link='" +link +"'";
strSQL+= " Where BlogId="+blogId.ToString();
}
else
{
strSQL="INSERT INTO BLOGITEMS (pubDate,title,description,link,public) VALUES('"+pubDate+"','"+title+"','"+description+"','" +link+"',1)";
}
Cmd.CommandText =strSQL;
Cmd.CommandType=CommandType.Text ;
Cmd.ExecuteNonQuery();
DataList1.EditItemIndex = -1;
BindList(Session["selectedDate"].ToString());
Cmd.Dispose();
Conn.Close();
}

I haven't implemented paging yet, but that's not going to be much different from any other implementation. I do have a nice search button that allows you to search the BlogItems table for a string search term in both the Description and in the Title columns. There is also a bit field, "Public" which is intended for later use, for example where you would log in with a username and password and be able to view and edit your own private items that would not normally display to others. Note also that the field names correspond with their RSS 2.0 counterparts which make exporting the contents to an RSS feed a snap.

Conclusion

SQLite, combined with the current iteration of the Finisar SQLite.NET Provider, represents an interesting alternative where low to moderate usage stress is likely, and is an attractive alternative to MSAccess, MSDE, MySQL and SQL Server where an open-source, well-supported "low footprint" zero-impact deployment database engine is needed. There is a growing amount of community open-source support, and the engine has had extensive testing. It's also being released with PHP 5.0 as "part of the package", indicating broad support in the development community. You can expect to see more offerings related to SQLite, as well as possibly a 100% Managed-code implementation. In addition, expect to see the developer community fully implement SQLite for the Compact Framework, making this an attractive and very compact offering for CF applications employing easily - deployable local data stores.

IMPORTANT NOTE: As of 11/3/2004, I've updated this solution to use the PagingDataList component, and the FreeTextBox component for editing, as well as refactoring the code to use SQLite version 3.06, and a button to save the blogitems as an RSS feed. In addition I've added my custom "FTPZigger" class that will allow you to Password-protect Zip your blog Database, and FTP it to your server using the Sync UP button, and also to FTP it back (say to another machine) and unzip it. See the web.config for the correct settings. If you download this solution and run it and you get write permission or similar errors, please don't send me emails about how "it doesn't work"! The blog.db file must have write permissions, both in the folder, and the file itself, and in the IIS application. This is the same as what is required to handle an MSAccess database file. If you have other problems, we have a nice forums section where many other intelligent developers other than I can read, answer, and benefit from your post. Thanks, and enjoy!

download the Visual Studio 2003 Solution that accompanies this article


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.
Article Discussion: