Andy WigleyMicrosoft, Senior Developer Evangelist

t: @andy_wigley b: andywigley.com

How do I handle data in a Windows 10 UWP app?

AgendaTopics: Data Storage in UWPXML or JSON serializationSQLiteEntity Framework 7Azure App Service Mobile Apps

Data Storage in UWP

Locations where apps can access data

App

App Package Folder

r/o

App data Folders

r/wApp data FoldersApp data

Folders

LocalRoaming

Temp

Removable

Storage (SD Card)

r/w

Known Folders

PicturesVideosMusic- Direct access needs manifest capabilities

r/w

Cloud

r/w

File Syste

m

File Open/Save Picker

APIsr/w

Credential Locker

r/w B/ground Transfer

Publishers Shared Folder

r/w

AppAppPicker Provider apps

Directly Accessible R/W Data Storage

RoamingFolder Setting

s

• Other devices can access what you put in here

• Data roamed cross-device

• Limited to 100kb per application

• Held in OneDrive storage

LocalFolder Setting

s

• Store local data here for use by your application

• Can store data up to the limit of the storage on the device

• Retained if the application is updated

TempFolde

r

• Use for temporary storage

• No guarantee it will still be here next time your program runs

• Cleaned up in a low storage condition

Windows.Storage.ApplicationData Windows.Security.

CredentialsPasswordVault

Credentials

• Credential Locker

• Use for secure storage of PasswordCred-ential objects

• Data roamed cross-device

Publisher CacheFolder

• Shared storage for apps from same publisher

• Declare in app manifest

Serializing POCO to files

Data serialization using Windows.Data.Json; .. public string Stringify() {     JsonArray jsonArray = new JsonArray();     foreach (School school in Education)     {         jsonArray.Add(school.ToJsonObject());     }

    JsonObject jsonObject = new JsonObject();     jsonObject["id"] = JsonValue.CreateStringValue(Id);     // Treating a blank string as null     if (String.IsNullOrEmpty(Phone))         jsonObject["phone"] = JsonValue.CreateNullValue();     else         jsonObject["phone"] = JsonValue.CreateStringValue(Phone);

    jsonObject["name"] = JsonValue.CreateStringValue(Name);     jsonObject["education"] = jsonArray;     return jsonObject.Stringify(); }

Data deserialization using Windows.Data.Json; ..

JsonObject jsonObject = JsonObject.Parse(jsonString);

Id = jsonObject.GetNamedString("id", "");

IJsonValue phoneJsonValue = jsonObject.GetNamedValue("phone"); if (phoneJsonValue.ValueType == JsonValueType.Null) {     Phone = null; } else {     Phone = phoneJsonValue.GetString(); }

Name = jsonObject.GetNamedString("name", "");

Local SettingsWindows.Storage.ApplicationDataContainer localSettings = Windows.Storage.ApplicationData.Current.LocalSettings;// Create a simple settinglocalSettings.Values["exampleSetting"] = "Hello Windows";

// Read data from a simple settingif (localSettings.ContainsKey("exampleSetting")){ // Access data in value string data = localSettings.Values[ "exampleSetting" ].ToString();}

// Delete a simple settinglocalSettings.Values.Remove("exampleSetting");

// Composite setting Windows.Storage.ApplicationDataCompositeValue composite = new Windows.Storage.ApplicationDataCompositeValue(); composite["intVal"] = 1; composite["strVal"] = "string"; localSettings.Values["exampleCompositeSetting"] = composite;

Roaming Settings and Roaming FolderRoaming data enables an application to synchronise data and/or settings across different devicesSynced across all the users’ devices where the same app is installed

RoamingFolder and RoamingSettings are synced through the cloudLimited to max 100KB for apps acquired from the consumer store

SQLite

Why SQLite?Worlds’ most popular databaseWidely used on iOS, Android, Python, Mono etc…Public DomainSmall, Fast, Reliable

Rich FeaturesEmbedded SQL in-process database engineRead/writes to ordinary disk filesSupports multiple tables, indices, triggers and viewsCross-platform – freely copy database files between 32-bit and 64-bit, or little endian and big endian

ReliableReputation for being very reliableLarge automated test suiteAll transactions are ACID even if interrupted by system crashes or power failures

SQLite.orgDocumentation

SQL Syntax

C/C++ API Reference

Source and toolsdownload

SQLite now included in the UWP SDKSince Windows 10 v1511/SDK Build 10586, native code can reference SQLite directly from SDKTo reference the SDK SQLite:• include the following header in your project:

#include <winsqlite/winsqlite3.h> • Configure the project to link to winsqlite3.lib (right-click project > Properties > Linker >

Input > add winsqlite3.lib to Additional Dependencies)

Benefits:• reduce the size of your application package• rely on the platform to update the library periodically• Using the SDK SQLite might also lead to performance advantages such as faster launch

times

Managed code SQLite usageRely on WinRT component wrappers, or C# p/invoke codeOn NuGet:• SQLitePCL• SQLiteWinRT• SQLite-NET *doesn’t work currently!*

These need sqlite3.dll to be referenced by your projectSQLite for Universal App Platform Visual Studio Extension (.vsix)Install from Visual StudioTools – Extensions and Updates…

Choice of .NET APIsSQLite-NETLINQ syntaxLightweight ORM

SQLitePCL or SQLite-WinRTSQL statementsThin wrapper around the SQLite C API using (var conn = new SQLiteConnection("demo.db")){      Customer customer = null;

using (var statement = conn.Prepare( "SELECT Id, Name FROM Customer WHERE Id = ?"))

{ statement.Bind(1, customerId); if (SQLiteResult.DONE == statement.Step()) {

customer = new Customer() { Id = (long)statement[0], Name = (string)statement[1] }; } } }

