WI005 - Offline data sync
con SQLite in Universal
Windows Platform
Erica Barone
presenta
Massimo Bonanni
Microsoft Technical Evangelist
Microsoft MVP, Intel Black Belt
Intel Software Innovator
@_ericabarone
@massimobonanni
[email protected]
[email protected]
www.wpc2015.it – [email protected] - +39 02 365738.11 - #wpc15it
1
• SQLite UWP
What is/isn’t SQLite
SQLite for UWP
.NET APIs
SQLite.NET-PCL vs SQLitePCL
Agenda
Tools
• Offline Data sync
Mobile App
SyncAsync
PCL
www.wpc2015.it – [email protected] - +39 02 365738.11
2
What is SQLite?
•
•
•
Open source RDBMS.
Works as library instead of service (in-process)
Single file database.
•
•
•
Cross Platform database (Mac, Windows, Linux).
Cross Technology Database (WPF, UWP, Win Form).
Implements most of the SQL standard (SQL92).
•
Zero-configuration
Triggers
Tables
Indices
Views
RIGHT and FULL OUTER JOIN
Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE
VIEWs in SQLite are read-only.
www.wpc2015.it – [email protected] - +39 02 365738.11 - #wpc15it
3
What isn’t SQLite?
• Not a full database application
No forms
No reports
No saved queries
www.wpc2015.it – [email protected] - +39 02 365738.11
4
SQLite for Universal App Platform
• 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
www.wpc2015.it – [email protected] - +39 02 365738.11
5
.NET APIs
SQLite.NET-PCL
LINQ syntax
Lightweight ORM (no navigation properties)
SQLitePCL
SQL statements (ADO Style)
Thin wrapper around the SQLite C API
From Microsoft Open Technologies
www.wpc2015.it – [email protected] - +39 02 365738.11
6
Create a database
SQLite.NET-PCL
var conn = new SQLiteConnection(new Platform.WinRT.SQLitePlatformWinRT(), dbName);
conn.CreateTable<Libro>();
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; }
}
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
Query
No Navigation
Properties
SQLite.NET-PCL
var libri = DB.Table<Libro>().Where(l => l.IdAutore == idAutore)
SQLitePCL
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));
}
}
www.wpc2015.it – [email protected] - +39 02 365738.11
8
CRUD Operations
SQLite.NET-PCL
DB.Insert(entity, typeof(Libro))
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();
}
}
www.wpc2015.it – [email protected] - +39 02 365738.11
9
Transactions
SQLite.NET-PCL
DB.BeginTransaction();
// do something with Database
if (/* something wrong */) DB.Rollback();
else DB.Commit();
SQLitePCL
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 changes
using (var statement = dbconn.Prepare("COMMIT TRANSACTION"))
{
statement.Step();
}
}
www.wpc2015.it – [email protected] - +39 02 365738.11
10
Tools
www.wpc2015.it – [email protected] - +39 02 365738.11
11
DEMO
Biblioteca SQLite
www.wpc2015.it – [email protected] - +39 02 365738.11
12
Sync your data
on all devices
Demo overview
Offline sync
SQL DB
User
Authentication
Mobile App
VS 2015
UWP App
Authentication
Twitter provider
Mobile App
SyncAsync
Push sends all CUD changes since the
last push. Note that it is not possible
to send only an individual table's
changes. Push executes a series of
REST calls to your Azure Mobile App
backend, which in turn will modify
your server database.
Every time the data change, the method SyncAsync
must be called in order to update the SQL Database
Pull is performed on a per-table basis and
can be customized with a query to retrieve
only a subset of the server data. The Azure
Mobile client SDKs then insert the resulting
data into the local store.
Wrap your code into a PCL
Offline sync
SQL DB
User
Authentication
UWP App
Mobile App
PCL
Authentication
Twitter provider
Android App
UWP App running on PC - Android App running on Android Tablet
Q&A
www.wpc2015.it – [email protected] - +39 02 365738.11 - #wpc15it
21
OverNet Education
[email protected]
www.overneteducation.it
Tel. 02 365738
Contatti
OverNet
Education
@overnete
www.facebook.com/OverNetEducation
www.linkedin.com/company/overnet-solutions
www.wpc2015.it
www.wpc2015.it – [email protected] - +39 02 365738.11 - #wpc15it
22
