如何在 .NET 中使用 Dapper 处理数据库连接

更新：MarredCheese评论的澄清：

"无需使用 using 语句。Dapper 将自动打开，关闭，并为您处理连接。 这是不正确的。Dapper 将自动打开关闭的连接，并且它将自动关闭它自动打开的连接，但不会自动释放连接。马克·格拉维尔和埃里克·利珀特两者都主张在这里与 Dapper 一起使用。

Microsoft.AspNetCore.All： v2.0.3 |分量：v1.50.2

我

不确定我是否正确使用了最佳实践，但我这样做是为了处理多个连接字符串。

如果只有 1 个连接字符串，则很容易

启动.cs

using System.Data;
using System.Data.SqlClient;
namespace DL.SO.Project.Web.UI
{
    public class Startup
    {
        public IConfiguration Configuration { get; private set; }
        // ......
        public void ConfigureServices(IServiceCollection services)
        {
            // Read the connection string from appsettings.
            string dbConnectionString = this.Configuration.GetConnectionString("dbConnection1");
            
            // Inject IDbConnection, with implementation from SqlConnection class.
            services.AddTransient<IDbConnection>((sp) => new SqlConnection(dbConnectionString));
            // Register your regular repositories
            services.AddScoped<IDiameterRepository, DiameterRepository>();
            // ......
        }
    }
}

直径存储库.cs

using Dapper;
using System.Data;
namespace DL.SO.Project.Persistence.Dapper.Repositories
{
    public class DiameterRepository : IDiameterRepository
    {
        private readonly IDbConnection _dbConnection;
        public DiameterRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }
        public IEnumerable<Diameter> GetAll()
        {
            const string sql = @"SELECT * FROM TABLE";
            // No need to use using statement. Dapper will automatically
            // open, close and dispose the connection for you.
            return _dbConnection.Query<Diameter>(sql);
        }
        // ......
    }
}

具有 1 个以上连接字符串时的问题

由于Dapper利用IDbConnection，您需要想出一种方法来区分不同的数据库连接。

我尝试创建多个接口，从IDbConnection"继承"，对应于不同的数据库连接，并在Startup上注入具有不同数据库连接字符串的SqlConnection。

这失败了，因为SqlConnection继承自DbConnection，并且DbConnection不仅IDbConnection而且Component类。因此，您的自定义接口将无法仅使用SqlConnection的宽容。

我还尝试创建自己的 DbConnection 类，该类采用不同的连接字符串。这太复杂了，因为您必须实现DbConnection类中的所有方法。你失去了SqlConnection的帮助.

我最终做了什么

1. 在Startup期间，我将所有连接字符串值加载到字典中。我还为所有数据库连接名称创建了一个enum以避免魔术字符串。
2. 我将字典注入为单例。
3. 我没有注入IDbConnection，而是创建了IDbConnectionFactory并将其作为瞬态注入到所有存储库中。现在所有存储库都采用IDbConnectionFactory而不是IDbConnection。
4. 何时选择正确的连接？在所有存储库的构造函数中！为了使事情变得干净，我创建了存储库基类，并让存储库从基类继承。可以在基类中选择正确的连接字符串。

数据库连接名称.cs

namespace DL.SO.Project.Domain.Repositories
{
    public enum DatabaseConnectionName
    {
        Connection1,
        Connection2
    }
}

IDb连接工厂.cs

using System.Data;
namespace DL.SO.Project.Domain.Repositories
{
    public interface IDbConnectionFactory
    {
        IDbConnection CreateDbConnection(DatabaseConnectionName connectionName);
    }
}

DapperDbConenctionFactory - 我自己的工厂实现

namespace DL.SO.Project.Persistence.Dapper
{
    public class DapperDbConnectionFactory : IDbConnectionFactory
    {
        private readonly IDictionary<DatabaseConnectionName, string> _connectionDict;
        public DapperDbConnectionFactory(IDictionary<DatabaseConnectionName, string> connectionDict)
        {
            _connectionDict = connectionDict;
        }
        public IDbConnection CreateDbConnection(DatabaseConnectionName connectionName)
        {
            string connectionString = null;
            if (_connectDict.TryGetValue(connectionName, out connectionString))
            {
                return new SqlConnection(connectionString);
            }
            throw new ArgumentNullException();
        }
    }
}

启动.cs

namespace DL.SO.Project.Web.UI
{
    public class Startup
    {
        // ......
         
