[Edited based on input from MikeSW]
My opinion (joining Moo-Juice here) is that you need to pick the implementation that best suits you. The repository pattern is a good one (Gabriel's answer describes a good implementation), however it can be a lot of work if implemented in its pure form. ORMs automate a lot of the grunt work.
Whichever approach you choose, you will need the following components:
Your business interface - the methods your client side programmers would need to call, such as GetAllEmployees(criteria), UpdateEmployee(Employee employee), etc. If you have client/server architecture, these would correspond to service calls with data contracts.
Your internal logic that creates the proper output to satisfy your contracts. This would be the layer that composes queries, or performs multiple database updates, for example UpdateEmployee might have to validate that the employee exists, that the updater has permissions to update, then update several tables, and insert audit records or records into a review queue. This would involve querying and updating, and would be one unit of work.
Your data access architecture, called by your internal logic. This is where the repository pattern would come in. This needs, regardless of what you use, the following:
3.1 A class to implement unit of work . In repository pattern, this has only Save() - but that requires in-memory state management. I prefer using the following interface for sql-driven implementation:
public interface ITransactionContext : IDisposable
{
IDbTransaction BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted);
void CommitTransaction();
void RollbackTransaction();
int ExecuteSqlCommand(string sql, params object[] parameters);
IEnumerable<T> SqlQuery<T>(string sql, params object[] parameters);
IEnumerable<T> SqlQuery<T>(string sql, object[] parameters, IDictionary<string, string> mappings);
bool Exists(string sql, params object[] parameters);
}
public interface ITransactionDbContext : ITransactionContext
{
int SaveChanges();
}
I use EF but we have an old db where we need to write SQL, and this looks and operates a lot like the EF DbContext. Note the interace ITransactionDbContext, that adds the SaveChanges() - which is the only one an ORM needs. But if you don't do ORM, you need the others.
This is the implementation. Note that it is entirely interface-based. You can give your concrete database connection via the factory method.
public class TransactionContext : ITransactionContext
{
protected IDbTransaction Transaction;
protected IDbConnection Connection;
protected readonly Func<IDbConnection> CreateConnection;
public TransactionContext(Func<IDbConnection> createConnection)
{
this.CreateConnection = createConnection;
}
public virtual IDbConnection Open()
{
if (this.Connection == null)
{
this.Connection = this.CreateConnection();
}
if (this.Connection.State == ConnectionState.Closed)
{
this.Connection.Open();
}
return this.Connection;
}
public virtual IDbTransaction BeginTransaction(IsolationLevel isolationLevel)
{
Open();
return this.Transaction ?? (this.Transaction = this.Connection.BeginTransaction(isolationLevel));
}
public virtual void CommitTransaction()
{
if (this.Transaction != null)
{
this.Transaction.Commit();
}
this.Transaction = null;
}
public virtual void RollbackTransaction()
{
if (this.Transaction != null)
{
this.Transaction.Rollback();
}
this.Transaction = null;
}
public virtual int ExecuteSqlCommand(string sql, params object[] parameters)
{
Open();
using (var cmd = CreateCommand(sql, parameters))
{
return cmd.ExecuteNonQuery();
}
}
public virtual IEnumerable<T> SqlQuery<T>(string sql, object[] parameters )
{
return SqlQuery<T>(sql, parameters, null);
}
public IEnumerable<T> SqlQuery<T>(string sql, object[] parameters, IDictionary<string, string> mappings)
{
var list = new List<T>();
var converter = new DataConverter();
Open();
using (var cmd = CreateCommand(sql, parameters))
{
var reader = cmd.ExecuteReader();
if (reader == null)
{
return list;
}
var schemaTable = reader.GetSchemaTable();
while (reader.Read())
{
var values = new object[reader.FieldCount];
reader.GetValues(values);
var item = converter.GetObject<T>(schemaTable, values, mappings);
list.Add(item);
}
}
return list; }
public virtual bool Exists(string sql, params object[] parameters)
{
return SqlQuery<object>(sql, parameters).Any();
}
protected virtual IDbCommand CreateCommand(string commandText = null, params object[] parameters)
{
var command = this.Connection.CreateCommand();
if (this.Transaction != null)
{
command.Transaction = this.Transaction;
}
if (!string.IsNullOrEmpty(commandText))
{
command.CommandText = commandText;
}
if (parameters != null && parameters.Any())
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected void Dispose(bool disposing)
{
if (this.Connection != null)
{
this.Connection.Dispose();
}
this.Connection = null;
this.Transaction = null;
}
}
3.2.
Then you need to implement an Update based on a command. Here is mine (simplified):
public class UpdateHelper
{
private readonly ITransactionContext transactionContext;
public UpdateHelper(ITransactionContext transactionContext)
{
this.transactionContext = transactionContext;
}
public UpdateResponse Update(UpdateRequest request)
{
this.transactionContext.BeginTransaction(IsolationLevel.RepeatableRead);
var response = new UpdateResponse();
foreach (var command in request.Commands)
{
try
{
response = command.PerformAction(transactionContext);
if (response.Status != UpdateStatus.Success)
{
this.transactionContext.RollbackTransaction();
return response;
}
}
catch (Exception ex)
{
this.transactionContext.RollbackTransaction();
return HandleException(command, ex);
}
}
this.transactionContext.CommitTransaction();
return response;
}
private UpdateResponse HandleException(Command command, Exception exception)
{
Logger.Log(exception);
return new UpdateResponse { Status = UpdateStatus.Error, Message = exception.Message, LastCommand = command };
}
}
As you see, this takes a Command that will perform the action (which is the Command pattern). Basic command implementation:
public class Command
{
private readonly UpdateCommandType type;
private readonly object data;
private readonly IDbMapping mapping;
public Command(UpdateCommandType type, object data, IDbMapping mapping)
{
this.type = type;
this.data = data;
this.mapping = mapping;
}
public UpdateResponse PerformAction(ITransactionContext context)
{
var commandBuilder = new CommandBuilder(mapping);
var result = 0;
switch (type)
{
case UpdateCommandType.Insert:
result = context.ExecuteSqlCommand(commandBuilder.InsertSql, commandBuilder.InsertParameters(data));
break;
case UpdateCommandType.Update:
result = context.ExecuteSqlCommand(commandBuilder.UpdateSql, commandBuilder.UpdateParameters(data));
break;
case UpdateCommandType.Delete:
result = context.ExecuteSqlCommand(commandBuilder.DeleteSql, commandBuilder.DeleteParameters(data));
break;
}
return result == 0 ? new UpdateResponse { Status = UpdateStatus.Success } : new UpdateResponse { Status = UpdateStatus.Fail };
}
}
3.3 You need the object to database mapping. This is used by the update methods. In this example, if the mappings are ommitted, it is assumed that the EntityType's properties correspond to the database columns. You want a mapping for each table.
public interface IDbMapping
{
string TableName { get; }
IEnumerable<string> Keys { get; }
Dictionary<string, string> Mappings { get; }
Type EntityType { get; }
bool AutoGenerateIds { get; }
}
public class EmployeeMapping : IDbMapping
{
public string TableName { get { return "Employee"; } }
public IEnumerable<string> Keys { get { return new []{"EmployeeID"};} }
public Dictionary<string, string> Mappings { get { return null; } } // indicates default mapping based on entity type } }
public Type EntityType { get { return typeof (Employee); } }
public bool AutoGenerateIds { get { return true; } }
}
3.4. You need a query builder object. This builds your query based on user input in sql. For example, you might want to search employees by lastname, firstname, department, and join date. You could implement a query interface like this:
public interface IEmployeeQuery {
IEmployeeQuery ByLastName(string lastName);
IEmployeeQuery ByFirstName(string firstName);
IEmployeeQuery ByDepartment(string department);
IEmployeeQuery ByJoinDate(Datetime joinDate);
}
This can be concretely implemented by a class that either builds a sql query or a linq query. If you are going with sql, implement string Statement and object[] Parameters. Then your logic layer can write code like this:
public IEnumerable<Employee> QueryEmployees(EmployeeCriteria criteria) {
var query = new EmployeeQuery();
query.ByLastName(criteria.LastName);
query.ByFirstName(criteria.FirstName);
//etc.
using(var dbContext = new TransactionContext()){
return dbContext.SqlQuery<Employee>(query.Statement, query.Parameters);
}
}
3.5. You need a command builder for your objects. I suggest you use a common commandbuilder. You can either use the SqlCommandBuilder class, or you can write your own SQL generator. I do not suggest you write sql for every table and every update. That's the part that will be really tough to maintain. (Speaking from experience. We had one and we couldn't maintain it, eventually I wrote a SQL generator.)
Note: if you do not have many updates (i.e. your application is mostly display-oriented), you might omit this and just write your updates by hand when you need them.
Here is a generic builder (this code is NOT TESTED, you need to work it as you need it):
public interface ICommandBuilder
{
string InsertSql { get; }
string UpdateSql { get; }
string DeleteSql { get; }
Dictionary<string, object> InsertParameters(object data);
Dictionary<string, object> UpdateParameters(object data);
Dictionary<string, object> DeleteParameters(object data);
}
public class CommandBuilder: ICommandBuilder
{
private readonly IDbMapping mapping;
private readonly Dictionary<string, object> fieldParameters;
private readonly Dictionary<string, object> keyParameters;
public CommandBuilder(IDbMapping mapping)
{
this.mapping = mapping;
fieldParameters = new Dictionary<string, object>();
keyParameters = new Dictionary<string, object>();
GenerateBaseSqlAndParams();
}
private void GenerateBaseSqlAndParams()
{
var updateSb = new StringBuilder();
var insertSb = new StringBuilder();
var whereClause = new StringBuilder(" WHERE ");
updateSb.Append("Update " + mapping.TableName + " SET ");
insertSb.Append("Insert Into " + mapping.TableName + " VALUES (");
var properties = mapping.EntityType.GetProperties(); // if you have mappings, work that in
foreach (var propertyInfo in properties)
{
var paramName = propertyInfo.Name;
if (mapping.Keys.Contains(propertyInfo.Name, StringComparer.OrdinalIgnoreCase))
{
keyParameters.Add(paramName, null);
if (!mapping.AutoGenerateIds)
{
insertSb.Append(paramName + ", ");
}
whereClause.Append(paramName + " = @" + paramName);
}
updateSb.Append(propertyInfo.Name + " = @" + paramName + ", ");
fieldParameters.Add(paramName, null);
}
updateSb.Remove(updateSb.Length - 2, 2); // remove the last ","
insertSb.Remove(insertSb.Length - 2, 2);
insertSb.Append(" )");
this.InsertSql = insertSb.ToString();
this.UpdateSql = updateSb.ToString() + whereClause;
this.DeleteSql = "DELETE FROM " + mapping.TableName + whereClause;
}
public string InsertSql { get; private set; }
public string UpdateSql { get; private set; }
public string DeleteSql { get; private set; }
public Dictionary<string, object> InsertParameters(object data)
{
PopulateParamValues(data);
return mapping.AutoGenerateIds ? fieldParameters : keyParameters.Union(fieldParameters).ToDictionary(pair => pair.Key, pair => pair.Value);
}
public Dictionary<string, object> UpdateParameters(object data)
{
PopulateParamValues(data);
return fieldParameters.Union(keyParameters).ToDictionary(pair => pair.Key, pair => pair.Value);
}
public Dictionary<string, object> DeleteParameters(object data)
{
PopulateParamValues(data);
return keyParameters;
}
public void PopulateParamValues(object data)
{
var properties = mapping.EntityType.GetProperties(); // if you have mappings, work that in
foreach (var propertyInfo in properties)
{
var paramName = propertyInfo.Name;
if (keyParameters.ContainsKey(paramName))
{
keyParameters[paramName] = propertyInfo.GetValue(data);
}
if (fieldParameters.ContainsKey(paramName))
{
fieldParameters[paramName] = propertyInfo.GetValue(data);
}
}
}
}
Example usage of the update with the update helper and a command builder in the logic layer for update:
public class Logic
{
private readonly Func<ITransactionContext> createContext;
private readonly Func<ITransactionContext, UpdateHelper> createHelper;
public Logic(Func<ITransactionContext> createContext,
Func<ITransactionContext, UpdateHelper> createHelper)
{
this.createContext = createContext;
this.createHelper = createHelper;
}
public int UpdateEmployee(Employee employeeData)
{
using (var context = createContext())
{
var request = new UpdateRequest();
request.Commands.Add(new Command(UpdateCommandType.Update, employeeData, new EmployeeMapping()));
var helper = createHelper(context);
var response = helper.Update(request);
return response.TransactionId ?? 0;
}
}
}
The ORM would really help you with:
- data mapping
- command building (you don't need to do it)
- query building - you can use the built-in Linq-to-Sql.
Overall, this approach uses the Unit of Work from the Repository pattern, but instead of the repository object and its Add, Update and Delete methods, it uses an UpdateHelper class to do the updates based on the command pattern. This allows writing SQL directly, without an ORM mapper.
Well, this was long, but apparently without all the detail my answer was deemed unworthy. I hope this helps.
