08. Windows Phone Local Storage

Content preview:

• Windows Phone Database Support
• LINQ to SQL
• Performance and best practices

LINQ to everything:

Supports complex structures:

Supports Foreign keys:

WebService cache:

Local Storage:

Architecture:

Object:

Definition table:

  // Define the tables in the database  
[Table]  
public class Wine : INotifyPropertyChanged, INotifyPropertyChanging  
{      
private string wineID;      
private string name;      
[Column(IsPrimaryKey=true)]     
 public string WineID       
{          
get { return wineID; }        
 set {             
InvokePropertyChanging(new PropertyChangingEventArgs("WineID"));             
wineID = value;             
InvokePropertyChanged(new PropertyChangedEventArgs("WineID"));         
}         
}      
[Column]      
public string Name { ... }      
...}

Define data context:

// Define the data context.public partial class WineDataContext : DataContext {public Table<Wine> Wines;public Table<Vineyard> Vineyards;public WineDataContext(string connection) : base(connection) { }}...// Create the database from data context, using a connection stringDataContext db = new WineDataContext("isostore:/wineDB.sdf");if (!db.DatabaseExists())     db.CreateDatabase();

Use sqlmetal code generation tool:

c:\>Sqlmetal /code:northwindEntities.cs                   
/context:NorthwindDataContext                  
/pluralize northwind.sdf

Query:

 // 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;

Insert, update, and delete:Don't forget submitchanges

Insert:

Wine newWine = new Wine{WineID = “1768",Name = “Windows Phone Syrah",Description = “Bold and spicy"};db.Wines.InsertOnSubmit(newWine);db.SubmitChanges();

Update:

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

Delete:

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

Update database structure:

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

Performance and best practices:

• Keep the set of changes small. In other words, submit the changes as soon as possible to avoid data loss during program termination.
• Use background threads.
• Optimized read-only queries.
• Fill in a large amount of data in advance.
• Right Tools, a large amount of complex data is used in databases, and small data is stored independently.