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

问题描述

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

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

我在 MicroORM 中使用 Dapper 来检索数据并将其保存到 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.

我有一个接口，它定义了每个 Repository 中的所有方法，然后是具体的实现.

I have an interface which defines all the methods in each Repository and then the concrete implementations.

下面显示了一个示例方法(调用一个存储过程来执行数据库插入(注意存储过程的实际字符串变量在类的顶部被定义为私有字符串):

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

这一切正常，但我发现自己在每个存储库的每个方法中重复使用块.我有两个真实的问题概述如下.

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 都继承自 BaseRepository 类，而 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?

****更新****

基于 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

推荐答案

当然，创建和处理 Connection 的函数会很好用.

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

以及您的简化呼叫站点:

And your simplified call site:

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屋！