Diary of a Windows developer

SQL CE is one of the most appreciated features for developers introduced in Windows Phone 7.5: before SQL CE the only way to store data locally was serialization or remote services (for example, on the cloud). With SQL CE, instead, we have full access to a relational database and we can work with familiar concepts like tables, relations and so on. Windows Phone officially supports two ways to work with a SQL CE database:

• Code first: the database is created starting from the definition of our entities. We create a class for each entity and, with the help of some special attributes, the database is created when the application is launched for the first time. These attributes will help us to define the tables, the columns, the relations and so on. Since the database will be created when the application is launched, it will be obviously empty.
• Read only: the database is created out from the application, with an external tool (like the Server Explorer tool, that is part of Visual Studio). The file will be a SDF file, that will be included into the Visual Studio project. This solution is the best choice when we have a prepopulated database and we need to display the data in our application. The downside of this approach is that the database will be read only: we’ll be able only to read data from it, we won’t have write access.

In my experience I had often the need to have best of both worlds: I have a database that is prepopulated with data that is displayed in the application but, at the same time, I want the chance to write stuff. There’s a way to do it: copying the database in the isolated storage when the application is launched for the first time. This way, we can simply add the database to the project like if we want to use it in read only mode: after we’ve copied it into the storage, we will be able to write data into it like if it was created in code first mode.

The code to achieve this result is not really straightforward, so please welcome SQL Server Compact Toolbox: I’m a big fan of this extension, developed by the fellow MVP ErikEJ, and I’ve used it extensively in my Windows Phone project. It’s a Visual Studio extension, so it can be installed directly from the Visual Studio Gallery.

This extensions provides a series of utilities that come in handy when you work with SQL CE database: one of them is the Windows Phone support, that allows, starting from an existing SQL CE database, to generate all the classes needed to work with it in a Windows Phone application (both entities and data context).

And what about our scenario? The generated data context contains a method called CreateIfNotExists, that overrides the original data context methods that are used to check if the database already exists, and if not, to create it. Basically, this the the code that we were writing in the past:

using (CommunityDaysContext db = new CommunityDaysContext(CommunityDaysContext.ConnectionString))
{
    if (!db.DatabaseExists())
    {
        db.CreateDatabase();
    }
}

Using SQL Server Compact Toolbox we can simply write instead:

using (CommunityDaysContext db = new CommunityDaysContext(CommunityDaysContext.ConnectionString))
{
    db.CreateIfNotExists();
}

This method will return true or false to reflect if the database has been created or not. If you take a deeper look to the DataContext class generated by SQL Server Compact Toolbox you’ll notice that the CreateIfNotExists method is much more than simply a wrapper to the original DataContext methods: that’s because the method contains all the logic needed to check if there’s a SQL CE database embedded into the project and to copy it into the Isolated Storage.

To trick the magic we simply have to copy the SDF file into our project and make sure that the Build Action (that can be changed in the Properties panel) is set to  EmbeddedResource.

And now you’re done: simply call the CreateIfNotExists when the application is starting  (for example, in the Application_Launching event inside the App.xaml.cs file, or when the MainPage of your app is loaded) and the database will be copied into the isolated storage. To access to it you’ll have to use the regular connection string (since it’s not a read only database): the generated DataContext will automatically provide it with the ConnectionString static property.

Watch out the database size!

Be careful that the bigger is the database, the longer is the time needed to copy the database into the storage. Remember to provide a loading animation in case the loading operation is longer than 2 or 3 seconds and to postpone it after that the application is fully loaded, otherwise you’ll risk to exceed the 5 seconds time limit allowed to display the splash screen.

What if the database is stored in another project?

If, as a developer, you are used to split your solution in different projects (for example, one for the main application, one for the services, etc.) you may find yourself in the situation where the database is stored in one project, while the entities and the data context in another one.

In this case, the code generated by SQL Server Compact Toolbox won’t work, because the CreateIfNotExists method will look for the database inside the same assembly of the application (using reflection). Don’t worry, there’s a workaround! The first step is to change the Build Action of the database to Content, then you’ll have to change the CreateIfNotExists method in the following way:

