sam_3243

In this Mango development tutorial we are going to explore a new SDK feature; local database storage. This was not available to us in the pre-mango SDK hence the tutorial on how to use Isolated Storage. Isolated Storage worked for storage purposes when we had no local database and using web services were out of the question. Now that this sweet Mango treat has come our way, it is time to dive in. This will be a different approach for most new developers as we are going to use LINQ to SQL but you can learn as we go.

LINQ to SQL is a little different when it comes to coding SQL statements and the like as there are no actual written queries. The first thing we want to keep in mind is that because we are going to use Language Integrated Query (LINQ) we are going to use what is known as a data context which is a proxy or and object that represents our database.  This object will have Table objects that hold entities or columns within our database. This data context is what we are going to use to bridge the gap and use Local Database Storage within our Windows Phone 7 Mango application.

.
Seeing as how we are at an intermediate level in our Windows Phone 7 development career, we are not going to cover the mundane job of adding all of the text blocks or labels as this should already be common knowledge.  If this is new to you, please check out our Windows Phone 7 Development 101 series on Binary Wasteland to get a jump start. In this tutorial we are going to cover to App.xaml.cs, the data context class and the view model class which will allow us to implement a button click event to demonstrate pulling all three together in a cohesive unit.

// Data Context Layer
using System;
using System.ComponentModel;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace WP7LDBStorage.Model
{
public class DataContextClass : DataContext
{
// Pass the connection string to the base class.
public DataContextClass(string connectionString)
: base(connectionString)
{ }

// Specify a table for the items.
public Table<Information> Clients;
}

The first thing that we want to do is create a data context. To do this we need to create a class within our project. For my class name, I chose to name it DataContextClass.cs. Once the class is created, we can erase the using statements that are unneeded and add in the ones above. It ia important to note that the using statements focusing on LINQ will be key in having this project be successful and functional. Once our using statements are defined, we can then create the base class or the constructor to call when we need to use methods later on in the code. This constructor will take a string parameter and use this as a connection string. We also need to define which table we are going to use to store all of our items in. In this case, I chose to use a table called Information which we will define below.

[Table]
public class Information : INotifyPropertyChanged, INotifyPropertyChanging
{
// Define ID: Private field, public property, and database column
private int _informationID;

[Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
public int InformationID
{
get { return _informationID; }
set
{
if (_informationID != value)
{
NotifyPropertyChanging("InformationID");
_informationID = value;
NotifyPropertyChanged("InformationID");
}
}
}

// Define item name: private field, public property, and database column
private string _firstName;

[Column]
public string FirstName
{
get { return _firstName; }
set
{
if (_firstName != value)
{
NotifyPropertyChanging("FirstName");
_firstName = value;
NotifyPropertyChanged("FirstName");
}
}
}

// Define item name: private field, public property, and database column
private string _middleName;

[Column]
public string MiddleName
{
get { return _middleName; }
set
{
if (_middleName != value)
{
NotifyPropertyChanging("MiddleName");
_middleName = value;
NotifyPropertyChanged("MiddleName");
}
}
}

// Define item name: private field, public property, and database column
private string _lastName;

[Column]
public string LastName
{
get { return _lastName; }
set
{
if (_lastName != value)
{
NotifyPropertyChanging("LastName");
_lastName = value;
NotifyPropertyChanged("LastName");
}
}
}

// Define item name: private field, public property, and database column
private string _address1;

[Column]
public string Address1
{
get { return _address1; }
set
{
if (_address1 != value)
{
NotifyPropertyChanging("Address1");
_address1 = value;
NotifyPropertyChanged("Address1");
}
}
}

// Define item name: private field, public property, and database column
private string _address2;

[Column]
public string Address2
{
get { return _address2; }
set
{
if (_address2 != value)
{
NotifyPropertyChanging("Address2");
_address2 = value;
NotifyPropertyChanged("Address2");
}
}
}

// Define item name: private field, public property, and database column
private string _city;

[Column]
public string City
{
get { return _city; }
set
{
if (_city != value)
{
NotifyPropertyChanging("City");
_city = value;
NotifyPropertyChanged("City");
}
}
}

// Define item name: private field, public property, and database column
private string _province;

[Column]
public string Province
{
get { return _province; }
set
{
if (_province != value)
{
NotifyPropertyChanging("Province");
_province = value;
NotifyPropertyChanged("Province");
}
}
}

// Define item name: private field, public property, and database column
private string _postalCode;

[Column]
public string PostalCode
{
get { return _postalCode; }
set
{
if (_postalCode != value)
{
NotifyPropertyChanging("PostalCode");
_postalCode = value;
NotifyPropertyChanged("PostalCode");
}
}
}

// Define item name: private field, public property, and database column
private string _country;

[Column]
public string Country
{
get { return _country; }
set
{
if (_country != value)
{
NotifyPropertyChanging("Country");
_country = value;
NotifyPropertyChanged("Country");
}
}
}

// Define item name: private field, public property, and database column
private string _phone;

[Column]
public string Phone
{
get { return _phone; }
set
{
if (_phone != value)
{
NotifyPropertyChanging("Phone");
_phone = value;
NotifyPropertyChanged("Phone");
}
}
}

// Define item name: private field, public property, and database column
private string _email;

[Column]
public string Email
{
get { return _email; }
set
{
if (_email != value)
{
NotifyPropertyChanging("Email");
_email = value;
NotifyPropertyChanged("Email");
}
}
}

// Version column aids update performance.
[Column(IsVersion = true)]
private Binary _version;

In the lengthy bit of code above is our table definition as defined by a public class with “[table]” above the class definition. Within the class we define getters and setters and attach a “[column]” tag to the top of each one. The first column header is the most complicated with it being coded as “[Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = “INT NOT NULL Identity”, CanBeNull = false, AutoSync = AutoSync.OnInsert)]”. This tells LINQ that we need to set this as a primary key, we need to not have the value be null, it will be generated by the database and that it will autosync on insert. In the setter method we have a NotifyPropertyChanging and NotifyPropertyChanged in between where set a value to our column object. We will cover those below. The final column we add is the , Version column which is a Binary object and will aid in the update performance.

#region INotifyPropertyChanged Members

public event PropertyChangedEventHandler PropertyChanged;

// Used to notify that a property changed
private void NotifyPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}

#endregion

#region INotifyPropertyChanging Members

public event PropertyChangingEventHandler PropertyChanging;

// Used to notify that a property is about to change
private void NotifyPropertyChanging(string propertyName)
{
if (PropertyChanging != null)
{
PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
}
}

#endregion
}
}

