New Data Features in Mango SQL Server Compact

New Data Features in “Mango”: SQL Server Compact and User Data Access Andy Wigley APPA Mundi @andy_wigley http: //bit. ly/andywigley andy. wigley@appamundi. com

Session Outline LINQ to SQL LINQ to User Data • Overview – Architecture – Code-first development • Implementation details – Queries – Inserts, updates, deletes… – Database schema upgrades • Performance and best practices – End-user consent – Supported account types • Implementation details – Querying contacts – Querying appointments • Performance and best practices

LINQ to Everything

Complex Schema • Numerous relationships and constraints • Example: Shopping List – 7 tables – 100 s of records – 5 foreign keys

Reference Data • Huge amounts of static reference data • Example: dictionary app – 3 tables – 1 table with 500 k rows

Web Service Cache • Fetch reference data from cloud • Cache locally • Combine with user-specific data Cloud Service Windows Phone

User Data • Filter contacts – Birthdays in the next month • Query all appointments – Find an available time for a meeting Filter

Local Database Storage

Database Support

Local Data Storage: Overview Apps store private data in Isolated Storage § Settings and properties in the app dictionary § Unstructured data in Isolated Storage files § Structured data in database files Package Manager App Data Folder Install Creates root folder sandboxed to App DB Database File (r/o) Iso Store Folder App WP 7 Isolated Storage APIs Creates/Manages files and settings Application Settings File DB Database file Application Files

Architecture 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 #Lambda 1)), '(. Lambda #Lambda 2)). Lambda #Lambda 1(db. Wines $w) { $w. Country == “USA" }. Lambda #Lambda 2(w. Country $w) { $w. Name } Custom Data Context App Objects Object Identity Materialization Management Change Tracking Update Processing Core ADO. NET (System. Data) select Name from Wines where Country = “USA” SQLCE ADO. NET Provider (System. Data. Sql. Server. Ce) SQL CE DB

Objects, objects… § Create object model: wines, varietals, vineyards, etc. Design time § Decorate objects with attributes for persistence § Create Data. Context reference to database § Translate object model into a database file Run time § Submit API persists changes to DB § Create new objects to enable new features Database upgrade § Use upgrade APIs to change DB Varietals Wines Vineyards Wine. Makers

Database Creation: Example // Define the data context. public partial class Wine. Data. Context : Data. Context { public Table<Wine> Wines; public Table<Vineyard> Vineyards; public Wine. Data. Context(string connection) : base(connection) { } } // Define the tables in the database [Table] public class Wine { [Column(Is. Primary. Key=true] public string Wine. ID { get; set; } [Column] public string Name { get; set; } …… } // Create the database form data context, using a connection string Data. Context db = new Wine. Data. Context("isostore: /wine. DB. sdf"); if (!db. Database. Exists()) db. Create. Database();

Queries: Examples // Create the database form data context, using a connection string Data. Context db = new Wine. Data. Context("isostore: /wine. DB. sdf"); // Find all wines currently at home, ordered by date acquired var q = from w in db. Wines where w. Varietal. Name == “Shiraz” && w. Is. At. Home == true orderby w. Date. Acquired select w;

Inserts/Updates/Deletes • It’s all about the Data. Context – Changes made against the Data. Context first – Changes persisted by calling Submit. Changes() Name Yellow Tail Name Little Penguin Varietal Pinot Noir Varietal Pinot. Noir At. Home True At. Home False True • Submit. Changes – LINQ to SQL determines change set and submits to DB DB

Inserts/Updates/Deletes Insert Update Wine new. Wine = new Wine { Wine. ID = “ 1768", Name = “Windows Phone Syrah", Description = “Bold and spicy" }; Wine wine = (from w in db. Wines where w. Wine. ID == “ 1768" select w). First(); wine. Description = “Hints of plum and melon"; db. Wines. Insert. On. Submit(new. Wine); db. Submit. Changes();

Inserts/Updates/Deletes Delete var vineyards. To. Delete = from Vineyards v in db. Vineyards where v. Country == “Australia” select v; db. Vineyards. Delete. All. On. Submit (vineyards. To. Delete); db. Submit. Changes();

Programming SQL Server Compact

Relationships • Express one-many, one-one and many-many relationships using Entity. Set<T> and Entity. Ref<T> columns • In the relational database, a child table has a column – the Foreign Key - that stores the unique ID of a record in the parent table

