Lesson 5

Local Data Storage: Overview Apps store private data in Isolated Storage App Root Folder Install

Package Manager

Creates root folder sandboxed to App

Database file (r/o)

App Data Folder

App WP7 Isolated Storage APIs

Creates/manages files and settings Application Settings file Database file

Application files

var query = from w in db.Wines where w.Country == “USA" select w.Name; .Call System.Linq.Queryable.Select( .Call System.Linq.Queryable.Where( .Constant(Table(Wines)), '(.Lambda #Lambda1)), '(.Lambda #Lambda2)) .Lambda #Lambda1(db.Wines $w) { $w.Country == “USA" } .Lambda #Lambda2(w.Country $w) { $w.Name }

select Name from Wines where Country = “USA”

Custom data context

Object materialization

App objects

Identity management

Change tracking

Core ADO.NET (System.Data) SQLCE ADO.NET Provider (System.Data.SqlServerCe)

Update processing

Objects Design time

Varietals

Wines

Vineyards

WineMakers

Run time

Wines

Varietals PK

VarietalID

PK

WineID

FK2 FK1

Name Description RetailPrice VarietalID VineyardID

Name

Database upgrade

Winemaker

Vineyards PK

VineyardID Name Latitude Longitude Country

PK

WinemakerID FirstName LastName

// Define the data context. public partial class WineDataContext : DataContext { public Table Wines; public Table Vineyards; public WineDataContext(string connection) : base(connection) { } } // Define the tables in the database [Table] public class Wine { [Column(IsPrimaryKey=true] public string WineID { get; set; } [Column] public string Name { get; set; } …… } // Create the database form data context, using a connection string DataContext db = new WineDataContext("isostore:/wineDB.sdf"); if (!db.DatabaseExists()) db.CreateDatabase();

// Create the database form data context, using a connection string DataContext db = new WineDataContext("isostore:/wineDB.sdf"); // Find all wines currently at home, ordered by date acquired var q = from w in db.Wines where w.Varietal.Name == “Shiraz” && w.IsAtHome == true orderby w.DateAcquired select w;

Inserts/Updates/Deletes • It’s all about the DataContext – Changes made against the DataContext first – Changes persisted by calling SubmitChanges()

• SubmitChanges – LINQ to SQL determines change set and submits to DB

Your app code

Name

Yellow Tail

Name

Little Penguin

Varietal

Pinot Noir

Varietal

Pinot Noir

AtHome

True

AtHome

False True

Insert Wine newWine = new Wine { WineID = “1768", Name = “Windows Phone Syrah", Description = “Bold and spicy" };

Update Wine wine = (from w in db.Wines where w.WineID == “1768" select w).First(); wine.Description = “Hints of plum and melon";

db.Wines.InsertOnSubmit(newWine); db.SubmitChanges(); db.SubmitChanges();

Delete var vineyardsToDelete = from Vineyards v in db.Vineyards where v.Country == “Australia” select v; db.Vineyards.DeleteAllOnSubmit (vineyardsToDelete); db.SubmitChanges();

Foreign key constraint will cause exception here if Wines associated with the Vineyards are not deleted first

var vineyardsToDelete =

from Vineyards v in db.Vineyards where v.Country == “Australia" select v;

foreach (Vineyards v in vineyardsToDelete) { db.Wines.DeleteAllOnSubmit(v.Wines); } db.Vineyards.DeleteAllOnSubmit(vineyardsToDelete); db.SubmitChanges();

Database Schema Upgrades • DatabaseSchemaUpdater allows simple upgrades on your existing DB • Supports adding – Tables – Columns – Indices – Associations/foreign keys

• DatabaseSchemaVersion available for tracking upgrades • Schema updates are transactional

WineDataContext wineDC = new WineDataContext(App.WineDBConnectionString); DatabaseSchemaUpdater dsu = wineDC.CreateDatabaseSchemaUpdater(); if (dsu.DatabaseSchemaVersion == 1) { dsu.AddColumn("BottleType"); dsu.DatabaseSchemaVersion = 2;

dsu.Execute(); }

Exercise • Create the simple database application for storing „things to do”. Application must present the list of things and allow to add, modify and delete each of „things to do”.