SQL CE Exposed

(Re-post from my old blog...)

Occasionally, I get that ‘wow, that’s cool’ feeling when looking at some technology. 

This happened to me late last week when I was doing some work with SQL CE on my PocketPC device.  Now, I knew that SQL CE existed – and I know I have even mentioned it in a couple of talks on Smart Client technology - but to actually see it in action was quite the eye-opener. 

I’ll admit – I am a skeptic.  I know about the history and complexity of relational databases – and the thought of taking any of this to my mediocre 64Mb Pocket PC was somewhat daunting. 

For the application I was writing (which needed to store some local data on the device), initially I was looking at binary serialization.  By using p/Invoke to get to some of the underlying CE APIs, I created a class that serialized and deserialized objects into a binary stream which I then stored in a file.  This was working well, but, as it turned out – this wasn’t enough.  As I became more involved in the application I started thinking about how I should search and index the file when I was working offline – something that would have been difficult to implement with a large binary blob of data.

I took the proverbial plunge and restarted my sample, looking at SQL CE.  The results were immediate.  To create a database was remarkable easy using the classes provided in the System.Data.SqlServerCE namespace of the.NET compact framework…

            SqlCeEngine engine = new SqlCeEngine("Data Source = MyDB.sdf");

            engine.CreateDatabase();

            conn = new SqlCeConnection("Data Source = MyDB.sdf");

            conn.Open();

            SqlCeCommand cmd = conn.CreateCommand ();

            cmd.CommandText = "CREATE TABLE DetailsTbl (ProductName NTEXT, ListPrice MONEY)";

            cmd.ExecuteNonQuery();

I ran the code and before my very eyes not only was SQL CE automatically installed, but in my application folder was an .SDF database file!  I admit that the SDF file wasn’t as optimized as my previous attempt using binary serialization, but the benefits of having SQL-like functionality on such a small device far outweigh the small, additional amount of my device’s memory.

To show just one of these benefits, when SQL CE installs, you also get a pocket version of Query Analyzer (which I can say I was definitely not expecting).  Clicking on the SDF file launches this application.

Not only can you browse the actual database, tables and columns, but as with the real application it is also possible to enter SQL specific commands to search and make modifications to the underlying data.

So as I start to explore this more, I expect to hit natural limitations when it comes to certain functionality, storage space and performance given the scope of the device – but as a means of storing offline data on my handheld and with the ability to take advantage of some neat replication routines through ActiveSync, this definitely scores well on the ‘wow, that’s cool’ factor.