Transcript ItemReferenceData PK ItemId FK1 ItemName ItemDescription CategoryId Categories PK CategoryId CategoryName Stores ListItems Lists PK ListId PK FK1 ListName FK2 ListItemId ListItemName ListId Quantity Category Description StoreId History Favorites PK FK1 FavoriteItemId FavoriteItemName FavoriteItemCategory FavoriteItemQuantity FavoriteItemDescription FavoriteItemListId FavoriteItemPhoto PK PK FK1 HistoryItemId HistoryItemName HistoryItemCategory HistoryItemQuantity HistoryItemDescriptioin HistoryItemDateAdded HistoryItemListId HistoryItemPhoto StoreId StoreName StoreLocationLat StoreLocationLong StoreAddressLine1 StoreAddressLine2 StoreAddressCity StoreAddressState StoreAddressCountry StoryAddressZip Words PK WordId Word Pronunciation Definition AlternateSpellings Origin Favorites PK FavoriteId FK1 WordId History PK HistoryItemId FK1 WordId AddedDate Local Database (SQL CE) SQLite Windows Phone 7.1   Windows Phone 8   Windows 8 Store Apps   LINQ syntax  (*SQLite-NET) SQL statement execution   Cross Platform   Supports.

ItemReferenceData
PK
ItemId
FK1
ItemName
ItemDescription
CategoryId
Categories
PK
CategoryId
CategoryName
Stores
ListItems
Lists
PK
ListId
PK
FK1
ListName
FK2
ListItemId
ListItemName
ListId
Quantity
Category
Description
StoreId
History
Favorites
PK
FK1
FavoriteItemId
FavoriteItemName
FavoriteItemCategory
FavoriteItemQuantity
FavoriteItemDescription
FavoriteItemListId
FavoriteItemPhoto
PK
PK
FK1
HistoryItemId
HistoryItemName
HistoryItemCategory
HistoryItemQuantity
HistoryItemDescriptioin
HistoryItemDateAdded
HistoryItemListId
HistoryItemPhoto
StoreId
StoreName
StoreLocationLat
StoreLocationLong
StoreAddressLine1
StoreAddressLine2
StoreAddressCity
StoreAddressState
StoreAddressCountry
StoryAddressZip
Words
PK WordId
Word
Pronunciation
Definition
AlternateSpellings
Origin
Favorites
PK
FavoriteId
FK1 WordId
History
PK
HistoryItemId
FK1 WordId
AddedDate
Local Database (SQL CE)
SQLite
Windows Phone 7.1


Windows Phone 8


Windows 8 Store Apps


LINQ syntax

(*SQLite-NET)
SQL statement execution


Cross Platform


Supports Views


Supports Foreign Keys


Supports Stored Procedures


Supports Triggers


Database encryption


