Windows Phone and Database Support Part 2

October 24, 2011 — 4 Comments

Back in June, I did a few blog posts on Windows Phone 7.1 SDK and some new features for developers.   I also did one specifically on Windows Phone and database support as this was a huge pain point for developers including myself.  Well with Windows Phone 7.5, things are a lot better.  I’ll quickly go through how to get a database up and running, access the data and update the data.

For this sample, we’ll be creating a quick application to track kilometers for a car. We are basically going to have a Car class, and a car will have a collection of Kilometer Entry Items.

Creating our Model

Creating your via code is a manual process, unlike the desktop devs, we don’t have a designer, although I would imagine you could generate the code with the desktop tools and then modify the generated code.  I’ll take the long way as you have more control and see what is going on and so you learn more IMO. First we have to create our model and will essentially look like this (NOTE: This is not an extensive Kilometer tracking application, just a sample, feel free to extend, make better and sell on marketplaceSmile)

image

Because we are using LINQ to SQL we will be creating ‘mostly’ some plain old CLR objects (POCOs) and decorating our class and properties with some attributes.  Specifically we will be using the System.Data.Linq.Mapping.TableAttribute for our class which will represent our tables and System.Data.Linq.Mapping.ColumnAttribute for our properties which will represent our table columns.

Since a Car will have a collection of KilometerItems we will also have to use System.Data.Linq.AssociationAttribute which will allow us to associate a collection of records to a Car record.

As for the POCOs, you will noticed I said ‘mostly’ POCO.  The reason for this is because of our Association requires a little bit of code to make LINQ to SQL work.  We’ll start with the Car class

Few things to notice

  1. EntitySet – we have a an EntitySet<KilometerItem> which holds a reference to our collection of KilometerItems for the associated car
  2. Constructor – when a KilometerItem is added or removed, we want to set or remove the reference to the Car object, so we pass in a Delegate to the EntitySet.Removed and EntitySet.Added parameters.

Now here is our KilometerItem class

The main thing here is the Car property.  If you remember from the Car class, we set this KilometerItem.Car property when an item is added or removed from the Car.KilometerItems EntitySet.  This allows us to have a reference to the Car object that ‘owns’ this KilometerItem.  Other than that it’s a standard POCO with a bunch of attributes.

In both classes you will also want to notice the Id Property.  You will require this because we are making a 1 to many association from Car to KilometerItem.  You will notice our Column attribute also specifies some parameters to auto generated the property, that it’s a primary key etc.

 

Creating the Database

There are two ways to create the database, either via code or you can embed a database prepopulated with data in your application. To Create a database manually do the following

  1. In Visual Studio, open Server Explorer
  2. Click on the Add Connection button image
  3. You’ll be presented with a dialog, should be self explanatory from there Smile

I don’t think you learn much from Wizards, so we’ll create one using code.  First thing we have to create is a class that inherits from System.Data.Linq.DataContext.  Here is our CarTrackerContext class

Couple things to cover

  • Connection strings: to get all the details on Connection String and Local Database on Windows Phone see MSDN.  But one key thing to notice is isostore which is unique to Windows Phone
    • isostore: this essentially means your database was created in code, is read/write and located in isolated storage for your app
    • appdata: this essentially means your database was included in your application, is in the installation folder and is read only.  You can copy this file into Isolated Storage to make it read/write.
  • Initialize – we call this so we can check to see if the database exists and if it does not create it.
  • Updating Database – I don’t go through this as it’s another post on it’s own.  Read this on MSDN on updating a local database on Windows Phone, it’s a must read!

And that is essentially it!  The DataContext class will take care of a lot of the heavy lifting to create the database and make lookups into tables using LINQ to SQL work.

Hooking It All Up

Usually you would want some sort of MVVM pattern to make things all connect.  But again this is a sample and not best practices, so although your code will work, it’s really not a best practice so be warned.  I’m also not going through every details such as creating a new page etc as I’m assuming you already know how to do that.

First thing we do is create our DataContext CarTrackerContext.  You can add the following code to your App.Xaml.cs

Next, we need to bind the collection of cars to a list in the MainPage.xaml.  Our page will look like the following

image

To bind the list, we will have to create an observable collection so the list can automatically update when something is added.  MainPage.xaml will have to implement INotifyPropertyChanged as follows

In your MainPage.Xaml, the ListBox should have the ItemSource property set as follows (again see source for a complete set)

In your MainPage constructor add the following which will set the DataContext for your page

Then add the following code to allow us to bind a collection to the UI.

And of course, because we implement INotifyPropertyChanged we need the following

Our Add button at the bottom will allow us to add a new Car record so in the button click handler we add the following

Now we create a NewCar.xaml page to allow us to create a new car as follows

image