Above we have two methods that are vital to our column setters and they are NotifyPropertyChanging and NotifyPropertyChanged. NotifyPropertyChanging is used for when the property is about to be changed for the transaction. We then change the value and pass the NotifyPropertyChanged object to the setter to let the setter know the transaction has completed successfully. This will complete the data context layer we have made and we can now move onto the View Model Layer.


// View Model Layer
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;

// Directive for the Data Model
using WP7LDBStorage.Model;

namespace WP7LDBStorage.ViewModel
{
public class ViewModelClass : INotifyPropertyChanged
{
// Linq to SQL data context for the local database.
private DataContextClass clientInfoDB;

// Class constructor, create the data context object
public ViewModelClass(string clientInfoDBConnectionString)
{
clientInfoDB = new DataContextClass(clientInfoDBConnectionString);
}

For the View Model we are going to make sure we have the using statements in place for the LINQ to SQL to be effective. We also want to include a using statement to include our data context layer by included “using WP7LDBStorage.Model;” under the rest of our using statements. We then can extend our public ViewModelClass by using “: INotifyPropertyChanged”. After this we need to create a LINQ to SQL data context for the local database and we need to make it private and name it clientInfoDB. Then we can make a public class constructor of ViewModelClass that will accept a string parameter. Inside the class constructor we will make the private data context we created equal to the string parameter in the class constructor.

// All Client Information Items
private ObservableCollection<Information> _allClientInfoItems;
public ObservableCollection<Information> AllClientInfoItems
{
get { return _allClientInfoItems; }
set
{
_allClientInfoItems = value;
NotifyPropertyChanged("AllClientInfoItems");
}
}

Above we are creating a private ObservableCollection based off of the table “Information” and it will be called “_allClientInformation”. The public ObservableCollection will use the same table and will be called “AllClientInfoItems” and we can make the getter return our private ObservableCollection. The setter will be the private ObserableCollection equal to the value we give it. We then can then use NotifyPropertyChanged for AllClientInfoItems.

public void SaveChangesToDB()
{
clientInfoDB.SubmitChanges();
}

// Query database and load the collection and list used by the pivot pages
public void LoadCollectionsFromDatabase()
{
// Specifiy the query for all Client Info Items in the database
var ClientInfoInDB = from Information info in clientInfoDB.Clients
select info;

// Query the database and load all the Client Information Items
AllClientInfoItems = new ObservableCollection(ClientInfoInDB);
}

In the above code we are using the method “SaveChangesToDB()” to save the changes, additions, or deletions we make to to our ObservableCollection. In the next method, we are going to query the database and load all client information items. You will see that the first line in our method to load all items that we are using a query to do it without actually typing any query code. This is because we are using LINQ to SQL to satisfy this requirement. In the next line of code we are handing our query results to the setter in our public ObservableCollection “AllClientInfoItems”. After this is done we can move on to the next part.

// Add Client Information Item to the database and collection
public void AddClientInfoItem(Information newClientInfoItem)
{
// Add a client info item to the data context.
clientInfoDB.Clients.InsertOnSubmit(newClientInfoItem);

// Save changes to the database
clientInfoDB.SubmitChanges();

// Add a client info item to the "all" observable collection.
AllClientInfoItems.Add(newClientInfoItem);
}

// Remove a client info item from the database and collection
public void DeleteClientInfoItem(Information clientInfoForDelete)
{
// Remove the client info item from the "all" observable collection.
AllClientInfoItems.Remove(clientInfoForDelete);

// Remove the client info item from the data context.
clientInfoDB.Clients.DeleteOnSubmit(clientInfoForDelete);

// Save changes to the database.
clientInfoDB.SubmitChanges();
}

#region INotifyPropertyChanged Members

public event PropertyChangedEventHandler PropertyChanged;

// Used to notify Silverlight that a property has changed.
private void NotifyPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
#endregion
}
}

