Local LINQtoSQL Database For Your Windows Phone 7 Application
- by Tim Murphy
There aren’t many applications that are of value without having some for of data store. In Windows Phone development we have a few options. You can store text directly to isolated storage. You can also use a number of third party libraries to create or mimic databases in isolated storage. With Mango we gained the ability to have a native .NET database approach which uses LINQ to SQL. In this article I will try to bring together the components needed to implement this last type of data store and fill in some of the blanks that I think other articles have left out. Defining A Database The first things you are going to need to do is define classes that represent your tables and a data context class that is used as the overall database definition. The table class consists of column definitions as you would expect. They can have relationships and constraints as with any relational DBMS. Below is an example of a table definition. First you will need to add some assembly references to the code file. using System.ComponentModel;using System.Data.Linq;using System.Data.Linq.Mapping;
You can then add the table class and its associated columns. It needs to implement INotifyPropertyChanged and INotifyPropertyChanging. Each level of the class needs to be decorated with the attribute appropriate for that part of the definition. Where the class represents the table the properties represent the columns. In this example you will see that the column is marked as a primary key and not nullable with a an auto generated value.
You will also notice that the in the column property’s set method It uses the NotifyPropertyChanging and NotifyPropertyChanged methods in order to make sure that the proper events are fired.
[Table]public class MyTable: INotifyPropertyChanged, INotifyPropertyChanging{ 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)); } } private int _TableKey; [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)] public int TableKey { get { return _TableKey; } set { NotifyPropertyChanging("TableKey"); _TableKey = value; NotifyPropertyChanged("TableKey"); } }
The last part of the database definition that needs to be created is the data context. This is a simple class that takes an isolated storage location connection string its constructor and then instantiates tables as public properties.
public class MyDataContext: DataContext{ public MyDataContext(string connectionString): base(connectionString) { MyRecords = this.GetTable<MyTable>(); } public Table<MyTable> MyRecords;}
Creating A New Database Instance
Now that we have a database definition it is time to create an instance of the data context within our Windows Phone app. When your app fires up it should check if the database already exists and create an instance if it does not. I would suggest that this be part of the constructor of your ViewModel.
db = new MyDataContext(connectionString);if(!db.DatabaseExists()){ db.CreateDatabase();}
The next thing you have to know is how the connection string for isolated storage should be constructed. The main sticking point I have found is that the database cannot be created unless the file mode is read/write. You may have different connection strings but the initial one needs to be similar to the following.
string connString = "Data Source = 'isostore:/MyApp.sdf'; File Mode = read write";
Using you database
Now that you have done all the up front work it is time to put the database to use. To make your life a little easier and keep proper separation between your view and your viewmodel you should add a couple of methods to the viewmodel. These will do the CRUD work of your application. What you will notice is that the SubmitChanges method is the secret sauce in all of the methods that change data.
private myDataContext myDb;private ObservableCollection<MyTable> _viewRecords;public ObservableCollection<MyTable> ViewRecords{ get { return _viewRecords; } set { _viewRecords = value; NotifyPropertyChanged("ViewRecords"); }}public void LoadMedstarDbData(){ var tempItems = from MyTable myRecord in myDb.LocalScans select myRecord; ViewRecords = new ObservableCollection<MyTable>(tempItems);}public void SaveChangesToDb(){ myDb.SubmitChanges();}public void AddMyTableItem(MyTable newScan){ myDb.LocalScans.InsertOnSubmit(newScan); myDb.SubmitChanges();}public void DeleteMyTableItem(MyTable newScan){ myDb.LocalScans.DeleteOnSubmit(newScan); myDb.SubmitChanges();}
Updating existing database
What happens when you need to change the structure of your database? Unfortunately you have to add code to your application that checks the version of the database which over time will create some pollution in your codes base. On the other hand it does give you control of the update. In this example you will see the DatabaseSchemaUpdater in action. Assuming we added a “Notes” field to the MyTable structure, the following code will check if the database is the latest version and add the field if it isn’t.
if(!myDb.DatabaseExists()){ myDb.CreateDatabase();}else{ DatabaseSchemaUpdater dbUdater = myDb.CreateDatabaseSchemaUpdater(); if(dbUdater.DatabaseSchemaVersion < 2) { dbUdater.AddColumn<MyTable>("Notes"); dbUdater.DatabaseSchemaVersion = 2; dbUdater.Execute(); }}
Summary
This approach does take a fairly large amount of work, but I think the end product is robust and very native for .NET developers. It turns out to be worth the investment.
del.icio.us Tags: Windows Phone,Windows Phone 7,LINQ to SQL,LINQ,Database,Isolated Storage
