This article is Day #30 in a series called 31 Days of Mango, and was written by guest author Chris Woodruff. Chris can be reached on Twitter at @cwoodruff.
What is a Local Database?
In the original version of Windows Phone 7, we could save data but it took either custom code or using third party databases like SterlingDB to have the relational data repository that some applications need. This limited the types of applications some developers could produce for users.
In Windows Phone 7 Mango, developers still have isolated storage to keep data and information stored for their applications but now have SQL CE as a relational storage feature to created even better applications for Windows Phone.
Like other database solutions for the original Windows Phone 7, the native SQL CE database built into Mango has it data stored in the device’s Isolated Storage. You can learn more about Isolated Storage here. Microsoft also did not create new ways to work with the data on the phone and instead implemented LINQ to SQL for all database operations. LINQ to SQL is used to do all functions for the application when dealing with data including creating the data, populating data into the database, getting data and finally saving and deleting data.
A good tutorial for LINQ to SQL is here on MSDN.
Setting up a Local Database for your Mango Application
Like all starting points for Windows Phone 7, we will start with creating the Windows Phone Databound Application project within Visual Studio 2010.
We could have started with just the Windows Phone Application project but I like the additional features that the Databound project gives you to allow your application to have better design patterns like Model-View-ViewModel (MVVM).
Next we will update the MainPage of the project to allow for the data to be added to the database. Our data example will be collecting ideas that we all have and need to remember. We will not go into much detail about the design of the MainPage but here is the XAML to get the look and feel for our Idea collector.
x:Class="_31DaysMangoStorage.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
d:DataContext="{d:DesignData SampleData/MainViewModelSampleData.xaml}"
FontFamily="{StaticResource PhoneFontFamilyNormal}"
FontSize="{StaticResource PhoneFontSizeNormal}"
Foreground="{StaticResource PhoneForegroundBrush}"
SupportedOrientations="Portrait" Orientation="Portrait"
shell:SystemTray.IsVisible="True">
<!–LayoutRoot is the root grid where all page content is placed.–>
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<!–TitlePanel contains the name of the application and page title.–>
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="31 Days of Mango" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="Idea Tracker" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!–ContentPanel – place additional content here.–>
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<Grid.RowDefinitions>
<RowDefinition Height="Auto" />
<RowDefinition Height="Auto" />
</Grid.RowDefinitions>
<!– Bind the list box to the observable collection. –>
<ListBox x:Name="toDoItemsListBox" ItemsSource="{Binding IdeaItems}"
Grid.Row="0" Margin="12, 0, 12, 0" Width="440">
<ListBox.ItemTemplate>
<DataTemplate>
<Grid HorizontalAlignment="Stretch" Width="440">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="50" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="100" />
</Grid.ColumnDefinitions>
<CheckBox
IsChecked="{Binding IsComplete, Mode=TwoWay}"
Grid.Column="0"
VerticalAlignment="Center"/>
<TextBlock
Text="{Binding ItemName}"
FontSize="{StaticResource PhoneFontSizeLarge}"
Grid.Column="1"
VerticalAlignment="Center"/>
<Button
Grid.Column="2"
x:Name="deleteTaskButton"
BorderThickness="0"
Margin="0"
Click="deleteTaskButton_Click">
<Image Source="appbar.delete.rest.png"/>
</Button>
</Grid>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
<Grid Grid.Row="1">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
<ColumnDefinition Width="Auto" />
</Grid.ColumnDefinitions>
<TextBox
x:Name="newIdeaTextBox"
Grid.Column="0"
Text="add new idea"
FontFamily="{StaticResource PhoneFontFamilyLight}"
GotFocus="newIdeaTextBox_GotFocus"/>
<Button
Content="add"
Grid.Column="1"
x:Name="newIdeaAddButton"
Click="newIdeaAddButton_Click"/>
</Grid>
</Grid>
</Grid>
</phone:PhoneApplicationPage>
Also to allow for your application to compile and run without doing anything the code below will be added to the MainPage.xaml.cs file inside the MainPage class implementation and after the constructor.
{
// Clear the text box when it gets focus.
newIdeaTextBox.Text = String.Empty;
}
private void newIdeaAddButton_Click(object sender, RoutedEventArgs e)
{
}
protected override void OnNavigatedFrom(System.Windows.Navigation.NavigationEventArgs e)
{
// Call the base method.
base.OnNavigatedFrom(e);
}
Working with the Data Context
The Data Context is the point that will allow us to work with the database and also the proxy classes that represent our database tables. The data context is also a class and works against a number of “plain old CLR object” (POCO) classes that we will create for this project. The table objects that represent our database tables will contain a collection of entities for each table record stored in the database. Other details about our database are also given to us through the data context such as table keys and association mappings between tables.
Just a reminder that this local database has no connection with SQL Server 2008 R2 that is running locally on your PC or a server in your company or hosting provider and is only to hold data on your device.
There is not much to the DataContext class besides the connection string we are familiar in development and properties to each table that will exist in our database. Think of the DataContext as the “Hub” for your application’s data. The code for the sample application’s DataContext is below.
{
// Specify the connection string as a static, used in main page and app.xaml.
public static string DBConnectionString = "Data Source=isostore:/Ideas.sdf";
// Pass the connection string to the base class.
public IdeaDataContext(string connectionString)
: base(connectionString)
{ }
// Specify a single table for the to-do items.
public Table<IdeaItem> IdeaItems;
}
Note that IdeaItem type will be detailed in the next section covering creating the Database.
Creating the Database
Unlike applications that are run from your PC or on IIS 7, databases in Windows Phone 7 Mango must be created and initialized on the first instance your app runs on the phone. We will first look at creating the classes that will represent our database tables and then look at database initialization.
For each table that we need to be built and exposed through the database on our phone for the application a new POCO needs to be created. Since these classes represent the entities that will be stored in the database let’s call them Entity classes. To begin the Entity class must adhere to the following two interfaces:
· INotifyPropertyChanged — The INotifyPropertyChanged interface is used to notify clients, typically binding clients that a property value has changed.
· INotifyPropertyChanging — The INotifyPropertyChanging interface is used to notify clients, typically binding clients that a property value is changing.
These two interfaces will allow each entity to notify the DataContext that is in the process of changing or has changed. This will then be reflected to the XAML views of our application through the binding we have set up.
The Enity class must be annotated as a Table to allow the DataContext to know how to work with it. An Entity classes must also have private and public properties for each Entity property as well as having the private property annotated to give valuable metadata about the Entity property (aka the database column). Remember to have primary key properties for each of your Entity classes.
Below is the IdeaItem Entity class that will be located in the IdeaItems table referenced in the DataContext we created before.
public class IdeaItem : INotifyPropertyChanged, INotifyPropertyChanging
{
private int _ideaItemId;
[Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
public int IdeaItemId
{
get
{
return _ideaItemId;
}
set
{
if (_ideaItemId != value)
{
NotifyPropertyChanging("IdeaItemId");
_ideaItemId = value;
NotifyPropertyChanged("IdeaItemId");
}
}
}
private string _itemName;
[Column]
public string ItemName
{
get
{
return _itemName;
}
set
{
if (_itemName != value)
{
NotifyPropertyChanging("ItemName");
_itemName = value;
NotifyPropertyChanged("ItemName");
}
}
}
private bool _isComplete;
[Column]
public bool IsComplete
{
get
{
return _isComplete;
}
set
{
if (_isComplete != value)
{
NotifyPropertyChanging("IsComplete");
_isComplete = value;
NotifyPropertyChanged("IsComplete");
}
}
}
[Column(IsVersion = true)]
private Binary _version;
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));
}
}
}
Finally we must create the database is it does not exist. This will be done in the Application class’s constructor. You will find this in the App.xaml.cs file. The code that will be added to the end of the constructor method is as followed.
{
if (db.DatabaseExists() == false)
{
db.CreateDatabase();
}
}
We now have a database that has been created and initialized in application’s Isolated Storage. One more thing to remember is that local databases in Windows Phone 7 mango cannot be shared directly between apps on the phone due to the sandboxing security set up in the Windows Phone 7 Mango operating system.
LINQ to SQL Support for Windows Phone Mango
Windows Phone 7.1 SDK allows for some but not all features of LINQ to SQL inside the Windows Phone. The following are just a few things to remember when working with data and LINQ to SQL in Windows Phone 7 Mango.
· ExecuteCommand is not supported
· ADO.NET Objects (such as DataReader) are not supported
· Only Microsoft SQL Server Compact Edition (SQL CE) data types are supported
To get more limitations and features of using LINQ to SQL on Mango please read the MSDN page here.
Retrieving Data from a Local Database
To retrieve data or any data related work with the local database, we must first create the DataContext and connect to the database. This will happen in MainPage.xaml.cs through a private variable for the DataContext, an observable collection property for the ideas in the database table and in the MainPage constructor as followed.
private ObservableCollection<IdeaItem> _ideaItems;
public ObservableCollection<IdeaItem> IdeaItems
{
get
{
return _ideaItems;
}
set
{
if (_ideaItems != value)
{
_ideaItems = value;
NotifyPropertyChanged("IdeaItems");
}
}
}
public MainPage()
{
InitializeComponent();
ideaDB = new IdeaDataContext(IdeaDataContext.DBConnectionString);
this.DataContext = this;
}
To get our ideas located in the local database we will use LINQ to SQL to query and get the collection back from the database via the DataContext.
{
var ideaItemsInDB = from IdeaItem todo in ideaDB.IdeaItems
select todo;
IdeaItems = new ObservableCollection<IdeaItem>(ideaItemsInDB);
base.OnNavigatedTo(e);
}
The ideas are now bound and reflected in the MainPage.xaml.
Storing Data to a Local Database
Finally we will build the saving of our idea data to the local database. We will not send the ideas to the database until we need to increase performance. We will keep all of the ideas in the local observable collection we created as a MainPage property (IdeaItems). The addition of new ideas to the local database will take place on the button click when the new idea is added to the IdeaItems collection.
{
IdeaItem newIdea = new IdeaItem { ItemName = newIdeaTextBox.Text };
IdeaItems.Add(newIdea);
ideaDB.IdeaItems.InsertOnSubmit(newIdea);
}
As mentioned before the ideas collected from the user will not be stored in the database until the application has transitioned from the MainPage either when the user exits the application or the application moves to a new page in the application. The code for the OnNavigateFrom event for the MainPage is below.
{
base.OnNavigatedFrom(e);
ideaDB.SubmitChanges();
}
Summary
So that’s pretty much it! You now have a simple way to create, store, and retrieve relational data in your Windows Phone applications. How are you going to use this?
To download a full Windows Phone project that includes all of the code and concepts from this article, click the Download Code button below.
Tomorrow, in the final article of this series, we will cover the best practices for promoting your Windows Phone applications. See you then!
JEFF BLANKENBURG 
Leave a Reply