Diary of a Windows developer

Dec 27, 2012 in SQLite and Windows Phone
Working with SQLite in Windows Phone 8: a sqlite-net version for mobile

With a perfect timing, as soon as I’ve published my previous post about using the chsarp-sqlite engine in combination with sqlite-net, Peter Huene has released a porting of the famous library for Windows Phone 8. What does it mean? That, finally, we are able to use the native SQLite engine that has been released as a Visual Studio extension and that we can use a common library to share our data layer with a Windows Store app for Windows 8.

At the moment, the project isn’t available on NuGet yet and requires two steps: the first one is to add a native class, that acts as a wrapper for the functions used by sqlite-net, and the second is to download a specific sqlite-net version, where the developer has replaced the usage of the csharp-sqlite engine with the native one.

Let’s start!

Please welcome GitHub

Both projects are hosted on GitHub (a popular website to host open source projects that also acts as a source control system based on Git), so the best way to download and use both them is using Git: you can also download the project in a single zip file but, this way, every time the developer will change something you’ll have to download everything again and add the new project to your solution. If you’re not familiar with Git, the easiest way to use it is to download GitHub for Windows, which is a Windows client that is able to connect to repositories hosted on GitHub and to keep files in sync with the server.

Just download and install the application from here: after you’ve launched it you’ll have to configure it for the first time. You’ll need to have a valid GitHub account: if you don’t have it, simply go to the website and create one. Once you’ve done you should see a window like this:

Unless you’ve already used GitHub and you already own one or more repositories, the window will be empty. Now go to the GitHub website and, specifically, to the sqlite-net-wp8 repository, that is available at the URL https://github.com/peterhuene/sqlite-net-wp8. At the top of the page, in the toolbar, you’ll find a button labeled Clone in Windows. Click on it and make sure that you’ve logged in in the website with the same credentials you used for the application, otherwise you’ll be redirected to the page to download the GitHub client.

Once you’ve done it the GitHub client will be opened and the repository will be automatically added to the local repositories list: after a while (the progress bar will show you the status of the operation) the whole repository will be downloaded in the default location, that is the folder _C:\Users\User\Documents\GitHub_ (where User is your Windows username). Inside it you’ll find a folder called sqlite-net-wp8: that is the project that we need to add to our solution.

Since we’re already playing with GitHub, let’s download also the sqlite-net fork adapted to work with Windows Phone 8: repeat the operations we’ve just made on the repository available at the URL https://github.com/peterhuene/sqlite-net.

The last thing to do is to make sure you’ve installed the SQLite for Windows Phone extension, that is available from the Visual Studio Gallery.

Now that we have everything we need, we can start working on our Windows Phone 8 project.

Let’s play with SQLite

The first thing to do is to open Visual Studio 2012 and to create a Windows Phone 8 application. Once you have it, it’s time to add to the solution the sqlite-net-wp8 project we’ve downloaded from GitHub: simply right click on the solution, choose Add existing project and look for the file Sqlite.vcxproj in the sqlite-net-wp8 folder (that should be C:\Users\User\Documents\GitHub\sqlite-net-wp8). You’ll see the new project added in the Solution Explorer: it will have a different icon than the Windows Phone project, since it’s written in native code and not in C#.

As I’ve previously explained, this is just the native wrapper for some of the functions used by sqlite-net: now we need to add the real sqlite-net and we do that by simply copying the Sqlite.cs and SqliteAsync.cs files that are stored inside the src folder of the solution (that will be available, as for the other one, in the C:\Usesr\User\Documents\GitHub folder) into our project. We can do that by simply right clicking on the Windows Phone project and choosing Add existing item.

Now we need to add a reference in our Windows Phone application both to the sqlite-net-wp8 library and to the SQLite engine: right click on your project, choose Add reference and, in the Solution tab, look for the sqlite library; after that, look for the SQLite for Windows Phone library, that is available in the Windows Phone – Extensions section.

