The transaction operation cannot be performed because there are pending requests working
I have had this issue before and the problem was the reader needed to be closed. Try this:
public foo Dequeue(SqlConnection connection, SqlTransaction transaction)
{
using (var command = new SqlCommand(DEQUEUE_SPROC, connection) {CommandType = CommandType.StoredProcedure, Transaction = transaction})
{
var reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
ID = (Guid) reader["ID"];
Name = reader["Name"].ToString();
reader.Close();//Closing the reader
return this;
}
return null;
}
}
public string GetFilePathUri(SqlConnection connection, SqlTransaction transaction)
{
string filePathUri = "";
using (var command = new SqlCommand(FILEPATH_SPROC, connection) {CommandType = CommandType.StoredProcedure, Transaction = transaction})
{
var reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
filePathUri = reader["Path"].ToString();
}
reader.Close();//Closing the reader
}
return filePathUri;
}
I had this problem when I forgot to use await on an async method that was doing the DB call - the connection was being disposed while the transaction was running because the program was not waiting for the query to complete before trying to dispose everything.