In the code above we finish up our ViewModelClass by adding the ability to add a single information item to the database and ObservableCollection as well as the ability to remove an information item from both places. To get started we will begin with adding a single information item. The first line of code we are going to call is to submit a completed Information item that is accepted in the parameters to the database using the “InsertOnSubmit()” method that LINQ to SQL provides to us. We then call “SubmitChanges()” to complete the transaction and we can then add the item to the ObservableCollection so we are able to use it in our current session if we needed to without having to reload our entire database into the ObservableCollection again. The next method we will tackle is removing an item from the database by accepting a single Information item that will erase the row that contains the matching data. We then pass that item to our ObservableCollection for deletion and then to our database for the same to happen. We then want to use our “SubmitChanges()” method to save what we have done. All there is left to do for this class now is code the NotifyPropertyChanged method which can be copied from the previous class we coded.

// App.xaml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Phone.Controls;
using Microsoft.Phone.Shell;

// Directives
using WP7LDBStorage.Model;
using WP7LDBStorage.ViewModel;

namespace WP7LDBStorage
{
public partial class App : Application
{
// The Static ViewModel, to be used across our application.
private static ViewModelClass viewModel;
public static ViewModelClass ViewModel
{
get
{
return viewModel;
}
}

We can now move onto our App.xaml and more specifically, the .cs file behind the App.xaml. We need to add and modify some parts of the file so we are able to use our ViewModel we created throughout the whole application. The first thing we need to do is insert a using statement for the Data Context and the View Model classes we created. After this is completed we can create a private static variable of viewModel to be used accross the entire app. To accompany this, we will need a public ViewModel variable with just a getter included to return our private static view model.

/// <summary>
/// Provides easy access to the root frame of the Phone Application.
/// </summary>
/// <returns>The root frame of the Phone Application.</returns>
public PhoneApplicationFrame RootFrame { get; private set; }

/// <summary>
/// Constructor for the Application object.
/// </summary>
public App()
{
// Global handler for uncaught exceptions.
UnhandledException += Application_UnhandledException;

// Standard Silverlight initialization
InitializeComponent();

// Phone-specific initialization
InitializePhoneApplication();

// Show graphics profiling information while debugging.
if (System.Diagnostics.Debugger.IsAttached)
{
// Display the current frame rate counters.
Application.Current.Host.Settings.EnableFrameRateCounter = true;

// Show the areas of the app that are being redrawn in each frame.
//Application.Current.Host.Settings.EnableRedrawRegions = true;

// Enable non-production analysis visualization mode,
// which shows areas of a page that are handed off to GPU with a colored overlay.
//Application.Current.Host.Settings.EnableCacheVisualization = true;

// Disable the application idle detection by setting the UserIdleDetectionMode property of the
// application's PhoneApplicationService object to Disabled.
// Caution:- Use this under debug mode only. Application that disables user idle detection will continue to run
// and consume battery power when the user is not using the phone.
PhoneApplicationService.Current.UserIdleDetectionMode = IdleDetectionMode.Disabled;
}

The above code is standard and is generated when we create our project. This is of no use to us for our local database storage and so we will move past it but by all means feel free to peruse and google what use this code may have for you in the future.

// Specify the local database connection string
string DBConnectionString = "Data Source=isostore:/Clients.sdf";

// Create the database if it does not exist
using (DataContextClass db = new DataContextClass(DBConnectionString))
{
if(db.DatabaseExists() == false)
{
// Create the local database
db.CreateDatabase();

db.SubmitChanges();
}
}

// Create the ViewModel Object
viewModel = new ViewModelClass(DBConnectionString);

// Query the local database and load observable collections
viewModel.LoadCollectionsFromDatabase();

}

In the code above we are doing a couple different things but the first is to specify the local database connection string. We do this by making “Data Source=isostore:/Clients.sdf” equal to our string. Basically what the string is telling the connection is that the data source can be found in Isolated Storage of the application under the SDF file we entered above. We are going to then check and see if a database in the applications Isolated Storage exists with the same name and if it does not we want to create the database and then submit the changes. After this we can create the ViewModel object referencing our our connection string as our parameter foe the ViewModel to accept. We then are going to query the database and load the results into the viewModel variable we created.

// Code to execute when the application is launching (eg, from Start)
// This code will not execute when the application is reactivated
private void Application_Launching(object sender, LaunchingEventArgs e)
{
}

// Code to execute when the application is activated (brought to foreground)
// This code will not execute when the application is first launched
private void Application_Activated(object sender, ActivatedEventArgs e)
{
}

// Code to execute when the application is deactivated (sent to background)
// This code will not execute when the application is closing
private void Application_Deactivated(object sender, DeactivatedEventArgs e)
{
}

// Code to execute when the application is closing (eg, user hit Back)
// This code will not execute when the application is deactivated
private void Application_Closing(object sender, ClosingEventArgs e)
{
}

// Code to execute if a navigation fails
private void RootFrame_NavigationFailed(object sender, NavigationFailedEventArgs e)
{
if (System.Diagnostics.Debugger.IsAttached)
{
// A navigation has failed; break into the debugger
System.Diagnostics.Debugger.Break();
}
}

// Code to execute on Unhandled Exceptions
private void Application_UnhandledException(object sender, ApplicationUnhandledExceptionEventArgs e)
{
if (System.Diagnostics.Debugger.IsAttached)
{
// An unhandled exception has occurred; break into the debugger
System.Diagnostics.Debugger.Break();
}
}

#region Phone application initialization

// Avoid double-initialization
private bool phoneApplicationInitialized = false;

// Do not add any additional code to this method
private void InitializePhoneApplication()
{
if (phoneApplicationInitialized)
return;

// Create the frame but don't set it as RootVisual yet; this allows the splash
// screen to remain active until the application is ready to render.
RootFrame = new PhoneApplicationFrame();
RootFrame.Navigated += CompleteInitializePhoneApplication;

// Handle navigation failures
RootFrame.NavigationFailed += RootFrame_NavigationFailed;

// Ensure we don't initialize again
phoneApplicationInitialized = true;
}

// Do not add any additional code to this method
private void CompleteInitializePhoneApplication(object sender, NavigationEventArgs e)
{
// Set the root visual to allow the application to render
if (RootVisual != RootFrame)
RootVisual = RootFrame;

// Remove this handler since it is no longer needed
RootFrame.Navigated -= CompleteInitializePhoneApplication;
}

#endregion
}
}

