DAL with dapper and C#

Question

DAL with dapper and C#

c# dapper

Question

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?

Anthony Joanes

View more on Stack Overflow

Anthony Joanes · Accepted Answer · 7/6/2015 3:34:26 PM

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;
    }
}

DAL with dapper and C#

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator