SQL in Silverlight

It never ceases to amaze me how short my attention span has become...

what was I saying :) ?

Aaanyway, I sat down in front of my PC today planning to accomplish a few different things... but before that I had to catch up on blogs and twitter. A tweet caught my eye


"Great idea" I thought... but seemed like quite a big job and certainly not something I have the time to tackle. Then I started to recall seeing a C# port of an SQL engine (ages ago) and thought it might be fun to have a poke around. At least it would be easier to port something already in .NET land.

A couple of Google results later, I came across Sharp HSql, a circa 2001 port of the Java-based hsqldb (licence). The code was subsequently added to Codeplex with the following warning: This version is not ready for production grade applications. More testing is needed and some important bugs has to be fixed before that. You are warned.

Long story short...

it's a couple of hours later and I've done nothing that I'd planned to, but here is an 'alpha' of SharpHSql for Silverlight (try it out) - there's some test SQL at the bottom of the post.


The "port" mainly consisted of:

• 'implementing' ArrayList and Hashtable (umm, can you say List<object> and Dictionary<object, object>)
• removing the 'Provider' and all the dependencies on System.Data that work fine in the full framework, but are missing from Silverlight (would be nice to add some sort of wrapper back in...)
• converting FileInfo and related operations to IsolatedStorageFileStream (still some persistence testing to do...)
• breaking DateTime handling (for now)

The REAL work was all done by Mark Tutt so don't get the idea that I'm taking any credit - all the goodness is his, any errors are mine.

The full source of the consuming Silverlight app is this Page.xaml and Page.xaml.cs; the full Visual Studio 2008 project ZIP (199Kb) is available to download. Anywhere I've touched the code you'll find a //HACK: tag!

Disclaimer: I've no idea if this is fit for any purpose at all... it seems to be able to DROP and CREATE tables, INSERT rows and SELECT/JOIN - but even that could be a fluke. Try it out at your own risk; leave a comment if you find something good or bad. Have fun!

UPDATE: the output is a lot prettier (try it) with the Silverlight DataGrid and Vladimir Bodurov's awesome dynamic datasource compiler - updated for generic types

(updated source (277Kb))

Test SQL

#1

DROP TABLE IF EXIST "books";CREATE TABLE "books" ("id" INT NOT NULL PRIMARY KEY, "name" char, "author" char, "qty" int, "value" numeric);

INSERT INTO "books" VALUES (1, 'Book000', 'Amy', 1, 23.5);
INSERT INTO "books" VALUES (2, 'Book001', 'Andy', 2, 43.9);
INSERT INTO "books" VALUES (3, 'Book002', 'Andy', 3, 37.25);
INSERT INTO "books" VALUES (4, 'Book 21', 'Amy', 99, 20.5);
INSERT INTO "books" VALUES (5, 'Book 22', 'Andy', 2, 903.9);
INSERT INTO "books" VALUES (6, 'Book 23', 'Andy', 5, 0.25);

SELECT * FROM "books" ORDER BY "value"

DROP TABLE IF EXIST "author";CREATE TABLE "author" ("name" char NOT NULL PRIMARY KEY, "country" char);

INSERT INTO "Author" VALUES ('Andy', 'UK');
INSERT INTO "Author" VALUES ('Amy', 'USA');

SELECT * FROM "author" ORDER BY "value"

SELECT * FROM "books" LEFT JOIN "author" ON "author"."name" = "books"."name"

#2

CREATE TABLE "clients" ("id" int NOT NULL IDENTITY PRIMARY KEY, "DoubleValue" double, "nombre" char, "photo" varbinary, "created" date );
INSERT INTO "clients" ("DoubleValue", "nombre", "photo", "created") VALUES (1.1, 'NOMBRE')
SELECT * FROM "clients"