1

The following post relates to the System.Data.SQLite data provider by phxsoftware (http://sqlite.phxsoftware.com)

I have a question (and possibly a problem) with DbDataReader’s Read method and/or Visual Studio 2008. In many examples I see things like the following (and I know this code doesn't make a lot of sense ... but it serves a purpose):

DbDataReader reader = null;
Long ltemp = 0;
lock (m_ClassLock)
{
   DbCommand cmd = dbCnn.CreateCommand();
   cmd.CommandText = “SELECT col1 FROM table1”;
   reader = cmd.ExecuteReader();

   if (null != reader)
   {
      while (reader.Read())
      {
         ltemp += (long)reader[0];
      }
   }
reader.Close();

First question - What I dont understand from this example is am I missing data the first time through the while loop by calling reader.Read() upfront? For instance, if the reader has values (3,5,7,9) the returned reader from cmd.ExecuteReader() should be pointing at 3 initially, correct? reader.Read() would then move to 5, 7, and 9 on subsequent invocations within the while loop. But, because reader.Read() is invoked before the first "ltemp += ..." line am I skipping past the first result (3)?

Second question - (and I'm starting to think this might be a bug in VS) If I step through this set of code in the debugger when I stop at a breakpoint on the "if (null != ..." line I can clearly see mu mousing over and drilling down in the popup that reader has multiple row data values assigned to it. However, if I close that popup information, and then try to bring it back up, when I drill down I now see the line "Enumeration yielded no results" where there was clearly data before.

Can anyone explain this behavior?

Sam Saffron
  • 128,308
  • 78
  • 326
  • 506
  • Why do you lock this reader action? Sqlite can deal with simultaneous selecting of data. Update/delete and insert can't happen simultaneously but selecting can. – tuinstoel Jun 17 '09 at 08:24

1 Answers1

1

  1. Think about it like this after you run ExecuteReader the set is on row -1. You need to execute Read to get to row 0.

  2. IDataReader is a forward only structure, you can only iterate through it once, the debugger is iterating through it.

General questions:

  • Why the lock?
  • Why the null check for reader - I am not aware of any issues where ExecuteReader return null after a select.
  • Why not "SELECT SUM(col1) from table1
  • Why are you not following the dispose pattern?
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506
  • Ok that clears up my question 1. The documentation seemed to imply that after the call cmd.ExecuteReader() reader would be primed to 0. I feel better about that now. As for question 2 the debugger is iterating through the list? That sure sounds like a bug in the debugger to me (altering the behavior of the code by inspection of variables). Ouch! Questions lock - poor cut and paste example null check - i agree sum - this was just sample code, the "ltemp +=" I stripped out the real work and put in something simple to talk to. dispose - again just sample code thanks for the insight! –  Jun 17 '09 at 02:37
  • re code executing in debugger, its a necessary evil if you want your watch window to work. See: http://blogs.msdn.com/jmstall/archive/2005/11/15/funceval-rules.aspx for gritty details – Sam Saffron Jun 17 '09 at 02:47