offline data sync presenta con sqlite in universal …...agenda •sqlite uwp what is/isn’tsqlite...

presenta

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 - #wpc15it 1

WI005 - Offline data synccon SQLite in Universal Windows Platform

Erica BaroneMicrosoft Technical Evangelist

@_ericabarone

erbarone@microsoft.com

Massimo Bonanni

Microsoft MVP, Intel Black Belt

Intel Software Innovator

@massimobonanni

massimo.bonanni@tiscali.it

Agenda

• SQLite UWPWhat is/isn’t SQLite

SQLite for UWP

.NET APIs SQLite.NET-PCL vs SQLitePCL

Tools

• Offline Data syncMobile App

SyncAsync

PCL

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 2

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 - #wpc15it 3

• Open source RDBMS.

• Works as library instead of service (in-process)

• Single file database. Triggers

Tables

Indices

Views

• Cross Platform database (Mac, Windows, Linux).

• Cross Technology Database (WPF, UWP, Win Form).

• Implements most of the SQL standard (SQL92). RIGHT and FULL OUTER JOIN

Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE

VIEWs in SQLite are read-only.

• Zero-configuration

What is SQLite?

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 4

• Not a full database application No forms

No reports

No saved queries

What isn’t SQLite?

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 5

• Contains an extension SDK and all other components needed to use SQLite for UAP application development with Visual Studio 2015.

• Visual Studio Extension (.vsix) Install from Visual Studio (Tools – Extensions and Updates…)

Or download from SQLite.org

SQLite for Universal App Platform

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 6

SQLite.NET-PCLLINQ syntax

Lightweight ORM (no navigation properties)

.NET APIs

SQLitePCLSQL statements (ADO Style)

Thin wrapper around the SQLite C APIFrom Microsoft Open Technologies

var conn = new SQLiteConnection(new Platform.WinRT.SQLitePlatformWinRT(), dbName);conn.CreateTable<Libro>();

var conn = new SQLiteConnection(dbName);sql = @"CREATE TABLE IF NOT EXISTS [Libri](

[Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [Titolo] VARCHAR(100) NOT NULL,[Abstract] VARCHAR(255), [ISBN] CHAR(13), [Pagine] SMALLINT, [Copertina] IMAGE, [IdAutore] INTEGER NOT NULL, CONSTRAINT [Autiri_Libri] FOREIGN KEY([IdAutore]) REFERENCES Autori([Id]));";

using (var statement = conn.Prepare(sql)){

statement.Step();}

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 7

Create a database

SQLite.NET-PCL

SQLitePCL

[Table("Libri")]public class Libro : ILibro{

[PrimaryKey, AutoIncrement]public long Id { get; set; }[MaxLength(100)]public string Titolo { get; set; }[MaxLength(255)]public string Abstract { get; set; }[MaxLength(13)]public string ISBN { get; set; }public long Pagine { get; set; }public byte[] Copertina { get; set; }public long IdAutore { get; set; }public IAutore Autore { get; set; }public ICollection<IRecensione> Recensioni { get; set; }

}

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 8

Query

SQLite.NET-PCL

SQLitePCL

var libri = DB.Table<Libro>().Where(l => l.IdAutore == idAutore)

using (var dbconn = new SQLiteConnection(DatabaseName)){

using (var statement = dbconn.Prepare(@"SELECT [Libri].[Titolo], [Libri].[Id] as IdLibro, [Libri].[Abstract],[Libri].[ISBN], [Libri].[Pagine], [Libri].[Copertina], [Libri].[IdAutore], [Autori].[Cognome], [Autori].[Nome]

FROM [Libri] INNER JOIN [Autori] ON [Autori].[Id] = [Libri].[IdAutore]"))

{while (SQLiteResult.ROW == statement.Step())

retList.Add(Mapper.Map<ISQLiteStatement, Libro>(statement));}

}

No Navigation

Properties

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 9

CRUD Operations

SQLite.NET-PCL

SQLitePCL

using (var dbconn = new SQLiteConnection(DatabaseName)){

using (var statement = dbconn.Prepare(@"INSERT INTO [Libri] ([Titolo],[Abstract],[ISBN],[Pagine],[Copertina],[IdAutore])VALUES (@Titolo,@Abstract,@ISBN,@Pagine,@Copertina,@IdAutore)"))

{statement.Bind("@Titolo", entity.Titolo);statement.Bind("@Abstract", entity.Abstract);statement.Bind("@ISBN", entity.ISBN);statement.Bind("@Pagine", entity.Pagine);statement.Bind("@Copertina", entity.Copertina);statement.Bind("@IdAutore", entity.IdAutore);

result = SQLiteResult.OK == statement.Step();}

}

DB.Insert(entity, typeof(Libro))

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 10

Transactions

SQLite.NET-PCL

SQLitePCL

DB.BeginTransaction();// do something with Databaseif (/* something wrong */) DB.Rollback();else DB.Commit();

using (var dbconn = new SQLiteConnection(DatabaseName)){

using (var statement = dbconn.Prepare("BEGIN TRANSACTION")){

statement.Step();}// Execute one or more statements...using (var lbrCommand = dbconn.Prepare("INSERT INTO Libri (Titolo, Abstract, ISBN) VALUES (@Titolo, @Abstract, @ISBN)")){

//.....}using (var recCommand = dbconn.Prepare("INSERT INTO Recensioni (Testo, Voto, IdLibro) VALUES (@Testo, @Voto, @IdLibro)")){

//.....}// COMMIT to accept all changes or ROLLBACK TRANSACTION to discard pending changesusing (var statement = dbconn.Prepare("COMMIT TRANSACTION")){

statement.Step();}

}

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 11

Tools

DEMO

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 12

Biblioteca SQLite

Sync your data on all devices

Mobile App

SQL DB

User Authentication

Offline sync

Twitter provider

AuthenticationUWP App

VS 2015

Demo overview

Mobile App

SyncAsync

Every time the data change, the method SyncAsync must be called in order to update the SQL Database

Push sends all CUD changes since thelast push. Note that it is not possibleto send only an individual table'schanges. Push executes a series ofREST calls to your Azure Mobile Appbackend, which in turn will modifyyour server database.

Pull is performed on a per-table basis andcan be customized with a query to retrieveonly a subset of the server data. The AzureMobile client SDKs then insert the resultingdata into the local store.

UWP App

Android App

PCL

Mobile App

SQL DB

User Authentication

Twitter provider

Authentication

Offline sync

Wrap your code into a PCL

UWP App running on PC - Android App running on Android Tablet

Q & A

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 - #wpc15it 21

Contatti OverNetEducation

OverNet Educationinfo@overneteducation.it

www.overneteducation.it

Tel. 02 365738

@overnete

www.facebook.com/OverNetEducation

www.linkedin.com/company/overnet-solutionswww.wpc2015.it

www.wpc2015.it – info@wpc2015.it - +39 02 365738.11 - #wpc15it 22