        public void ConfigureServices(IServiceCollection services)
        {
            var connectionDict = new Dictionary<DatabaseConnectionName, string>
            {
                { DatabaseConnectionName.Connection1, this.Configuration.GetConnectionString("dbConnection1") },
                { DatabaseConnectionName.Connection2, this.Configuration.GetConnectionString("dbConnection2") }
            };
            // Inject this dict
            services.AddSingleton<IDictionary<DatabaseConnectionName, string>>(connectionDict);
            // Inject the factory
            services.AddTransient<IDbConnectionFactory, DapperDbConnectionFactory>();
            // Register your regular repositories
            services.AddScoped<IDiameterRepository, DiameterRepository>();
            // ......
        }
    }
}

直径存储库.cs

using Dapper;
using System.Data;
namespace DL.SO.Project.Persistence.Dapper.Repositories
{
    // Move the responsibility of picking the right connection string
    //   into an abstract base class so that I don't have to duplicate
    //   the right connection selection code in each repository.
    public class DiameterRepository : DbConnection1RepositoryBase, IDiameterRepository
    {
        public DiameterRepository(IDbConnectionFactory dbConnectionFactory)
            : base(dbConnectionFactory) { }
        public IEnumerable<Diameter> GetAll()
        {
            const string sql = @"SELECT * FROM TABLE";
            // No need to use using statement. Dapper will automatically
            // open, close and dispose the connection for you.
            return base.DbConnection.Query<Diameter>(sql);
        }
        // ......
    }
}

DbConnection1RepositoryBase.cs

using System.Data;
using DL.SO.Project.Domain.Repositories;
namespace DL.SO.Project.Persistence.Dapper
{
    public abstract class DbConnection1RepositoryBase
    {
        public IDbConnection DbConnection { get; private set; }
        public DbConnection1RepositoryBase(IDbConnectionFactory dbConnectionFactory)
        {
            // Now it's the time to pick the right connection string!
            // Enum is used. No magic string!
            this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.Connection1);
        }
    }
}

然后，对于需要与其他连接通信的其他存储库，您可以为它们创建不同的存储库基类。

using System.Data;
using DL.SO.Project.Domain.Repositories;
namespace DL.SO.Project.Persistence.Dapper
{
    public abstract class DbConnection2RepositoryBase
    {
        public IDbConnection DbConnection { get; private set; }
        public DbConnection2RepositoryBase(IDbConnectionFactory dbConnectionFactory)
        {
            this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.Connection2);
        }
    }
}
using Dapper;
using System.Data;
namespace DL.SO.Project.Persistence.Dapper.Repositories
{
    public class ParameterRepository : DbConnection2RepositoryBase, IParameterRepository
    {
        public ParameterRepository (IDbConnectionFactory dbConnectionFactory)
            : base(dbConnectionFactory) { }
        public IEnumerable<Parameter> GetAll()
        {
            const string sql = @"SELECT * FROM TABLE";
            return base.DbConnection.Query<Parameter>(sql);
        }
        // ......
    }
}

希望所有这些都有所帮助。

大约 4 年前有人问过...但无论如何，也许答案对这里的人有用：

我在所有项目中都是这样做的。首先，我创建一个基类，其中包含一些帮助程序方法，如下所示：

public class BaseRepository
{
    protected T QueryFirstOrDefault<T>(string sql, object parameters = null)
    {
        using (var connection = CreateConnection())
        {
            return connection.QueryFirstOrDefault<T>(sql, parameters);
        }
    }
    protected List<T> Query<T>(string sql, object parameters = null)
    {
        using (var connection = CreateConnection())
        {
            return connection.Query<T>(sql, parameters).ToList();
        }
    }
    protected int Execute(string sql, object parameters = null)
    {
        using (var connection = CreateConnection())
        {
            return connection.Execute(sql, parameters);
        }
    }
    // Other Helpers...
    private IDbConnection CreateConnection()
    {
        var connection = new SqlConnection(...);
        // Properly initialize your connection here.
        return connection;
    }
}

有了这样一个基类，我可以轻松地创建真正的存储库，而无需任何样板代码：

public class AccountsRepository : BaseRepository
{
    public Account GetById(int id)
    {
        return QueryFirstOrDefault<Account>("SELECT * FROM Accounts WHERE Id = @Id", new { id });
    }
    public List<Account> GetAll()
    {
        return Query<Account>("SELECT * FROM Accounts ORDER BY Name");
    }
    // Other methods...
}

