Easy SQL CE on Windows Phone

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.