DAL with dapper and C#

问题:

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?

回答1:

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

回答2:

I have been using Dapper on a project of mine recently and I have grown to like the fact its very bare bones. This is at least some of the reason its so fast.

But I do also understand what you are saying with regard to the repetition, If you are calling dbo.stp_Product_Search from many locations in your code you don't want to have to map the parameters and write out the entire query block each time.

You could look at wrapping Dapper inside a repository, but I dont like that approach, to me it feels like its a step back from the bare bones that I've grown to love about dapper.

I would consider a manager class.

public class ProductManager : IProductManager {
    //Constructor code here
    public IEnumerable<Product> ProductSearch(int? userId, DateTime? modifiedAfter, DateTime? modifiedBefore, Guid? productId)
    {
        using (var connection = _connection.OpenConnection())
        {
            const string sproc = "dbo.stp_Product_Search";

            return connection.Query<Product>(sproc, new
            {
                User_ID = userId,
                Modified_After = modifiedAfter,
                Modified_Before = modifiedBefore,
                Product_ID = productId
            }, commandType: CommandType.StoredProcedure);
        }
    }
}

Make use of the above class to call product specific reusable bits of code.