SQL CE Exposed

  • Article

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

"urn:schemas-microsoft-com:office:office" />

 

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.