这里说的其他用法,是指 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);
}