Tim Gabrhel

Upgrading your Windows Phone SQL CE Apps Part 2

This is Part 2 in a series on Upgrading Windows Phone SQL CE apps. You can find Part 1 here.

Part 1 of this series ended where we have a Web Api that accepts a SQL CE Database file, and a unit test project to demonstrate submitting and the json data returned from the service. This post will demonstrate how to process the received data from the Web API in a Universal app.

Please note that all code provided below uses the applications LocalFolder for data storage, and does not exercise potential data syncing across Windows Phone and Windows apps via the RoamingFolder.

Universal App

Start out by creating a new Universal app.

MigrateSqlCePt2-01

You should now have a new Windows, Windows Phone, and Shared projects added to the solution.

Let’s get started by

  • Move MainPage.xaml into the Shared project by clicking and dragging it in.
  • Delete MainPage.xaml from both Windows and Windows Phone projects, so we use the same MainPage.xaml across both
  • Add a page to the Shared project named MigrateDbPage.xaml. This page will serve as page to handle running the data migration on the first run of the new application.

Detecting first run of Universal

Keep in mind that the scenario we are playing out is that an existing WP7/8 app was upgraded to a Universal application. This means, that our database already exists within the application. We need to keep track whether or not our Universal app has run the data migration.

Start out by creating a new static class called DbHelper.cs, and add a public static string called DatabaseName with the name of your new database file.

public static class DbHelper
{
    public static string DatabaseName = "checkbook.db";
}

Next add an async method named DoesDbExist() to check whether or not the SQLite database exists. There is no method to check if a file exists, so we must try to get & handle exceptions as necessary.

public static async Task<bool> DoesDbExist()
{
    bool dbexist = true;
    try
    {
        var storageFile = await ApplicationData.Current.LocalFolder.GetFileAsync(DatabaseName);
    }
    catch
    {
        dbexist = false;
    }

    return dbexist;
}

Head back to your App.xaml.cs and mark the OnLaunched override method as async.

protected async override void OnLaunched(LaunchActivatedEventArgs e)

Now locate a space at approximately line 95, just before if(!rootFrame.Navigate(typeof(MainPage), e.Arguments)). Add in call to our database helper to see if the database was created.

// check to see if the new SQLite database was created
var dbExists = await DbHelper.DoesDbExist();

And create a Type variable to determine which page to navigate to, depending on if the database exists yet.

// if the database wasn't created, navigate to the migration page.
Type navigatePageType = (dbExists == false) ? typeof(MigrateDbPage) : typeof(MainPage);

Now update the navigate method to use our navigatePageType variable for navigation instead.

if (!rootFrame.Navigate(navigatePageType, e.Arguments))

Simulate the upgrade

At the point in the app process, we’ll have detected this is a new run of the app, and get navigated to MigrateDbPage.xaml. In order to test, we need the database file to exist as if it was already there during an upgrade.

Start out by adding a new folder called SqlCe into your Shared project, then add your SQL CE database file into it.

Next, to override the pages OnNavigatedTo method, paste in the code below. This will locate the SQL CE database file, and write it to our LocalFolder, as if the application was running in an upgrade.

protected async override void OnNavigatedTo(NavigationEventArgs e)
{
    /*
        * In order to simulate the upgrade scenario, we will read the SqlCe database packaged in the solution,
        * and write it to the location where the database will be during an upgrade
        */

    // get the install directory, locate the SqlCe folder, then get our folder & SDF file

    var installFolder = Windows.ApplicationModel.Package.Current.InstalledLocation;
    var subFolder = await installFolder.GetFolderAsync("SqlCe");
    var sdfFile = await subFolder.GetFileAsync("Checkbook.sdf");

    // convert the database to a byte array so we can write it into our LocalFolder
    var sdfStream = await sdfFile.OpenStreamForReadAsync();
    var sdfBytes = new byte[sdfStream.Length];
    await sdfStream.ReadAsync(sdfBytes, 0, sdfBytes.Length);

    // create the local storage file
    var localFile = await ApplicationData.Current.LocalFolder.CreateFileAsync("Checkbook.sdf");

    // write the content
    await Windows.Storage.FileIO.WriteBytesAsync(localFile, sdfBytes);
}

Prep for migration

In order to actually be able to submit the data and do something with it, we’ll need to do a bit of setup.

SQLite

There is an existing guide on setting up SQLite on Codeproject. Please follow this guide to set up SQLite for both Windows and Windows Phone. You cannot continue without this completed.

Download a copy of the source at this point if you’d like for a starting point. Be sure to download and install the Windows and Windows Phone SQLite extensions as well.

Nuget

Start by installing the following nuget packages to both Windows and Windows Phone projects:
– Json.NET (Newtonsoft.Json)
– sqlite-net (sqlite-net)

