Storing Data in Azure Mobile Apps

I have been creating a new Universal Windows App over the last few weeks. It’s simple and really to figure out concepts. I’ve already handled authentication against my Microsoft Account. Now it’s time to use that information to store data on a backend SQL Service. This comprises of two parts – the client piece and the server piece. I’m going to be using the latest azure-mobile-apps NodeJS SDK for the server and updating my C#.NET client on the frontend.

Let’s start with the backend. The azure-mobile-apps SDK makes this easy. I have the code in GitHub and have linked it into an Azure App Service via continuous deployment. The server.js file is simple enough:

var webApp = require('express')(),
    morgan = require('morgan'),
    mobileApp = require('azure-mobile-apps')();

webApp.use(morgan('combined'));

mobileApp.tables.import('./tables');

webApp.use(mobileApp);

webApp.listen(process.env.PORT || 3000);

This is a basic app model. I create an ExpressJS application, link in logging with Morgan and then do a standard import of the Azure Mobile Apps table definition. The table definition in tables/TaskItem.js is pretty basic:

var mobileApps = require('azure-mobile-apps');

var table = mobileApps.table();
table.columns = {
  'email': 'string',
  'text': 'string',
  'complete': 'boolean'
};
// We want the app to manage the schema
table.dynamicSchema = true;
// We want to authenticate the users
table.authorize = true;

table.read(function (ctx) {
  ctx.query.where({ email: ctx.user.id });
  return ctx.execute();
});

table.insert(function (ctx) {
  ctx.item.email = ctx.user.id;
  return ctx.execute();
});

module.exports = exports = table;

There is a couple of other things we want to take care of here. Firstly, the insert method fills in the email field in the database based on the authenticated user. Right now, this will be something like ‘sid:really-long-string-of-hex-digits’. We can adjust this later to be the email address (but that’s another blog post). Similarly, in the read method, we add a predicate to the query such that only records that match the authenticated user are returned. We now have a personal table – the user can submit and retrieve records from the table, but not see other records belonging to other people.

So much for the server code – it was really basic. Now, on to the client. The first thing I did was move the MobileServiceUser from a private variable in MainPage.xaml.cs to a basic property in TaskStore.cs. I also rewired the references accordingly. This was a fairly simple re-factor, so I’m not going to show it. Let’s take a look at the more extensive changes to TaskStore.cs:

    class TaskStore : ObservableCollection<TaskItem>
    {
        private IMobileServiceTable<TaskItem> tableController = App.MobileService.GetTable<TaskItem>();

        public TaskStore()
        {
            Add(new TaskItem { Id = Guid.NewGuid().ToString(), Title = "Task 1" });
            Add(new TaskItem { Id = Guid.NewGuid().ToString(), Title = "Task 2" });
            User = null;
        }

The important thing to note here is the tableController – this is the object that actually does the connection to the backend. You will need to add a reference to the NuGet package WindowsAzure.MobileServices here – ensure you add v2.0.0-beta-2 (which you should have done from the authentication setup). Once the table controller is set up, I can add async methods to read and write data to the backend:

        public async Task Create(TaskItem item)
        {
            item.Id = Guid.NewGuid().ToString();
            Add(item);
            if (User != null)
            {
                System.Diagnostics.Debug.WriteLine("Inserting item into remote table");
                await tableController.InsertAsync(item);
            }
        }

        public async Task Update(TaskItem item)
        {
            for (var idx = 0; idx < Count; idx++)
            {
                if (Items[idx].Id.Equals(item.Id))
                {
                    Items[idx] = item;
                }
            }
            if (User != null)
            {
                System.Diagnostics.Debug.WriteLine("Updating item in remote table");
                await tableController.UpdateAsync(item);
            }
        }

        public async Task Delete(TaskItem item)
        {
            Remove(item);
            if (User != null)
            {
                System.Diagnostics.Debug.WriteLine("Deleting item in remote table");
                await tableController.DeleteAsync(item);
            }
        }

        public async Task Refresh()
        {
            try
            {
                System.Diagnostics.Debug.WriteLine("Refreshing from the remote table");
                var items = await tableController.ToCollectionAsync();
                Clear();
                var e = items.GetEnumerator();
                while (e.MoveNext())
                {
                    Add(e.Current);
                }
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                System.Diagnostics.Debug.WriteLine(String.Format("Cannot read from remote table: {0}", ex.Message));
                await new MessageDialog(ex.Message, "Error loading itmes").ShowAsync();
            }
        }

In each of the cases – Create, Update and Delete – I firstly add the record to the local ObservableCollection then I call the appropriate Async method on the table controller to do the same thing. I only do this if I am logged in. I’ve also got a Refresh method that refreshes the data from the remote table. This is wired up in MainPage.xaml.cs as follows:

        private async void LoginSync_Clicked(object sender, RoutedEventArgs e)
        {
            if (store.User == null)
            {
                try
                {
                    store.User = await App.MobileService.LoginAsync(MobileServiceAuthenticationProvider.MicrosoftAccount);
                    System.Diagnostics.Debug.WriteLine(String.Format("User is logged in - username is {0}", store.User.UserId));
                    loginSyncButton.Label = "Sync";
                    // Refresh from the backend store
                    await filteredStore.Refresh();
                }
                catch (MobileServiceInvalidOperationException ex)
                {
                    System.Diagnostics.Debug.WriteLine(String.Format("Mobile Services Error: {0}", ex.Message));
                    store.User = null;
                    var dialog = new MessageDialog(ex.Message);
                    dialog.Commands.Add(new UICommand("OK"));
                    await dialog.ShowAsync();
                }
            }
            else
            {
                await filteredStore.Refresh();
                System.Diagnostics.Debug.WriteLine("MobileServices Sync");
            }
        }

Wait – I’m calling Refresh() on the filtered store, not the store. That’s because I’m passing through the Refresh() method from the FilteredStore to the Store:

        public async Task Refresh()
        {
            await _store.Refresh();
            RefreshView();
        }

This will refresh the store first, then refresh the view based on the store. That’s pretty much it – you can use the SQL Server 2014 Management Studio to connect to your SQL Service in the Azure cloud and check out the data that is stored.

This isn’t the end of this topic. I’ve noticed a few things along the way that need refactoring. Specifically, I should treat the view (FilteredTaskStore) and the store separately and connect them via event delegates. That way the view would update when the store updates. I wouldn’t have to wrap the FilteredTaskStore according to the store. Similarly, I kind of hate the UWP version of ObservableCollection – I’d like to get a generic version of this going that is based on something reasonable – maybe handle the manipulations all in LINQ. So I’ll continue working on this.

Next up is handling offline synchronization of data – because there are times when you aren’t on a network. Until then, there is always the GitHub Repository.