Again, above is standard generated code but may hold use for you in the future.

// MainPage.xaml.cs
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using Microsoft.Phone.Controls;

// Directive for the data model
using WP7LDBStorage.Model;

namespace WP7LDBStorage
{
public partial class MainPage : PhoneApplicationPage
{
// Constructor
public MainPage()
{
InitializeComponent();

this.DataContext = App.ViewModel;
}

We are now in the .cs file of our MainPage.xaml and can start looking to implement our local database storage. The first thing to do is include a using statement for the data model and then set your cursor in the constructor of the page. Once there, we add “this.DataContext = App.ViewModel;” under “InitializeComponent()L to set the data context to the view model we implemented in the App.xaml.cs file.

private void btnSubmit_Click(object sender, EventArgs e)
{
if (txtFirstName.Text.Length > 0)
{
// Create client item
Information newClientInfo = new Information
{
FirstName = txtFirstName.Text,
MiddleName = txtMiddleName.Text,
LastName = txtLastName.Text,
Address1 = txtAddress1.Text,
Address2 = txtAddress2.Text,
City = txtCity.Text,
Province = txtProvince.Text,
PostalCode = txtPostalCode.Text,
Country = txtCountry.Text,
Phone = txtPhone.Text,
Email = txtEmail.Text
};

App.ViewModel.AddClientInfoItem(newClientInfo);

MessageBox.Show("Data Added Successfully!");

btnClear_Click(sender, e);
}
}

We are now going to utilize the local database storage by actually adding an item. To do so we need to check and see if the first text box “txtFirstName” is not empty and if it is not, we may proceed. We start out by making a client item to be inserted into the database and observable collection. Within this declaration, we need to set all of the columns to their respective text fields. We then use the AddClientInfoItem method and insert the newClientInfo into the parameter to tell the method this is what we would like to add to the database and observable collection. After the data has been added, we throw a MessageBox to the screen to let the user know the data has been entered successfully. After the user presses below we can call our btnClear_Click event handler to wipe the slate clean as we have the information added in our local database. We can also use the click event handler for one of our menu items to clear the screen all at once if the user wishes.

private void btnClear_Click(object sender, EventArgs e)
{
txtFirstName.Text = "";
txtMiddleName.Text = "";
txtLastName.Text = "";
txtAddress1.Text = "";
txtAddress2.Text = "";
txtCity.Text = "";
txtProvince.Text = "";
txtPostalCode.Text = "";
txtCountry.Text = "";
txtPhone.Text = "";
txtEmail.Text = "";
}

private void btnNumber_Click(object sender, EventArgs e)
{
MessageBox.Show("Total number of entries: " + App.ViewModel.AllClientInfoItems.Count);
}

private void mItemAbout_Click(object sender, EventArgs e)
{
NavigationService.Navigate(new Uri("/About.xaml", UriKind.Relative));
}
}
}

The code above uses a click event handler called btnNumber_Click to throw a Messagebox onto the screen with the total number of entries within our ObservableCollection and in our database. To do this we just have to append “.count()” to our “AllClientInfoItems” query and it will return the entries count. The next event handler is used to transfer the user to an about page when a menu item is clicked.

With all of the code completed to make a local database in isolated storage for this app you should now have a solid reference on how to proceed. If you are looking to enhance your knowledge in this section, feel free to go back to our Isolated Storage example and recode the example to use local database storage. If you are able to do this then you know you have a firm understanding of the concepts discussed in this post. Below you can find a gallery of the HTC Titan X310e running with Local Database Storage. Until the next Development tutorial, Happy Hacking!

[nggallery id=6]

Share This