If you have spent any time on eggheadcafe.com, chances are you have found one or more of my articles about the SQLite database engine. Since Visual Studio 2005 came out, Robert Simpson branched off the original ADO.NET SQLite provider at Sourceforge.net, and started his own implementation of a SQLite ADO.NET 2.0 Provider.

The ADO.NET 2.0 provider follows the new Provider model completely, was written from scratch on VS2005 specifically for ADO.NET 2.0, and uses all the new changes in the ADO.NET framework including DbProviderFactory support, automatic distributed transaction enlistment, extensive schema support, and all the classes inherit from the ADO.NET 2.0 base classes. This provider and therefore the QA clone I present here support only the new Version 3 database format.

In addition, Robert has now provided an MSI installer that takes care of all the dirty-work of installation, registration and full Designer support in the VS.NET 2005 IDE.

For runtime operation, the only requirement is the System.Data.SQLite.dll assembly which includes the database engine and requires no installation whatsoever.

Now that the System.Data.SQLite provider can be considered mature, I decided to retrofit a project that was originally contributed by CodeMonkey for the original ADO.NET (Finisar) provider for 1.1, and bring this into Visual Studio 2005 with full support for the new ADO.NET 2.0 provider.

Converting the solution was really a no-brainer; its just that I hadn't been able to find where anybody else had done it, so I decided it was worth the trouble. Actually, the only real change that was required besides changing the provider to System.Data.SQLite was to implement the "Compact Database" (vacuum) function which is slightly changed from the original 1.1 provider. Otherwise, all of CodeMonkey's original work remains virtually unchanged.

The download below includes the complete solution. If you don't want the solution and simply want to use the SQLiteQA3 Query Analyzer clone, just copy everything in the /bin/release folder to the folder of your choice and put a shortcut to the SQLiteQA.exe onto your desktop. If you aren't familiar with SQLite SQL Syntax, which is slightly different from SQL Server, the Help menu item will take you to the online documentation.

SQLite is a very fast, zero installation database engine that supports most of the SQL 92 standard, offers triggers, indexes, and even an "in memory" database option where, with an attached physical database file, it is possible to have a lightning fast in-memory database that can be persisted back to disk. This can be extremely useful for in-memory caching because it offers the full SQL query language against an in-memory data store. Most of the ORM (Object - Relational - Mapper) frameworks also offer support for SQLite.

One item that comes up often is speed of the database engine. Since SQLite supports ACID database operations, it is wise to get in the habit of wrapping repetive SQLiteCommand invocations in a transaction with the following pattern:

using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
SQLiteParameter myparam = new SQLiteParameter();
int n;

mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
mycommand.Parameters.Add(myparam);

for (n = 0; n < 100000; n ++)
{
myparam.Value = n + 1;
mycommand.ExecuteNonQuery();
}
}
mytransaction.Commit();
}

When you do the above you will find that SQLite combined with the ADO.NET 2.0 Provider is lightning fast compared to other file-based databases.

Aside from any caveats in the original license, this project is in the public domain and may be used freely for any purpose.

Download the Visual Studio 2005 solution that accompanies this article