public bool CreateIfNotExists()
{
    bool created = false;
    using (var db = new DbContext(DbContext.ConnectionString))
    {
        if (!db.DatabaseExists())
        {
            //string[] names = this.GetType().Assembly.GetManifestResourceNames();
            //string name = names.Where(n => n.EndsWith(FileName)).FirstOrDefault();
            StreamResourceInfo stream = Application.GetResourceStream(new Uri("Database/Recipes.sdf", UriKind.Relative));
            if (stream != null)
            {
                using (Stream resourceStream = stream.Stream)
                {
                    if (resourceStream != null)
                    {
                        using (IsolatedStorageFile myIsolatedStorage = IsolatedStorageFile.GetUserStoreForApplication())
                        {
                            using (IsolatedStorageFileStream fileStream = new IsolatedStorageFileStream(FileName, FileMode.Create, myIsolatedStorage))
                            {
                                using (BinaryWriter writer = new BinaryWriter(fileStream))
                                {
                                    long length = resourceStream.Length;
                                    byte[] buffer = new byte[32];
                                    int readCount = 0;
                                    using (BinaryReader reader = new BinaryReader(resourceStream))
                                    {
                                        // read file in chunks in order to reduce memory consumption and increase performance
                                        while (readCount < length)
                                        {
                                            int actual = reader.Read(buffer, 0, buffer.Length);
                                            readCount += actual;
                                            writer.Write(buffer, 0, actual);
                                        }
                                    }
                                }
                            }
                        }
                        created = true;
                    }
                    else
                    {
                        db.CreateDatabase();
                        created = true;
                    }
                }
            }
            else
            {
                db.CreateDatabase();
                created = true;
            }
        }
    }
    return created;
}

In this code we’ve changed the loading by reflection with the Application.GetResourceStream method, that is able to load a resource inside the XAP (so it works even if the database is not in the same project) as a Stream. This method requires, as input parameter, the Uri of the file (starting from the root of the project): in the example, the database is called Recipes.sdf and is placed inside the Database folder.

That’s all! Have fun with SQL CE and Windows Phone!

The APIs provided by Windows Phone to create secondary tiles or to update the main tile are very simple to use, but not very flexible: you can set a title, a background, a counter, etc., but you can’t use a custom layout (even simple scenarios, like breaking the title in two lines, aren’t supported).

One way to override this limit has been suggested directly by Microsoft itself in this post: the idea is to create a custom XAML control and to “capture a screenshot” of the control itself, that can be used as background of the tile.

The benefit of using this approach is that we have total control on the tile layout: since it’s a standard user control, we can add all the XAML controls we want and, at runtime, customize them. For example, we can insert a TextBox control and change the text simply by changing the Text property.

The downside is that the code needed to achieve this result is not really straightforward: we have to manually convert the user control into an image and save it to the storage.

For this reason, please welcome the Ree7 Tile Toolkit for Windows Phone 7, an open source library (available on NuGet or on Codeplex) that does all the dirty work for us: the approach is the same described before, but the toolkit will take care of converting our XAML user control into an image automatically. The basic idea behind this toolkit is that it’s able to convert any object that inherits from the UIElement class (basically, any visual control in the Silverlight for Windows Phone Runtime).

Let’s start to take a look to one of the most important features of the toolkit: the templates support.

Templates

A template defines the layout of a tile: the toolkit support two kinds of templates.

• A standard template, that is built in into the library, that can be used to create tiles with the same look & feel of the Messages hub or the Mail hub: a title, an image and, on the right, a counter (in the Messages hub, for example, it’s used to display the number of unread messages).
• A custom template: as explained before, any object that inherits from the UIElement class can be used as a template.

During this post we’ll see how to use the standard template: in the next one we’ll cover how to use the custom one.

The standard template

The standard template provided by the toolkit supports four different customizations: the title, the background image, an icon and the counter. You can see an example in the following image.

Here is how to create this custom tile by code (obviously, you should already have added the library to your project, NuGet is the easiest and best way to do it)

public void Create()
{
    NativeCountTileTemplate template = new NativeCountTileTemplate
        {
            AppName = "Sample app",
            Count = "10",
            Icon = new BitmapImage(new Uri("/SampleTileIcon.png", UriKind.Relative)),
            Background = null
        };

    CustomTile tile = CustomTileFactory.GetTemplatedTile("SampleTile", template, TileSize.Standard);
    ShellTile.Create(new Uri("/MainPage.xaml?ID=1", UriKind.Relative), tile.GetShellTileData());
}

