SqlCeResultSet - cannot determine the record number after a seek

Jerryscole,

Thanks for your input.  Great minds think alike.  There are a couple of tables that are read only where that's exactly what I've done - added an "OrdinalPosition" field which is, of course, the record number.  Using that approach on tables that are editable won't work because the numbers would be wrong as soon as a record was deleted.

I'm not too crazy about reading the entire database into memory because handheld memory is somewhat constrained.

This problem may just be an oversite on the part of Microsoft.  If that's the case, I'll probably stick with my current scheme of including an OrdinalPosition on the read only tables and counting the records on the others.  BTW (so you don't think I'm a dullard), the code snippet I provided was a simplified version just to show the principle.  The real code stores the record number in a private (class) field and marks it as needing to be updated whenever the record selection changes.

JCC

jerryscole,

I believe what you're suggesting is to count the records that are <= the current record.  On the one hand, I suppose that would be more efficient than my C# "count backwards" routine.  On the other, I would need to instantiate another result set which would probably eat up any savings.  Sounds like you know what you're talking about so my hopes for a more elegant solution are diminishing.  I was hoping someone would respond with: "Hey dummy!  Here's how you do that..."  I'm going to leave the question as unanswered for another day or so and, if nothing shows up I'll award you the points.

Regards,

JCC

jerryscole,

Prompted by your last response, I did a little experimenting.  I wrote a method that, as you suggested, did a scaler count query to determine the record number of the currently selected record.  See the code snippet for an example.

Then, I did some timed comparisons using a table with about 30,000 records.  Using Ticks to measure the elapsed time, I found there was no difference between my "count backwards" routine and the count query shown in the snippet.  Both took about 200 milliseconds.  Then I did the same experiment on a table with only 140 records.  Just doing it once, the elapsed time was not measurable.  When I retrieved the record number 100 times, I found the "count backwards" approach to be about three times faster than count query.  Regardless, the elapsed time was negligable in either case (10 ms vs 30 ms).  The bottom line is, either approach is quite acceptable.

Best regards,

JCC

public int GetCurrentRecNum()
{
  SqlCeCommand sqlCeSelectCommand = null;
 
  if (this.ItemNumber == null)
  {
    return -1;
  }
  try
  {
    // Create a command object & set the required properties
    sqlCeSelectCommand = sqlCeConnection.CreateCommand();
    sqlCeSelectCommand.CommandText = @"SELECT COUNT(*) AS RecCount
                                       FROM Item
                                       WHERE (ItemNumber < N'" + ItemNumber + "')";
    sqlCeSelectCommand.IndexName = "PK_Item";
    sqlCeSelectCommand.CommandType = System.Data.CommandType.Text;
 
    // Execute the command to get the record count as an object
    object currentRecNum = sqlCeSelectCommand.ExecuteScalar();
 
    // Return the record number converted to an integer
    return (int)currentRecNum;
  }
  catch (Exception)
  {
    throw;
  }
}

Select allOpen in new window

jerryscole,
Thanks for your help on this.  Not the answer I wanted but I got some education from the experimenting.