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

of 21 /21
presenta www.wpc2015.it – [email protected] - +39 02 365738.11 - #wpc15it 1 WI005 - Offline data sync con SQLite in Universal Windows Platform Erica Barone Microsoft Technical Evangelist @_ericabarone [email protected] Massimo Bonanni Microsoft MVP , Intel Black Belt Intel Software Innovator @massimobonanni [email protected]

Author: others

Post on 27-May-2020

7 views

Category:

Documents


1 download

Embed Size (px)

TRANSCRIPT

  • presenta

    www.wpc2015.it – [email protected] - +39 02 365738.11 - #wpc15it 1

    WI005 - Offline data synccon SQLite in Universal Windows Platform

    Erica BaroneMicrosoft Technical Evangelist

    @_ericabarone

    [email protected]

    Massimo Bonanni

    Microsoft MVP, Intel Black Belt

    Intel Software Innovator

    @massimobonanni

    [email protected]

  • 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 – [email protected] - +39 02 365738.11 2

  • www.wpc2015.it – [email protected] - +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 – [email protected] - +39 02 365738.11 4

    • Not a full database application No forms

    No reports

    No saved queries

    What isn’t SQLite?

  • www.wpc2015.it – [email protected] - +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 – [email protected] - +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();

    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 – [email protected] - +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 Recensioni { get; set; }

    }

  • www.wpc2015.it – [email protected] - +39 02 365738.11 8

    Query

    SQLite.NET-PCL

    SQLitePCL

    var libri = DB.Table().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(statement));}

    }

    No Navigation

    Properties

  • www.wpc2015.it – [email protected] - +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 – [email protected] - +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 – [email protected] - +39 02 365738.11 11

    Tools

  • DEMO

    www.wpc2015.it – [email protected] - +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 – [email protected] - +39 02 365738.11 - #wpc15it 21

  • Contatti OverNetEducation

    OverNet [email protected]

    www.overneteducation.it

    Tel. 02 365738

    @overnete

    www.facebook.com/OverNetEducation

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

    www.wpc2015.it – [email protected] - +39 02 365738.11 - #wpc15it 22

    mailto:[email protected]://www.overneteducation.it/http://www.facebook.com/OverNetEducationhttp://www.linkedin.com/company/overnet-solutionshttp://www.wpc2015.it/