A Look at Microsoft’s PCL for SQLite

Having grown up in a world where the concept was anathema to Microsoft, it seems strange to think of them now on the side of open source. In 2002, for instance, there was public debate about the GPL with Microsoft taking a stand against it. Now, there’s a part of Microsoft that’s pushing out open source, “Building bridges between Microsoft and non-Microsoft technologies.”

Now it has to be said that this does not come from sheer altruism. Microsoft has come to realize that there’s an awful lot of good software running on multiple platforms and it makes sense for developers using its technologies — such as Windows Phone or .NET — to be able to use them.

SQLiteSpy

It comes as quite a surprise, though, when you look at the range of projects and see the likes of MongoDB, Apache Cordova and ZooKeeper, Hudson and Jenkins plugins for Windows Azure and even an SQL Server connector for Apache Hadoop. It’s even more of a surprise when you look at Microsoft’s archives and blogs and realize it has been doing this for five years.

I found the site through an announcement about Microsoft open sourcing a portable class library for SQLite. This is one of my favorite databases, as I’ve used it on a major piece of development in the last 12 months, replacing MS Access 95 in a refresh of Delphi code I wrote in 1998. Apart from not supporting updates based on a join between two tables that had to be rewritten in code, SQLite was an excellent replacement and one I’ve considered using with other C# projects. If you need an SQL database in your app but don’t want to have the hassle of installing a server on your client’s PC, SQLite provides an alternative.

SQLite is one of the most widely used databases, found in Android and iOS smartphones and the third favorite in a survey of 900 developers. It only adds 500KB to your project to build in the database engine and the provider acts as a wrapper around sqlite3.dll, which you download from the SQLite website. The database is a single file.

Flexibility

If you prefer C# code rather than the Microsoft wrapper around the compiled C code DLL (which is limited to 32 bit on .NET and 64 bit on Windows 8.1 Runtime), there is also a C# port (a reimplementation) of SQLite called C#-SQLite. That’s been around since 2009 and has the blessing of SQLite’s creator D. Richard Hipp. Bear in mind that it’s not an official version and is completely independent of SQLite. There are benchmarks from 2011 comparing SQLite with C#-sqlite that show C#-sqlite as slower at inserting and deleting but faster at iterating and searching.

If you’ve ever wanted to learn SQL and use it in a project, SQLite is an excellent place to start. There’s no installation needed. My favorite client is the excellent Windows freeware SQLiteSpy, though there are plenty of alternatives. All let you create and manage databases and run SQL to create tables, add indexes and do the usual select, update, insert and delete statements.

SQLitespy

But don’t forget that anyone who has the SQLite database file can access it. There are several solutions to this including the dual license SQLCipher, which provides an open source Community Edition written in C and allows commercial use, and a commercial version for several platforms. Then there’s the licensed SQLite Encryption Extension (SEE) which is provided by the SQLite developers and the commercial sqlite-crypt.