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

Question

提问by Donald Hughes

I've been playing with Dapper, but I'm not sure of the best way to handle the database connection.

我一直在玩 Dapper，但我不确定处理数据库连接的最佳方式。

Most examples show the connection object being created in the example class, or even in each method. But it feels wrong to me to reference a connection string in every clss, even if it's pulling from the web.config.

大多数示例显示在示例类中创建的连接对象，甚至在每个方法中。但是我觉得在每个类中引用一个连接字符串是错误的，即使它是从 web.config 中提取的。

My experience has been with using a DbDataContextor DbContextwith Linq to SQL or Entity Framework, so this is new to me.

我的经验是使用DbDataContext或DbContext与 Linq to SQL 或实体框架，所以这对我来说是新的。

How do I structure my web apps when using Dapper as my Data Access strategy?

使用 Dapper 作为我的数据访问策略时，如何构建我的 Web 应用程序？

Answer 1

回答by Sam Saffron

Best practice is a real loaded term. I like a DbDataContextstyle container like Dapper.Rainbowpromotes. It allows you to couple the CommandTimeout, transaction and other helpers.

最佳实践是一个真正的负载术语。我喜欢DbDataContext像Dapper.Rainbow推广的风格容器。它允许您耦合CommandTimeout、事务和其他帮助程序。

For example:

例如：

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

Answer 2

回答by Shuaib

Try this:

尝试这个：

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

    }

Answer 3

回答by Shawn Hubbard

I created extension methods with a property that retrieves the connection string from configuration. This lets the callers not have to know anything about the connection, whether it's open or closed, etc. This method does limit you a bit since you're hiding some of the Dapper functionality, but in our fairly simple app it's worked fine for us, and if we needed more functionality from Dapper we could always add a new extension method that exposes it.

我创建了具有从配置中检索连接字符串的属性的扩展方法。这让调用者不必知道连接的任何信息，无论它是打开还是关闭等。由于您隐藏了一些 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);
        }
    }

Answer 4

回答by Romi Petrelis

I do it like this:

我这样做：

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

Then, wherever I wire-up my dependencies (ex: Global.asax.cs or Startup.cs), I do something like:

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

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

Answer 5

回答by Gabriel Scavassa

Hi @donaldhughes I'm new on it too, and I use to do this: 1 - Create a class to get my Connection String 2 - Call the connection string class in a Using

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

Look:

看：

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

And it works fine.

它工作正常。

Answer 6

回答by Pavel Melnikov

It was asked about 4 years ago... but anyway, maybe the answer will be useful to someone here:

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

I do it like this in all the projects. First, I create a base class which contains a few helper methods like this:

我在所有项目中都是这样做的。首先，我创建了一个基类，其中包含一些像这样的辅助方法：

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

And having such a base class I can easily create real repositories without any boilerplate code:

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

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

So all the code related to Dapper, SqlConnection-s and other database access stuff is located in one place (BaseRepository). All real repositories are clean and simple 1-line methods.

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

I hope it will help someone.

我希望它会帮助某人。

Answer 7

回答by Zabbu

Everyone appears to be opening their connections entirely too early? I had this same question, and after digging through the Source here - https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper/SqlMapper.cs

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

You will find that every interaction with the database checks the connection to see if it is closed, and opens it as necessary. Due to this, we simply utilize using statements like above without the conn.open(). This way the connection is opened as close to the interaction as possible. If you notice, it also immediately closes the connection. This will also be quicker than it closing automatically during disposal.

您会发现每次与数据库的交互都会检查连接以查看它是否已关闭，并在必要时打开它。因此，我们只使用上面没有 conn.open() 的 using 语句。这样，连接会在尽可能接近交互的情况下打开。如果您注意到，它也会立即关闭连接。这也将比在处理过程中自动关闭更快。

One of the many examples of this from the repo above:

上面回购中的许多示例之一：

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

Below is a small example of how we use a Wrapper for Dapper called the DapperWrapper. This allows us to wrap all of the Dapper and Simple Crud methods to manage connections, provide security, logging, etc.

下面是我们如何使用名为 DapperWrapper 的 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;
      }
    }
  }

Answer 8

回答by David Liang

Microsoft.AspNetCore.All: v2.0.3 | Dapper: v1.50.2

