The return of SQL CE !

11 August 2011 Michael-Van-Wesemael

After playing a few weeks with the Mango-beta, I’m even more convinced that the Windows Phone 7 simply is the best smartphone available. Driving in your car, receiving a text-message, and suddenly a voice over Bluetooth  asks me if the message should be read! And after the message has been read, it asks if I would like to dictate an answer (or call). How cool is that ? (unfortunately only in English for the moment, so when trying to dictate in Dutch, you get funny results ).

One of the things that Mango brings is SQL Server CE. I remember using this on Windows Mobile, when you had to use stuff like SqlCeConnection, SqlCeCommand, etc… It seems that these classes are not existing on Mango, but of course we have Linq to SQL we can use. Using IsolatedStorageFile’s for storing data is nice, but having a “real” database for storing relational data is nicer. Still, during my experiment I saw that there are a few things missing to make life easier. It’s still in beta of course, so hopefully it will get added in the near future.

I wanted to do a little trial showing the products and Categories out of the NorthWind-database. I decided to use SQL Integration Services for creating a SQL CE database for me, copying the needed data. Unfortunately this is not possible for CE. I needed to create the database and the tables myself. In the beta-version of Mango developer-tools it is yet impossible to select a SQLCE-file for item to add. I simply created it in a dummy console-app, and then copied it into my WP7-application. Double clicking on the sdf-file opens up the designer.

image

Adding a table is easy: Right-click the Tables-folder, select “Create Table” and you can start designing. This is what I created :

image

Specifying the Primary Key and the relationship between Products and Categories can be created by calling the table properties:

image

So far for the database structure. Copying the data from the “normal” Northwind to my CE-tables can be done through SQL Integration services. Now what about the LINQ to SQL classes ? No way of adding such an item in my project. The way to go, is like in the beginning of LINQ: use SQLMetal (You’ll find it here : C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin). The following command generates the necessary code:

Sqlmetal /code:NorthWindCE.cs Northwind.sdf

 

The generated code look nice, but if your looking for any navigation-properties: nowhere to find…

Next roadblock: I can only access databases stored in isolatedstorage. Makes sense, although it would be nice to be able to share data over multiple apps. Since there is no direct way of adding files for IsolatedStorage in VisualStudio, I wrote a bit of code for copying my sdf-file (which has been added as a resource) to IsolatedStorage:

   1:  private void CheckIsolatedStorageDb()
   2:  {
   3:      var storage = IsolatedStorageFile.GetUserStoreForApplication();
   4:      if (!storage.FileExists("NorthWind.sdf"))
   5:      {
   6:          Uri uri = new Uri("SQLTest;component/NorthWind.sdf",UriKind.Relative);
   7:          StreamResourceInfo info = App.GetResourceStream(uri);
   8:   
   9:          if (info!=null)
  10:          {
  11:              Stream stream = info.Stream;
  12:              BinaryReader reader = new BinaryReader(stream);
  13:              byte[] buffer = new byte[stream.Length];
  14:              reader.Read(buffer, 0, Convert.ToInt32(stream.Length));
  15:              reader.Dispose();
  16:   
  17:              IsolatedStorageFileStream iStream= storage.CreateFile("NorthWind.sdf");
  18:              iStream.Write(buffer, 0, buffer.Length);
  19:              iStream.Flush();
  20:              iStream.Dispose();
  21:              stream.Dispose();
  22:          }
  23:      }
  24:  }

 

I call this function on Application_launch, and my sdf-file gets copied.  From now on I can start using my db, using LINQ. Let’s start by adding a listbox to our MainPage like this:

<ListBox DisplayMemberPath="ProductName" ItemsSource="{Binding}" />

 

Code looks like this :

private void PhoneApplicationPage_Loaded(object sender, RoutedEventArgs e)
{
    db = new NorthWind("Data Source=isostore:/NorthWind.sdf");
 
    this.DataContext = from p in db.Products
                       where p.UnitPrice < 20
                       select p;    
}

As you can see, I provide a connectionstring pointing to Isolated storage, binding the datacontext to some LINQ-statement, and voila.

Now what about navigating between categories and products ? First I extend my SQLmetal-generated code with these (copied these by generating code for the “big” Northwind) :

In Products:

   1:  private EntityRef<Categories> _Category;
   2:   
   3:  [global::System.Data.Linq.Mapping.AssociationAttribute(Name = "Category_Product", Storage = "_Category",
   4:      ThisKey = "CategoryID", OtherKey = "CategoryID", IsForeignKey = true)]
   5:  public Categories Category
   6:  {
   7:      get
   8:      {
   9:          return this._Category.Entity;
  10:      }
  11:      set
  12:      {
  13:          Categories previousValue = this._Category.Entity;
  14:          if (((previousValue != value)
  15:                      || (this._Category.HasLoadedOrAssignedValue == false)))
  16:          {
  17:              this.SendPropertyChanging();
  18:              if ((previousValue != null))
  19:              {
  20:                  this._Category.Entity = null;
  21:                  previousValue.Products.Remove(this);
  22:              }
  23:              this._Category.Entity = value;
  24:              if ((value != null))
  25:              {
  26:                  value.Products.Add(this);
  27:                  this._CategoryID = value.CategoryID;
  28:              }
  29:              else
  30:              {
  31:                  this._CategoryID = default(Nullable<int>);
  32:              }
  33:              this.SendPropertyChanged("Category");
  34:          }
  35:      }
  36:  }

And in Categories:

   1:  private EntitySet<Products> _Products;
   2:   
   3:  [global::System.Data.Linq.Mapping.Association(Name="Category_Product", Storage="_Products",
   4:      ThisKey="CategoryID", OtherKey="CategoryID")]
   5:  public EntitySet<Products> Products 
   6:  {
   7:      get { return this._Products; }
   8:      set { this._Products.Assign(value); }
   9:  }

 

  • Let’s now change our MainPage a little: I’ll have two Listboxes this time:
<ListBox DisplayMemberPath="CategoryName" Name="listBox2" ItemsSource="{Binding}" />
<ListBox Grid.Row="1" Name="listBox1" DataContext="{Binding ElementName=listBox2, Path=SelectedItem}"
         DisplayMemberPath="ProductName" ItemsSource="{Binding Products}" />

 

Also set the datacontext to categories this time :

private void PhoneApplicationPage_Loaded(object sender, RoutedEventArgs e)
{
    db = new NorthWind("Data Source=isostore:/NorthWind.sdf");
 
    this.DataContext = db.Categories;            
}

And thanks to the powers of databinding, I get all products from the category I select.

image

Now I’m already happy to have this in the beta, but I’d like to have some extras in the final version:

  • - Give me an easy way of copying existing structures to my CE database
  • - Get me a way of creating my LINQ to SQL classes from within Visual Studio
  • - Make sure that my Navigation properties are being generated
  • - and give me something, so I can specify files to be directly saved in Isolated Storage. (Some Build Action ? Next to Resource, Content,… also an option IsolatedStorage ?)

Tags :