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

异步

描述

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

var sql = "SELECT * FROM Invoice; SELECT * FROM InvoiceItem;";

using (var connection = My.ConnectionFactory())
{
	connection.Open();

	using (var multi = connection.QueryMultipleAsync(sql, new { InvoiceID = 1 }).Result)
	{
		var invoice = multi.Read<Invoice>().First();
		var invoiceItems = multi.Read<InvoiceItem>().ToList();
	}
}

---

缓冲

描述

• 默认值：True

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

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

string sqlInvoices = "SELECT * FROM Invoice;";

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

---

事务

描述

Dapper支持事务和范围事务。

事务

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

var sql = "Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
	connection.Open();

	using (var transaction = connection.BeginTransaction())
	{
		var affectedRows = connection.Execute(sql,
			new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
			commandType: CommandType.StoredProcedure,
			transaction: transaction);

		transaction.Commit();
	}
}

范围事务

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

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

执行多个

执行多个存储过程。

var sql = "Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
	connection.Open();

	var affectedRows = connection.Execute(sql,
		new[]
		{
			new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1"},
			new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2"},
			new {Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3"}
		},
		commandType: CommandType.StoredProcedure
	);

	My.Result.Show(affectedRows);
}