[VSD] Getting started with the ResultSet (SqlCeResultSet)

[If you're new to device data development, read my first post in this series]

Probably the biggest new thing for device data developers in VS 2005 is the introduction of the ResultSet (to be specific, the SqlCeResultSet class) in Sql Mobile. Of course, I'm neglecting to mention that there was no IDE support for data back in VS 2003 so everything is kinda new anyway :-). To understand the ResultSet, a little bit of history is required.

A little history

Once upon a time, dinosaurs roamed the Earth. Of course, we're probably not too interested in those times so lets fast forward a 100 or so million years. A few years ago, if you wanted to work with data on a device, you had a few options. You had  eDb,(the now defunct) AdoCe and Sql Ce and life was good. Of course, you could also get all Don Box-ish and store your stuff in XML or plain old text files but that doesn't really scale well on the resource constrained device environment. 

With VS 2003, for the first time, you could use VS access data from your .Net Compact application using Sql Ce 2.0. We soon discovered something about Ado.Net. DataSets are totally killer and absolutely the best way to do data programming - but they're not exactly the leanest, meanest objects around. To be specific, on a device, you could not afford to let DataSet objects eat up oodles of RAM. And that was a trap which was very easy to fall into.

Of course, you could go and use the faster DataReader - but then, you would not get the ease of use of typed DataSets. You would not be able to write code like NorthWindDataSet.Employees(row).Country.It was pretty clear (to us atleast), that there had to be a third way. And thus was born the ResultSet.

What is a ResultSet (SqlCeResultSet)?

Think of a ResultSet as something that gives you the programmatic ease that a DataSet gives you but the performance of a DataReader. Using the typed ResultSet that VS 2005 generates, you can write strongly typed code. However, since the data isn't being double buffered, your application doesn't eat up any memory at all. So to use a cliché, you get the best of both worlds.

How do I get started using the ResultSet?

I'll walk you through a HelloWorld style application using ResultSets in VS 2005. Since I'm in the mood, I'll include pretty screenshots of VS 2005 as well :-).

1.Fire up VS and create a new PocketPC project. You could use Windows Mobile 5 Smartphone as well but Smartphone 2003 is not supported (Sql Mobile needs .Net CF 2.0 to run which Smartphone 2003 doesn't run).

2. Click on Data->Add New Data Source

This launches the new Data Source wizard. To learn more about this wizard as well as all the new data features in the IDE , see the VBData team's blog where Steve Lasker and company hang out.

3. Your data source is going to need a connection to the SqlMobile database. If this is the first time you're doing this, you'll need to create a new connection. To do so, click on 'New Connection'. You'll be prompted to pick a provider - pick the SqlMobile provider.

4. At this point, you get to create a spanking new connection - don't you feel special and tingly all over? Umm...anyway,click 'Browse' and pick the sample 'Northwind.sdf' that ships with VS 2005. If you've been paying attention so far, you should have a screen which looks something like this. Click on 'Test Connection' if you're the type of person who goes for that sort of thing.

You now have a connection the Northwind database. Now, let's use it to get ourselves a data source.

5. Now that you created a connection, the only thing that is left to do is to choose the tables you want in the data source. In this case, I'm going to pick only the Employees table. Click 'Finish' and close the wizard. Congratulations! You have your very own personal data source. Let's take it for a spin, shall we?


6. Conversion of the DataSet to a ResultSet. Here's the most important step. By default, the data source wizard generates a dataset for you. To change it to a ResultSet, click on the dataset's .xsd file. In this case, it is NorthwindDataSet.xsd. Go to the properties window and change the 'Custom Tool' property to 'MSResultSetGenerator'. Tada! your dataset now becomes a ResultSet.

What actually happened here?

When you ran through the data source wizard, it created a typed DataSet for you. That's basically a generated class which lets you work at the strongly typed schema level and makes calls to the right data APIs for you. When you switched the generator to generate ResultSets, the designer code was now changed to make calls to the ResultSet class instead.

Where did all this stuff get generated? The meat of it is in the Designer.cs file. Here's a snippet from mine


    public partial class EmployeesResultSet : System.Data.SqlServerCe.SqlCeResultSet {       

        private string resultSetConnectionString = null;     

        private System.Data.SqlServerCe.SqlCeConnection sqlCeConnection = null;

        private System.Data.SqlServerCe.ResultSetOptions resultSetOptions;


public int Employee_ID {

            get { return ((int)(base.GetInt32(base.GetOrdinal("Employee ID")))); }

            set { base.SetInt32(base.GetOrdinal("Employee ID"), value); } }


You're now free to drag drop the ResultSet onto your form from the Data Sources Window. Doing so will automatically generate a datagrid and the other plumbing required. However, much cooler than datagrids is the new ability to do details view in VS 2005.

If you decide that you don't want a ResultSet, switch the 'Custom Tool' back to 'MSDataSetGenerator' and you'll get back everything as it was.

7. In VS 2003, dragging dropping of data components usually gave you a grid of some sort. Which meant that if you wanted any other UI, you were on your own. In VS 2005, you can use drag drop to generate any kind of UI, or to connect with existing UI. To do this, you can change the default generation from DataGrid to Details as shown below.

8. Drag drop any of the elements of the EmployeesResultSet to the form or drag drop the entire ResultSet itself. The matching UI will be auto generated for you. Move the UI around to suit your whims and fancies. Take a step back and marvel at your own creation :-)

We saw some of the new ResultSet stuff here. I'll try to drill down in the ResultSet as well as new IDE features in upcoming blog posts. Same Bat-time, same Bat-channel!