匿名类型参数

描述

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