C# SqlClient | Connection Pooling | Error When Opening Second Connection-CodePudding

I'm trying to figure out how to setup my database access correctly while using the SqlClient hitting a Microsoft SQL Server. For the most part it is working, but there's a particular scenario that is giving me trouble. Namely: attempting to simultaneously use two connections in the same thread; one with an open data reader and the other performing a delete operation.

The following code demonstrates my conundrum:

public class Database { 
   ...
   internal SqlConnection CreateConnection() => new SqlConnection(connectionString);
   ...
}

public IEnumerable<Model> GetModel() {
   var cmd = new SqlCommand() { ... };
   using(var conn = db.CreateConnection()) {
      conn.Open();
      cmd.Connection = conn;
      using(var reader = cmd.ExecuteReader()) {
         while(reader.Read()) {
            var m = new Model();
            // deserialization logic
            yield return m;
         }
      }
   }
}

public void Delete(int id) {
   var cmd = new SqlCommand() { ... }
   using(var conn = db.CreateConnection()) {
      conn.Open(); // throwing the error here
      cmd.Connection = conn;
      cmd.ExecuteNonQuery();
   }
}

Application Code:

using(var scope = new TransactionScope()) {
   var models = GetModels();
   foreach(var m in models) {
      Delete(m.Id); // throws an exception
   }
   scope.Complete();
}

For whatever reason, an exception is thrown by the above code while trying to execute the Delete operation:

quote System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. ---> System.Data.SqlClient.SqlException: There is already an open DataReader associated with this Command which must be closed first. ---> System.ComponentModel.Win32Exception: The wait operation timed out quote

Now, I have confirmed that if I either set MultipleActiveResultSets=true or Pooling=false on the ConnectionString, that then the above application code will work without error. However, it doesn't seem like I should need to set either of those. If I open two connections simultaneously, should they not be separate connections? Why then am I getting an error from the Delete connection saying that there's an open DataReader?

Please help <3

CodePudding user response：

The main reason here as far as I understand is your yielding iteration.

So the DB connection has not yet called disposed as it's still being used in your iteration (foreach). If for example, you called .ToList() at that point it should return all the entries and then dispose of the connection.

See here for a better explanation on how yield works in an iteration: https://stackoverflow.com/a/58449809/3329836

CodePudding user response：

By far the easiest fix here is to simply load all the models outside the transaction before you go deleting any. Eg

var models = GetModels().ToList();
using(var scope = new TransactionScope()) {
 
   foreach(var m in models) {
      Delete(m.Id); // throws an exception
   }
   scope.Complete();
}

Even fetching the models inside the transaction shold work

using(var scope = new TransactionScope()) {
   var models = GetModels().ToList(); 
   foreach(var m in models) {
      Delete(m.Id); // throws an exception
   }
   scope.Complete();
}

so long as you don't leave the connection open during the iteration. If you allow the connection in GetModels() to close, it will be returned to the connection pool, and be available for use for subsequent methods that are enlisted in the same transaction.

In the current code the connection in GetModels() is kept open during the foreach loop and Delete(id) has to open a second connection and try to create a distributed transaction, which is failing.