Dapper官方文档(六)【参数之匿名类型,动态类型,列表类型,字符串类型】

匿名类型参数

描述

Dapper通过支持匿名类型使其可以简单、安全（SQL注入）的使用参数。

单次

执行一次SQL命令。

var sql = "EXEC 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);
}

多次

执行多次SQL命令。

var sql = "EXEC 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
	);
}

---

动态类型参数

描述

在Dapper方法中创建并使用参数。

单次

执行一次SQL命令。

var sql = "EXEC Invoice_Insert";

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

	DynamicParameters parameter = new DynamicParameters();

	parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
	parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);
	parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

	connection.Execute(sql,
		parameter,
		commandType: CommandType.StoredProcedure);

	int rowCount = parameter.Get<int>("@RowCount");
}

多次

执行多次SQL命令。

var sql = "EXEC Invoice_Insert";

var parameters = new List<DynamicParameters>();

for (var i = 0; i < 3; i++)
{
	var p = new DynamicParameters();
	p.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
	p.Add("@Code", "Many_Insert_" + (i + 1), DbType.String, ParameterDirection.Input);
	p.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

	parameters.Add(p);
}

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

	connection.Execute(sql,
		parameters,
		commandType: CommandType.StoredProcedure
	);

	var rowCount = parameters.Sum(x => x.Get<int>("@RowCount"));
}

---

列表类型参数

描述

Dapper允许您使用列表在IN子句中指定多个参数。

var sql = "SELECT * FROM Invoice WHERE Kind IN @Kind;";

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

	var invoices = connection.Query<Invoice>(sql, new {Kind = new[] {InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice}}).ToList();
}

---

字符串类型参数

描述

var sql = "SELECT * FROM Invoice WHERE Code = @Code;";

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

	var invoices = connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength = false, Length = 9, IsAnsi = true}}).ToList();

	My.Result.Show(invoices);
}