After sqlite-net installs, you’ll find two new SQLite.cs and SQLiteAsync.cs files in each project. Simply this by created a new folder in your Shared project named SQLite, drag one copy of these files into this folder, and delete them from the Windows and Windows Phone projects. This way we have 1 copy of the files to worry about.

Models

Create a new folder named Models in your Shared project, and define a class for each type that was in your DataContext from Part 1. I opted to create a separate file for each model.

It’s important that you decorate each class with the Table attribute and specify the name of the table. You can use PrimaryKey/AutoIncrememt where appropriate for your scenario.

You’ll want to use Ignore on any properties defined from your DataContext object that contained EntitySet or EntityReference‘s. These of course aren’t saved into the database so we need to tell SQLite to ignore them.

[Table("Accounts")]
public class Account : BindableBase
{
    [PrimaryKey, AutoIncrement]
    public int AccountId { get; set; }
    public string AccountName { get; set; }
    public int AccountType { get; set; }
    public float AccountBalance { get; set; }
    public float AccountBalanceCleared { get; set; }
    public bool? Active { get; set; }
    public int GroupId { get; set; }
    public int? SortId { get; set; }

    [Ignore]
    public List<Transaction> Transactions { get; set; }

    [Ignore]
    public Group Group { get; set; }
}

Next, create a new class within called DbMigrationResult.cs. This will be the container object that we transform our json into. Therefore, you’ll need to add instances of your models that matches the data structure of your data context. For example, here’s my `DbMigrationResult':

public class DbMigrationResult
{
    public List<Account> Accounts { get; set; }

    public List<Category> Categories { get; set; }

    public List<Group> Groups { get; set; }

    public List<Payee> Payees { get; set; }

    public List<Transaction> Transactions { get; set; }

    public List<Transfer> Transfers { get; set; }

    public List<Image> Images { get; set; } 
}

Compare this to the structure of my data context, wherein I instead had Table‘s, which are translate to a List<T> in this case:

public Table<Account> Accounts;
public Table<Category> Categories;
public Table<Group> Groups;
public Table<Images> Images;
public Table<Payee> Payees;
public Table<Transaction> Transactions;
public Table<Transfer> Transfers;

Now that we have our models defined, add a new static method called CreateDatabase() to the DbHelper.

public static async Task CreateDatabase()
{
    var conn = new SQLiteAsyncConnection(DatabaseName);
    await conn.CreateTableAsync<Group>();
    await conn.CreateTableAsync<Account>();
    await conn.CreateTableAsync<Payee>();
    await conn.CreateTableAsync<Category>();
    await conn.CreateTableAsync<Transaction>();
    await conn.CreateTableAsync<Image>();
}

Query the service

Within the MigrateDbPage, we’ll create a new method called ExtractJsonFromSdf. This method will read our local database file, and run an HTTP Request to get the JSON data back from the service.

I have published the Web API project in this solution to a free Azure web site instance. Feel free to run this solution to test against that.

public async static Task<string> ExtractJsonFromSdf(string databaseFileName)
{
    using (var client = new HttpClient())
    {
        using (var content = new MultipartFormDataContent())
        {
            StorageFile sdf;
            try
            {
                // local folder is the replacement for isolatedstorage
                sdf = await ApplicationData.Current.LocalFolder.GetFileAsync(databaseFileName);
            }
            catch (Exception)
            {
                throw new Exception("Database file " + databaseFileName + " could not be found.");
            }

            var sdfStream = await sdf.OpenStreamForReadAsync();
            var fileContent = new StreamContent(sdfStream);
            fileContent.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = databaseFileName
            };
            content.Add(fileContent);

            var requestUri = "http://sqlcemigration.azurewebsites.net/api/sqlcemigration";
            var result = await client.PostAsync(requestUri, content);

            if (result.IsSuccessStatusCode)
            {
                return await result.Content.ReadAsStringAsync();
            }
            else
            {
                return result.StatusCode.ToString();
            }
        }
    }
}

Running the upgrade

Add a Button to MigrateDbPage.xaml and create a new event handler for the Clicked event. This is a good location to present upgrade information to your user, show progress, etc.

<Button Content="Migrate SQLCE Database" Click="MigrateDatabase_Click" />

In the pages code behind, we’ll run the following steps:
– Call the Web API to get json data back
– Deserialize the json into an instance of our DbMigrationResult.
– Create the new database file
– Create a new database connection, and insert our migrated data

private async void MigrateDatabase_Click(object sender, RoutedEventArgs e)
{
    MessageDialog msg = null;
    try
    {
        // call our migration helper and get json back from the database
        var json = await ExtractJsonFromSdf("Checkbook.sdf");
        var jsonClean = JsonConvert.DeserializeObject(json);

        // deserialize the string into our model
        var result = JsonConvert.DeserializeObject<DbMigrationResult>(jsonClean.ToString());

        // create sqlite database. 
        // http://www.codeproject.com/Articles/826602/Using-SQLite-as-local-database-with-Universal-Apps
        await DbHelper.CreateDatabase();

        // add data into sqlite
        var conn = new SQLiteAsyncConnection(DbHelper.DatabaseName);
        await conn.RunInTransactionAsync(delegate(SQLiteConnection connection)
        {
            connection.InsertAll(result.Groups);
            connection.InsertAll(result.Accounts);
            connection.InsertAll(result.Categories);
            connection.InsertAll(result.Payees);
            connection.InsertAll(result.Transactions);
            connection.InsertAll(result.Transfers);
            connection.InsertAll(result.Images);
        });

        msg = new MessageDialog(result.ToString(), "Success");
    }
    catch (Exception ex)
    {
        msg = new MessageDialog(ex.StackTrace, ex.Message);
    }

    await msg.ShowAsync();
}

All systems go

If you re-run a fresh instance of the app, the code we started with in App.xaml.cs will now detect that our new database file exists, and will navigate us to MainPage.xaml. Check out the source on Github which reads some of the SQLite data and writes it out.

MigrateSqlCePt2-02

From this point, your new upgraded app should no-longer use MigrateDbPage.xaml and can function as a brand new app with existing data!

DOWNLOAD SOURCE

Hope this helps! Go build something awesome.

Upgrading your Windows Phone SQL CE Apps

Back in WP7/WP8, they supported SQL CE 3.5 with the use of LinqToSql on top. A very powerful tool. Universal/WinRT apps are here, and we know they’re going to get significantly better with Windows 10. With Universal apps, the life of SQL CE (Compact Edition) databases are near the end, that is, if you’d like to continue making updates & using the latest developer tools. Now is the time to upgrade from these platforms, before it’s too late.

If you’re upgrading an existing SQL CE app to a Universal app, you have two choices: 1) retain user data or 2) wipe it away and start over. Obviously we’d like to retain the users data so they can have a smooth transition to the updated data store.

ASP.NET 4.0/4.5

As of ASP.NET 4.0, you’ve been able to target SQL CE databases in your ASP.NET web applications. With the power of Azure and Web Api, that’s exactly what we’re going to do.

Project

Let’s start by creating a new ASP.NET MVC 4 Web Application with .NET Framework of 4.5.3. An alternative scenario is adding this project into your existing Windows Phone solution.

Continue reading

Auto Aligning Popups on WinRT Apps

Popup‘s in WinRT apps are really powerful to pop up content to prompt a user for some form of input. They are generally pretty straight forward to work with. They could be full screen, pinned to a corner, or you could open them relative to a button (or other element) the user interacts with. What happens when that button is near the edge of the screen, and the pop up opens off screen? Shortly, I’ll introduce the AutoAlignedPopup, but first, let’s start with the basics.

Popup

A Popup is a very basic element. It’s essentialy a container object that has properties for user interaction, and contains a child UIElement.

Continue reading

Flyout on Windows Phone 8.1 Runtime

Windows Runtime Flyout on Windows Phone is extremely versatile.The flyout control is a simple control you can attach to any FrameworkElement, or define as a resource and open it programatically.

Placement

A flyout has five placement modes, Top, Bottom, Left, Right, and Full. However, when working on the phone, only Top (default) and Full will work. This means, either you get a full screen, or pinned to the top. Definitely not ideal when trying to create a menu related to an app bar button. This comes straight from MSDN.

On Windows Phone, a Flyout is shown at the top of the screen by default. You can change the Placement property to FlyoutPlacementMode.Full to make the flyout cover the full screen. The Top, Bottom, Left, and Right values don’t have any effect in Windows Phone apps.

There are numerous ways to interact with a flyout, be it declarative, programmatic, or both.

Continue reading

Uniform grid layouts in WP8.1 Runtime Apps

With the advent of multiple device resolutions on Windows Phone, the challenge to provide a consistent experience across devices and resolutions increases. One of these specific challenges is providing a consistent grid layout, a concept very familiar to Windows Phone. As seen in the screenshot below, it’s not straight forward to fully utilize the full dimensions of the screen, while providing a nicely spaced grid.

Lead image credit goes to MVP Martin Suchan on Twitter

Continue reading

Windows Phone 8.1 WinRT Context Menus

If you search for Windows Phone 8.1 Context Menu, you’ll likely be riddled with Silverlight solutions, just as I did. Luckily, since Windows Phone 8.1 WinRT shares many components of Windows 8, using a context menu on your list data is quite simple, provided you know how to search for it. I was lucky to stumble upon the Menu Flyout so I’m here to share my finding.

WinRT MenuFlyout

MSDN has a great example on Menu Flyouts. A menu flyout in Windows 8 is a simple popup menu relative to the control the user is interacting with. On Windows Phone 8.1, this MenuFlyout control will be displayed as a full width popup, mimicking the context menu known to Windows Phone. Head over to MSDN to see the example.

Hope this helps.

Copyright © 2014 Tim Gabrhel

Theme by Anders NorenUp ↑