Microsoft.AspNetCore.All: v2.0.3 | 小巧玲珑：v1.50.2

I am not sure if I am using the best practices correctly or not, but I am doing it this way, in order to handle multipleconnection strings.

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

It's easy if you have only 1 connection string

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

Startup.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>();

            // ......
        }
    }
}

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

        // ......
    }
}

Problems if you have more than 1 connection string

如果您有 1 个以上的连接字符串，则会出现问题

Since Dapperutilizes IDbConnection, you need to think of a way to differentiate different database connections.

既然Dapper利用了IDbConnection，就需要想办法区分不同的数据库连接。

I tried to create multiple interfaces, 'inherited' from IDbConnection, corresponding to different database connections, and inject SqlConnectionwith different database connection strings on Startup.

我尝试创建多个接口，从 '继承' IDbConnection，对应不同的数据库连接，并SqlConnection在Startup.

That failed because SqlConnectioninherits from DbConnection, and DbConnectioninplements not only IDbConnectionbut also Componentclass. So your custom interfaces won't be able to use just the SqlConnectionimplenentation.

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

I also tried to create my own DbConnectionclass that takes different connection string. That's too complicated because you have to implement all the methods from DbConnectionclass. You lost the help from SqlConnection.

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

What I end up doing

我最终会做什么

During Startup, I loaded all connection string values into a dictionary. I also created an enumfor all the database connection names to avoid magic strings.
I injected the dictionary as Singleton.
Instead of injecting IDbConnection, I created IDbConnectionFactoryand injected that as Transient for all repositories. Now all repositories take IDbConnectionFactoryinstead of IDbConnection.
When to pick the right connection? In the constructor of all repositories! To make things clean, I created repository base classes and have the repositories inherit from the base classes. The right connection string selection can happen in the base classes.

在期间Startup，我将所有连接字符串值加载到字典中。我还enum为所有数据库连接名称创建了一个以避免魔术字符串。
我将字典注入为单例。
相反注射的IDbConnection，我创建IDbConnectionFactory并注入作为瞬态所有存储库。现在，所有的仓库取IDbConnectionFactory来代替IDbConnection。
何时选择正确的连接？在所有存储库的构造函数中！为了使事情变得干净，我创建了存储库基类并让存储库从基类继承。正确的连接字符串选择可以发生在基类中。

DatabaseConnectionName.cs

数据库连接名称.cs

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

IDbConnectionFactory.cs

using System.Data;

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

DapperDbConenctionFactory - my own factory implementation

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

Startup.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>();

            // ......
        }
    }
}

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

Then for other repositories that need to talk to the other connections, you can create a different repository base class for them.

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

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

        // ......
    }
}

Hope all these help.

希望所有这些都有帮助。

Answer 9

回答by Sergey

I wrap connection with the helper class:

我用助手类包装连接：

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
}

Examples of usage:

用法示例：

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

So I don't have to explicitly open the connection every time. Additionally, you can use it this way for the convenience' sake of the future refactoring:

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

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

What is TableName<T>()can be found in this answer.

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

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

提问by Donald Hughes

回答by Sam Saffron

回答by Shuaib

回答by Shawn Hubbard

回答by Romi Petrelis

回答by Gabriel Scavassa

回答by Pavel Melnikov

回答by Zabbu

回答by David Liang

It's easy if you have only 1 connection string

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

Problems if you have more than 1 connection string

如果您有 1 个以上的连接字符串，则会出现问题

What I end up doing

我最终会做什么

回答by Sergey

相关推荐

最近更新

标签

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

提问by Donald Hughes

回答by Sam Saffron

回答by Shuaib

回答by Shawn Hubbard

回答by Romi Petrelis

回答by Gabriel Scavassa

回答by Pavel Melnikov

回答by Zabbu

回答by David Liang

It's easy if you have only 1 connection string

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

Problems if you have more than 1 connection string

如果您有 1 个以上的连接字符串，则会出现问题

What I end up doing

我最终会做什么

回答by Sergey

相关推荐

C# 以编程方式检查复选框列表中的项目，其中文本等于我想要的

C# 如何使用 ComboBox 的 SelectedIndexChanged-Event

C# 何时更改“生成序列化程序集”值？

如何 AES-128 在 Delphi 中使用密码加密字符串并在 C# 中解密？

相关推荐

最近更新

标签