Databases for Windows Phone

Technology

chris-koenig
  • 1. LINQ to SQL onWindows PhoneChris Koenigchris.koenig@microsoft.com | @chriskoenig | http://chriskoenig.netNorthwest Arkansas .NET User Group – January 2012
  • 2. Agenda Overview of Windows Phone database support Creating and interrogating a database Updating your database with your applications Deploying a reference database with your app2 Windows Phone
  • 3. http://aka.ms/wpsdk
  • 4. http://aka.ms/databaseformango http://aka.ms/WP7Resources
  • 5. SQL Server on Windows Phone Basedon SQL Server Compact Edition 3.5 Runtime is distributed with the operating system Interaction managed through LINQ to SQL’s code-firstmodel  Create classes that represent tables  Annotate classes and fields/properties with[Table] and [Column] attributes  Create a custom DataContext object tomanage interactions Automatic conversion of datatypes  Properties are available Encrypt the database using a password on theconnection string5 Windows Phone
  • 6. Local Data Storage: OverviewApps store private data in Isolated Storage Settings and properties in the app dictionaryApp Root Folder Unstructured data in Isolated Storage files Structured data in database files InstallCreates root folder Package Managersandboxed to App Database file (r/o) App Data FolderCreates/manages Appfiles and settings WP7 Isolated Storage APIs ApplicationApplicationSettings filefilesDatabase file 6 Windows Phone
  • 7. Inserts/Updates/Deletes Actions made on the objects and collectionsYour app code  Create new objects  Add objects to collectionsManaged by the DataContext Name Yellow Tail Name Little Penguin Varietal Pinot NoirVarietal Pinot Noir  Changes made againstthe DataContext firstAtHome TrueAtHome True False  Changes persistedby calling SubmitChanges() SubmitChanges  LINQ to SQL determines change set and submitsto DB
  • 8. Creating a databaseCRUD operationsDemo
  • 9. Updating an existing database Start with a good schema  Buddy-project?  Get a copy from the Isolated Storage Tool Make updates to your Model classes  Keep track of ALL the changes to your model so you know how to “add” them later Update the schema at Runtime  Use the DatabaseSchemaUpdater to push changes into the database  Check new version against current version to determine upgrade path9 Windows Phone
  • 10. Isolated Storage Toolc:Program FilesMicrosoft SDKsWindows Phonev7.1ToolsIsolatedStorageExplorerTool<ts|rs|dir[:device-folder]> ts = Take Snapshot rs = Restore Snapshot dir = show directory contents (optional path can be supplied)<xd|de> xd = run on the emulator de = run on the device<Product GUID> GUID taken from the WMAppManifest.xml[<desktop-path>] Optional – desktop path for download and upload10 Windows Phone
  • 11. IsoStoreSpy Graphical tool for browsing the IsolatedStorage areas on the Phone Device andEmulator Open Source project on CodePlex http://isostorespy.codeplex.com/ Multiple file formats supported  Text Files  Images  Multimedia  Ringtones  SQL CE Databases11 Windows Phone
  • 12. DatabaseSchemaUpdaterMake schema changes to existing databases AddTable<T>()var updater = db.CreateDatabaseSchemaUpdater(); AddColumn<T>() if (updater.DatabaseSchemaVersion < 2) { AddIndex<T>()updater.AddTable<History>();updater.AddColumn<Widget>("CreationDate"); AddAssociation<T>()updater.DatabaseSchemaVersion = 2;updater.Execute(); DatabaseSchemaVersion }Make sure that your new Model classes are already created, and thensupply them to the DatabaseSchemaUpdater12 Windows Phone
  • 13. Updating your databaseDemo
  • 14. Deploying a database with your applicationUse a Buddy Project to create the initial SDFAdd the database to your phone project  Set properties as Content, with the “Copy Always” optionUse as a read-only databasevar db = new MyDataContext("Data Source=appdata:/ReferenceDB.sdf;File Mode=read only;");Copy to Isolated Storage to use as a read-write databaseIsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();Uri uri = new Uri("ReferenceDB.sdf", UriKind.Relative);using (Stream input = Application.GetResourceStream(uri).Stream)using (IsolatedStorageFileStream output = iso.CreateFile(“ApplicationDB.sdf")) {byte[] readBuffer = new byte[4096];int bytesRead = -1;while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0) {output.Write(readBuffer, 0, bytesRead);}}14 Windows Phone
  • 15. Deploying a database with your appDemo
  • 16. Agenda Overview of Windows Phone database support Creating and interrogating a database Updating your database with your applications Deploying a reference database with your app16 Windows Phone
  • 17. Call to Action Download the tools from http://aka.ms/wp7sdk Download the Starter Kits fromhttp://code.msdn.microsoft.com Register for AppHub at http://create.msdn.com Have fun getting rich building apps for WindowsPhone!17 Windows Phone
  • 18. Chris Koenigchris.koenig@microsoft.comQ&A http://chriskoenig.net@chriskoenig214-385-5616
  • 19. 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.© 2011 Microsoft Corporation.All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
    Please download to view
  • 1
    All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
    Description
     
    Text
    • 1. LINQ to SQL onWindows PhoneChris Koenigchris.koenig@microsoft.com | @chriskoenig | http://chriskoenig.netNorthwest Arkansas .NET User Group – January 2012
  • 2. Agenda Overview of Windows Phone database support Creating and interrogating a database Updating your database with your applications Deploying a reference database with your app2 Windows Phone
  • 3. http://aka.ms/wpsdk
  • 4. http://aka.ms/databaseformango http://aka.ms/WP7Resources
  • 5. SQL Server on Windows Phone Basedon SQL Server Compact Edition 3.5 Runtime is distributed with the operating system Interaction managed through LINQ to SQL’s code-firstmodel  Create classes that represent tables  Annotate classes and fields/properties with[Table] and [Column] attributes  Create a custom DataContext object tomanage interactions Automatic conversion of datatypes  Properties are available Encrypt the database using a password on theconnection string5 Windows Phone
  • 6. Local Data Storage: OverviewApps store private data in Isolated Storage Settings and properties in the app dictionaryApp Root Folder Unstructured data in Isolated Storage files Structured data in database files InstallCreates root folder Package Managersandboxed to App Database file (r/o) App Data FolderCreates/manages Appfiles and settings WP7 Isolated Storage APIs ApplicationApplicationSettings filefilesDatabase file 6 Windows Phone
  • 7. Inserts/Updates/Deletes Actions made on the objects and collectionsYour app code  Create new objects  Add objects to collectionsManaged by the DataContext Name Yellow Tail Name Little Penguin Varietal Pinot NoirVarietal Pinot Noir  Changes made againstthe DataContext firstAtHome TrueAtHome True False  Changes persistedby calling SubmitChanges() SubmitChanges  LINQ to SQL determines change set and submitsto DB
  • 8. Creating a databaseCRUD operationsDemo
  • 9. Updating an existing database Start with a good schema  Buddy-project?  Get a copy from the Isolated Storage Tool Make updates to your Model classes  Keep track of ALL the changes to your model so you know how to “add” them later Update the schema at Runtime  Use the DatabaseSchemaUpdater to push changes into the database  Check new version against current version to determine upgrade path9 Windows Phone
  • 10. Isolated Storage Toolc:Program FilesMicrosoft SDKsWindows Phonev7.1ToolsIsolatedStorageExplorerTool<ts|rs|dir[:device-folder]> ts = Take Snapshot rs = Restore Snapshot dir = show directory contents (optional path can be supplied)<xd|de> xd = run on the emulator de = run on the device<Product GUID> GUID taken from the WMAppManifest.xml[<desktop-path>] Optional – desktop path for download and upload10 Windows Phone
  • 11. IsoStoreSpy Graphical tool for browsing the IsolatedStorage areas on the Phone Device andEmulator Open Source project on CodePlex http://isostorespy.codeplex.com/ Multiple file formats supported  Text Files  Images  Multimedia  Ringtones  SQL CE Databases11 Windows Phone
  • 12. DatabaseSchemaUpdaterMake schema changes to existing databases AddTable<T>()var updater = db.CreateDatabaseSchemaUpdater(); AddColumn<T>() if (updater.DatabaseSchemaVersion < 2) { AddIndex<T>()updater.AddTable<History>();updater.AddColumn<Widget>("CreationDate"); AddAssociation<T>()updater.DatabaseSchemaVersion = 2;updater.Execute(); DatabaseSchemaVersion }Make sure that your new Model classes are already created, and thensupply them to the DatabaseSchemaUpdater12 Windows Phone
  • 13. Updating your databaseDemo
  • 14. Deploying a database with your applicationUse a Buddy Project to create the initial SDFAdd the database to your phone project  Set properties as Content, with the “Copy Always” optionUse as a read-only databasevar db = new MyDataContext("Data Source=appdata:/ReferenceDB.sdf;File Mode=read only;");Copy to Isolated Storage to use as a read-write databaseIsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();Uri uri = new Uri("ReferenceDB.sdf", UriKind.Relative);using (Stream input = Application.GetResourceStream(uri).Stream)using (IsolatedStorageFileStream output = iso.CreateFile(“ApplicationDB.sdf")) {byte[] readBuffer = new byte[4096];int bytesRead = -1;while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0) {output.Write(readBuffer, 0, bytesRead);}}14 Windows Phone
  • 15. Deploying a database with your appDemo
  • 16. Agenda Overview of Windows Phone database support Creating and interrogating a database Updating your database with your applications Deploying a reference database with your app16 Windows Phone
  • 17. Call to Action Download the tools from http://aka.ms/wp7sdk Download the Starter Kits fromhttp://code.msdn.microsoft.com Register for AppHub at http://create.msdn.com Have fun getting rich building apps for WindowsPhone!17 Windows Phone
  • 18. Chris Koenigchris.koenig@microsoft.comQ&A http://chriskoenig.net@chriskoenig214-385-5616
  • 19. 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.© 2011 Microsoft Corporation.All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
  • Comments
    Top