Dynamic SQL scripting on Windows Phone 7 with LINQ
I recently had cause to look into running dynamic SQL on WP7 for a customer. On a desktop this is easy, you just run some SQL statements through ADO.NET and you’re sorted. On Windows Phone 7 it’s a little trickier. This is because WP7 only exposes LINQ to SQL and it doesn’t support arbitrary SQL execution against your SQL CE databases.
So how to handle it when you have a corrupt DB and want to send a fix to a remote device or you just want to update the DB with some new data.
DB Operations
The first thing I decided was what operations I wanted to support, I limited this to supporting create, update and delete operations since those are the ones we are really look at here. I also limited it to each operation only operating against a single entity type at a time, it just made it easier and that covers most of the scenarios I needed at the time.
Command Serialization
The first problem I needed to tackle was getting the commands down to the phone application, how am I going to script this? I chose to create an object model of DatabaseInstruction objects which represent the serialized format of one of the above operations. I then used JSON as the format for my script as it’s nice and easy to then return that object model from a server to the client and it’s more compact than XML. Of course you can always change the serialization format by creating a new IDatabaseInstructionParser to process different serialization formats into the object model.
I used the excellent JSON.Net library to handle the serialization for me.
Database Commands
Once I’ve gotten a list of DatabaseInstruction objects I convert them into DbCommand objects, these are the objects that do the actual work of talking to the database using LINQ to SQL. That’s handled by calling CreateCommand against each instruction. If you’d like to reuse the same DatabaseInstruction classes in the server component to build the script then I suggest refactoring this to separate the command creation out. I started doing that with the CommandBuilder class but never finished it, currently that class is incomplete and unused.
Parsing
So far so simple. The next challenge was filtering the data in the instructions, in order to do that I needed to pass in predicates in my scripts to apply changes to certain rows or to delete specific items. I downloaded and slightly modified the Dynamic LINQ sample from Scott Guthrie’s blog. This almost worked on the phone out of the box but not quite, I had to chop out support for ‘new’ since the phone can’t do the dynamic code gen bits that the sample uses and I didn’t need the operator for what I was doing.
In the commands I then use the DynamicExpression.ParseLamba() method to create an Expression predicate from the predicate from the script. This can then be used in my command execution with a custom Where implementation that takes an Expression object.
And finally…
Using the library is fairly easy, I’ve put together a sample WP7 app that creates a database and then provides 2 buttons, one runs an JSON script to insert data, the other runs a JSON script to update the data and delete a row. The scripts are in the Content folder in the project and I just use the DatabaseInstructions.Execute() method to run the script. This can either take a TextReader or an IEnumerable<DatabaseInstruction> if you’d prefer to handle parsing yourself.
As usual the normal disclaimers about demo code apply, use at your own risk. To build the sample just download the DynamicLinq sample code and load it in Visual Studio 2010. It uses NuGet to pull down the JSON.Net bits so just build the solution and it’ll do that automatically.
Enjoy.
Original Post by Rob Garfoot on Feb 10th, 2012
Here: https://garfoot.com/blog/2012/02/dynamic-sql-scripting-on-windows-phone-7-with-linq/