SQL CE & Windows Phone

1. The easy way SQL CE & Windows Phone Paul Scivetti paul@synergensoftware.com @TheIdeaGuy BuzzTheCloud.com • January 17, 2012

2. SQL CE + Win Phone!

3. Table Designer Stored Procs MS Dev Tools T-SQL Entity Framework

5. L2S + “Code First”

6. Seriously?

7. #%*$!! SQL CE

8. Better Way?

9. Simple • Useful • Balanced

10. SQL CE Basics • Tools • Using L2S • Performance

11. Demo

12. SQL CE Basics • Where is the Database? • Create / Modify Database • Create Tables

13. Where is the Database? usingSystem.Data.Linq; public partialclass DBDataContext : DataContext { public DBDataContext() : base("Data Source=isostore:/PhoneDemoDB.sdf") { OnCreated(); } }

14. Create Database DBDataContext db = new DBDataContext(); if (!db.DatabaseExists()) { db.CreateDatabase(); var updater = db.CreateDatabaseSchemaUpdater(); updater.DatabaseSchemaVersion = DB_CURRENT_VERSION; updater.Execute(); }

15. Update Schema var updater = db.CreateDatabaseSchemaUpdater(); intdbVersion = updater.DatabaseSchemaVersion ; if (dbVersion < DB_CURRENT_VERSION) { if (dbVersion < 2) { updater.AddTable<Log>(); updater.AddColumn<Food>("Calories"); } updater.DatabaseSchemaVersion = DB_CURRENT_VERSION; updater.Execute(); }

16. Code

17. SQL CE Basics • Where is the Database? • Create / Modify Database • Create Tables

23. #%*$!! SQL CE

24. SQL CE Basics • Tools • Using L2S • Performance

25. 1. Design Tables 4. Use L2S in App 2. Run SQLMetal 3. Tweak code

27. SQLMetal • Windows SDK • Command line tool • Generates mapping classes • AlmostWinPhone Compatible

28. .\SqlMetal.exe /server:.\sqlexpress/database:phonedemo/code:c:\metal\phonedemo.cs/context:DBDataContext/pluralize/namespace:PhoneApp2

29. Code

30. SQL CE Basics • Tools • Using L2S • Performance

31. Query Syntax: DBDataContext db = new DBDataContext(); varqry = from f in db.Foods orderbyf.Description select f; foreach (Food item in qry) { // process each food item }

32. Fluent Syntax: DBDataContext db = new DBDataContext(); varqry = db.Events.OrderByDescending(o => o.MeetingDate) .ThenBy(o => o.Topic); foreach (Event item in qry) { // process each event }

33. CRUD: Insert DBDataContext db = new DBDataContext(); Food f = new Food(); f.Description = “Mushroom Pizza”; db.Foods.InsertOnSubmit(f); db.SubmitChanges();

34. Code

35. SQL CE Basics • Tools • Using L2S • Performance

36. Async Access using System.Threading.Tasks; private async void LoadFoodAsync() { ObservableCollection<Food> res = new ObservableCollection<Food>(); await TaskEx.Run(() => { DBContext db = new DBContext(); varqry = from s in db.Food select s; res = new ObservableCollection<Food>(qry); }); this.FoodItems = res; }

37. Performance Tuning usingSystem.Data.Linq; public partial class DBDataContext : DataContext { // default buffer size: 384K public DBDataContext() : base("Data Source=isostore: /PhoneDemoDB.sdf;max buffer size=1024") { OnCreated(); } }

38. Resources Windows Phone Development create.msdn.com LINQ to SQL

39. The easy way SQL CE & Windows Phone Paul Scivetti paul@synergensoftware.com @TheIdeaGuy BuzzTheCloud.com • January 17, 2012

40. The easy way SQL CE & Windows Phone Paul Scivetti paul@synergensoftware.com @TheIdeaGuy BuzzTheCloud.com • January 17, 2012