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;
}
GetCommandmethod body. It is very simple and just createsSqlCommandobject.