因此，所有与Dapper，SqlConnection-s和其他数据库访问相关的代码都位于一个地方(BaseRepository(。所有真正的存储库都是干净简单的 1 行方法。

我希望它能帮助某人。

我使用从配置中检索连接字符串的属性创建了扩展方法。这使呼叫者不必了解有关连接的任何信息，无论是打开还是关闭等。这种方法确实限制了你，因为你隐藏了一些 Dapper 功能，但在我们相当简单的应用程序中，它对我们来说工作得很好，如果我们需要 Dapper 的更多功能，我们可以随时添加一个公开它的新扩展方法。

internal static string ConnectionString = new Configuration().ConnectionString;
    internal static IEnumerable<T> Query<T>(string sql, object param = null)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            return conn.Query<T>(sql, param);
        }
    }
    internal static int Execute(string sql, object param = null)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            return conn.Execute(sql, param);
        }
    }

我是这样做的：

internal class Repository : IRepository {
    private readonly Func<IDbConnection> _connectionFactory;
    public Repository(Func<IDbConnection> connectionFactory) 
    {
        _connectionFactory = connectionFactory;
    }
    public IWidget Get(string key) {
        using(var conn = _connectionFactory()) 
        {
            return conn.Query<Widget>(
               "select * from widgets with(nolock) where widgetkey=@WidgetKey", new { WidgetKey=key });
        }
    }
}