UPDATE: the developer, to keep supporting also the C# engine I’ve talked about in my previous post, has recently added a new requirement to use his library; you’ll have to add a specific contitional build symbol, in order to properly use the native engine. To do that, right click on your project (the one that contains the Sqlite.cs and SqliteAsync.cs files you’ve previously added), choose Properties, click on the Build tab and, in the Conditional compilation symbols textbox add at the end the following symbol: USE_WP8_NATIVE_SQLITE. In a standard Windows Phone 8 project, you should have something like this:

SILVERLIGHT;WINDOWS_PHONE;USE_WP8_NATIVE_SQLITE

And now? Now we can simply copy and paste the code we’ve already seen in the original post about Windows 8 or in the more recent post about csharp-sqlite: since all these libraries are based on sqlite-net, the code needed to interact with the database and to create or read data will be exactly the same. Here are the usual examples I make about doing common operations:

UPDATE: as some readers have pointed out in the comments, with the previous code eveyrthing was working fine, but the database file was missing in the local storage. As the sqlite-net-wp8 developer pointed me out, there’s a difference between the Windows 8 and the Windows Phone version of the library. In Windows 8 you don’t have to set the path, it’s automatically created in the root of the local storage, unless you specify differently. In Windows Phone 8, instead, you have to pass the full path of the local storage where you want to create the databse: the code below has been updated to reflect this change.
```
//create the database
private async void CreateDatabase()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);
    await conn.CreateTableAsync<Person>();
}

//insert some data
private async void Button_Click_1(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);

    Person person = new Person
    {
        Name = "Matteo",
        Surname = "Pagani"
    };

    await conn.InsertAsync(person);
}

//read the data
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"), true);

    var query = conn.Table<Person>().Where(x => x.Name == "Matteo");
    var result = await query.ToListAsync();
    foreach (var item in result)
    {
        Debug.WriteLine(string.Format("{0}: {1} {2}", item.Id, item.Name, item.Surname));
    }
}
```
###

Be careful!

There are some things to keep in mind when you work with this library and SQLite. The first one is that, actually, both libraries are not available on NuGet: you’ll have to keep them updated by using GitHub for Windows and, from time to time, by syncing the repositories, in order to have your local copy updated with the changes. If you’re going to add the sqlite-net-wp8 project to the solution, like I did in the post, you won’t have to do anything, you’ll just have to rebuild your project. In case of the sqlite-net fork, instead, since we’ve simply copied the files, you’ll need to overwrite the old ones with new ones, in case they are updated. Or, even better, you can add the two files as a link from the original project: this way you’ll simply have to update the libraries from GitHub to see the updates in your application.

The second important thing to consider is that the sqlite-net-wp8 library is built against a specific SQLite version: if the SQLite team releases an update to the Visual Studio extension (so that Visual Studio is going to prompt you that there’s an update to install), don’t update it until the sqlite-net-wp8 project has been updated. Otherwise, many references will be missing and you won’t be able to open the project at all.

Have fun!
Dec 21, 2012 in SQLite and Windows Phone
A workaround to use SQLite in a Windows Phone 8 application

Recently I’ve made a post where I’ve tried to explain better the SQL Lite support situation on Windows Phone 8. The core of the post was very simple: the SQLite engine is available for Windows Phone 8, but actually a library to manipulate data using a high level language like C# or VB.NET is missing.

One of the workarounds I’ve talked about was using csharp-sqlite, an open source project hosted on Google Code that embeds the SQLite engine into a C# library, so that it can be used also by applications based on the .NET framework. This project also features a client library, that can be used to manipulate the database: there’s also a Windows Phone porting of the library, but you can’t share it with a Windows 8 project due to the lack of support to the .NET framework.

Luckily, there’s still a solution to share your data layer with a Windows 8 application: by using sqlite-net. I’ve already talked about this library in a previous post and this is, probably, the most famous wrapper for the native SQLite engine. The good news is that sqlite-net supports two different SQLite engines, according to the platform: the native one on Windows 8 and the csharp-sqlite one on Windows Phone 7 or 8. The result is that that you’ll be able to use the sqlite-net classes and methods on both platforms and share the code between them: according to the platform, the proper SQLite engine will be used. Is there a downside? Yes, csharp-sqlite is a porting and is not managed directly by the SQLite team. This means that every time the SQLite team releases an update of the engine the C# engine should be recompiled against the new one. Plus, it seems that the project has been abandoned: the latest version has been released on 26th August 2011, so it’s a little bit old. This means that you’re going to miss all the improvements that the SQLite team has made in the last year.

