Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

13. Windows Phone 7 > Storing data in a local SQL CE database

Storing data in a local SQL CE database

Storing data in a local SQL CE database Isolated Storage is a good way of storing data locally on the device. With the release of Windows Phone Mango (7.5), an option was added to support a client-side database. This database is a SQL CE (Compact Edition) database. SQL CE databases are file-based and in the case of WP7, the database file is stored as a physical file inside the Isolated Storage. As developers, we can talk to the database using LINQ to SQL. In this recipe, we'll introduce the local database to store the downloaded list of Blu-rays. Getting ready This recipe uses a starter solution, which can be found in the Chapter13/BluRayCollector_Database_Starter. The finished solution can be found in the Chapter13/BluRayCollector_Push_Notifications_Completed folder. How to do it... Working with the local database requires a few specific steps related to setting up the database. Once the setup is done, we can change our existing code to interact with the database instead of the Isolated Storage. This is done by writing LINQ to SQL queries. Follow along with these steps to get this to work: Open the starter solution as outlined in the Getting ready section for this recipe.Microsoft recommends using the code-first approach. This means that we describe the model in code instead of starting with a database model. With code-first, we don't really have to worry about database queries—we just write our code using the classes from our model. To apply this approach, we need to write a context class first. This class can be understood as a class representing the database from code. To add the context, create a new class named BluRayDataContext. This class should inherit from DataContext. The initial code for this class is shown in the following code snippet: using System.Data.Linq; public class BluRayDataContext: DataContext { ... } The DataContext class lives in the System.Data.Linq namespace. This namespace requires that we add a reference to the System.Data.Linq assembly in the WP7 project.Next, we need to create a connection string, containing the location of the database. Add the following line to the BluRayDataContext class: public static string DBConnectionString = "Data Source=isostore:/BluRayCollector.sdf"; Note that the file containing the database is an *.sdf file. This is the typical file extension for SQL CE.Our custom context needs to pass the connection string to the base class. This can be done using the following code where we do exactly that from the constructor: public BluRayDataContext(string connectionString): base(connectionString){ } Each entity in our model is represented by a Table<T>. In our case, we have just a BluRay table. This is represented by the following code: public Table<BluRay> BluRays; With the context ready (and thus the model outlined), we can move on to describing the entities. In our case, we need to describe from code just the BluRay class. To indicate that this class is used as a mapping between our model and the database, we need to attribute it with the Table attribute. To perform this step, add a new class named BluRay and add the following code: using System.Data.Linq.Mapping; using System.ComponentModel; [Table] public class BluRay : INotifyPropertyChanged, INotifyPropertyChanging { ... } Notice that this class implements two interfaces. INotifyPropertyChanging is used to notify clients that the value is about to change while INotifyPropertyChanged is used to notify clients after the value has changed. For this code to work, make sure to add the two using statements indicated here as well. Also, a reference to the mscorlib.Extensions assembly needs to be added to the project.In this class, we now need to describe all its properties as well as how these properties should be mapped inside the database. Remember that a database will be created based on the model we are describing here, so all information regarding keys, database types, and relations is to be added in this class. Add the following code to the BluRay class. To save space, not all properties have been written out; the code is similar though. [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)] public int BluRayId { get { return _bluRayId; } set { if (_bluRayId != value) { NotifyPropertyChanging("BluRayId"); _bluRayId = value; NotifyPropertyChanged("BluRayId"); } } } private string _movieName; [Column] public string MovieName { get { return _movieName; } set { if (_movieName != value) { NotifyPropertyChanging("MovieName"); _movieName = value; NotifyPropertyChanged("MovieName"); } } } private int _movieLength; [Column] public int MovieLength { ... } private string _movieImageUrl; [Column] public string MovieImageUrl { ... } private bool _isFavorite; [Column] public bool IsFavorite { ... } private bool _isBadMovie; [Column] public bool IsBadMovie { ... } private string _movieDescription; [Column] public string MovieDescription { ... } private string _movieType; [Column] public string MovieType { ... } public event PropertyChangedEventHandler PropertyChanged; private void NotifyPropertyChanged(string propertyName) { if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } public event PropertyChangingEventHandler PropertyChanging; private void NotifyPropertyChanging(string propertyName) { if (PropertyChanging != null) { PropertyChanging(this, new PropertyChangingEventArgs(propertyName)); } } This class also contains the implementation for the two implemented interfaces.We're now ready to start using the database. In the App.xaml.cs file, we're going to add code that creates the database file for our application to use if it doesn't exist yet. Based on the model, the *.sdf file will be generated. In the constructor of the App class, add the following code: using (BluRayDataContext db = new BluRayDataContext(BluRayDataContext.DBConnectionString)) { if (db.DatabaseExists() == false) { db.CreateDatabase(); } } We now know for sure that the database file was created. We can now safely work with the context as our intermediary to talk with the database itself. Change the code in the BluRayOverview.xaml.cs file as follows: private BluRayDataContext _bluRayDataContext; public BluRayOverview() { InitializeComponent(); _bluRayDataContext = new BluRayDataContext (BluRayDataContext.DBConnectionString); this.DataContext = this; } In the OnNavigatedTo method, we only want to get the list of Blu-rays if the database table is still empty. We can write a simple LINQ query here and then use the Count() method. Add the following code to do so: var localBluRays = from BluRay b in _bluRayDataContext.BluRays select b; if (localBluRays.Count() > 0) { //DB is locally filled already AllBluRayListBox.ItemsSource = localBluRays; } else { proxy.GetAllBluRaysForUserCompleted += new EventHandler <BluRayService.GetAllBluRaysForUserCompletedEventArgs> (proxy_GetAllBluRaysForUserCompleted); proxy.GetAllBluRaysForUserAsync(1); } Initially, the list will need to be downloaded, so the GetAllBluRaysForUserAsync will be triggered. In this callback, we will loop over the result and insert new BluRay instances in the Table<BluRay> property of the context. Finally, we call the SubmitChanges() method which will commit the added instances. This is done as follows: void proxy_GetAllBluRaysForUserCompleted(object sender, BluRayService.GetAllBluRaysForUserCompletedEventArgs e) { AllBluRayListBox.ItemsSource = e.Result; foreach (var serviceBluRay in e.Result) { var localBluRay = new BluRay() { BluRayId = serviceBluRay.BluRayId, IsBadMovie = serviceBluRay.IsBadMovie, IsFavorite = serviceBluRay.IsFavorite, MovieDescription = serviceBluRay.MovieDescription, MovieImageUrl = serviceBluRay.MovieImageUrl, MovieLength = serviceBluRay.MovieLength, MovieName = serviceBluRay.MovieName, MovieType = serviceBluRay.MovieType }; _bluRayDataContext.BluRays.InsertOnSubmit(localBluRay); } _bluRayDataContext.SubmitChanges(); } With this code added, the sample now uses the SQL CE database instead of isolated storage to store the downloaded information. How it works... Having a local database at our disposal within a WP7 application opens up a lot of new ways of working with data locally on the device. Isolated Storage allows us to work with any type of data (such as raw data that we can store as XML but also binary data such as images). Custom libraries, such as the Sterling database (which we used in Chapter 5, Working with Local Data, for Silverlight but available for WP7 as well) allow us to interact with data in an easier way. With the addition of a SQL CE database, we can work with data in a relational way. This client-side database is a perfect addition for applications that work together with services as well: it can be used to store downloaded data to work in offline mode as well. The database context To work with the local database, Microsoft recommends using a code-first approach. As mentioned before, this basically frees us from thinking in relational ways about our data. We as developers can write a model in code and don't have to worry about how this will be stored in the database. All this is handled for us. The context class is the start of this approach. It's a class that describes the model. Each entity inside this model is a table in the database that will be created for us. All these entities are described as properties of the context. The type of these properties should be Table<T>. The context also contains the link to the physical database file in the form of a connection string. The connection string we used here is as follows: public static string DBConnectionString = "Data Source=isostore:/BluRayCollector.sdf"; The file type is *.sdf, which is the default file type for a SQL CE database. Such a database is file-based. The model classes The entities themselves used in the context are classes as well. The class is attributed with the Table attribute. This is an indication that this class is used for mappings between the model in code and the actual database. This is reflected in attributes on the properties of the class. Properties will have a Column attribute applied to them that means this property should become a field in the database table. To this attribute, we can optionally pass more information such as specifying that the property is to be the primary key, database type, and so on. Interaction with the database Once the model is declared, we can have the database file created for us. In the sample, we placed code in the constructor of the app that uses the CreateDatabase() method to generate the database file based on the context. Of course, this only has to be done if the database hasn't been generated yet. If needed, it's possible to package an initial database with your application and copy it to isolated storage from code. This can be useful if your application has a lot of initial settings that need to be packaged in the database that comes with your application. Once the database is created, we can write LINQ to SQL queries. In the code from this recipe, we wrote a regular LINQ query that retrieves all Blu-Rays. Inserting (as well as updating and deleting) instances is done using the context as well. In the sample code, we used the InsertOnSubmit() method to add instances to the Table<BluRay>. The SubmitChanges() method commits the changes to the database. Save often It's a good idea to commit changes using the SubmitChanges() method often. It might be tempting to do all editing of the data in-memory without committing and then perform the commit when the application exits. The latter isn't such a good idea. In WP7, applications have a limited amount of time when exiting to perform tasks before being killed off. If we were have to do a lot of commits to perform in that timeframe, we might just hit that time limit. If that happens, there's no guarantee that the data is still intact inside the database! See also In Chapter 5, Working with Local Data, in the Using the Sterling Isolated Storage database recipe, we used the Sterling database for Silverlight. This is also applicable to WP7 development.

  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free 10-Day Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint