SQLite A Good Alternative

In the past I have talked about SQL Server Desktop Edition and VistaDB..

I've really been searching for that perfect - elusive, small, embedded, free database. Something that doesn't need an installation, supports encryption and just works. Especially in C#....

.Net is capable of xcopy deployment, and it would be nice if my database could go with me..

I have been leaning toward SQL CE (especially 3.5). It meets most of my requirements (you can copy the dll's for private deployment - and don't need to install)..

But, I think I found a better candidate: SQLite!.

And it is already inside of some things you are probably already using:

• Adobe (Photoshop Lightroom, Air, possibly Acrobat Reader)
• Apple (Apple Mail, Safari, possibly iPhone, and iPod touch)
• Firefox
• Google (Google Gears, Android)
• McAfee
• Microsoft (maybe in a game?)
• Philips (mp3 players)
• PHP
• Python
• REALbasic
• Skype
• Sun (Solaris 10)
• Symbian
• Toshiba

I have been playing with it for a while in Visual Studio 2008, and am very happy with what I am seeing. The ADO.Net provider gives very clean integration. I am having no problem accessing my data either through the Server Explorer, Direct Connections, LINQ, or even ADO.Net Entity Framework. It is fast, small, and has encryption support (the ADO.NET provider version -link provided below- includes 128 RC4 encryption).

Of course there are a few things to work around. SQLite itself is written in C, and the ADO.NET provider is a managed wrapper around the compiled code. So although .Net can target AnyCPU, there are flavors of the managed wrapper for x86, x64, Itanium, and ARM. Fortunately .Net seems to utilize late binding, and you can figure out your processor architecture and copy the correct DLL into place in time for your app to run properly.

SQLite Home Page
System.Data.SQLite ADO.NET Provider
SQLite Admin - UI Management Console

An example of copying the correct DLL in place:

    public partial class App : Application
    {
        protected override void OnStartup(StartupEventArgs e)
        {
            base.OnStartup(e);

            try
            {
                FileInfo fi = new FileInfo(Assembly.GetEntryAssembly().Location);
                string strSource, strDest = fi.DirectoryName + "\\System.Data.SQLite.dll";
                if (Microsoft.Build.Utilities.ProcessorArchitecture.CurrentProcessArchitecture == Microsoft.Build.Utilities.ProcessorArchitecture.AMD64)
                {
                    strSource = fi.DirectoryName + "\\System.Data.SQLitex64.dll";
                }
                else
                {
                    strSource = fi.DirectoryName + "\\System.Data.SQLitex86.dll";
                }
                // Copy the correct dll in place
                if (!File.Exists(strDest)  new FileInfo(strSource).Length != new FileInfo(strDest).Length)
                {
                    File.Copy(strSource, strDest, true);
                }

            }
            catch (Exception exc)
            {
                MessageBox.Show(string.Format("Error copying DLL: {0}",exc.Message));
            }
        }
    }