Example: Parent Object [Table] public class Contact : INotify. Property. Changed, INotify. Property. Changing { // Fields private Entity. Set<Lead> leads = new Entity. Set<Lead>(); [Association(Storage = "leads", Other. Key = "Contact. ID")] public Entity. Set<Lead> Leads { get { return this. leads; } set { Invoke. Property. Changing(new Property. Changing. Event. Args("Leads")); this. leads. Assign(value); Invoke. Property. Changed( new Property. Changed. Event. Args("Leads")); } }

Example: Child Object [Table] [Index (Name="Contact. ID_Idx", Columns="Contact. ID", Is. Unique=false)] public class Lead : INotify. Property. Changed, INotify. Property. Changing { private Entity. Ref<Contact> contact; [Column] public long Contact. ID {get; set; } [Association(Storage = "contact", This. Key = "Contact. ID", Is. Foreign. Key=true)] public Contact { get { return this. contact. Entity; } set { Invoke. Property. Changing( new Property. Changing. Event. Args("Contact")); this. contact. Entity = value; Invoke. Property. Changed( new Property. Changed. Event. Args("Contact")); if (value != null) this. Contact. ID = value. Contact. ID; } }

Deletes Delete var vineyards. To. Delete = from Vineyards v in db. Vineyards where v. Country == “Australia” select v; db. Vineyards. Delete. All. On. Submit (vineyards. To. Delete); db. Submit. Changes();

Delete Child Objects First var vineyards. To. Delete = from Vineyards v in db. Vineyards where v. Country == “Australia" select v; foreach (Vineyards v in vineyards. To. Delete) { db. Wines. Delete. All. On. Submit(v. Wines); } db. Vineyards. Delete. All. On. Submit(vineyards. To. Delete); db. Submit. Changes();

Entity Relationships Foreign Keys

Database Schema Upgrades • Database. Schema. Updater allows simple upgrades on your existing DB • Supports adding – – Tables Columns Indices Associations/foreign keys • Schema updates are transactional

Database Schema Upgrades Wine. Data. Context wine. DC = new Wine. Data. Context(App. Wine. DBConnection. String); Database. Schema. Updater dsu = wine. DC. Create. Database. Schema. Updater(); if (dsu. Database. Schema. Version == 1) { dsu. Add. Column<Wine>("Bottle. Type"); dsu. Database. Schema. Version = 2; dsu. Execute(); }

Schema Upgrades

Performance and Best Practices • Keep change sets small – Submit early and often to avoid data loss on app termination • Use background threads – Non-trivial operations will impact app responsiveness if done on UI thread • Optimize read-only queries – Set Object. Tracking. Enabled to minimize memory usage • Use secondary indices for properties which you query often

Performance and Best Practices • Populate large reference data tables in advance – Create a simple project to prepopulate data in emulator – Pull out database file using Isolated Storage explorer • Use the right tool for the job – Database for large or complex data sets – Isolated. Storage. Settings or basic files for small data sets

User Data

New and updated APIs in “Mango” • Chooser Tasks related to user data – Email. Address. Chooser. Task – Phone. Number. Chooser. Task – Address. Chooser. Task • Microsoft. Phone. User. Data for direct access – Contacts – Appointments

Address. Chooser. Task private Address. Chooser. Task address. Chooser. Task; // Constructor public Main. Page() { this. address. Chooser. Task = new Address. Chooser. Task(); this. address. Chooser. Task. Completed += new Event. Handler<Address. Result>( address. Chooser. Task_Completed); } private void address. Chooser. Task_Completed(object sender, Address. Result e) { if (null == e. Error && Task. Result. OK == e. Task. Result) {. . . = e. Display. Name; . . . = e. Address; } }

Contacts and Appointments • Important points – Contacts and Appointments APIs are read only – Third party social network data cannot be shared

Contacts/Appointments Data Shared Contact Name and Picture Other contact data Appointments/Events Windows Live Social YES YES Windows Live Rolodex (user created and SIM import) YES n/a Exchange accounts (corporate plus Google, etc. ) YES YES Operator Address Books YES n/a Facebook YES NO NO Other networks in the People Hub (e. g. , Twitter) NO NO NO

Contacts: Hello, World! Contacts contacts = new Contacts(); contacts. Search. Completed += new Event. Handler<Contacts. Search. Event. Args>((sender, e) => {. . . = e. Results; }); // E. g. search for all contacts. Search. Async(string. Empty, Filter. Kind. None, null); state filter expression Filter kind: name, email , (not a regex) phone or pinned to start) // E. g. search for all contacts with display name matching "ja" contacts. Search. Async("ja", Filter. Kind. Display. Name, null);

Appointments: Hello, World! Appointments appointments = new Appointments(); appointments. Search. Completed += new Event. Handler<Appointments. Search. Event. Args>((sender, e) => {. . . = e. Results; }); start date and time // E. g. get next appointment (up to 1 week away) appointments. Search. Async(Date. Time. Now, Date. Time. Now + Time. Span. From. Days(7), 1, null); Maximum items to return state end date and time

Performance and Best Practices • Be responsible – Your privacy policy should cover how you use the user’s contact information • Keep out of the way – Users have widely varying contact list sizes – Your UI should handle delays gracefully • Don’t let data get stale – Data returned is a snapshot – Refresh state when reasonable

Accessing User Data

Q&A

© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.