Dapper官方文档(八)【工具之异步,缓冲,事务,存储过程】

小灰灰 2021-07-04 15:48 833阅读 0赞

# 异步 #

## 描述 ##

Dapper还使用了`Async`（异步）方法扩展了`IDbConnection`接口：

*  ExecuteAsync
 *  QueryAsync
 *  QueryFirstAsync
 *  QueryFirstOrDefaultAsync
 *  QuerySingleAsync
 *  QuerySingleOrDefaultAsync
 *  QueryMultipleAsync

我们只在本教程中添加了非异步版本，以便于阅读。

## ExecuteAsync ##

var sql = "Invoice_Insert";
    
    using (var connection = My.ConnectionFactory())
    { 
    	connection.Open();
    
    	var affectedRows = connection.ExecuteAsync(sql,
    			new { Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
    			commandType: CommandType.StoredProcedure)
    		.Result;
    }

## QueryAsync ##

var sql = "Invoice_Insert";
    
    using (var connection = My.ConnectionFactory())
    { 
    	connection.Open();
    
    	var invoices = connection.QueryAsync<Invoice>(sql).Result.ToList();
    }

## QueryFirstAsync ##

var sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";
    
    using (var connection = My.ConnectionFactory())
    { 
    	connection.Open();
    
    	var invoice = connection.QueryFirstAsync<Invoice>(sql, new { InvoiceID = 1}).Result;
    }

## QueryFirstOrDefaultAsync ##

var sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";
    
    using (var connection = My.ConnectionFactory())
    { 
    	connection.Open();
    
    	var invoice = connection.QueryFirstOrDefaultAsync<Invoice>(sql, new { InvoiceID = 1}).Result;
    }

## QuerySingleAsync ##

var sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";
    
    using (var connection = My.ConnectionFactory())
    { 
    	connection.Open();
    
    	var invoice = connection.QuerySingleAsync<Invoice>(sql, new { InvoiceID = 1}).Result;
    }

## QuerySingleOrDefaultAsync ##

var sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID;";
    
    using (var connection = My.ConnectionFactory())
    { 
    	connection.Open();
    
    	var invoice = connection.QuerySingleOrDefaultAsync<Invoice>(sql, new { InvoiceID = 1}).Result;
    }

## QueryMultipleAsync ##

--------------------

# 缓冲 #

## 描述 ##

*  默认值：True

缓冲查询一次返回整个读取器，这在大多数情况下是理想的。

非缓冲查询与流式传输等效，您只需按需加载对象，这对于一个非常大的查询来减少内存使用情况可能很有用。

string sqlInvoices = "SELECT * FROM Invoice;";
    
    using (var connection = My.ConnectionFactory())
    { 
    	var invoices = connection.Query<Invoice>(sqlInvoices, buffered: false).ToList();
    }

--------------------

# 事务 #

## 描述 ##

Dapper支持事务和范围事务。

## 事务 ##

从连接开始一个新事务，并将其传递给事务可选参数。

## 范围事务 ##

在开始连接之前开始一个新的范围事务。

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

--------------------

# 存储过程 #

## 描述 ##

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

## 执行多个 ##

执行多个存储过程。