使用Dapper的存储库设计模式 [英] Repository Design Pattern with Dapper

问题描述

这可能是代码审查而不是堆栈溢出的问题。

This is maybe more a question for code review rather than stack overflow.

我正在使用Dapper进行MicroORM检索并将数据保存到SQL Server 2014。在DTO Proj中有DTO类，它们表示从数据库检索或保存到数据库的数据。

I am using Dapper for a MicroORM to retrieve and Save Data to SQL Server 2014. I have got DTO classes in a DTO Proj that represent the Data retrieved from the DB or saved to the DB.

我正在使用存储库模式，因此在服务层中，如果存储库是必需的。我正在使用构造函数DI注入该依赖项，然后在存储库上调用该方法来完成工作。

I am using the Repository Pattern so at my Service layer if a repository is required I am using constructor DI to inject that dependency and then call the method on the Repository to do the work.

所以可以说我有2个名为CustomerService的服务， CarService。

so let say I have 2 services called CustomerService and CarService.

然后我有2个存储库，一个CustomerRepository和一个CarRepository。

I then have 2 Repositories a CustomerRepository and a CarRepository.

我有一个定义所有

下面显示了一个示例方法（调用Stored Proc进行数据库INSERT（注意实际的字符串变量存储的proc在类顶部定义为私有字符串）：

An example method is shown below (calling a Stored Proc to do the DB INSERT (note the actual string variable for the stored proc is defined as a private string at the top of the class):

    public void SaveCustomer(CustomerDTO custDTO)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            db.Execute(saveCustSp, custDTO, commandType: CommandType.StoredProcedure);
        }
    }

这一切都很好，但是我发现自己在重复在每个存储库中的每个方法中使用block。我在下面概述了两个真实的问题。

This all works fine but I am finding myself repeating the using block in every method in every repository. I have two real questions outlined below.

有没有一种更好的方法，也许我可以使用BaseRepository类，其他所有Repository都继承该类，而Base将实现数据库连接的实例化？

Is there a better approach which I could be using perhaps somehow using a BaseRepository class which every other Repository inherits from and the Base would implement the instantiation of the DB connection?

对于系统上的多个并发用户仍然可以正常工作吗？

Would that still work ok for multiple concurrent Users on the system?

**** UPDATE ** **

****UPDATE****

基于Silas的答案，我创建了以下

Based on Silas answer I have created the following

public interface IBaseRepository
{
    void Execute(Action<IDbConnection> query);
}

public class BaseRepository: IBaseRepository
{
        public void Execute(Action<IDbConnection> query)
        {
            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
            {
                query.Invoke(db);
            }
        }
}

但是，在我的存储库中，我还有其他如下方法：

However, in my repositories, I have other methods such as the below:

    public bool IsOnlyCarInStock(int carId, int year)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            var car = db.ExecuteScalar<int>(anotherStoredSp, new { CarID = carId, Year = year },
                                commandType: CommandType.StoredProcedure);

            return car > 0 ? true : false;
        }
    }

和

    public IEnumerable<EmployeeDTO> GetEmployeeDetails(int employeeId)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            return db.Query<EmployeeDTO>(anotherSp, new { EmployeeID = employeeId },
                                commandType: CommandType.StoredProcedure);
        }
    }

将它们添加到我的基础中的正确方法是什么存储库使用通用类型T，因此我可以返回任何类型的DTO或任何C＃本机类型

What is the correct way to add these to my Base repository using Generic Type T so I could return any type of DTO or any C# Native type

推荐答案

当然，该函数可以创建并

Sure, a function to create and dispose your Connection will work great.

protected void Execute(Action<IDbConnection> query)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
    {
        query.Invoke(db);
    }
}

您的简化呼叫站点：

public void SaveCustomer(CustomerDTO custDTO)
{
    Execute(db => db.Execute(saveCustSp, custDTO, CommandType.StoredProcedure));
}

---

具有返回值：

---

With Return Values:

public T Get<T>(Func<IDbConnection, T> query)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
    {
        return query.Invoke(db); 
    }
}

在您的呼叫站点中，只需编写所需的逻辑

In your call site, just write the logic you wish to use.

public IEnumerable<EmployeeDTO> GetEmployeeDetails(int employeeId)
{
    return Get<IEnumerable<EmployeeDTO>(db => 
        db.Query<EmployeeDTO>(anotherSp, new { EmployeeID = employeeId }, CommandType.StoredProcedure));
}

这篇关于使用Dapper的存储库设计模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！