title: Read old data from the table, while some transaction is writing to the same table
body: <p>What I want is being able to read old data from the table, while some transaction is writing to the same table. When such atomic writing is completed, I want to replace the old data with the new one. </p>
  
  <p>Transaction I'm coping with is long and I don't want enable dirty reads nor block reading possibility.</p>
  
  <p>I've turned on snapshot isolation level on my database:</p>
  
  <pre><code>SET ALLOW_SNAPSHOT_ISOLATION ON
  </code></pre>
  
  <p>and used code like this to write data in transaction:</p>
  
  <pre><code>using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew,
                         new TransactionOptions
                             { IsolationLevel = IsolationLevel.Snapshot }))
  {
      update();
      scope.Complete();
  }
  </code></pre>
  
  <p>Unfortunately while I'm trying to read some data using <code>SELECT * FROM [Table]</code> in Microsoft SQL Server Management Studio, query execution is waiting with message <code>Executing query...</code>, and retrieves data after transaction is completed. What I've missed ?</p>
  
  <p><strong>EDIT:</strong> code for update() - basically simple inserts within a loop, mixed with no db related stuff connected with WCF which slows down the process:</p>
  
  <pre><code>var val = Web.Download();
  using (var connection = new SqlConnection(connection))
  {
      connection.Open();
      using (var command = connection.CreateCommand())
      {
          command.CommandType = CommandType.Text;
          command.CommandText = "INSERT INTO [table]([value]) VALUES (@val)";
          command.Parameters.Add(CreateParam(command, "@val", val, DbType.String));
          command.ExecuteNonQuery();
      }
  }
  </code></pre>