A lot of time, I face a situation where I want to execute a number of statements without re-opening a connection.
So my practice so far is to create a 'unit of work' class, open a connection and pass it to all the repositories.
Here is an example of code that I have:
public class BasicEmailUnitOfWork : IBasicEmailUnitOfWork
{
private readonly IDNTConnectionFactory _conn;
public BasicEmailUnitOfWork(IDNTConnectionFactory connection)
{
_conn = connection;
}
public (string, string, string, string, string) RenderEmailTemplate(string emailTemplateEventName, int userId)
{
string userPhone = String.Empty;
string userEmail = String.Empty;
string emailTitletranslatedContent = String.Empty;
string emailBodytranslatedContent = String.Empty;
string smstranslatedContent = String.Empty;
try
{
IDbConnection connectionDb = _conn.GetConnection();
IEmailSMSTemplateRepository _emailSMSTemplateRepository = new EmailSMSTemplateRepository(connectionDb);
IUserInformationRepository _userInformationRepository = new UserInformationRepository(connectionDb);
List<EmailSMSTemplateTDO> emailSmsesList = _emailSMSTemplateRepository.GetAllTemplates(emailTemplateEventName);
UserInfoDTO userInfoDTO = _userInformationRepository.GetAllInformation(userId);
userPhone = userInfoDTO.Phone;
userEmail = userInfoDTO.Email;
foreach (EmailSMSTemplateTDO emailTemplate in emailSmsesList)
{
emailTitletranslatedContent = TranslateContent(connectionDb, userInfoDTO, emailTemplate.EmailTitle);
emailBodytranslatedContent = TranslateContent(connectionDb, userInfoDTO, emailTemplate.EmailBody);
if (emailTemplate.IsSMS)
smstranslatedContent = TranslateContent(connectionDb, userInfoDTO, emailTemplate.SMSBody);
}
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
_conn.CloseConnection();
}
return (userPhone, userEmail,emailTitletranslatedContent, emailBodytranslatedContent, smstranslatedContent);
}
private string TranslateContent(IDbConnection connectionDb, UserInfoDTO userInfoDTO, string content)
{
InterpreterContext intContext = new InterpreterContext();
intContext.user = userInfoDTO;
intContext.Content = content;
IExpression emailSMSContentInterpreter = new EmailSMSContentInterpreter(connectionDb);
emailSMSContentInterpreter.Interpret(intContext);
return emailSMSContentInterpreter.BodyContentOutPut;
}
}
Although, I can unit test the repositories without a problem, I have 2 dependencies in the function: EmailSMSTemplateRepository and UserInformationRepository.
What is the best practice? What other way is there to share the connection, take note also that I need some class to dispose of the connection when the object is being disposed or if there is an error.
By the way, I use Dapper for the application, same micro-ORM that was used to build this site.
In the past I've approached this via the use of a Connection Factory -> Data Session -> Unit of Work -> Repository pattern.
Something like -
public interface IUnitOfWork : IDisposable
{
Guid Id { get; }
IDbConnection Connection { get; }
IDbTransaction Transaction { get; }
void Begin();
void Commit();
void Rollback();
}
public sealed class UnitOfWork : IUnitOfWork
{
internal UnitOfWork(IDbConnection connection)
{
_id = Guid.NewGuid();
_connection = connection;
}
private readonly IDbConnection _connection = null;
private IDbTransaction _transaction = null;
private readonly Guid _id;
IDbConnection IUnitOfWork.Connection => _connection;
IDbTransaction IUnitOfWork.Transaction => _transaction;
Guid IUnitOfWork.Id => _id;
public void Begin()
{
_transaction = _connection.BeginTransaction();
}
public void Commit()
{
_transaction.Commit();
Dispose();
}
public void Rollback()
{
_transaction.Rollback();
Dispose();
}
public void Dispose()
{
_transaction?.Dispose();
_transaction = null;
}
}
//define enum's that describe various databases you might want to connect to
//the db type/engine is an implementation detail handled by the connection factory
public enum DatabaseInstance
{
YourDatabase,
SomeOtherDatabase,
AThirdDatabase
}
//abstracts the 'how do i connect to the database?' question
public interface IDbConnectionFactory
{
IDbConnection GetConnection(DatabaseInstance database);
}
//for instance -
public class SqlDbConnectionFactory : IDbConnectionFactory
{
public IDbConnection GetConnection(DatabaseInstance database)
{
//return a IDbConnection for a given DatabaseInstance value
//possible implementations could be storing a DatabaseInstance->Connectionstring map dictionary that is populated in the constructor
}
}
public interface IDataAccessSession : IDisposable
{
IUnitOfWork UnitOfWork { get; }
void StartSession(DatabaseInstance databaseInstance);
}
//one possible implementation - use a connection factory and requested instance to create an IUnitOfWork for consumers to use
public sealed class DataAccessSession : IDataAccessSession
{
private IDbConnection _connection = null;
private readonly IDbConnectionFactory _dbConnectionFactory;
public DalSession(IDbConnectionFactory dbConnectionFactory)
{
_dbConnectionFactory = dbConnectionFactory;
}
public void StartSession(DatabaseInstance databaseInstance)
{
_connection = _dbConnectionFactory.GetConnection(databaseInstance);
_connection.Open();
UnitOfWork = new UnitOfWork(_connection);
}
public IUnitOfWork UnitOfWork { get; private set; }
public void Dispose()
{
UnitOfWork.Dispose();
_connection.Dispose();
}
}
//then there's an extension method (in practice a helper overload per DatabaseInstance as well
//starts a session, passes the unit of work to the consumer task, commit/rollsback, and then disposes
public static class DataAccessSessionExtensions
{
public static async Task<T> RunTransactionAsync<T>(this IDataAccessSession dataAccessSession, DatabaseInstance instance, Func<IUnitOfWork, Task<T>> functionToRun, bool rollbackOnException=false)
{
dataAccessSession.StartSession(instance);
dataAccessSession.UnitOfWork.Begin();
try
{
var result = await functionToRun(dataAccessSession.UnitOfWork);
dataAccessSession.UnitOfWork.Commit();
return result;
}
catch (SqlException)
{
if(rollbackOnException)
dataAccessSession.UnitOfWork.Rollback();
else
dataAccessSession.UnitOfWork.Commit();
throw;
}
}
}
//then in your dependency injection container (asp.net core here)
....
//if you needed to say connect to mongo for one and sql for another this would need a bit further refinement as the MS DI Api doesn't handle that well, you'd need to do something like pass collections of connection factory/instance->connections instead as one possible solution
//this can be a singleton as it doesn't store live connections, just how to map requests to new connections
services.AddSingleton<IDbConnectionFactory>(i => new SqlDbConnectionFactory(/*pass in a values for connection strings per data base instance defined*/));
//should be scoped so that a new instance is created/disposed per DI request
services.AddScoped<IDalSession, DalSession>();
....
//finally usage would be like so
public class SomeService : ISomeService
{
private readonly IDataAccessSession _dataAccessSession;
private readonly ISomeRepo _someRepo;
Private readonly ISomeOtherRepo _someOtherRepo
public SomeService (IDataAccessSession dataAccessSession, ISomeRepo someRepo, ISomeOtherRepo someOtherRepo)
{
_dataAccessSession = dataAccessSession;
_someRepo = someRepo;
_someOtherRepo = someOtherRepo;
}
// at this point all needed repo's are handed a single UnitOfWork that represents the connection and transaction and a session that will clean up afterwards
public async Task<SomeResult> DoSomeThing(string param1, int param2)
{
var result = await _dataAccessSession.RunTransactionAsync(DatabaseInstance.Whatever, async uow =>
{
//critical - forget this and things will go boom
_someRepo.UnitOfWork = uow;
_someOtherRepo.UnitOfWork = uow;
var repo1Value = await _someRepo.SomeAction(param1);
var repo2Value = await _someOtherRepo.GetAThing(param2);
return new SomeResult(repo1Value, repo2Value);
});
return Task.FromResult(result);
}
}
One weak point was that I never did find a better way to pass the unit of work to the repo layer besides requiring them to implement a receiving property and then populated it before use. It means it would be easy for a dev to forget that step and run into errors.
public interface IHasUnitOfWork
{
IUnitOfWork UnitOfWork { get; set; }
}
public interface IDataRepo : IHasUnitOfWork
{
}
public interface ISomeRepo : IDataRepo
{
Task UpdateSomethingAsync();
}
//at the time of implementing the repo the promise is that UnitOfWork will be non-null with a valid open connection
//that is ready to exec against and that if a sql exception is thrown a rollback will be initiated (if flag is set)
//though note that if multiple repos are sharing this unit of work and a different one throws, this repo's calls will also rollback as it considers all actions to be under a single transaction scope per IDataAccessSession
public SomeRepo : ISomeRepo
{
public IUnitOfWork UnitOfWork { get; set; }
public async Task UpdateSomethingAsync()
{
await UnitOfWork.Connection.ExecuteAsync("sproc", .....);
}
}