Playing with the SqlCeResultSet

by William Ryan

As Sam has pointed out, there's not a whole lot out there on this guy (see nothing). I'm going to get pretty in depth with it tomorrow if the audience is interested. If not, oh well. Anyway, this is a totally different beast then what you're used to with Data Access. Why? Well, because you kinda need to leave the connection open to get much use out of it. Normally, this makes me cringe because, well, lots of folks don't like following the instructions on stuff and then bitch up a storm when it blows up. Most of the time they'll find a scapegoat like Microsoft because admitting "I'm professionally irresponsible and my arrogance caused this disaster" doesn't usually bode well for employment status. But I digress. Ok, back to the issue at hand. You need to leave the connection open if you want to do anything with it but you get a lot of cool stuff. First, it's fast as hell. You'll have to check out my webcast if you want numbers but trust me on it, it's faster than what you're used to. Second, you can walk it provided you set the appropriate property. Next you can bind it to stuff and update from it directly. This means that you can INSERT, DELETE and Modify data with it directly as opposed to SqlCeDataReader scenarios where you are in read only world and need another query to send anything back to the DB. A few nuances:
There are 10 types of rows, those that can read binary and those that can't. Seriously though, there are two main ones you'll be concerned with: 1) SqlCeRecord 2) SqlCeUpdatableRecord. Presently there's only one supported (SqlCeUpdatableRecord) but I have overheard that SqlCeRecord will be supported at game time. Since you probably don't give a rat's a33 about stuff that 'may' be out there, there's really only one type of record to worry about here... the SqlCeUpdatableRecord. To use one though, you need to use the ResultSetOptions.Updatable flag when you call ExecuteResultSet (you'll probably want to set the ResultSetOptions.Scrollable flag as well but for a different reason). Assuming you have a valid record in the hopper, here's the basic code to get it working (Remember, you've got to have an open connection):

private void btnNew_Click(object sender, EventArgs e) { SqlCeUpdatableRecord newRec = rs.CreateRecord(); newRec.SetValue(0,3335); newRec.SetValue(1, tbFirstName.Text); newRec.SetValue(2, tbLastName.Text); try {      rs.Insert(newRec);      rs.Update(); } catch (InvalidOperationException exc) {      Debug.Assert(false, exc.ToString()); } catch (SqlCeException ex) {      StringBuilder errMessage = new StringBuilder();      foreach (SqlCeError er in ex.Errors)      {        errMessage.Append(er.Message + "\r\n");      }        Debug.Assert(false, errMessage.ToString()); } }

That's easy enough right? Ok, next issue. How do you get the values out of the SqlCeResultSet? Fortunately it's totally consistent with what you are already to using with a Reader:

private void SetValues() {      nmID.Value = rs.GetInt32(0);      tbFirstName.Text = rs.GetString(1);      tbLastName.Text = rs.GetString(2); }

Ok, I'm sure some highly astute Jacka33 is going to tell me what a lame dickhead I am for not using ColumnNames instead of integers. Well, it's late and I'm tired. However, for the record you can use GetOrdinal or Bill Vaughn's concept of using an Enum. I like Bill's method mainly because he's smarter than me, he's Irish too and his first name is Bill but how you reference the column values is up to you. There's GetSchemaTable and all that good stuff so you have access to it as well.
Now, navigating is more like Old School ado 2.x:

private void btnNext_Click(object sender, EventArgs e) {      if (rs.Read()) {       SetValues();       btnPrev.Enabled = true; }       else { btnNext.Enabled = false; } } private void btnPrev_Click(object sender, EventArgs e) {      if (rs.ReadPrevious())      {       btnNext.Enabled = true;       SetValues();      }      else { btnPrev.Enabled = false; } } private void btnLast_Click(object sender, EventArgs e) {      if (rs.ReadLast())      {       SetValues();      } } private void btnFirst_Click(object sender, EventArgs e) {      if (rs.ReadFirst())      {       SetValues();      } }

This is the standard stuff but you have two more tricks you can use - the ReadRelative and ReadAbsolute methods. So if you want to read ahead 112 records from wherever you're at use the first one of these. If you want to read forward to position 112 use the second:

int SomeInt = 112;//Random number pulled out of the air rs.ReadRelative(SomeInt); rs.ReadAbsolute(SomeInt);

So what happens if you try to read to 112 and you don't have 112 records? Nothing. No exception but no nothing else. So you'll probably want to wrap that in an if() block. And if you want to delete something?

rs.Delete();

Finally, you can find out all sorts of stuff about the resultSet like if it's updatable, Scrollable , its sensitivity level , FieldCount and all that jazz by - well, take you're best guess - you'll be right. If you can't figure it out let me know or catch the tail end of my web cast tomorrow (hint, try a Period before the Property Name). Now the Sensitivity level is something that I'm too tired to discuss right now but I will have an update here tomorrow and some further explanations.
Also, please note that the examples above are just one way to use this guy. Yes, you can bind it to a grid and have all the work done for you..just remember:

rs = cmd.ExecuteResultSet(ResultSetOptionsScrollable|ResultSetOptions.Updatable);

Finally, you may be thinking that because this is supported that stuff like MARS is too. Well, give it a try for yourself. Make sure you hit both the connection and the resultset from multiple threads and don't synclock anything. Then try it again and synclock everything. You'll notice that either way you'll have succeeded in making yourself look like an idiot. Hence, if you're not into looking like an idiot - keep separate threads away from your resultsets and connections. P.S. This is probably the only instance where it's ok to instantiate a connection like this SqlCeConnection cn = new SqlCeConnection(); instead of using (SqlCeConnection cn = new SqlCeConnection()){ }; the reason for which should be readily evident.
BTW, as far as support for this goes, I know Caseyhad asked about it and so have some Other folks folks regarding support on TabletPC and Laptops. The good folks at MS have told me that it will be supported so it can definitely be used in other mobile scenarios.