Fourteen and a half crazy frog burpers

10th September 2010

Isolated Storage on Windows Phone 7

Filed under: C#,Databases,Windows Phone 7 — Tags: , , , — Alex Holt @ 9:30 pm

One of the most amazing omissions from the Windows Phone 7 OS is an SQL Server of some description that applications can use (I think there is a SQL Server Compact edition baked into the build, but it’s only exposed to the native apps). This leaves anyone familiar with doing all their storage via relational databases in a bit of a lurch.

Having a look around, there were a few work-arounds, namely third party libraries such as Perst & Sterling. These act as a layer of abstraction that allow you to use LINQ to access your objects and could be of benefit for some of the more complex applications. I found both of them to have their drawbacks.

Perst: From McObject is one alternative, but if you plan on doing a commercial application, you should bare in mind their licensing, of which, their FAQ says:

No, Perst is not free. McObject Perst is available under a dual license. Under the GPL, you may evaluate the source code free of charge and you may use Perst free of charge in an application for which the source code is also freely available. If you wish to use a Perst in an application but do not or cannot redistribute your application source code, you can use Perst under a commercial license.

Sterling: This seemed promising, but I had a number of issues getting the code to work (it was late, in fairness) I decided to give up.

In reality though, most applications that are going to be made don’t need any complicated database structure behind them. Database implementations are most likely going to be overkill. This is where I decided to do what probably the majority of WP7 developers have decided to do, and “Roll-Their-Own” database solution. Mine is based on a general accepted practice, using XML Serialisation to store my POCOs. While it might have limitations and performance issues later down the line, for now – it’s performing very admirably and there seemed no point in premature optimisation just yet.

My Solution

The main class I created (MyDataContext.cs) contains the functions for both saving and loading the data, as well as the ‘schema’ for the entire database.

using System;
using System.Collections.Generic;
using IsolatedExampleApp.Data.Models;
using System.IO.IsolatedStorage;
using System.IO;
using System.Xml.Serialization;
namespace IsolatedExampleApp.Data
  public class IsolatedDatabase
    public IsolatedDatabase()
      // For each model that is a list item, you need to add a initialising statement to the Constructor
      Albums = new List();
      Artists = new List();
    // These properties effectively form the publicly viewable schema of the database.
    public List Albums { get; set; }
    public List Artists { get; set; }
    public Options Options { get; set; }
    public void Load()
      using (IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication())
      using (IsolatedStorageFileStream stream = new IsolatedStorageFileStream("IsolatedExampleApp.txt", FileMode.OpenOrCreate, FileAccess.Read, store))
      using (StreamReader reader = new StreamReader(stream))
        XmlSerializer serializer = new XmlSerializer(typeof(IsolatedDatabase));
        var unserialized = reader.EndOfStream ? new IsolatedDatabase() : (IsolatedDatabase)serializer.Deserialize(reader);
        // Each schema is repopulated with information when the app is loaded.
        Albums = unserialized.Albums;
        Artists = unserialized.Artists;
        Options = unserialized.Options;
    public bool Save()
        using (IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication())
        using (IsolatedStorageFileStream stream = new IsolatedStorageFileStream("IsolatedExampleApp.txt", FileMode.Create, FileAccess.Write, store))
          XmlSerializer serializer = new XmlSerializer(typeof(IsolatedDatabase));
          serializer.Serialize(stream, this);
        return true;
      catch (Exception ex)
        return false;