var db =  new SQLite.SQLiteAsyncConnection(App.DBPath);

var _customer = await  (from c in db.Table<Customer>() where c.Id == customerId  select c).FirstOrDefaultAsync();

if (customer != null) {      var Id = _customer.Id;      var Name = _customer.Name;     }

…and others!

Create databaseprivate void LoadDatabase()        {              // Set the path to the SQLite database              var DBPath = Path.Combine( Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");     // Initialize the database if necessary              using (var db = new SQLite.SQLiteConnection(DBPath,  SQLite.SQLiteOpenFlags.ReadWrite | SQLite.SQLiteOpenFlags.Create)) {                  // Create the tables if they don't exist                  db.CreateTable<Customer>();                  db.CreateTable<Project>();              }         }

SQLite-NET

Define entity objectspublic class Customer     {    [PrimaryKey, AutoIncrement]          public int Id { get; set; }          public string Name { get; set; }          public string City { get; set; }          public string Contact { get; set; }     }

SQLite-NET

Create database and tablesprivate void LoadDatabase()        {              // Specify database location    var db = new SQLiteWinRT.Database(ApplicationData.Current.LocalFolder, "sqlitedemo.db"); try  {          // Open a connection to the SQLite database – creates it if it does not exist    await db.OpenAsync();

   string sql = @"CREATE TABLE IF NOT EXISTS Customer                    (Id      INTEGER PRIMARY KEY AUTOINCREMENT,             Name    VARCHAR( 140 ),                     City    VARCHAR( 140 ),                     Contact VARCHAR( 140 ) );";      await db.ExecuteStatementAsync(sql);    }        catch (Exception ex) {          var result = SQLiteWinRT.Database.GetSqliteErrorCode(ex.HResult);      throw new ApplicationException("Database create failed with error " + result);        }}

SQLite-WinRT

Tip: SQLite-NetThe SQLite-Net NuGet package does not work in UWPNuGet v3 does not allow a package to add additional content files to your projectSQLite-Net actually consists of two C# source files , SQLite.cs and SQLiteAsync.cs that use p/invoke to call functions exported from sqlite3.dll – NuGet V2 used to add these files!

Solution:1. Reference SQLite for Universal App Platform

extension SDK (as normal)2. Copy SQLite.cs and SQLiteAsync.cs from a

Windows 8.1 project

Demo:SQLite

A Developer's Guide to Windows 10: SQLite Local Databasehttps://channel9.msdn.com/Series/A-Developers-Guide-to-Windows-10/10

Further Information

Entity Framework

Entity Framework 7New PlatformsFull .NET FrameworkASP.NET 5Windows 10 UWPMacLinux

New Data Stores: Relational & non-relationalNot a magic abstraction that hides underlying data storeHigh level services that are useful on all/most storesNon-common concerns handled by provider extensions

Example providersRelational (SQL Server, SQLite, Postgres, etc.)Azure Table StorageRedisIn Memory (for testing)

SQLite is currently the only supported provider for UWP

Lightweight and extensible version of Entity Framework

“Code First” data model public class BloggingContext : DbContext { public DbSet<Blog> Blogs { get; set; } public DbSet<Post> Posts { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlite($"Filename=Blogging.db"); }

protected override void OnModelCreating(ModelBuilder modelBuilder) { // Make Blog.Url required modelBuilder.Entity<Blog>() .Property(b => b.Url) .IsRequired(); } }

Define entities public class Blog { public int BlogId { get; set; } public string Url { get; set; }

public List<Post> Posts { get; set; } }

public class Post { public int PostId { get; set; } public string Title { get; set; } public string Content { get; set; }

public int BlogId { get; set; } public Blog Blog { get; set; } }

Demo:EF7

Azure Data Storage

Mobile Experiences - not just mobile devices User is the center of the experience, not the device.

Available on the right device at the right time

Input model optimized for the experience.

Enabling Mobile Experiences with Universal Apps

The Experience you want on the device you want

User

Azure App Service Mobile AppsUWP

UWP

Mobile Apps Dynamic SchemaVery easy to configure backend data tables!Create new table in backend storeDefine data objects and a Database Initializer functionTable auto-configured on first client request with columns, plus sync tracking columnsDuring dev, schema changes cause table to be dropped and re-createdOr use Entity Framework data migrations

Adding offline sync to an app is usually hard.

With Azure Mobile App, it’s easy.

Why use mobile offline sync?Improve app responsiveness by caching server data locally on the deviceMake apps resilient against intermittent network connectivity Allow end-users to create and modify data even when there is no network accessSync data across multiple devices Detect and handle conflicts when the same record is modified by more than one client

Handle client sync conflictsClientConflict resolution

ServerConflict resolution

Summary: Azure Mobile App offline syncSupports both “primarily online” and “occasionally connected” scenariosExplicit push and pull leaves control to the developer

Works with a variety of server data storesSQL, Azure Tables, Mongo, Dynamics CRM, Salesforce, etc.

Cross-platform client SDKsWindows Universal, Xamarin, iOS, Android

Flexible and powerfulSupports custom local storage layersDetect and handle conflicts on server or client

Demo:Azure Backend

SummaryXML/JSON serialized data in fileSimple. Use if you don’t need more complex solution

SQLite using wrapper that supports SQL statementsUse if you are porting old code that uses existing proven SQL queriesOr if you just prefer working with SQL!

SQLite using SQLite-NetLightweight ORMLINQ to SQL –like syntax

Entity Framework 7Lightweight ORMShare data access code across UWP and ASP.NET, .NET, Xamarin….

Azure App Service Mobile AppsEasy to use Mobile BAASCross-platform client supportSupports offline and data synchronization

© 2016 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.