But, since there are no other options at the moment, it worth a try to take a closer look to this option, especially if you just have to store some data and you don’t have a complex database to manage.

###

Grabbing the engine

The first thing to do is to go the Download page of the csharp-sqlite project and download the latest available version, that is the 3.7.7.1.71. There isn’t a binary version or a package on NuGet: you’ll have to manually open the solution file Community.CsharpSqlite.SQLiteClient.WP.sln inside the Community.CsharpSqlite.SQLiteClient.WP folder. Visual Studio 2012 will update some files of the solutions, since it has been created using Visual Studio 2010. Now build the solution: you may get some compilation errors, in this case try to build just the Community.CsharpSqlite.WinPhone project; in fact, the build errors are just caused by some unit tests that are not using valid code.

Once you’ve done that, open the project’s folder and grab the Community.CsharpSqlite.WinPhone.dll file from the bin/Debug folder: it’s the reference you’ll need to add to your Windows Phone 8 project, so go and create a new one using the basic Windows Phone application template and choose Windows Phone OS 8.0 as target OS. Now you’ll need to add a reference to the library we’ve copied before, by right clicking on the project and choosing Add reference. After you’ve done that, it’s time to install sqlite-net by using NuGet: right click again on the project, choose Manage NuGet packages, look for and install the package called sqlite-net.

Playing with SQLite

Once you’ve done, you’ll find some new files in your project: open the SQLite.cs file; you’ll see, right at the top, what I was talking about at the beginning at the post.
```
#if WINDOWS_PHONE
    #define USE_CSHARP_SQLITE
#endif

#if USE_CSHARP_SQLITE
    using Community.CsharpSqlite;
    using Sqlite3DatabaseHandle = Community.CsharpSqlite.Sqlite3.sqlite3;
    using Sqlite3Statement = Community.CsharpSqlite.Sqlite3.Vdbe;
#else
    using Sqlite3DatabaseHandle = System.IntPtr;
    using Sqlite3Statement = System.IntPtr;
#endif
```
As you can see, by using conditional compilation instructions, sqlite-net will define and activate the symbol USE_CSHARP_SQLITE in case the app is running on Windows Phone. This symbol will be used across the library to wrap the different behaviors to the standard sqlite-net methods and classes: this way, you’ll be able to use the same code to read and write data to the database, regardless of the platform.

And now what? You can copy and paste the same code I’ve explained in this post: since the library is the same, you’ll be able to use the same approach to work with the data. For example, you can do something like this to create the database and a table to store a Person entity:
```
private async void CreateDatabase()
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");
    await conn.CreateTableAsync<Person>();
}
```
After that, you can insert some data in the database:
```
private async void Button_Click_1(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");

    Person person = new Person
    {
        Name = "Matteo",
        Surname = "Pagani"
    };

    await conn.InsertAsync(person);
}
```
or read the same data and write it into the Output Window:
```
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
    SQLiteAsyncConnection conn = new SQLiteAsyncConnection("people");

    var query = conn.Table<Person>().Where(x => x.Name == "Matteo");
    var result = await query.ToListAsync();
    foreach (var item in result)
    {
        Debug.WriteLine(string.Format("{0}: {1} {2}", item.Id, item.Name, item.Surname));
    }
}
```
If I would have isolated the code to interact with the database in another class (for example, a service class) I would have been able to use it both in a Windows 8 and a Windows Phone 8 application, without changing it: sqlite-net would have done all the dirty work of using the correct engine for me.

Conclusion

In this post we’ve seen a way to interact with a SQLite database and share the code between Windows Phone and Windows 8. It’s not the best approach, because it’s forcing us to use an unofficial porting of SQLite, that isn’t updated since more than a year. But, if you don’t have a complex scenario and you need to start working on your application as soon as possible and you need to share code with Windows 8 (so SQL CE it’s not a solution), it can be an acceptable approach. Obviously, the best solution would be to have a native sqlite-net implementation also for Windows Phone 8, in order to use the native SQLite engine that is already available in Windows Phone 8. It will come… eventually, one day or the other