As you can see from the code above, as you develop more data stores, the code needs updating in multiple locations.

  • Properties need setting for each one (they are the publicly available access points for the data).
  • Any LISTS<> require their lists initialised by the Constructor method.
  • When the data is ‘Load()’ed, each property must have it’s values manually set.
  • All the Models still need defining elsewhere in your application (in my example above, they are in the IsolatedExampleApp.Data.Models namespace.

If you can overlook this repeated typing, then this seems to be a good starting point for persisting data in your Windows Phone 7 Application. To use it should then be fairly straight forward.

_db = new IsolatedDatabase();
// Adding an album...
// Deleting an album...
// Add or Update an album...
Album existingAlbum = _db.Albums.Where(x => x.ID == album.ID).FirstOrDefault();
if (existingAlbum != null)
// Or just getting a specific album...
IEnumerable albums = _db.Albums as IEnumerable;
return albums.Where(x => x.ID == id).FirstOrDefault();

Any feedback, thoughts, options and optimisations for the above code will be well received. Happy isolating!

29th July 2009

PHP Connections to MSSQL & ntwdblib.dll

Filed under: Microsoft SQL,PHP — Tags: , — Alex Holt @ 12:40 pm

One of the systems within my workplace is a PHP5 application (via Apache on Windows) that uses MySQL5.0 as its main database. However, in addition to this, it has a requirement to an old Microsoft SQL7 Server which was running Windows NT. This machine gave up the ghost and was restored to a SQL2000 machine and it began to work again.

Except from the blasted PHP application mentioned above ! The swine!

So, after digging around and debugging and limiting down the dependencies that it could be, we established that:

  • It wasn’t MDB2 which we are using to abstract the DB connections, as mssql_connect would fail also.
  • It wasn’t a change in DB name, Server name, user, password or permissions as we could connect with identical criteria from elsewhere.
  • It wasn’t a networking error, as Query Express was able to connect when ran from the web-server itself.
  • It wasn’t remembering anything about the old server as we reset the Apache instances and rebooted the entire box to see if it would help.
  • It wasn’t the code, as we had EXACTLY the same code running against the live system from our development server and it didn’t even bat an eyelid.

Eventually, we happened across a few posts that eluded to the fact that PHP5 ships with a DLL of suckiness.

The difference between the live Apache web server, compared with the development machine was that MSSQL2000 was actually installed on the development one (along with a bunch of other crap). Because this install is in place, it meant that in C:\Windows\System32 was a working and newer copy of ntwdblib.dll .

The bottom line is that the fix is simply to replace the old version of ntwdblib.dll (which comes with PHP5, noted as version: 2000.8.2.0) and replace it with version 2000.80.194.0 Once we started the Apache instances again – BOOM! Amazingness reborn! Our web-servers could see SQL 2000 instances again! Fantastic!

5th June 2009

Multiple Subversion Projects in one Visual Studio Solution using svn:externals

Filed under: Subversion,Visual Studio — Tags: , , , — Alex Holt @ 7:05 pm

Cake mix:

400g of Visual Studio 2008
100g of fresh Subversion
1tsp of VisualSVN (1tsp of AnkhSVN subsitutes if you can’t get hold of any).
Instructions: Put into a large bowl and beat the living-poop out of it with a spike covered hammer.

Or alternatively….

I’ve been quite frustrated that no matter where I looked on the web, what I searched for, which search engine I used – all the articles I found on opening multiple projects in Visual Studio (while still using Subversion), all seemed to explain only a portion of what was needed and never the full tale. So being frustrated, I decided it’s the exact type of thing I needed to note here – even if it’s just me that ever reads it!

The Scenario:

I have a Visual Studio solution with two projects in it: Project01 which is an MVC Web Application and the associated Project01.Tests project for testing. As I want to apply to DRY principles, I created another solution with a Class Library project (and associated test project) for putting any code that might be used again outside of the scope of the current project. This causes a couple of pickles:

  • Visual Studio doesn’t let you open more than one solution, so I’m forced to chose between Project01 and Libraries (which is hardly perfect).
  • Moving the Libraries projects within the Project01 Solution causes Subversion to get confused and causes a coupling of the code that would be difficult when Project02 came along.

I read that svn:externals was the way to go – but I didn’t understand how exactly to go about this. Sometimes the guides weren’t clear on how to apply the svn:externals but almost always, they weren’t specifically detailing how to get the setup I wanted within Visual Studio.

The Solution:

People were right! svn:externals is definitely the way forward, but there are a few things that need to be done to get the (seemingly) perfect setup.

A few notes before we start:

  • My example below includes Tests projects, these are of great value but not needed as far as this example goes (obviously).
  • The Screenshots are for AnkhSVN, but the scenario works with VisualSVN. Where the stage differs, I’ll endevour to explain the differences where appropriate.
  • This is my current setup and I haven’t run into any issues with it. However, by following this guide you are aware that I’m occasionally wrong and that gremlins may eat your data if you don’t ensure that everything is working as it should. ;)
  • My working setups are Visual Studio 2008, VisualSVN Server 1.7.2 (which includes Subversion 1.6.2),TortoiseSVN and VisualSVN or AnkhSVN. Tested on Windows 7 and Windows XP.
  • I’m going to suggest using the standard trunk, tags and branches layout within a repository – but you’d prefer not to it should be easy to customise.

The Steps

  1. You may already have this step done if you are working from an existing code base, but I started with two seperate solutions; Project01 (my application) and Libraries (my shared resource).
  2. Add both solutions to Subversion however you normally do it. For example, in AnkhSVN you right-click the solution and select “Add Solution To Subversion” and fill in the following pop-up box similar to below.�

    Add both solutions to your SVN repository however you want.

    Add both solutions to your SVN repository however you want.

  3. After adding each of the solutions, you should make sure that you “Commit” to update the server’s version of the files.
  4. Your repository should look something like the following:
  5. Ok, you can close Visual Studio 2008 (not sure if you need to, but I feel safer doing so).
  6. Open your repository browser. (e.g, right clicking on a file in Windows Explorer -> TortoiseSVN -> repo-browser.�

    Your repository should look something like this now.

    Your repository should look something like this now.

  7. Right click on the trunk of Project01 and click “Show Properties”.
  8. A list of properties (possibly including svn:ignore are shown. Click “New”
    SVN Properties
  9. Within the “New Properties” window, you want to add “svn:externals” to the top right drop down box (this may not already be in the list – don’t be detered). Within the main box, you want to put the name of the directory within your Project01 you want to create, followed by the URL to the repository with the code you want to fill it with : e.g:
    Don't forget to type "svn:externals" into the top right hand box.

    Don't forget to type "svn:externals" into the top right hand box.

  10. Reopen Visual Studio and your Project01 Solution.
  11. Right click the Solution name and “Update” to the latest version. You should see it add the External Library project in the text that flies past. This is basically the main part finished, but you’ll spot the two Libraries projects are not visible within our solution still…
  12. Right click again on the Solution name and add an “Existing Project”. Navigate to your Project01 directory and you should spot there are now 4 project directories with two of them being your Libraries projects! Simply go into them and add them one at a time.�

    Adding an existing project in Visual Studio

    Adding an existing project in Visual Studio

  13. Commit your changes and you’re done!

Testing Your Setup

You should be able to test your project by making changes etc to both sets of project files and committing them to your database. What should happen is your Project01 files will go to the Project01 directory in your repository and the Libraries files will still (dispite being in your Project01 solution on your development machine) be committed to the Libraries solution directory in your repository.

This setup should work for Project02 as well. Just repeat the appopriate steps and bang! – you have a fully working second project using the same Libraries!

If anyone runs into any dramatic side effects or knows of any issues with this methodology, I’d be very interested in hearing them as I’m currently on Cloud 9 with it!

Older Posts »

Theme designed & built for by Alex Holt. Powered by WordPress