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);
}
// ......
}
}
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();
}
}
}
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);
}
}
}
然后,对于需要与其他连接通信的其他存储库,您可以为它们创建不同的存储库基类。
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);
}
// ......
}
}
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...
}
var connectionFactory = new Func<IDbConnection>(() => {
var conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["connectionString-name"];
conn.Open();
return conn;
});
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 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 });
});
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);
}
}
}
9条答案
按热度按时间9bfwbjaz1#
更新:来自MarredCheese评论的澄清:
“不需要使用using语句。Dapper将自动为您打开、关闭和处理连接。”这不正确。Dapper会自动打开已关闭的连接,也会自动关闭它自动打开的连接,但不会自动丢弃连接。Marc Gravell和Eric Lippert都主张使用Dapper here。
Microsoft.AspNetCore.All:v2.0.3|Dapper:v1.50.2
我不确定我是否正确地使用了最佳实践,但我这样做是为了处理多个连接字符串。
如果只有1个连接字符串,则很容易
Startup.cs
DiameterRepository.cs
如果您有多个连接字符串,则会出现问题
由于
Dapper
使用IDbConnection
,因此需要考虑区分不同数据库连接的方法。我尝试创建多个接口,从
IDbConnection
“继承”,对应于不同的数据库连接,并在Startup
上注入不同的数据库连接字符串SqlConnection
。这失败了,因为
SqlConnection
继承了DbConnection
,而DbConnection
不仅实现了IDbConnection
,还实现了IComponent
接口。因此,您的自定义接口将无法仅使用SqlConnection
实现。我还尝试创建自己的
DbConnection
类,它采用不同的连接字符串。这太复杂了,因为你必须实现DbConnection
类中的所有方法。你失去了SqlConnection
的帮助。我最终做了什么
1.在
Startup
期间,我将所有连接字符串值加载到字典中。我还为所有的数据库连接名创建了一个enum
,以避免使用魔术字符串。1.我把字典作为Singleton注入。
1.我没有注入
IDbConnection
,而是创建了IDbConnectionFactory
,并将其作为Transient注入所有存储库。现在所有的仓库都采用IDbConnectionFactory
而不是IDbConnection
。1.何时选择正确的连接?在所有仓库的构造函数中!为了使事情变得清晰,我创建了存储库基类,并让存储库从基类继承。正确的连接字符串选择可以发生在基类中。
DatabaseConnectionName.cs
IDbConnectionFactory.cs
DapperDbConenctionFactory -我自己的工厂实现
Startup.cs
DiameterRepository.cs
DbConnection1RepositoryBase.cs
然后,对于需要与其他连接通信的其他存储库,您可以为它们创建不同的存储库基类。
希望所有这些帮助。
o2rvlv0m2#
四年前有人问过…但不管怎样,也许这个答案对在座的某个人有用:
我在所有的项目中都这样做。首先,我创建了一个包含几个助手方法的基类,如下所示:
有了这样一个基类,我可以轻松地创建真实的仓库,而不需要任何样板代码:
因此,所有与Dapper、SqlConnection-s和其他数据库访问相关的代码都位于一个地方(BaseRepository)。所有真实的仓库都是干净简单的1行方法。
我希望它能帮助某人。
vu8f3i0k3#
我创建了一个扩展方法,它有一个从配置中检索连接字符串的属性。这使得调用者不必知道任何关于连接的信息,无论它是打开的还是关闭的,等等。这个方法确实有点限制你,因为你隐藏了一些Dapper的功能,但在我们相当简单的应用程序中,它对我们来说工作得很好,如果我们需要Dapper的更多功能,我们总是可以添加一个新的扩展方法来暴露它。
klr1opcd4#
我是这样做的:
然后,无论我在哪里连接依赖项(例如:Global.asax.cs或Startup.cs),我会执行以下操作:
bwntbbo35#
最佳实践是一个真实的术语。我喜欢
DbDataContext
风格的容器,比如Dapper.Rainbow。它允许你耦合CommandTimeout
、transaction和其他helper。举例来说:
m0rkklqb6#
试试这个:
tgabmvqs7#
每个人似乎都过早地打开了他们的连接?我也有同样的问题,在挖掘了这里的来源之后-https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper/SqlMapper.cs
您会发现,每次与数据库的交互都会检查连接是否关闭,并在必要时打开连接。因此,我们只是使用上面的using语句,而不使用conn.open()。这样,连接就尽可能靠近交互打开。如果你注意到了,它也会立即关闭连接。这也将比它在处理过程中自动关闭更快。
这是上面repo中的许多例子之一:
下面是一个小例子,说明我们如何使用Dapper的Wrapper,称为DapperWrapper。这允许我们 Package 所有Dapper和Simple Crud方法来管理连接,提供安全性,日志记录等。
dhxwm5r48#
我用helper类 Package 连接:
用法示例:
所以我不必每次都显式地打开连接。另外,为了方便将来的重构,你可以这样使用它:
什么是
TableName<T>()
可以在this answer中找到。7gyucuyw9#
你好@ donaldhu,我也是新手,我经常这样做:1 -创建一个类来获取我的连接字符串2 -在使用
你看:
DapperConnection.cs
DapperRepository.cs
而且效果很好。