presenta
WI005 - Offline data sync
con SQLite in Universal
Windows Platform
Erica Barone
Microsoft Technical Evangelist
@_ericabarone
Massimo Bonanni
Microsoft MVP, Intel Black Belt
Intel Software Innovator
@massimobonanni
Agenda
•
SQLite UWP
What is/isn’t SQLite
SQLite for UWP
.NET APIs
SQLite.NET-PCL vs SQLitePCL
Tools
•
Offline Data sync
Mobile App
SyncAsync
•
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
•
Not a full database application
No forms
No reports
No saved queries
•
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.NET-PCL
LINQ syntax
Lightweight ORM (no navigation properties)
.NET APIs
SQLitePCL
SQL statements (ADO Style)
Thin wrapper around the SQLite C API
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(); }
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; } }
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
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(); }
}
Transactions
SQLite.NET-PCL
SQLitePCL
DB.BeginTransaction();
// do something with Database
if (/* 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 changes
using (var statement = dbconn.Prepare("COMMIT TRANSACTION")) {
DEMO
Sync your data
on all devices
Mobile App
SQL DB
User
Authentication
Offline sync
Authentication
UWP App
VS 2015
Demo overview
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 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
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.
UWP App
Android App
PCL
Mobile App
SQL DB
User
Authentication
Authentication
Offline sync
Contatti
OverNet
Education
OverNet Education
www.overneteducation.it
Tel. 02 365738
@overnete
www.facebook.com/OverNetEducation
www.linkedin.com/company/overnet-solutions
www.wpc2015.it
