title: SQLite Transaction not committing
body: <p>For an application we are developing we need to read <em>n</em> rows from a table and then selectively update those rows based on domain specific criteria.  During this operation all other users of the database need to be locked out to avoid bad reads.  </p>
  
  <p>I begin a transaction, read the rows, and while iterating on the recordset build up a string of update statements.  After I'm done reading the recordset, I close the recordset and run the updates.  At this point I commit the transaction, however none of the updates are being performed on the database.</p>
  
  <pre><code> private static SQLiteConnection OpenNewConnection()
          {
  
          try
          {
              SQLiteConnection conn = new SQLiteConnection();
              conn.ConnectionString = ConnectionString;//System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
              conn.Open();
              return conn;
          }               
          catch (SQLiteException e)
          {
              LogEvent("Exception raised when opening connection to [" + ConnectionString + "].  Exception Message " + e.Message);
              throw e;
          }
      }
  
      SQLiteConnection conn = OpenNewConnection();
              SQLiteCommand command = new SQLiteCommand(conn);
              SQLiteTransaction transaction = conn.BeginTransaction();
  // Also fails           transaction = conn.BeginTransaction();
              transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
              command.CommandType = CommandType.Text;
              command.Transaction = transaction;
              command.Connection = conn;
              try
              {
                  string sql = "select * From X Where Y;";
                  command.CommandText = sql;
                  SQLiteDataReader ranges;
  
                  ranges = command.ExecuteReader();
                  sql = string.Empty;
                  ArrayList ret = new ArrayList();
                  while (MemberVariable &gt; 0 &amp;&amp; ranges.Read())
                  {
                      // Domain stuff
  
                      sql += "Update X Set Z = 'foo' Where Y;";
                  }
                  ranges.Close();
                  command.CommandText = sql;
                  command.ExecuteNonQuery();
                                  // UPDATES NOT BEING APPLIED
                  transaction.Commit();
                  return ret;
  
              }
              catch (Exception ex)
              {
                  transaction.Rollback();
                  throw;
              }
              finally
              {
                  transaction.Dispose();
                  command.Dispose();
                  conn.Close();
              }
  
              return null;
  </code></pre>
  
  <p>If I remove the transaction everything works as expected.  The "Domain stuff" is domain specfic and other than reading values from the recordset doesn't access the database.  Did I forget a step?</p>