Dapper 第二篇 Execute 方法介绍

描述

Execute是一种扩展方法，可以从IDbConnection类型的任何对象调用。它可以执行一次或多次命令并返回受影响的行数。此方法通常用于执行。

• 存储过程
• Insert语句
• Update 语句
• Delete 语句

参数
下表显示了Execute方法的不同参数。
名称 描述

1. SQL 要执行的命令文本。
2. PARAM 命令参数（default = null）。
3. DbTransaction 要使用的事务（default = null）。
4. CommandTimeout 命令超时（默认= null）
   命令类型 命令类型（默认= null）

示例：执行存储过程

1. 单对象操作

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

2. 多对象操作
   多次执行存储过程。一次为数组列表中的每个对象。

string sql = "Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
    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);
}

3. 示例：执行InSert
   执行单条信息插入

string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"});

    Console.WriteLine(affectedRows);

    var customer = connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ToList();

    FiddleHelper.WriteTable(customer);
}

多次执行INSERT语句。一次为数组列表中的每个对象。

string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.Open();

    var affectedRows = connection.Execute(sql,
    new[]
    {
    new {CustomerName = "John"},
    new {CustomerName = "Andy"},
    new {CustomerName = "Allan"}
    }
);
Console.WriteLine(affectedRows);

4. 示例：Update
   执行一条信息更新

string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{           
    var affectedRows = connection.Execute(sql,new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"});

    Console.WriteLine(affectedRows);
}

执行多条信息更新,一次为数组列表中的每个对象。

string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{   
    var affectedRows = connection.Execute(sql,
    new[]
    {
    new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"},
    new {CategoryID = 4, Description = "Cheeses and butters etc."}
    }
);
Console.WriteLine(affectedRows);

5. 示例:Delete
   执行单条Delete语句

string sql = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{           
    var affectedRows = connection.Execute(sql, new {CustomerID = 1});

    Console.WriteLine(affectedRows);
}

执行DELETE语句。一次为数组列表中的每个对象

string sql = "DELETE FROM OrderDetails WHERE OrderDetailID = @OrderDetailID";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{           
    var affectedRows = connection.Execute(sql, 
        new[]
    {
    new {OrderDetailID = 1},
    new {OrderDetailID = 2},
    new {OrderDetailID = 3}
    }
);

Console.WriteLine(affectedRows);