Download the sample project
Dec 18, 2012 in Mangopollo and Windows Phone
How to support Windows Phone 8 devices in a Windows Phone 7 application

As I’ve already highlighted in another blog post, Windows Phone 8 has introduced many new features; plus, lot of things have been changed under the hood: Microsoft has abandoned the old kernel, based on Window Mobile’s core, to introduce a new modern kernel, based on Windows 8’s one. As a consequence, also the architecture has deeply changed: no more Silverlight and .NET Framework, but Windows Runtime.

To allow developers to reuse as much as possible their code and skills, Microsoft has introduced two important features:
- Quirk mode: the old APIs are automatically mapped to the new ones; this way, all the applications written for Windows Phone 7.x are able to run on a Windows Phone 8 device without changes in most of the cases.
  - .NET API for Windows Phone, which is a set of libraries in the Windows Runtime that match the one that were part of the Silverlight for Windows Phone runtime. This way, when the developer decides to port his project to Windows Phone 8 he can focus on supporting the new features rather than rewriting the previous code to use the new Windows Runtime APIs. </ul>
  The best way to use the Windows Phone 8 features is to update your project to the new release, by using a specific Visual Studio option that is displayed when you right click on a Windows Phone 7 project.
  
  This scenario, even if it’s the most powerful, requires to maintain two different projects if we want to keep supporting also Windows Phone 7 users, since Windows Phone 8 applications are not compatible with the previous versions. Sometimes this solution can be too expensive for the developer, especially if your application is very simple and can gain few benefits from the Windows Phone 8 features.
  
  In this post we’re going to see two solutions that will help us to maintain a single Windows Phone 7 project that will be adapted so that Windows Phone 8’s devices owners will be able to get the best from their devices.
  
  Supporting the new resolutions
  
  One of the most important Windows Phone 8 new features is new resolutions support: Windows Phone 7 supported just the 800×480 resolution; as a consequence all the icons and images that are part of our project can degrade if they are displayed on a device with a higher resolution, like the Lumia 920 (1280×768) or the HTC 8X (1280×720).
  
  Now the Store accepts also Windows Phone 7.x applications with icons with higher resolution: you can embed in your project 336×336 tiles and 99×99 icons. This way, also Windows Phone 8 owners will be able to see images at the best possible quality.
  
  You just have to overwrite the original images, prepare a new XAP and sumit a new update of your application to the store.
  
  How to use some of the new Windows Phone 8 features
  
  By using a mechanism called reflection developers are able to use some of the new Windows Phone 8 features in a Windows Phone 7.x application, in case it’s running on a Windows Phone 8 device.
  
  What is reflection? This mechanism allows developers to use methods, properties and classes that are stored in a DLL library without adding it as a reference in a Visual Studio project. With this procedure you’ll be able to interact with some of the new Windows Phone 8 libraries from a Windows Phone 7.x project, even if Visual Studio isn’t able to show them.
  
  You’ll be able to use the following features:
  - The new launchers and choosers (for example, to add a new appointment to the calendar or download a map).
    - The new tiles templates and sizes. </ul>
    Using the reflection requires to write more complicated code compared to the approach you use when you have a reference in your Visual Studio project: for example, you don’t have access to Intellisense and you have to remember the exact name of every method and class you’re going to use.
    
    For this reason Rudy Hyun, a Windows Phone MVP, has developed a library called Mangopollo (the strange name comes from the fusion between Mango, the Windows Phone 7.5 codename, and Apollo, the Windows Phone 8 codename), which features a set of classes and methods that act as a wrapper to the reflection, so that you can interact with the Windows Phone 8 features using the usual approach.
    
    Mangopollo is an open source project that is available both on Codeplex and NuGet in two versions: Mangopollo and Mangopollo.Light. The difference is that the light version contains only the libraries to interact with the new tiles, so that it can be used also in a background agent project. Mangopollo already supports Windows Phone 7.8: it’s possible to use it to interact with the new tiles that are available on Windows Phone 7.8 devices.
    
    The simplest way to add Mangopollo to your project is by using NuGet: right click on your Windows Phone project, choose Manage NuGet packages and look for and install the package called Mangopollo.
    
    Now you’ll be able to use the new launchers and choosers (that are stored inside the Mangopollo.Tasks namespace) and the new tiles (which classes are inside the Mangopollo.Tiles namespace).
    
    Let’s see to examples on how to use this library: in the first one we’re going to use the SaveAppointmentTask launcher, so that the user will be able to add a new appointment to the calendar.
    
    Use the launchers
    
    Here is how to use the SaveAppointmentTask launcher:
