From version 7.5 (Mango) WP7 runs a version of SQL Server CE which is all setup waiting for your applications to make use of it. The good news is that this couldn’t be easier. One note though the phone does not include ADO.Net so the only way to query the database is via LinqToSql not that this is a problem.
Lets run through a quick example of how this works…
Create a new Windows Phone Application project in Visual Studio making sure to set the Windows Phone OS version to 7.1
Add a reference to System.Data.Linq.
Next we need to define our model, create a Models folder in your project and add a class called Film to it. Edit your new Film class to look like this….
using System;
using System.Data.Linq.Mapping;
namespace DatabasesExample.Models
{
[Table]
public class Film
{
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int Id { get; set; }
[Column(CanBeNull=false)]
public string Name { get; set; }
[Column]
public DateTime ReleaseDate { get; set; }
[Column]
public string Description { get; set; }
}
}
|
You can see we are making use of the Linq Mapping attributes in order to describe how the table schema should look. We then need to create a DataContext class, to do this create a new class in Models called MyContext and make it look like this…..
using System.Data.Linq;
namespace DatabasesExample.Models
{
public class MyContext : DataContext
{
public Table<film> Films;
public MyContext() : base("DataSource=isostore:/MyDatabase.sdf;") { }
}
}
|
You can change MyDatabase to be whatever name you want your database to be stored under in Isolated Storage. The last thing that needs to be done before we can start accessing our database is to tell the application to physically create the database. This can be done by adding the following code to your main App.cs constructor…
using (var myContext = new MyContext())
{
if (!myContext.DatabaseExists())
myContext.CreateDatabase();
}
|
On start your application will then check if the database exists, if it doesn’t it will create it.
We can now access it using LinqToSql, lets say we wanted to add a new film to the films table…
var myContext = new MyContext();
var ConAir = new Film { Name="Con Air", Description="Planes And Guns", ReleaseDate = new DateTime(1997,6,2) };
myContext.Films.InsertOnSubmit(ConAir);
myContext.SubmitChanges();
|
Or if we wanted to get a list of all the films…
var myContext = new MyContext();
var films = (from film in myContext.Films select film).ToList() ;
|
Schema Modifications
Unfortunately updating a tables schema or adding new tables isn’t as simple as just changing the model or the context. The best way to achieve this is to version your database and use the DatabaseSchemaUpdater object. For this example lets assume the database in its current state is version 0 (The default version) and we are working on changes to make it version 1. Lets assume we have created a new MusicAlbum class much like our Film class and updated our MyContext to have a second table called MusicAlbums. In your main app.cs class add the following
private int dbVersion = 1;
|
Now we need to modify our DB creation code to look like this….
using (var myContext = new MyContext())
{
if (myContext.DatabaseExists() == false)
{
myContext.CreateDatabase();
DatabaseSchemaUpdater dbUpdater = myContext.CreateDatabaseSchemaUpdater();
dbUpdater.DatabaseSchemaVersion = dbVersion;
dbUpdater.Execute();
}
else
{
DatabaseSchemaUpdater dbUpdater = myContext.CreateDatabaseSchemaUpdater();
if (dbUpdater.DatabaseSchemaVersion < dbVersion)
{
if (dbUpdater.DatabaseSchemaVersion < 1)
dbUpdater.AddTable<musicalbum>();
dbUpdater.DatabaseSchemaVersion = dbVersion;
dbUpdater.Execute();
}
}
}
|
The default version for a database schema is zero and as we never specified a version when we first created the database that is what version our database currently is. So if you run the application with the above changes (Assuming you have created a MusicAlbum class and updated the MyContext class to have the new table) then the new MusicAlbum table will get created as soon as it launches. The DatabaseSchemaUpdater class also has methods for AddIndex and AddColumn. When adding columns you need to either make the column nullable or set a default value so existing records can be populated. This can be done by using attributes on the property like this…
[Column(DbType = "int DEFAULT 0 NOT NULL ")]
public int Rating{get;set;}
|
One thing to note is that in the current version there is no way for the DatabaseSchemaUpdater to remove columns so be a bit careful with what you create.
Ref:
