I have a data access layer that utilises Dapper but can't help feeling that it could be far more elegant. The DAL is just passing in parameters and mapping the model as per the named responses of the model so that part is straight forward at least, but I hate code that looks duplicated.
Here is an example
public IEnumerable<Product> ProductSearch(int? userId, DateTime? modifiedAfter, DateTime? modifiedBefore, Guid? productId)
{
IList<Product> products;
using (var connection = _connection.OpenConnection())
{
const string sproc = "dbo.stp_Product_Search";
products = connection.Query<JobProduct>(sproc, new
{
User_ID = userId,
Modified_After = modifiedAfter,
Modified_Before = modifiedBefore,
Product_ID = productId
}, commandType: CommandType.StoredProcedure)
.ToList();
}
return products;
}
I have lots of code that is like this but with different parameters and entities used. Has anyone got any good examples?
Thanks to the suggestions. This is what I've used in the end and means I don't have to write using statements opening connections each time making my classes less lines of code:
public class Repository<T> where T : class
{
protected readonly IComplianceConnection Connection;
public Repository(IComplianceConnection connection)
{
Connection = connection;
}
public IEnumerable<T> Get(string query, object arguments)
{
IList<T> entities;
using (var connection = Connection.OpenConnection())
{
entities = connection.Query<T>(query, arguments, commandType: CommandType.StoredProcedure).ToList();
}
return entities;
}
public T GetSingleOrDefault(string query, object arguments)
{
T entity;
using (var connection = Connection.OpenConnection())
{
entity =
connection.Query<T>(query, arguments, commandType: CommandType.StoredProcedure).SingleOrDefault();
}
return entity;
}
public void Update(string query, object arguments)
{
using (var connection = Connection.OpenConnection())
{
connection.Execute(query, arguments, commandType: CommandType.StoredProcedure);
}
}
public int ExecuteScalar(string query, object arguments)
{
var id = 0;
using (var connection = Connection.OpenConnection())
{
id = connection.ExecuteScalar<int>(query, arguments, commandType: CommandType.StoredProcedure);
}
return id;
}
}