phxsoftware System.Data.SQLite DbDataReader misbehaving

Question

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?

Answer 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?