When inserting rows into MS Access from c# using TransactionLevel.ReadUncommitted from a prepared statement, the ordering seems off

Question

I have some code in c# that is writing information (row by row) into an access database using prepared statements. I am using OleDb, and a TransactionLevel.ReadUncommitted because sometimes I need to look at the data before committing it.

The problem seems to be that on 1 out of 7 different tables, the ordering that I retrieve the records from the database isn't the same ordering I put them in as. This happens about 1 out of every 4 times I try to use it, so I can't really seem to debug it.

Edit The reason ordering is important here is that we are dumping the table to an excel sheet which matches up with data already existing on there. Order by might make some progress, but there is a column which is ordered by a movement (i.e. N-NW) that couldn't be ordered to match up with the data on the sheet.

I'm leaning toward it being a race condition of the prepared statement's insert command (i.e. there are too many for access to handle at once, so they get jumbled).

Does anybody have any thoughts on this? Below are a couple of snippets of what I'm doing: (Sorry about the length, I tried to cut out as much as I could but still get the main components out)

protected override void PopulateTmpTable()
  {
    OleDbCommand objComm = null;
    try
    {
      objComm = new OleDbCommand("", mDbconn);
      ...
      //Begin SQL Transaction
      mTr = mDbconn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
      objComm.Transaction = mTr;
      //Start Populating Temp Table
      for (int i = 1; i <= mNrows; i++)
      {
        ...
        ProcessNode(objComm, node, approaches);
        ProcessNodeSummary(objComm, node);
      }
      ProcessSummary(objComm);
    }
    catch (Exception e) { }
    finally
    {
      if (mTr != null) mTr.Commit();
      if (objComm != null) objComm.Dispose();
    }
  }  //End Method PopulateTmpTable

  private void ProcessNode(OleDbCommand objComm, string node, List<string> approaches)
  {
    try
    {
      ...
      OleDbCommand objComm2 = new OleDbCommand("", mDbconn, mTr);
      for (int k = 0; k < MaxLegs; k++)
      {
        ...
            total = ProcessIterations(objComm, node, turning[m], m);
          }
          objComm.ExecuteNonQuery();
        }  //End if
      }  //End for
    }
    catch { }
  }  //End Method ProcessNode

private List<double> ProcessIterations(OleDbCommand objComm, string node, string turn, int m)
  {
    try
    {
      OleDbCommand objComm2 = new OleDbCommand("", mDbconn, mTr);
      OleDbDataReader objRead;
      objComm.Parameters["parameter"].Value = //Stuff (x2)
        for (int j = 0; j < mIterations; j++)
        {
          ...
          objComm2.CommandText = "SELECT ROUND(AVG(Delay),1), COUNT(VehNo) FROM [TABLE] WHERE NodeNo=" + node + " AND Movement='" + turn + "' AND Iteration=" + mIterationValue[j] + mFilter[1];
          objRead = objComm2.ExecuteReader();
          objRead.Read();
          try
          {
              objComm.Parameters["more parameters"].Value = objRead[0];
              ...
          }
          catch { }
          objRead.Close();
        }//End for
        ...
        objComm.ExecuteNonQuery();
        objComm2.CommandText = "UPDATE " + mTmptable + " SET ave=" + avg + ",minimum=" + mMini[m] + ",maximum=" + mMaxi[m] + ",dev=" + stDev + " WHERE node='" + node + "' AND movement = '" + temp + "';";
        objComm2.ExecuteNonQuery();
      }
    }
    catch{}
    return mTotal;
  }  //End Function ProcessIterations

Answer 1

In your PopulateTmpTable() method, you are commiting the transaction even if an error occured. Is that what you intended?

In ProcessNode() you pass in a command object, create a new one and then use the one passed in?

Answer 2

In Jet, the clustered index (physical ordering) of a table is determined by the PRIMARY KEY or, in absence of a PK, another key chosen (randomly?) by the engine. However, physical ordering only occurs when the database file (.mdb, .mde, .accdb) is compacted. Rows inserted subsequent to compacting (or before the first compact) are inserted in date/time order. Now, the smallest time granule supported by Jet's DATETIME data type is one second, though under the covers the value is stored as a double float, therefore I'm wondering whether the granularity is not fine enough for your purposes. But why rely on such poorly documented features of an elderly engine...?

If you know ther order in which you insert the rows then model this in the database e.g. use an INTEGER column to store a sequence number for which your app is in charge of supplying the values. And put a UNIQUE constraint on the column too, just to be sure. Then simply ORDER BY your sequence column in queries.