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 marketplace)

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

[Table]
public class Car
{
    private EntitySet<KilometerItem> m_KilometerItems;
    public Car()
    {
        this.m_KilometerItems = new EntitySet<KilometerItem>(
            ((item) => item.Car = this),
            ((item) => item.Car = null));
    }

    [Column(IsPrimaryKey = true, 
        IsDbGenerated = true, 
        DbType = "INT NOT NULL Identity", 
        CanBeNull = false, 
        AutoSync = AutoSync.OnInsert)]
    public int Id { get; set; }

    [Column]
    public int CurrentKilometers { get; set; }

    [Column]
    public string DisplayName { get; set; }

    [Association(Name = "FK_Car_KMItems", 
        Storage = "m_KilometerItems", 
        ThisKey = "Id", 
        OtherKey = "CarId")]
    public EntitySet<KilometerItem> KilometerItems
    {
        get { return m_KilometerItems; }
    }
}

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

[Table]
public class KilometerItem
{
    [Column(IsPrimaryKey = true,
        IsDbGenerated = true,
        DbType = "INT NOT NULL Identity",
        CanBeNull = false,
        AutoSync = AutoSync.OnInsert)]
    public int Id { get; set; }

    [Column(CanBeNull=false)]
    public DateTime Date { get; set; }

    [Column]
    public int StartKilometers { get; set; }

    [Column]
    public int EndKilometers { get; set; }

    [Column]
    public int TotalKilometersForEntry { get; set; }

    [Column]
    public TripType TripType { get; set; }

    [Column(CanBeNull = false)]
    public string DisplayName { get; set; }

    [Column(DbType = "Int")]
    public int? CarId { get; set; }

    private EntityRef<Car> m_CarRef = new EntityRef<Car>();
    [Association(Name = "FK_Car_KMItems",
        Storage = "m_CarRef",
        ThisKey = "CarId",
        OtherKey = "Id",
        IsForeignKey = true)]
    public Car Car
    {
        get
        {
            return this.m_CarRef.Entity;
        }
        set
        {
            var previousRef = this.m_CarRef.Entity;
            if (previousRef != value || !this.m_CarRef.HasLoadedOrAssignedValue)
            {
                if (previousRef != null)
                    this.m_CarRef.Entity = null;
                this.m_CarRef.Entity = value;
                if (value != null)
                    this.CarId = value.Id;
                else
                    this.CarId = default(int?);
            }
        }
    }
}

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.

[Column(IsPrimaryKey = true,
    IsDbGenerated = true,
    DbType = "INT NOT NULL Identity",
    CanBeNull = false,
    AutoSync = AutoSync.OnInsert)]

 

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
3. You’ll be presented with a dialog, should be self explanatory from there

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

public class CarTrackerContext : DataContext
{
    private static string CONNECTION_STRING
        = "Data Source=isostore:/CarTracker.sdf";
    public CarTrackerContext()
        : base(CONNECTION_STRING)
    {
    }

    /// <summary>
    /// Represents the car table
    /// </summary>
    public Table<Car> CarTable;

    public Table<KilometerItem> KilometerItems;

    public void Initialize()
    {
        if (!this.DatabaseExists())
            this.CreateDatabase();

    }
}

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

private static CarTrackerContext m_context;
public static CarTrackerContext Context
{
    get
    {
        if (m_context == null)
        {
            m_context = new CarTrackerContext();
            m_context.Initialize();
        }
        return m_context;
    }
}

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

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

public partial class MainPage : PhoneApplicationPage, INotifyPropertyChanged

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

<ListBox ItemsSource="{Binding CarItems}"

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

this.DataContext = this;

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

private ObservableCollection<Car> m_CarItems;
public ObservableCollection<Car> CarItems
{
    get { return m_CarItems; }
    set
    {
        if (value != m_CarItems)
        {
            m_CarItems = value;
            NotifyPropertyChanged("CarItems");
        }
    }
}

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

public event PropertyChangedEventHandler PropertyChanged;
void NotifyPropertyChanged(string propertyName)
{
    if (PropertyChanged != null)
        PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}

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

NavigationService.Navigate(new Uri("/NewCar.xaml", UriKind.Relative));

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

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

