我在存储库单元测试中使用TransactionScope
来回滚由测试所做的任何更改。
测试的设置和拆卸过程如下:
[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();
}
}
问题测试包括将记录插入数据库的代码和检索这些记录的CUT。
[Test]
public async void GetAll_DeviceHasSensors_ReturnsDevicesWithSensors() {
int device1Id = AddDevice();
AddSensor();
var devices = await repository.GetAllAsync();
// Asserts
}
AddDevice
和AddSensor
方法打开SQL连接并插入一行数据到数据库中:
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
方法打开连接,执行查询,并为每个获取的行打开新的连接以获取子对象。
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;
}
}
问题在于当
GetSensors
方法调用SqlConnection.Open
时,会出现以下异常:
System.Transactions.TransactionAbortedException:事务已中止。 ----> System.Transactions.TransactionPromotionException:尝试提升事务时失败。 ----> System.Data.SqlClient.SqlException:该命令已有与此关联的打开的DataReader,必须先关闭它。 ----> System.ComponentModel.Win32Exception:等待操作超时
我可以将获取子对象的代码移出第一个连接范围(这将起作用),但假设我不想这样做。
这个异常是否意味着在单个TransactionScope内无法同时打开对DB的连接?
编辑
GetCommand
只是调用SqlCommand
构造函数并进行一些日志记录。
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;
}
GetCommand
方法的主体。它非常简单,只是创建了一个SqlCommand
对象。 - aadam