The standard template is identified by the class NativeCountTileTemplate, that can be customized using the four properties AppName, Count, Icon and Background. Using these properties is really straightforward: just keep in mind that the Background property’s type is Brush, so we can set it using any of the Brush objects available in Silverlight (like SolidColorBrush if we want to use a simple color, LinearGradientBrush or RadialGradientBrush if we want to use a gradient and ImageBrush if we want to use an image). If we set this property to null (like in the example) the background will be transparent: this means that the background color will be the same of the phone accent color.

The next step is to create a CustomTile object using the CustomTileFactory class: the method GetTemplatedTile requires three parameters, that are the name of the tile (be aware that this name is used for the filename of the image generated by the toolkit, so if you want to create multiple custom tiles use different names), the template to use (in the example, the NativeCountTileTemplate object) and the tile size (that is a value of the enumerator TileSize; actually the only supported value is TileSize.Standard, but it’s been introduced to support future scenarios).

In the end, we create the tile in the usual way: we call the Create method of the ShellTile class passing the deep link uri and the data tile (that can be retrieved by calling the GetShellTileData method on the CustomTile object).

Using a custom template

The first step to use a custom template is to create a XAML user control: to do this let’s add to our project a new item which type is Windows Phone User Control.  What’s a user control? It’s a XAML based piece of code that works exactly like a Windows Phone page, except that it can be reused in multiple pages like a simple Silverlight control. The user control has its own layout (defined in the XAML) and logic (defined in the code behind).

For our needs, the custom control won’t have any logic: it will manage just the layout of the tile. Here is an example of an empty layout, where we simply set the Grid size to 173×173, which is the standard tile size.

<UserControl x:Class="TileHelper.Tile"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    d:DesignHeight="173" d:DesignWidth="173">

    <Grid x:Name="LayoutRoot" Width="173" Height="173">
        <StackPanel Margin="5, 0, 0, 0">
            <TextBlock Text="This is a text" />
            <TextBlock Text="This is another text" />
        </StackPanel>
    </Grid>
</UserControl>

Inside the Grid we can add all the needed control to create our custom layout. When we are ready, we can create the tile in the code using the same APIs we’ve used before. The difference is that, this time, we won’t use as template the NativeCountTileTemplate object, but the custom control we’ve just created. This custom control will be passed as parameter of the CustomTemplateFactory.GetTemplateTile method, like in the following example:

public void Create()
{
    Tile customTemplate = new Tile();

    CustomTile tile = CustomTileFactory.GetTemplatedTile("SampleTile", customTemplate, TileSize.Standard);
    ShellTile.Create(new Uri("/MainPage.xaml?ID=1", UriKind.Relative), tile.GetShellTileData());
}

Tile is the name of the class of the user control we’ve created: we simply create a new user control instance and we pass it to create the CustomTile object. This is an example of the final result:

In this example we’ve created a custom template, but the content is fixed: the value of the the two TextBox controls is always the same. In a usual application, instead, the layout of the tile can be different according to the scenario: for example, if we need to create multiple tiles, some properties (like the title) will be different.

Achieving this goal is really simple: we simply have to give a name (setting the x:Name property) to the controls that we need to modify and, in the code, we use the FindName method (exposed by every UIElement object) that simply returns a reference to a control according to its name. This way, we can change the control properties directly in the code, like in the following example:

public void Create()
{
    Tile customTemplate = new Tile();
    TextBlock first = customTemplate.FindName("txtFirst") as TextBlock;
    TextBlock second = customTemplate.FindName("txtSecond") as TextBlock;

    first.Text = "Hello";
    second.Text = "World";

    CustomTile tile = CustomTileFactory.GetTemplatedTile("SampleTile", customTemplate, TileSize.Standard);
    ShellTile.Create(new Uri("/MainPage.xaml?ID=1", UriKind.Relative), tile.GetShellTileData());
}

After creating a new instance of the custom control, we look for a reference to the two TextBlock controls: once we have it, we simply change the Text property. The next steps are the same described before, nothing changes: we create a new CustomTile object using the custom control instance as template. Here is an example of the final result:

Other alternatives

There are other alternatives to achieve the same result: for example, the Telerik controls suite features a control that behaves exactly in the same way. The biggest advantage of the Ree7 toolkit is that is free and open source, so you can change it to adapt to your needs and you can use it in your projects free of charge.