```
private void OnCreateAppointmentClick(object sender, RoutedEventArgs e)
{
if (Utils.IsWP8)
{
SaveAppointmentTask task = new SaveAppointmentTask();
task.Subject = "Windows Phone Developer Day";
task.StartTime = new DateTime(2012, 12, 5);
task.IsAllDayEvent = true;
task.Show();
}
else
{
MessageBox.Show("This is Windows Phone 7!");
}
}
```
    The first thing to do to use the new features is to check if the app is running on a Windows Phone 8 device, otherwise we would get an exception. We can perform this task by using the Utils.IsWP8 property, which is a simple Boolean.
    
    In case our code is running on a Windows Phone 8 device we create a new SaveAppointmentTask object and we define some properties that describe the appointment, like the title and the date. The definition of this task is the same that is available in the Windows Phone 8 APIs: the name of the methods and properties are the same that you can find in the new SDK.
    
    In the end, we invoke the launcher by calling the Show method. Now let’s test the application using both the emulators: the Windows Phone 8 one (at this point, it doesn’t matter which resolution you use) and the Windows Phone 7.1 one.
    
    In the first case we’ll see the launcher, that will ask to the user to fill all the missing information about the appointment; in the second case, instead, we’ll see a warning message we’ve defined in the code.
    
    Play with the tiles
    
    Windows Phone 8 now supports three new tiles sizes and three new templates. The new sizes are:
    - Small: the tile size is ¼ of the original size.
      - Medium: it’s the standard size.
        
        Wide: it’s the rectangular size, it’s like having two medium tiles one after the other. </ul>
        
        The supported templates, instead, are:
        
        Flip: it’s the only template that was available in Windows Phone 7; information (texts and pictures) are placed on the front and on the back of the tile, which is periodically flipped to show all of them.
        
        Cycle: you can include up to 9 images, that are displayed in rotation.
        
        Iconic: it’s used to create tiles with the same look & feel of the native ones, like Mail or Messages. You can use it to show an icon, a counter or text. </ul>
        
        As a developer, other than choosing the template, you’ll be able also to interact with the tile in different ways, according to the size: for example, we can show some content only in case the user is using a bigger size.
        
        Here is a sample code that uses the Iconic template, which is described by the IconicTileData class, to create a secondary tile.
        
        private void OnCreateWideTileClick(object sender, RoutedEventArgs e) { if (Utils.CanUseLiveTiles) { var tile = new IconicTileData { Title = "WP Day", Count = 8, BackgroundColor = Colors.Transparent, IconImage = new Uri("/Assets/Tiles/IconicTileMediumLarge.png", UriKind.Relative), SmallIconImage = new Uri("/Assets/Tiles/IconicTileSmall.png", UriKind.Relative), WideContent1 = "WP Developer Day", WideContent2 = "use Windows Phone 8 features", WideContent3 = "on Windows Phone 7 apps" }.ToShellTileData();
  ShellTileExt.Create(new Uri(“/MainPage.xaml?Id=5”, UriKind.Relative), tile, true); } else { MessageBox.Show(“This is Windows Phone 7”); } }</pre>
