3

I am using TransactionScope in my repository unit tests to rollback any changes made by tests.

Setup and teardown procedures for tests look like this:

[TestFixture]
public class DeviceRepositoryTests {
    private static readonly string ConnectionString =
        ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;

    private TransactionScope transaction;
    private DeviceRepository repository;

    [SetUp]
    public void SetUp() {
        transaction = new TransactionScope(TransactionScopeOption.Required);
        repository = new DeviceRepository(ConnectionString);
    }

    [TearDown]
    public void TearDown() {
        transaction.Dispose();
    }
}

Problematic test consists of code which inserts records to database and CUT that retrieves those records.

    [Test]
    public async void GetAll_DeviceHasSensors_ReturnsDevicesWithSensors() {
        int device1Id = AddDevice();
        AddSensor();

        var devices = await repository.GetAllAsync();

        // Asserts
    }

AddDevice and AddSensor methods open sql connection and insert a row into a database:

    private int AddDevice() {
        var sqlString = "<SQL>";
        using (var connection = CreateConnection()) 
        using (var command = new SqlCommand(sqlString, connection)) {
            var insertedId = command.ExecuteScalar();
            Assert.AreNotEqual(0, insertedId);
            return (int) insertedId;
        }
    }

    private void AddSensor() {
        const string sqlString = "<SQL>";
        using (var connection = CreateConnection()) 
        using (var command = new SqlCommand(sqlString, connection)) {
            var rowsAffected = command.ExecuteNonQuery();
            Assert.AreEqual(1, rowsAffected);
        }
    }

    private SqlConnection CreateConnection() {
        var result = new SqlConnection(ConnectionString);
        result.Open();
        return result;
    }

GetAllAsync method opens a connection, executes query, and for each fetched row opens new connection to fetch child objects.

public class DeviceRepository {
    private readonly string connectionString;

    public DeviceRepository(string connectionString) {
        this.connectionString = connectionString;
    }

    public async Task<List<Device>> GetAllAsync() {
        var result = new List<Device>();
        const string sql = "<SQL>";

        using (var connection = await CreateConnection())
        using (var command = GetCommand(sql, connection, null))
        using (var reader = await command.ExecuteReaderAsync()) {
            while (await reader.ReadAsync()) {
                var device = new Device {
                    Id = reader.GetInt32(reader.GetOrdinal("id"))
                };

                device.Sensors = await GetSensors(device.Id);

                result.Add(device);
            }
        }

        return result;
    }

    private async Task<List<Sensor>> GetSensors(int deviceId) {
        var result = new List<Sensor>();
        const string sql = "<SQL>";

        using (var connection = await CreateConnection()) 
        using (var command = GetCommand(sql, connection, null))
        using (var reader = await command.ExecuteReaderAsync()) {
            while (await reader.ReadAsync()) {
                // Fetch row and add object to result
            }
        }

        return result;
    }

    private async Task<SqlConnection> CreateConnection() {
        var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();
        return connection;
    }
}

The problem is that when GetSensors method calls SqlConnection.Open I get following exception:

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

I could move code that fetches child object out of the first connection scope (this would work), but let's say I don't want to.

Does this exception mean that it is impossible to open simultaneous connections to DB inside single TransactionScope?

Edit

GetCommand just calls SqlCommand contructor and do some logging.

private static SqlCommand GetCommand(string sql, SqlConnection connection, SqlParameter[] parameters) {
    LogSql(sql);
    var command = new SqlCommand(sql, connection);

    if (parameters != null)
        command.Parameters.AddRange(parameters);

    return command;
}
4
  • No. This Message tells you that you just can use ONE DbCommand object with ONE DbDataReader object. But you're trying to use the same DbCommand object on two DbDataReaders. Looks like the problem is your GetCommand method. Commented May 28, 2014 at 14:33
  • I updated my question with GetCommand method body. It is very simple and just creates SqlCommand object. Commented May 28, 2014 at 15:31
  • If you enable MultipleActiveResultSets does the problem go away? (I have no idea what is causing it) Commented May 28, 2014 at 15:38
  • It does. But why? I just wanted to create second connection from connection pool, not to use multiple readers inside one connection. Commented May 28, 2014 at 15:42

1 Answer 1

0

The issue is that two DataReader objects can't be open at the same time against the database (unless MARS is enabled). This restriction is by design. As I see it you have a few options:

  1. Enable MARS on your connection string; add this MultipleActiveResultSets=True
  2. Don't use the DataReader if it's really not necessary. But the way you've got your code written, it's pretty necessary.
  3. Populate the Sensor property after loading the devices.
  4. Use Dapper, it can do all of this (including populate the Sensor) and likely faster.

Using Dapper you could do something like this (and you wouldn't need GetSensors):

public async Task<List<Device>> GetAllAsync() {
    var result = new List<Device>();
    const string sql = "<SQL>";

    using (var connection = await CreateConnection())
    using (var multi = connection.QueryMultiple(sql, parms)) {
        result = multi.Read<Device>().ToList();
        var sensors = multi.Read<Sensors>().ToList();

        result.ForEach(device => device.Sensors =
            sensors.Where(s => s.DeviceId = device.Id).ToList());
    }

    return result;
}

Here your sql would look like this:

SELECT * FROM Devices
SELECT * FROM Sensors

See the Multi Mapping documentation for Dapper.

Sign up to request clarification or add additional context in comments.

7 Comments

Factually wrong. You can have multipel queries active at the same time... c-sharpcorner.com/uploadfile/sudhi.pro/…
I get that there is an attempt to reuse connection with DataReader opened. I just don't get why. Why wont framework just open new connection when I try to query Sensors? I thought thats what connection pooling is about: when I call new SqlConnection framework looks in pool for suitable (not in use, disposed) connection. If there isn't any then it creates new one and returns it. Connection used to query device table is not disposed at time when new one is requested to query sensors.
SqlConnection, SqlCommand etc. are the objects for an MSSQL-Server or? I've never had such an issue. I can open many concurrent SqlConnection's with concurrent SqlDataReaders?!
@user743414: take note to my first sentence (unless MARS is enabled).
Is this by default == TRUE?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.