然后，无论我在哪里连接我的依赖项(例如：Global.asax.cs或 Startup.cs(，我都会执行以下操作：

var connectionFactory = new Func<IDbConnection>(() => {
    var conn = new SqlConnection(
        ConfigurationManager.ConnectionStrings["connectionString-name"];
    conn.Open();
    return conn;
});

最佳实践是一个真正的加载术语。我喜欢像Dapper.Rainbow推广的DbDataContext式容器。它允许您将CommandTimeout，事务和其他助手耦合在一起。

例如：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Dapper;
// to have a play, install Dapper.Rainbow from nuget
namespace TestDapper
{
    class Program
    {
        // no decorations, base class, attributes, etc 
        class Product 
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string Description { get; set; }
            public DateTime? LastPurchase { get; set; }
        }
        // container with all the tables 
        class MyDatabase : Database<MyDatabase>
        {
            public Table<Product> Products { get; set; }
        }
        static void Main(string[] args)
        {
            var cnn = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=True");
            cnn.Open();
            var db = MyDatabase.Init(cnn, commandTimeout: 2);
            try
            {
                db.Execute("waitfor delay '00:00:03'");
            }
            catch (Exception)
            {
                Console.WriteLine("yeah ... it timed out");
            }

            db.Execute("if object_id('Products') is not null drop table Products");
            db.Execute(@"create table Products (
                    Id int identity(1,1) primary key, 
                    Name varchar(20), 
                    Description varchar(max), 
                    LastPurchase datetime)");
            int? productId = db.Products.Insert(new {Name="Hello", Description="Nothing" });
            var product = db.Products.Get((int)productId);
            product.Description = "untracked change";
            // snapshotter tracks which fields change on the object 
            var s = Snapshotter.Start(product);
            product.LastPurchase = DateTime.UtcNow;
            product.Name += " World";
            // run: update Products set LastPurchase = @utcNow, Name = @name where Id = @id
            // note, this does not touch untracked columns 
            db.Products.Update(product.Id, s.Diff());
            // reload
            product = db.Products.Get(product.Id);

            Console.WriteLine("id: {0} name: {1} desc: {2} last {3}", product.Id, product.Name, product.Description, product.LastPurchase);
            // id: 1 name: Hello World desc: Nothing last 12/01/2012 5:49:34 AM
            Console.WriteLine("deleted: {0}", db.Products.Delete(product.Id));
            // deleted: True 

            Console.ReadKey();
        }
    }
}

试试这个：

public class ConnectionProvider
    {
        DbConnection conn;
        string connectionString;
        DbProviderFactory factory;
        // Constructor that retrieves the connectionString from the config file
        public ConnectionProvider()
        {
            this.connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString.ToString();
            factory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[0].ProviderName.ToString());
        }
        // Constructor that accepts the connectionString and Database ProviderName i.e SQL or Oracle
        public ConnectionProvider(string connectionString, string connectionProviderName)
        {
            this.connectionString = connectionString;
            factory = DbProviderFactories.GetFactory(connectionProviderName);
        }
        // Only inherited classes can call this.
        public DbConnection GetOpenConnection()
        {
            conn = factory.CreateConnection();
            conn.ConnectionString = this.connectionString;
            conn.Open();
            return conn;
        }
    }

每个人似乎都过早地打开了他们的联系？ 我有同样的问题，在这里挖掘了源代码后 - https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper/SqlMapper.cs

您会发现，每次与数据库的交互都会检查连接以查看它是否已关闭，并根据需要打开它。 因此，我们只是使用像上面这样的语句而不使用 conn.open((。 这样，连接就尽可能靠近交互打开。 如果您注意到，它也会立即关闭连接。 这也比在处置过程中自动关闭更快。

上面存储库中的许多示例之一：

    private static int ExecuteCommand(IDbConnection cnn, ref CommandDefinition command, Action<IDbCommand, object> paramReader)
    {
        IDbCommand cmd = null;
        bool wasClosed = cnn.State == ConnectionState.Closed;
        try
        {
            cmd = command.SetupCommand(cnn, paramReader);
            if (wasClosed) cnn.Open();
            int result = cmd.ExecuteNonQuery();
            command.OnCompleted();
            return result;
        }
        finally
        {
            if (wasClosed) cnn.Close();
            cmd?.Dispose();
        }
    }

下面是一个小例子，说明我们如何将 Wrapper 用于 Dapper 的 Dapper。 这使我们能够包装所有 Dapper 和 Simple Crud 方法来管理连接、提供安全性、日志记录等。

  public class DapperWrapper : IDapperWrapper
  {
    public IEnumerable<T> Query<T>(string query, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
      using (var conn = Db.NewConnection())
      {
          var results = conn.Query<T>(query, param, transaction, buffered, commandTimeout, commandType);
          // Do whatever you want with the results here
          // Such as Security, Logging, Etc.
          return results;
      }
    }
  }

我包装了与帮助程序类的连接：

public class ConnectionFactory
{
    private readonly string _connectionName;
    public ConnectionFactory(string connectionName)
    {
        _connectionName = connectionName;
    }
    public IDbConnection NewConnection() => new SqlConnection(_connectionName);
    #region Connection Scopes
    public TResult Scope<TResult>(Func<IDbConnection, TResult> func)
    {
        using (var connection = NewConnection())
        {
            connection.Open();
            return func(connection);
        }
    }
    public async Task<TResult> ScopeAsync<TResult>(Func<IDbConnection, Task<TResult>> funcAsync)
    {
        using (var connection = NewConnection())
        {
            connection.Open();
            return await funcAsync(connection);
        }
    }
    public void Scope(Action<IDbConnection> func)
    {
        using (var connection = NewConnection())
        {
            connection.Open();
            func(connection);
        }
    }
    public async Task ScopeAsync<TResult>(Func<IDbConnection, Task> funcAsync)
    {
        using (var connection = NewConnection())
        {
            connection.Open();
            await funcAsync(connection);
        }
    }
    #endregion Connection Scopes
}

用法示例：

public class PostsService
{
    protected IConnectionFactory Connection;
    // Initialization here ..
    public async Task TestPosts_Async()
    {
        // Normal way..
        var posts = Connection.Scope(cnn =>
        {
            var state = PostState.Active;
            return cnn.Query<Post>("SELECT * FROM [Posts] WHERE [State] = @state;", new { state });
        });
        // Async way..
        posts = await Connection.ScopeAsync(cnn =>
        {
            var state = PostState.Active;
            return cnn.QueryAsync<Post>("SELECT * FROM [Posts] WHERE [State] = @state;", new { state });
        });
    }
}

所以我不必每次都显式打开连接。此外，为了方便将来的重构，您可以以这种方式使用它：

var posts = Connection.Scope(cnn =>
{
    var state = PostState.Active;
    return cnn.Query<Post>($"SELECT * FROM [{TableName<Post>()}] WHERE [{nameof(Post.State)}] = @{nameof(state)};", new { state });
});

TableName<T>()是什么，可以在这个答案中找到。

嗨@donaldhughes我也是新手，我曾经这样做：1 - 创建一个类来获取我的连接字符串2 - 在使用中调用连接字符串类

看：

DapperConnection.cs

public class DapperConnection
{
    public IDbConnection DapperCon {
        get
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ToString());
        }
    }
}

DapperRepository.cs

  public class DapperRepository : DapperConnection
  {
       public IEnumerable<TBMobileDetails> ListAllMobile()
        {
            using (IDbConnection con = DapperCon )
            {
                con.Open();
                string query = "select * from Table";
                return con.Query<TableEntity>(query);
            }
        }
     }

而且它工作正常。