Over the summer of 2012, Microsoft announced support for SQLite on both Windows 8 and Windows Phone 8.

This is exciting news because it allows developers to build enterprise and consumer apps that utilize the world’s most widely used embedded database engine.  We all know that there’s no such thing as ubiquitous wireless connectivity and therefore mobile apps must have an offline data store so users can keep working in the absence of a wireless network.  While apps with limited data requirements can serialize data offline as JSON or XML, certain enterprise line of business apps need a true mobile database to meet their larger data requirements.

For those of you who have worked with SQL Server Compact, you’ll find SQLite’s characteristics to be very familiar.  It’s an embedded database that runs in-process with your mobile app instead of running as a separate process like a server-based database.  It stores data in a single disk file, supports transactions, and doesn’t require any type of configuration to get started.  I can’t emphasize enough the importance of SQLite’s full ACID support.  Transactions will fully succeed or be safely rolled back in the face of your app crashing, your devices’ battery running out, or a full operating system crash.

The size of the database engine is a small as 350kb, and supports all our new platforms whether they’re x86, x64, or ARM.  SQLite supports most of the SQL92 standard so most database developers should feel right at home.  Most of the DDL and DML features you expect are supported.

To get started, go to http://www.sqlite.org/download.html and click sqlite-winrt-3071401.vsix to download the latest Precompiled Binaries for Windows Runtime from SQLite.org as shown below.

Once your download is complete, right-click on sqlite-winrt-3071401.zip and select Open with… as shown below.

In the How do you want to open this file? dialog, uncheck Use this app for all .zip files, scroll down and select Look for another app on this PC.

In the Open with… dialog, navigate to C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\VSIXInstaller.exe and click Open.  This will launch the VSIX Installer as shown below.  Click Install.

Of course, there’s another way to do this from within Visual Studio by selecting Tools | Extensions and Updates.  Expand Online and then search for SQLite.  The same SQLite for Windows Runtime that you installed manually will show up with the ability to download and install from within the tool as shown below.  You may be asked to restart Visual Studio.

With the SQLite for Window Runtime installed, launch Visual Studio 2012 on your Windows 8 computer and create a new Windows Store app.  In the Solution Explorer, right-click on References, and select Add Reference.  In the Reference Manager, expand Windows and select Extensions on the left side of the dialog.  Check both Microsoft Visual C++ Runtime Package and SQLite for Windows Runtime and click OK as shown below.

In order to create an app using SQLite, you won’t be able to utilize Any CPU compilation.  Therefore, from the Debug combo box, select Configuration Manager.  For both Debug and Release Active solution configurations, you must select either x86, x64, or ARM for your Active solution platform.  Make sure you switch to Release before submitting your app to the store.

In order to more easily work with SQLite, I suggest you get some wrapper code to help you out so you don’t have to PInvoke unmanaged SQLite functions yourself.  Your best bet is to right-click on References and select Manage NuGet Packages.  In the Manage NuGet Packages dialog, expand Online and then search for SQLite.  Your search results will include a lot wrappers, but I need to steer you in the direction of libraries that support x86, x64, and ARM while not depending on ADO.NET.  Select the LINQ-friendly sqlite-net library created by Frank Krueger, click Install and then close the dialog to add it to your solution.

We all know that serious mobile enterprise line of business apps require a mobile database to store data downloaded from on-premise and cloud-based servers, data captured in the field, and business transactions that occur on the device.  With SQLite we have a robust mobile database solution that spans Windows 8, Windows RT, and Windows Phone 8.

Assuming you still don’t want to create your own SQLite wrapper, I’ll show you how get started with some DDL using sqlite-net in the next article.

- Rob