```
                <strike>The code is placed inside a statement similar to the one that you’ver seen in the previous example: we’re going to execute the operation only if the app is running on a Windows Phone 8 device.</strike>
                    
                **UPDATE:** As Alessandro, a reader of this blog, correctly pointed me it’s better to use the **Utils.CanUseLiveTiles** property for this purpose: this way, the code will work also on a Windows Phone 7.8 device. Instead, it’s important to keep using the **Utils.IsWP8** property for launchers and choosers because they are available only in Windows Phone 8.
                    
                If you already had the chance to work with tiles the code will be familiar: we create a new **IconicTileData** object (which describes the iconic template) and we set up some properties that define the information displayed on the tile. I would like to highlight that we set also three properties (identified by the prefix **WideContent**), that are used only in case the user has chosen the wide size for the tile of our application.
                    
                The secondary tile is created using the **Create** method of the **ShellTileExt** class, that works exactly like the native **ShellTile** class but that is part of the **Mangopollo** library: in fact, it supports some parameters specific for the new tiles, like (other than deep link and the template) a Boolean which tell to the OS if the tile is going to support or not the wide tile.
                    
                Also in this case if we’re going to run the code on the Windows Phone 8 emulator everything will be just fine: the app will close and the new secondary tile will be created. If we play with the various tiles formats we can notice that, once we set the wide size, the information that we have specified in the **WideContent** properties will be displayed. Instead, if we run the code on the Windows Phone 7 emulator we’ll get the usual warning message.
                    
                [<img title="image" style="border-left-width: 0px; border-right-width: 0px; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://i2.wp.com/wp.qmatteoq.com/wp-content/uploads/2012/12/image_thumb4.png?resize=204%2C337" width="204" height="337"  data-recalc-dims="1" />](https://i2.wp.com/wp.qmatteoq.com/wp-content/uploads/2012/12/image4.png)
                    
                And what if we would like to update the tile? The task is very easy:
                    
                <pre class="brush: csharp;">private void OnUpdateTileClick(object sender, RoutedEventArgs e) { if (Utils.IsWP8) {
IconicTileData tile = new IconicTileData
                          {
                              WideContent1 = "This is the new content",
                              WideContent2 = "The tile has been updated",
                              WideContent3 = "with success"
                          };

Uri navigationUri = new Uri("/MainPage.xaml?Id=5", UriKind.Relative);
ShellTile.ActiveTiles.FirstOrDefault(x =&gt; x.NavigationUri == navigationUri).Update(tile); } }</pre>
                    
                We just define a new template using the **IconicTileData** class and we set only the properties that describe the information that we want to change compared with the original template definition (in the example, we change just the text displayed in the wide tile).
                    
                After that the update procedure is the same that we’ve learned to use in Windows Phone 7.5, since it’s indipendent from the template we use: we retrieve the tile to update by using the **ShellTile.ActiveTiles** collection according to the deep link uri, then we call the **Update** method, passing as parameter the updated template. Since this method accepts a **ShellTileData** object, which is the base class which every template inherits from, we can pass any supported template object, even the new ones that are defined in Mangopollo.
                    
                The code we’ve just seen to update a tile can be used also in a background agent, so that the tile can be periodically updated: the only thing you’ll have to remember, as I’ve anticipated in the introduction of this post, is to install the **Mangopollo.Light** package in your agent’s project.
                    
                ### In the end
                    
                In this post we have seen how to use some of the new Windows Phone 8 features in a Windows Phone 7 application, so that you’ll be able to improve the user experience for new device’s owners without forcing you to keep two different projects.
                    
                This is a very good solution, even if it has a lot of limitations compared to a real upgrade to a Windows Phone 8 project: the new features, in fact aren’t only some new launchers or the new tile sizes, but there are a lot more like NFC, Wallet, Lens Apps that are not available by using one of the strategies that are described in this post.
                    
                It’s up to you (and to your application’s category) to choose the best path!
                    
                <div id="scid:fb3a1972-4489-4e52-abe7-25a00bb07fdf:9b259a78-5371-46be-bbee-fd482a45bae9" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; padding-right: 0px">
                  <p>
                    <a href="http://wp.qmatteoq.com/wp-content/uploads/2012/12/Mangopollo6.zip" target="_blank">Here is a link to download a project sample.</a>
                  </p>
                </div>
```