XAML Apps – C#, VB
Direct3D Apps – C++
Sqlite3.dll
XAML
Maps
Geolocation
Sensors
IAP
Direct3D
HTML
XML
Threading
Touch
Speech
XAudio2
Phone Features
Push
Camera
Video
Proximity
Media Foundation
Calendar
Wallet
Contacts
Core Types
VoIP
STL
Multitasking
Live Tiles
Memory
Async
Enterprise
CRT
.NET - C# and VB
Windows Runtime - C#, VB, and C++
File system, Networking, Graphics, Media
Core Operating System
C++
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;
}
using (var db = new SQLiteWinRTPhone.Database(
ApplicationData.Current.LocalFolder, "demo.db"))
{
await db.OpenAsync();
using (var stmt = await db.PrepareStatementAsync
("SELECT name, age FROM people"))
{
while (await stmt.StepAsync())
{
var name = stmt.GetTextAt(0);
var age = stmt.GetIntAt(1);
}
}
}
http://bit.ly/Zxg2Ox
http://bit.ly/MuzL1e
http://bit.ly/130PpGa
http://sqlwinrt.codeplex.com
private 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 (COMException ex) {
var result = SQLiteWinRT.Database.GetSqliteErrorCode(ex.HResult);
throw new ApplicationException("Database create failed with error " + result);
}
}
try
{
// Connection already opened in app.xaml.cs - get reference
SQLiteWinRT.Database db = App.db;
using (var custstmt = await db.PrepareStatementAsync(
"INSERT INTO Customer (Name, City, Contact) VALUES (@name, @city, @contact)"))
{
// NOTE that named parameters have a leading "@",":" or "$".
custstmt.BindTextParameterWithName("@name", customer.Name);
custstmt.BindTextParameterWithName("@city", customer.City);
custstmt.BindTextParameterWithName("@contact", customer.Contact);
// Use StepAsync to execute a prepared statement
await custstmt.StepAsync();
}
}
catch (System.Runtime.InteropServices.COMException)
{ … }
public async Task<CustomerViewModel> GetCustomerAsync(int customerId)
{
CustomerViewModel customer = null;
using (var readstmt = await App.db.PrepareStatementAsync(
"SELECT Id, Name, City, Contact FROM Customer WHERE Id = " + customerId))
{
if (await readstmt.StepAsync() == true)
{
var customer = new CustomerViewModel()
{
Id = readstmt.GetIntAt(0),
Name = readstmt.GetTextAt(1),
City = readstmt.GetTextAt(2),
Contact = readstmt.GetTextAt(3)
};
}
}
return customers;
}
using (var readstmt = await App.db.PrepareStatementAsync(
"SELECT Id, Name, City, Contact FROM Customer WHERE Id = " + customerId))
{
// Enable the columns property
statement.EnableColumnsProperty();
if (await readstmt.StepAsync() == true)
{
// it isn't super useful as all columns are returned as text and must be parsed
var columns = statement.Columns;
var customer = new CustomerViewModel()
{
Id = int.Parse(columns["Id"]),
Name = columns["Name"],
City = columns["City"],
Contact = columns["Contact"]
};
}
}
// See if the customer already exists
var existingCustomer = await GetCustomerAsync(customer.Id);
if (existingCustomer != null)
{
using (var custstmt = await App.db.PrepareStatementAsync(
"UPDATE Customer SET Name = ?, City = ?, Contact = ? WHERE Id=?"))
{
// NOTE when using anonymous parameters the first has an index of 1, not 0.
custstmt.BindTextParameterAt(1, customer.Name);
custstmt.BindTextParameterAt(2, customer.City);
custstmt.BindTextParameterAt(3, customer.Contact);
custstmt.BindIntParameterAt(4, customer.Id);
await custstmt.StepAsync();
}
}
public async Task DeleteCustomerAsync(int customerId)
{
string sql = @"DELETE FROM Customer WHERE Id={0}";
sql = string.Format(sql, customerId);
// Can use ExecuteStatementAsync to run non row returning statements
await App.db.ExecuteStatementAsync(sql);
}
await db.ExecuteStatementAsync("BEGIN TRANSACTION");
using (var custstmt = await db.PrepareStatementAsync(
"INSERT INTO Customer (Name, City, Contact) VALUES (@name, @city, @contact)"))
{
custstmt.BindTextParameterWithName("@name", customer.Name);
...
await custstmt.StepAsync();
}
// …other statements
...
// Or ROLLBACK TRANSACTION to discard pending changes
await db.ExecuteStatementAsync("COMMIT TRANSACTION");
Customer
PK
Id
Name
City
Contact
Customer
PK
Id
Name
City
Contact
Project
Project
PK Id Project
PK Name
Id
PK Name
Id
Description
Name
Description
DueDate
Description
DueDate
FK1 CustomerId
DueDate
FK1 CustomerId
FK1 CustomerId
Project
Project
PK Id
PK Name
Id
Name
Description
Description
DueDate
DueDate
FK1 CustomerId
FK1 CustomerId
CREATE TABLE IF NOT EXISTS Project
(Id
INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
CustomerId INTEGER,
Name
VARCHAR( 140 ),
Description VARCHAR( 140 ),
DueDate
DATETIME,
FOREIGN KEY(CustomerId) REFERENCES Customer(Id)
);
// Turn on Foreign Key constraints
sql = @"PRAGMA foreign_keys = ON";
await db.ExecuteStatementAsync(sql);
Type
Description
SQLiteWinRT
PRIMARY KEY
Defines the column(s) of the primary key
- 1 per table max
Yes
In Column or Table definition in a
CREATE TABLE
SQL statement
UNIQUE
Column constraint enforces unique values in
that column
Yes
In Column definition
NOT NULL
Column constraint prevents null values
Yes
In Column definition
CHECK
Column or Table constraint: constraint
expression is evaluated on every insert or
update, and if ‘0’ returned, constraint fails
Yes
In Column or Table definition
http://sqlite.org/lang_createtable.html
// Create index on Foreign Key column
sql = @"CREATE INDEX IF NOT EXISTS
fk_customer_project_idx
ON project (customerId) ASC";
await db.ExecuteStatementAsync(sql);
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
http://sqlitestudio.pl/
http://sqliteadmin.orbmu2k.de/
http://www.sqliteexpert.com/
http://wptools.codeplex.com

Install Folder


Install
Package
Manager
DB
Database
File (r/o)
App
Application
Settings File
DB
Database file
Application
Files
// Open the SQLite database
var db = new SQLiteWinRT.FromApplicationUriAsync(
new Uri("ms-appx:///sqliteReadOnly.db", UriKind.Absolute));
await db.OpenAsync(SQLiteWinRTPhone.SqliteOpenMode.OpenRead);
// Force temporary files to be created in memory
await db.ExecuteStatementAsync("PRAGMA temp_store = 2");
http://bit.ly/19fToRR
-- Create ChangeId table
-- Add ChangeId column to those tables where changes are to be tracked
-- Create Customer Tombstone table to save IDs of deleted records
-- Create TRIGGER track_customer_deletion
-- Increment the master ChangeId
-- Increment the master ChangeId
-- Create trigger track_customer_insertion
-- Create TRIGGER track_customer_update
-- Get all Inserted/Updated rows
SELECT Name, ChangeId FROM Customer WHERE ChangeId IS NOT NULL
AND (ChangeId > (SELECT LastSyncId FROM ChangeId));
-- Get IDs of all deleted rows
SELECT CustomerId FROM CustomerTombstone
WHERE ChangeId > (SELECT LastSyncId FROM ChangeId));
http://bit.ly/123duhy
http://bit.ly/19fToRR
http://bit.ly/199xYbr
http://bit.ly/1bo7wrc
http://bit.ly/18TOagR
http://channel9.msdn.com/Events/TechEd
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn