In Windows Phone is very simple to add and use SQL CE database.
What you need? Nothing... it's built in the Windows Phone Runtime.
Let's start...
If not exists, add the reference
System.Data.Linq in your project.
Create the class to map the entity to the table:
[Table(Name="Utenti")]
public class Utente
{
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int Id { get; set; }
[Column(CanBeNull = false)]
public string Name { get; set; }
}
You
can see the attributes for the table and columns: table name, primary key, etc..
Now add a class for
DataContext with the reference to the table
Utenti. The
DataContext wraps all operations you can do with database:
public class MyDataContext: DataContext
{
public const string ConnectionString = "isostore:/mydatabase.sdf";
public Table<Utente> Utenti { get; set; }
public MyDataContext(string connectionString)
: base(connectionString)
{
this.Utenti = this.GetTable<Utente>();
}
}
ConnectionString has the special path to locate the database file in the isolated storage.
The classes for SQL CE are ready.
The last thing is create the physical
.sdf file when the application start.
private void Application_Launching(object sender, LaunchingEventArgs e)
{
using (var context = new MyDataContext(MyDataContext.ConnectionString))
{
if (!context.DatabaseExists())
context.CreateDatabase();
}
}
And now enjoy with your LinqToSql queries!
using (var context = new MyDataContext(MyDataContext.ConnectionString))
{
var utenti = context.Utenti.OrderByDescending(u => u.Name).ToList();
}
Remember the "using" statement, because you need to dispose your DataContext to prevent high memory usage. The cost to create a new istance is very small.
NOTE: if you want to build an application for Windows 8/RT, maybe SQL CE is not your way. Why? Unfortunately Windows 8/RT don't support it. In this case you can use SQLITE.