On the button click event for the done button (the checkmark image) we add the following

Key things to note here is how we save the data.  We essentially create a new Car object, add it to the CarTrackerContext.CarTable and call CarTrackerContext.SubmitChanges and it will be added to the database.  Yes, it’s as easy as that to create a record!

When we navigate back to the main page we still won’t see any data in the list because it’s not bound to the CarItems property.  First we need to set this property and we do this in the OnNavigatedTo method of the MainPage.xaml.cs as follows

A few things I do here, first, we check to see if the NavigationMode (which is new in Windows Phone 7.1 SDK) tells us if this is a New or a Back navigation.  We can assume if it’s new, the app is just launching, and if it’s back we are coming back from a different page.

If we are launching for the first time, we just go ahead and fill up our ObservableCollection that is bound to the UI using a LINQ Query.  If it’s coming back from a different page, we want to only add new items to the list.  This way if an item is selected that item will stay selected and if the listbox is scrolled to a different position other than the top, that position won’t reset.  Remember it’s small things like this that make users happy but they don’t know it until it happens and they get irritated Smile

What About Tracking Kilometers

Adding the functionality to add a kilometer record for a car record is essentially the same as adding a new card record. So I’m not going to go through all the code just some of the important points.

On the MainPage.xaml.cs we want to handle the ListBox.SelectionChanged event as follows

You will notice we are adding a query string to the URI, this way we know what KilometerItems we need to open on the new page which will look as follow

image

On the CarKilometerItemsPage.xaml we again have a ListBox so we have an ObservableCollection that is bound to a list of KilometerItems associated with a Car object in our database.  On the OnNavigatedTo we again have some code to update our list as follows

Things to note

  1. We get the querystring to get a CarId.  If one is not sent, we show a message and navigate back as we can’t do anything without a Car.Id value
  2. If it’s a New Navigation, we are finding the Car record using a LINQ query, then we go and get all KilometerItems and fill our observable collection which is bound to our UI
  3. If it’s a Back Navigation, we only want to add new items to the observable collection
  4. Nothing else, pretty easy Smile

Adding a KilometerItem Record

Adding a KilometerItem is pretty straight forward also and is pretty much the same process as adding a new Car Record.  To start our page will look as follows

image

First thing we do is add some code to our list of KilometerItem page to navigate to our new page as follows

You will notice we are again passing the Car.Id value to our new page, this way we know what Car record the new KilometerItem record should be assigned to.  When the user is ready, they can click the checkmark and we can run the following code to save the new record

Few things to note

  • We first want to find the Car record in the database and we use a Lambda expression to get the object returned to us.
  • To create the record, we use the Car.KilometerItems.Add method and add a new record to the collection.
  • Then we call the Context.SubmitChanges() and we are good to go!

Conclusion

A couple of points that you should probably be aware of

  1. Be careful using NText as you may get some issues when trying to update those column types.  Try to use NVarChar(4000) instead.  If you must use it, set your column attribute to the following [Column(UpdateCheck=UpdateCheck.Never)], this way you will not get the ‘SQL Server does not handle comparison of NText, Text, Xml, or Image data types’ error
  2. If you are used to creating your own SQL Statements or using things like Table Direct to optimize table access, your out of luck on for Windows Phone.  Although I have not done in performance testing, I’m pretty confident applications like the Kilometer Tracking example will not suffer at all from a performance bottle neck
  3. If you are writing to the table from separate threads, you may want to create different dataContexts as you need them.  The DataContext is not guaranteed to be thread safe
  4. DataContext implements IDisposable but if you want to pass around an object and then try and get a collection associated with that object, it will through an ObjectDisposedException if you call Dispose or use a using statement.

And that’s it, pretty straight forward to Local Database support in Windows Phone now that the tools have been updated. Here is the download to the source.  Comments or feedback feel free to contact me here or via twitter @MarkArteaga!

4 responses to Windows Phone and Database Support Part 2

  1. Any idea when we will see Visual Studio designer support in the windows phone dev tools for the mobile linq-to-sql? There are some community members that have tried to fill the gap with tools that will convert the "desktop" dbml to WP7.1, but it just feels hacky. I’d also love to see an article about how to migrate a WP7.1 linq-to-sql database from one version to the next (like if existing tables change). Thanks for the article!

  2. What about images? Do you prefer to store in database or to leave it out and make some logic-linking?

  3. Michael: not sure if there will be tools but my hope is they are working on it. As a long time mobile developer, used to doing things manually until tools catch up 🙂

    Betim: not sure, you can use the Image type and store your images there or store a reference to a file name in Isolated Storage. Not sure what the advantages or disadvantages of either are or performance metrics for either.

  4. You couldn’t pay me to ingore these posts!

Leave a Reply to Gerry Cancel reply