C# 数据访问层的设计模式

Design pattern for Data Access Layer

提问by DevT

You might feel this is homework, for that I am sorry. I have searched but couldn't find a proper answer.

你可能觉得这是作业，为此我很抱歉。我已经搜索过，但找不到正确的答案。

So my question is:

所以我的问题是：

I have several classes and each class has a method to save. So I created a separate class for database handling.

我有几个班级，每个班级都有一个保存方法。所以我创建了一个单独的类来处理数据库。

namespace HospitalMgt.Data
{
    public static class DBConnection
    {
        public static string constr = "Data Source=ABD;Initial Catalog=HospitalMgt;User Id=sa;Password=123";
        public static SqlConnection con;
      //  public static SqlCommand com;

        public static SqlConnection OpenConnection()
        {
            con= new SqlConnection(constr);
            con.Open();
            return con;
        }

    }
}

However, I don't think it's suitable to implement all the classes with a DBConnection class.

但是，我认为用 DBConnection 类实现所有类并不合适。

My question :

我的问题 ：

1. What design pattern is suited to overcome this problem?
2. Is it good practise to create DBConnection as class? (Or should it be an Interface)

1. 什么设计模式适合解决这个问题？
2. 作为类创建 DBConnection 是一种好习惯吗？（或者它应该是一个接口）

I found a few articles about DA layers using the Factory method, but according to my knowledge, that pattern does not suit my situation.

我找到了一些关于使用工厂方法的 DA 层的文章，但据我所知，这种模式不适合我的情况。

采纳答案by Jo?o Sim?es

Normally, if I can't use any existing framework, I use both the Repository and Active patterns.

通常，如果我不能使用任何现有框架，我会同时使用 Repository 和 Active 模式。

For simplicity, you could use only the Repository pattern. I normally define it like this:

为简单起见，您只能使用 Repository 模式。我通常这样定义它：

public interface IEntity<T> { }

//  Define a generic repository interface
public interface IRepository<TKey, TEntity>
    where TEntity : IEntity<TKey>
{
    void Add(TEntity entity);
    void AddRange(IEnumerable<TEntity> entities);
    IEntity<TKey> Get(TKey key);
    IEnumerable<TEntity> GetRange(IEnumerable<TKey> keys);
    IEnumerable<TEntity> GetAll();
    //  ..., Update, Delete methods
}

//  Create an abstract class that will encapsulate the generic code
public abstract class Repository<TKey, TEntity> : IRepository<TKey, TEntity>
    where TEntity : IEntity<TKey>
{
    protected Repository(/*parameter you may need to implement the generic methods, like a ConnectionFactory,  table name, entity type for casts, etc */) { }

    public override void Insert(IEntity<TKey> entity)
    {
        //  do the insert, treat exceptions accordingly and encapsulate them in your own and more concise Exceptions, etc
    }
    //  ...
}

//  Create the entities classes, one for each table, that will represent a row of that table
public class Car : IEntity<string> {/* Properties */}

//  Create a specific repository for each table
//  If the table have a composed key, just create a class representing it
public class CarRepository : Repository<string, Car>
{
    public CarRepository() {/* pass the base parameters */}

    // offer here your specific operations to this table entity
    public IEnumerable<Car> GetByOwner(PersonKey ownerKey)
    {
        //  do stuff
    }
}

Obviously, when doing your own implementations, you must take into account thread safety making good using of transactions, specially across diferent entity repositories.

显然，在进行自己的实现时，您必须考虑线程安全，以便充分利用事务，尤其是跨不同实体存储库。

//  simple example
ITransaction t = TransactionFactory.GetNewTransaction();
t.begin();
try{
    //  create person entity
    personRepository.Add(person, t);
    //  create cars assigned to person
    carRepository.AddRange(cars, t);
    t.commit();
}catch(Exception){
    t.rollback();
}

Just be sure that you really want to create your own DAL since it can end beeing extremelly complex, specially trying to develop the most generic solution.

请确保您真的想创建自己的 DAL，因为它可能会变得极其复杂，特别是尝试开发最通用的解决方案。

回答by Richard Schneider

I suggest using an ORM, Entity Framework or NHibernate will do nicely. Then you do not have to worry about a db context or create SQL statements.

我建议使用 ORM、Entity Framework 或 NHibernate 会很好。然后您不必担心数据库上下文或创建 SQL 语句。

回答by Sergey Brunov

First of all, I would like to recommend you the article Design Patterns for Data Persistence by Jeremy Miller.

首先，我想向您推荐Jeremy Miller的文章Design Patterns for Data Persistence。

There are some data access layer patterns:

有一些数据访问层模式：

