When trying to insert an entity into a database using a unitOfWork with an transactionScope. I will actually get an expected results back of which user I added (int expectedUserid = uowFactory.Create().AddBasicUser(user);). But after the UnitOfWork & transactionScope are disposed I don't see any changes in the database. This make me believe I have implemented the transactionScope incorrectly in the UnitOfWork.
I also noticed that when running (debugging) the following unitTest and have a breakPoint on the following line : 60, I am not able to query the database table of 'BasicUser' via MSSQL mngmnt studio (Blocked, JustSaying : Executing query...).
The UnitTest itself passes.
Question: Why does it seem the UnitOfWork works as intended, but do I not see any changes in the database? And how it comes that the Db is blocked while the transactionScope/UnitOfWork is already disposed.
Thanks a lot!
Refrence Code:
IUnitOfWorkFactory
namespace DomainServices
{
public interface IUnitOfWorkFactory
{
IUnitOfWork Create();
}
}
UnitOfWorkFactory
using System;
using DomainServices;
using System.Transactions;
namespace DataAccess
{
public class UnitOfWorkFactory
{
private readonly IsolationLevel _isolationLevel;
private readonly Func<IsolationLevel, IUnitOfWork> CreateUnitOfWork;
public UnitOfWorkFactory(Func<IsolationLevel, IUnitOfWork> createUnitOfWork, IsolationLevel isolationLevel)
{
_isolationLevel = isolationLevel;
CreateUnitOfWork = createUnitOfWork;
}
public IUnitOfWork Create()
{
return CreateUnitOfWork.Invoke(_isolationLevel);
}
}
}
IUnitOfWork
using System;
namespace DomainServices
{
public interface IUnitOfWork : IDisposable
{
IUserRepository UserRepository { get; }
void Dispose();
void Commit();
}
}
UnitOfWork
using DataAccess.Repository;
using DomainServices;
using System.Transactions;
namespace DataAccess
{
public class UnitOfWork : IUnitOfWork
{
private readonly DatabaseConnection _dbConnection;
private readonly TransactionScope _transactionScope;
private bool isDisposed;
public IUserRepository _userRepository;
public IUserRepository UserRepository
{
get
{
if (this._userRepository == null)
{
this._userRepository = new UserRepository(this._dbConnection, this._transactionScope);
}
return this._userRepository;
}
}
public UnitOfWork(DatabaseConnection dbConnection, IsolationLevel isolationLevel)
{
this.isDisposed = false;
this._dbConnection = dbConnection;
this._transactionScope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions
{
IsolationLevel = isolationLevel,
Timeout = TransactionManager.DefaultTimeout
});
}
public void Commit()
{
this._transactionScope.Complete();
}
public void Dispose()
{
this.Dispose(true);
}
protected void Dispose(bool disposing)
{
if (!this.isDisposed)
{
if (disposing)
{
this._transactionScope.Dispose();
}
this.isDisposed = true;
}
}
}
}
IUserRepository
using Domain;
using System.Collections.Generic;
namespace DomainServices
{
public interface IUserRepository
{
int AddBasicUser(BasicUser basicUser);
IEnumerable<BasicUser> GetAllBasicUsers();
}
}
UserRepository
using System.Collections.Generic;
using Domain;
using DomainServices;
using System.Data.SqlClient;
using System;
using System.Transactions;
namespace DataAccess.Repository
{
public class UserRepository : IUserRepository
{
private readonly DatabaseConnection _dbConnection;
private readonly TransactionScope _transaction;
public UserRepository(DatabaseConnection dbConnection, TransactionScope transaction)
{
this._dbConnection = dbConnection;
this._transaction = transaction;
}
public int AddBasicUser(BasicUser basicUser)
{
string QueryString = "INSERT INTO BasicUser (Username, RegisterDate) OUTPUT Inserted.Id VALUES (@username, @registerDate);";
TODO MarWolt: Use a Maybe instead of magic values.
int basicUserId = -1;
//using (TransactionScope scope = new TransactionScope())
using (SqlConnection conn = new SqlConnection(_dbConnection.ConnectionString))
using (SqlCommand cmd = new SqlCommand(QueryString, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("Username", basicUser.UserName.Value);
cmd.Parameters.AddWithValue("RegisterDate", basicUser.RegisterDate);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
basicUserId = Convert.ToInt32(reader["Id"]);
}
}
scope.Complete();
}
return basicUserId;
}
public IEnumerable<BasicUser> GetAllBasicUsers()
{
var QueryString = "SELECT Id, Username, LastLogin, RegisterDate FROM BasicUser;";
var result = new List<BasicUser>();
using (var conn = new SqlConnection(_dbConnection.ConnectionString))
using (var cmd = new SqlCommand(QueryString, conn))
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
result.Add(CreateBasicUserFromReader(reader));
}
return result;
}
}
private BasicUser CreateBasicUserFromReader(SqlDataReader reader)
{
return new BasicUser(
id: Convert.ToInt32(reader["Id"]),
userName: new UserName(Convert.ToString(reader["Username"])),
lastLogin: DateTime.Now, //Convert.ToDateTime(reader["LastLogin"]), //TODO MarWolt This...
registerDate: Convert.ToDateTime(reader["RegisterDate"]),
rideTokens: new List<RideToken>()
);
}
}
}
UnitTestProject
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Transactions;
using DataAccess;
using DataAccess.Repository;
using Domain;
using DomainServices;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace UnitTestProject
{
[TestClass]
public class UserRepositoryTests
{
private UnitOfWorkFactory uowFactory;
private DatabaseConnection testDatabaseConnection;
public UserRepositoryTests()
{
testDatabaseConnection = new DatabaseConnection(
"Server=.\\SQLEXPRESS;Initial Catalog=TestDb;User Id=DbUser;Password=DbUserPassword;Integrated Security=false;Connect Timeout=5;");
uowFactory = new UnitOfWorkFactory((IsolationLevel) => new UnitOfWork(testDatabaseConnection, IsolationLevel), IsolationLevel.ReadCommitted);
}
[TestInitialize()]
public void Initialize()
{
//TODO MarWolt: delete everything from the database on a clean way.
using (SqlConnection conn = new SqlConnection(testDatabaseConnection.ConnectionString))
using (SqlCommand cmd = new SqlCommand(Properties.Resources.ClearDbSqlScript, conn))
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
[TestMethod]
public void AddNewBasicUser()
{
var user = new BasicUser(new UserName("UnitTestBasicUserName"), DateTime.UtcNow, new List<RideToken>() { });
var users = uowFactory.Create().UserRepository.GetAllBasicUsers();
Assert.AreEqual(0, users.Count());
using (IUnitOfWork unitOfWork = uowFactory.Create())
{
unitOfWork.UserRepository.AddBasicUser(user);
unitOfWork.Commit();
}
users = uowFactory.Create().UserRepository.GetAllBasicUsers();
Assert.AreEqual(1, users.Count());
var recievedUser = users.First();
Assert.AreEqual(user.UserName.Value, recievedUser.UserName.Value);
// I'm unable to query the database via mssql server management studio when this breakpoint is active.
// I would actually expect it already to be done after line 51.
Console.WriteLine(string.Empty);
}
}
}
I've taken the time to fully recreate your question into a git repo: SafariUOW. And for me it works just fine.
Why does it seem the UnitOfWork works as intended, but do i not see any changes in the database?
I assume there is a problem in your database with read or write rights if it doesn't actually "Commit".
And how it comes that the Db is blocked while the transactionScope/UnitOfWork is already disposed.
Well, Db access should be blocked if the transactionScope or UnitOfWork is disposed. It could be you are creating a Transaction with an incorrect Isolationlevel. You could look in the IsolationLevel msdn docs what the correct level is for your scope.
Please let me know if this answer is good enough for your question.
Regards, Mario