Derik Whittaker

Syndication

News


Using SQL CE on WP7 Mango–Getting Started

One of the major new ‘features’ of WP7 Mango is the addition of SQL CE 3.5 support.  Prior to Mango CE was loaded on the phone but 3rd party applications (the ones you and I write) were not able to access it.  With Mango this has all changed and CE is not a full first class citizen.  If you have never used CE before (myself included) than you will find it a bit different to use, but this is mostly due to the way you have to setup and define your tables via code. 

In this post I will walk you though the steps on how to setup a very simple database on CE.  In future posts we will dive deeper and learn how to use some of CE’s more complex features.

Step 1: Installing SQL CE via Nuget

Before we start writing a line of code we need to install/reference the SQL CE assemblies.  The easiest way is to open the Package Manager Console for Nuget and type Install-Package SqlServerCompact.  This will download the latest CE assemblies and add them to your project. 

Your Nuget window should look like:
image

Once you have SQL CE installed you should have the following references added to your project:
image

Ok, now you are done with the setup and you can start coding away.

Step 2: Creating and setting up the Data Context

Per MSDN the DataContext is

The data context is a proxy, an object that represents the database. A data context contains Table objects, each of which represents a table in the database. Each Table object is made up of entities that correspond to rows of data in a database. Each entity is a “plain old CLR object” (POCO) with attributes. The attributes on each entity determine the database table structure and define the mapping between the object model of the data and the schema of the database. For example, an entity having Name and PhoneNumber properties would result in a database table having Name and PhoneNumber columns.

In short this is your 'Database In Code’.  You will need to create this and have it in memory for the duration of the run of your application.  Below is the code we are going to use to create our data context

using System.Data.Linq;

public class DimecastsDataContext : DataContext
{
    public DimecastsDataContext() : base("Data Source=isostore:/DimecastsData.sdf")
    {

    }

    public Table<Episode> Episodes;

}

You will notice that my context class is very small, this is a good thing.  There are a few items in this class which are worth pointing out

  1. The ‘Connection String’ – This is needed in order to tell CE where to find the data stored in isolated storage.  In my example I am hard wiring it into the constructor call.
  2. The Table<ENTITY> field – This tells the Data Context that there is going to be a table defined, and accessed, which is of type Episode.  This is required.  If you fail to create this you will receive a SQLCEException – Table does not exist error
  3. No place in code will you set a pointer to the Episodes ‘table’ this is done via black magic by the DataContext.  Its odd I know but trust me it just works.

Step 3: Creating our entity which represents our Table

Before we can actually start creating any ‘real’ code we must define an entity (Episode) which represents our table structure.

using System.Data.Linq;
using System.Data.Linq.Mapping;

[Table]
public class Episode
{
    [Column(IsPrimaryKey = true, IsDbGenerated = false, DbType = "INT NOT NULL", CanBeNull = false)]
    public Int32 Id { get; set; }

    [Column]
    public Int32 Number { get; set; }

    [Column]
    public string Name { get; set; }
   
}

Lets take a look at the above class because there are a few things I would like to point out

  1. [Table] Attribute – Designates a class as an entity class that is associated with a database table.
  2. [Column] Attribute - Associates a class with a column in a database table.
    1. IsPrimaryKey Property - Represents a column that is part or all of the primary key of the table.
    2. IsDbGenerated Property – Represents whether a column contains values that the database auto-generates – I have this turned off in my example because my data is pushed from a backend server w/ the ID created
    3. DbType Property – Represents type of the database column, this is optional as the type can be inferred as well
    4. CanBeNull Property – Represents whether a column can contain null values.

As you can see this is pretty straight forward and pretty easy to setup.

Step 4: Creating and Using the DataContext to init your database
var dimecastsDataContext = new DimecastsDataContext();

if (!dimecastsDataContext.DatabaseExists())
{
    dimecastsDataContext.CreateDatabase();    
}

The code above will create an instance of our DataContext.  When we have this DataContext we can create the database (after making sure it does not already exist) by calling the .CreateDatabase().  Scan your code looking for classes w/ the [Table] marker and setup/create your db schema.

Step 5: Creating and Querying you database
var episode1 = new Episode
                    {
                        Id = 1,
                        Number = 1,
                        Name = "Episode 1"
                    };

dimecastsDataContext.Episodes.InsertOnSubmit(episode1);

dimecastsDataContext.SubmitChanges();

var foiundEpisodes = (from e in dimecastsDataContext.Episodes
                select e).ToList();

In the code above we are doing a few things. 

  1. We are creating the episode entity we want to add to the database
  2. We are adding the newly created entity to the table (.Episodes is our table pointer remember)
  3. We are calling .SubmitChanges() on our DataContext in order to save our changes
  4. We are running a Linq query to get the newly entered data out of our database.

Using Sql Ce is pretty easy, but this is ‘the simple’ case, but it is also a great jumping point to learning how to use the database.  In a future posts we will take a look at more in-depth features/usages of Sql CE on WP7.

Till next time,


Posted 07-21-2011 4:52 PM by Derik Whittaker
Filed under: , ,

[Advertisement]

Comments

ErikEJ wrote re: Using SQL CE on WP7 Mango–Getting Started
on 07-23-2011 6:22 AM

Installing SQL Server Compact 4.0 via NuGet is completely irrelevant, as Windows Phone with Linq to SQL does not need any reference to SQL Server Compct, and the version on the device is 3.5

Derik Whittaker wrote Using SQL CE On WP7 Mango–Working with Associations
on 07-23-2011 2:20 PM

In my previous post I took a step by step look at how to get up and running using SQL CE inside of your

Derik Whittaker wrote Using SQL CE On WP7 Mango–Working with Indexes
on 07-24-2011 3:58 PM

This is another post in a series of posts on how to use SQL CE on WP7 Mango.  If you would like

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)