try
{
    App.Context.CarTable.InsertOnSubmit(
        new Model.Car() { 
            CurrentKilometers = Int32.Parse(txtKMs.Text), 
            DisplayName = txtName.Text });
    App.Context.SubmitChanges();
    if (NavigationService.CanGoBack) NavigationService.GoBack();
}
catch (Exception ex)
{
    MessageBox.Show(string.Format("Unable to save record.  Error: {0}", ex.Message));
}

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

protected override void OnNavigatedTo(NavigationEventArgs e)
{
    if (e.NavigationMode == NavigationMode.Back)
    {
        var tCarItems = from t in App.Context.CarTable
                        select t;

        if (CarItems == null)
            CarItems = new ObservableCollection<Car>();

        foreach (var car in tCarItems)
        {
            var existingCar = from t in CarItems
                                where t.Id.Equals(car.Id)
                                select t;

            if (existingCar.Count() == 0)
            {
                //nothing found so add it to the bottom
                CarItems.Add(car);
                NotifyPropertyChanged("CarItems");
            }
        }
    }
    else if (e.NavigationMode == NavigationMode.New)
    {
        if (CarItems == null)
            CarItems = new ObservableCollection<Car>();

        var tCarItems = from t in App.Context.CarTable
                        select t;

        CarItems = new ObservableCollection<Car>(tCarItems);
    }
    base.OnNavigatedTo(e);
}

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

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

private void ListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    var lb = sender as ListBox;
    if (lb != null)
        NavigationService.Navigate(new Uri(
            string.Format("/CarKilometerItemsPage.xaml?carId={0}", 
            (lb.SelectedItem as Car).Id), UriKind.Relative));
}

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

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

protected override void OnNavigatedTo(NavigationEventArgs e)
{
    //grab the query string
    if (NavigationContext.QueryString.ContainsKey("carId"))
        CarId = Int32.Parse(NavigationContext.QueryString["carId"]);

    if (CarId.HasValue)
    {
        if (e.NavigationMode == NavigationMode.Back)
        {
            var tCarItem = (from t in App.Context.CarTable
                            where t.Id.Equals(CarId.Value)
                            select t).FirstOrDefault();

            if (KMItems == null)
                KMItems = new ObservableCollection<KilometerItem>();

            foreach (var item in tCarItem.KilometerItems)
            {
                var existingItem = from t in KMItems
                                    where t.Id.Equals(item.Id)
                                    select t;

                if (existingItem.Count() == 0)
                {
                    //nothing found so add it to the bottom
                    KMItems.Add(item);
                    NotifyPropertyChanged("CarItems");
                }
            }
        }
        else if (e.NavigationMode == NavigationMode.New)
        {
            if (KMItems == null)
                KMItems = new ObservableCollection<KilometerItem>();

            var tCarItem = (from t in App.Context.CarTable
                            where t.Id.Equals(CarId.Value)
                            select t).FirstOrDefault();

            KMItems = new ObservableCollection<KilometerItem>(tCarItem.KilometerItems);
        }
    }
    else
    {
        ThreadPool.QueueUserWorkItem((o) =>
            {
                Thread.Sleep(250);
                Dispatcher.BeginInvoke(() =>
                    {
                        MessageBox.Show("Unable to get the car id sorry :(");
                        NavigationService.GoBack();
                    });
            });
    }
    base.OnNavigatedTo(e);
}

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

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

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

private void NewEntry_Click(object sender, System.EventArgs e)
{
    NavigationService.Navigate(new Uri(string.Format("/NewKMEntry.xaml?carId={0}", CarId.Value), UriKind.Relative));
}

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

var Car = App.Context.CarTable.Where(t =>
    t.Id.Equals(
    Int32.Parse(NavigationContext.QueryString["carId"]))).FirstOrDefault();
if (Car != null)
{
    Car.KilometerItems.Add(new Model.KilometerItem()
        {
            StartKilometers = Int32.Parse(txtKMs.Text),
            EndKilometers = Int32.Parse(txtEndKm.Text),
            DisplayName = txtDescription.Text,
            Date = DateTime.Now,
        });
    App.Context.SubmitChanges();
}
else
{
    MessageBox.Show("Can't find the car, something went wrong.  We will navigate back and try it again");
}
Dispatcher.BeginInvoke(() =>
{
    if (NavigationService.CanGoBack) NavigationService.GoBack();
});

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!