Dapper学习(三)之其他用法
- 2019 年 10 月 10 日
- 笔记
这里说的其他用法，是指 Async,Buffered,Transaction,Stored Procedure。
1. 首先 dapper支持异步
ExecuteAsync, QueryAsync, QueryFirstAsync, QueryFirstOrDefaultAsync, QuerySingleAsync, QuerySingleOrDefaultAsync, QueryMultipleAsync
ExecuteAsync 用法示例：
string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var affectedRows = connection.ExecuteAsync(sql, new {CustomerName = "Mark"}).Result; Console.WriteLine(affectedRows); var customer = connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ToList(); FiddleHelper.WriteTable(customer); }
2. Buffered
默认是：True
A buffered query return the entire reader at once.
A non-bufferd query is equivalent as streaming. you only load objects on demand. That can be useful for a very large query to reduce memory usage.
string sql = "SELECT * FROM OrderDetails;"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { var orderDetails = connection.Query<OrderDetail>(sql, buffered: false).ToList(); FiddleHelper.WriteTable(orderDetails.Take(10)); }
3. Transaction
dapper支持 transaction 和 TransactionScope
Transaction
使用示例如下：
string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);"; using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools())) { connection.Open(); using (var transaction = connection.BeginTransaction()) { var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"}, transaction: transaction); transaction.Commit(); Console.WriteLine(affectedRows); } }
TransactionScope
在连接之前，使用 transaction scope
// using System.Transactions; using (var transaction = new TransactionScope()) { var sql = "Invoice_Insert"; using (var connection = My.ConnectionFactory()) { connection.Open(); var affectedRows = connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}, commandType: CommandType.StoredProcedure); } transaction.Complete(); }
4. Stored Procedure
在dapper中使用存储过程，只需要指定命令类型
var sql = "Invoice_Insert"; using (var connection = My.ConnectionFactory()) { connection.Open(); var affectedRows = connection.Execute(sql, new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}, commandType: CommandType.StoredProcedure); My.Result.Show(affectedRows); }
