Using the Sterling database in your Windows Phone 7 App

I am working on a Windows Phone 7 app (stay tuned. Will reveal soon) and needed a database to manage my data. As you may be aware, the current release of Windows Phone 7 does not have a native embedded database, unlike SQLite for the iPhone platform. A Google search landed me this proof of concept project that makes SQLite available for Windows Phone 7. Coupled with the SQLite Manager Firefox add-on, it looked like I had everything I need for my data storage solution. This blog post, which outlines the creation of a helper class, made using SQLite in my app even easier.

But then I hit a roadblock. No matter what, I could not get my app to talk to my SQLite database. I could not get rid of the “Unable to open database file” exception. So, I gave up and resumed my search. This time, search revealed Sterling, an Isolated Storage database for Windows Phone 7 with LINQ support created by Jeremy Likness. Perfect.

The codeplex home of Sterling has everything you need to start using this excellent storage solution for your Windows Phone 7 app. Source, sample code, documentation (MS Word and PDF formats). You get everything. However, I did run into a few hiccups and hopefully my findings below will help you get past any blocks. This is not a replacement for the excellent documentation for Sterling. I am filling a few gaps that will help those not familiar with Silverlight and/or Windows Phone 7 get Sterling setup quickly.

Here’s how you do it.

1. Download the latest Sterling changeset (69384 as of this writing) from here and extract the zip file to a folder of your choice

2. The following folders will be extracted

Folder Name Description
BuildProcessTemplates Templates used by TFS build process
doc Sterling documentation in Word and PDF formats
src Sterling source code and sample projects

3. The src folder contains the following sub-folders

SetupSln Visual Studio setup solution
SterlingSln Sterling source for Phone and Silverlight projects, samples

4. The SterlingSln folder contains the following

SterlingExample A sample Silverlight project demonstrating the use Sterling
SterlingExample.Web ASP.Net web project that hosts the SterlingExample
WindowsPhoneSterling A sample Windows Phone project demonstrating the use of Sterling
Wintellect.Sterling Sterling engine for use in Silverlight applications
Wintellect.Sterling.Test Visual Studio test project for Wintellect.Sterling
Wintellect.Sterling.WindowsPhone Sterling engine for use in Windows Phone applications

5. The SterlingSln folder also contains the following Visual Studio 2010 solutions

WindowsPhoneSterlingSln.sln VS2010 solution with a Windows Phone sample app (WindowsPhoneSterling) and Windows Phone Sterling engine (Wintellect.Sterling.WindowsPhone)
Wintellect.Sterling.sln VS2010 solution that loads the Sterling engine for Silverlight (Wintellect.Sterling) and a VS2010 test project (Wintellect.Sterling.Test)
Wintellect.Sterling.WindowsPhone.sln VS2010 solution that loads the Sterling engine for Silverlight (Wintellect.Sterling), VS2010 test project (Wintellect.Sterling.Test) and the Sterling engine for Windows Phone (Wintellect.Sterling.WindowsPhone)

The Windows Phone 7 sample app that demonstrates the use of the Sterling engine is WindowsPhoneSterlingSln.sln. In my opinion, this solution file should be renamed as WindowsPhoneSterlingSampleSln.

6. Now that we have an idea of what we get in our download, it’s time to use the engine in your project. You can either build Wintellect.Sterling.WindowsPhone.csproj and reference the generated DLL in your Phone app or add Wintellect.Sterling.WindowsPhone.csproj to your Windows Phone solution and opt for Project reference.

7. Peruse the Quick start sections in the documentation to understand how to use Sterling. The “Using Sterling” section outlines the required steps.

And here is where I stumbled.

The sample code following the “Using Sterling” section shows a service class that can be created in your app to ease the use of Sterling. The caption states that the code is from the Sterling Phone example project. However, if you search WindowsPhoneSterling.csproj or WindowsPhoneSterlingSln.sln, you won’t find the service class. A sample service class (SterlingService.cs) is included in SterlingExample.csproj. But that project is a classic Silverlight project and not a Windows Phone 7 project. I was confused. If you look in the WindowsPhoneSterling.csproj sample project, Sterling engine usage code is in App.xaml.cs file. The code in App.xaml.cs follows the steps outlined in the “Using Sterling” section from the documentation. And yet, the sample code that follows service class, for example, code in “Saving your class” section, references the DatabaseService class.

So, after spending a couple of head-scratching moments this is what I comprehended.

The Sterling engine can be setup in your Windows Phone project in two ways

1. Follow the steps outlined in “Using Sterling” section from the documentation and sprinkle code in the various Application service methods (activating the engine in the Application_Launching event handler, deactivating the engine in the Application_Deactivated and Application_Closing event handlers, etc)

or

2. Create a service class that performs the activation, database registration, deactivation and disposal.

Personally I like option 2. Much cleaner code and I don’t have to monkey with various Application service event handlers. Also, I don’t have to declare a public property in the App class to allow access to the current instance of the database. The service class exposes a static property named “Current” using which you can access the current database instance.

Once you create your service class (complete implementation sample can be found in the documentation for Sterling), you invoke it in your App.xaml. Here is the code that you need to add to your App.xaml

1. Add the namespace declaration

xmlns:yourTag="clr-namespace:ServiceClassNamespace"

Replace “yourTag” above with whatever fancies you and “ServiceClassNamespace” with the namespace of your service class

2. Add the service declaration markup to the <Application.ApplicationLifetimeObjects> node in your App.xaml file like so

<yourTag:YourDatabaseService />

When your Windows Phone app starts up, the methods (StartService, Starting) on your service class will be invoked auto-magically. Likewise, when the app is deactivated or shut down, the appropriate methods (Exiting, Exited) will be invoked again. As long as you follow the conventions,

So, there you have it. Hopefully this has been useful. The Sterling database engine is a fantastic Isolated storage solution for your Windows Phone App. Very few moving parts and dead easy to use. Jeremy Linkess has done a very good job of creating an ingeniously simple solution.

Advertisements

About Ramesh Sringeri

Yet another chaos monkey. View all posts by Ramesh Sringeri

4 responses to “Using the Sterling database in your Windows Phone 7 App

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: