title: Get Updated Row Count in Transaction Scope
body: <p>I am doing some insertion operation in SQL server within TransactionScope.</p>
  
  <p>Here is my code</p>
  
  <pre><code>using (var scope = new TransactionScope(TransactionScopeOption.Required))
  {
      Int32 existingDBRowCount = GetTableRowCount(currentTableName);
      //... Code to Insert Data into Table
       Int32 newDBRowCount = GetTableRowCount(currentTableName);
  
      // New Row Count should be equal to Existing DB Row Count + Rows to be inserted
       if (newDBRowCount != (existingDBRowCount + toBeInsertedRowCount))
            Transaction.Current.Rollback();
       else 
         scope.Complete();  // Commit the Transaction
  }
  
  
  public Int32 GetTableRowCount(string tableName)
  {
    using (SqlConnection sqlConnection = GetSQLConnection())
    {
       if (sqlConnection.State != ConnectionState.Open)
          sqlConnection.Open();
  
        string queryString = "SELECT COUNT(*) FROM [" + tableName + "]";
        using (var sqlCommand = new SqlCommand())
        {
          sqlCommand.Connection = sqlConnection;
          sqlCommand.CommandType = CommandType.Text;
          sqlCommand.CommandText = queryString;
  
          var rowCount = (int) sqlCommand.ExecuteScalar();
          return rowCount;
         }
      }
   }
  </code></pre>
  
  <p>Basically What i am doing is I keep a track of count of Rows in the table and then after Inserting new data, I check if new row count = exisiting Rows + Count of Rows to be Inserted</p>
  
  <p>If Both matches, I commit the transaction, otherwise rollback</p>
  
  <p>I have two questions:</p>
  
  <p>1) Is this apprach correct ?</p>
  
  <p>2) In some cases new DB Row Counts is coming as same as existing rows, I assumed that it is because my transaction hasn't been committed till that point, but it is very random, mostly it returns correct number of rows. I cant figure out whats going wrong</p>