1. Active record pattern(wiki, Detailed info).
2. Repository pattern(Detailed info).

1. 活动记录模式（维基，详细信息）。
2. 存储库模式（详细信息）。

回答by Pablo Rodríguez

I suggest you to use a RepositoryBase for all this common operations. If you decide to use an ORM for data access it's good to think in a implementation of repositories based on a Generic Type repository.

我建议您将 RepositoryBase 用于所有这些常见操作。如果您决定使用 ORM 进行数据访问，最好考虑基于通用类型存储库的存储库实现。

Here is a good article about it:

这是一篇关于它的好文章：

http://lostechies.com/jimmybogard/2009/09/03/ddd-repository-implementation-patterns/

http://lostechies.com/jimmybogard/2009/09/03/ddd-repository-implementation-patterns/

回答by Amit Joshi

Its too old but just came around this question and could not resist to post my thoughts.

它太旧了，但刚刚解决了这个问题，无法抗拒发表我的想法。

I found Repositorywith UnitOfWorkwith some descent ORM is good approach. This minimizes most of the issues.

我发现带有UnitOfWork 的Repository和一些下降 ORM 是一个很好的方法。这最大限度地减少了大多数问题。

The UoW mentioned in above link can be injected in Repository. That increases the flexibility of usage. Also, all DB Communication code is centralized at one place. The example is not complete but a startup point.

上面链接中提到的 UoW 可以注入到 Repository 中。这增加了使用的灵活性。此外，所有 DB 通信代码都集中在一个地方。该示例并不完整，而是一个启动点。

Repository pattern mentioned in above link is actually a generic base class. You can create new class for each of your concrete Repository that derives from it.

上面链接中提到的存储库模式实际上是一个通用基类。您可以为从中派生的每个具体存储库创建新类。

Generic repository is considered an anti pattern; there are lot many articles on internet that explains it.

通用存储库被认为是一种反模式；互联网上有很多文章对此进行了解释。

Why generic repository is anti-pattern?

为什么通用存储库是反模式的？

1. A repository is a part of the domain being modeled, and that domain is not generic.
   • Not every entity can be deleted.
   • Not every entity can be added
   • Not every entity has a repository.
   • Queries vary wildly; the repository API becomes as unique as the entity itself.
   • For GetById(), identifier types may be different.
   • Updating specific fields (DML) not possible.
2. Generic query mechanism is the responsibility of an ORM.
   • Most of the ORMs expose an implementation that closely resemble with Generic Repository.
   • Repositories should be implementing the SPECIFIC queries for entities by using the generic query mechanism exposed by ORM.
3. Working with composite keys is not possible.
4. It leaks DAL logic in Services anyway.
   • Predicate criteria if you accept as parameter needs to be provided from Service layer. If this is ORM specific class, it leaks ORM into Services.

1. 存储库是正在建模的域的一部分，该域不是通用的。
   • 并非每个实体都可以删除。
   • 并非每个实体都可以添加
   • 并非每个实体都有一个存储库。
   • 查询千差万别；存储库 API 变得与实体本身一样独特。
   • 对于GetById()，标识符类型可能不同。
   • 无法更新特定字段 (DML)。
2. 通用查询机制是 ORM 的职责。
   • 大多数 ORM 公开了一个与通用存储库非常相似的实现。
   • 存储库应该使用 ORM 公开的通用查询机制来实现实体的 SPECIFIC 查询。
3. 无法使用复合键。
4. 无论如何，它会泄漏服务中的 DAL 逻辑。
   • 如果您接受作为参数的谓词标准，则需要从服务层提供。如果这是 ORM 特定的类，它会将 ORM 泄漏到服务中。

I suggest you read these (1, 2, 3, 4, 5) articles explaining why generic repository is an anit-pattern.

我建议你阅读这些（1, 2, 3, 4, 5）文章，解释为什么通用存储库是一个反模式。

Solution:

解决方案：

1. Write an abstract Generic Repository that is wrapped by a concrete repository. That way you can control the public interface but still have the advantage of code-reuse that comes from generic repository.
2. Use a Generic Repository but do use composition instead of inheritance and do not expose it to the domain as a contract.

1. 编写一个由具体存储库包装的抽象通用存储库。这样您就可以控制公共接口，但仍然具有来自通用存储库的代码重用的优势。
2. 使用通用存储库，但确实使用组合而不是继承，并且不要将其作为合同公开给域。

In any case, do not expose Generic Repository to calling code. Also, do not expose IQueryablefrom concrete repositories.

在任何情况下，不要将通用存储库暴露给调用代码。另外，不要IQueryable从具体的存储库中公开。