Dapper 执行存储过程

 public async Task<int> GenerateWorkshopProductionRuleRecord(List<WorkshopProductionRule> list)
  {
    int result = 0;
    string procedure = "GenerateWorkshopProductionRuleRecord";
    using var conn = _dapper.CreateConnection();
    foreach (var item in list)
    {
      var count = await conn.ExecuteAsync(procedure,
        new
        {
          item.Id,
          item.OrganizationId,
          item.ProductId,
          item.UserId,
          item.UpdateDate,
          item.CustomerShortNames,
          item.Molds,
        }, commandType: CommandType.StoredProcedure);
      result += count;
    }
    return result;
  }

　　

使用 Dapper 执行存储过程有两种方法： 使用CommandTypeas Text；或作为StoredProcedure。两者效果一样。

[Route(nameof(QueryByCommandTypeAsText))]
        [HttpGet]
        public async Task<ActionResult> QueryByCommandTypeAsText()
        {//使用CommandTypeas Text
            var sql = "exec [Sales by Year] @Beginning_Date,@Ending_Date";
            var values = new { Beginning_Date = "1998-1-1", Ending_Date = "1998-12-30" };
            using (var con = new SqlConnection(MyConfig.ConnectionString))
            {
                var reuslts =await con.QueryAsync(sql, values);
                return Ok(reuslts);
            }
        }
        [Route(nameof(QueryByCommandTypeAsStoredProcedure))]
        [HttpGet]
        public async Task<ActionResult> QueryByCommandTypeAsStoredProcedure()
        {//使用CommandTypeas StoredProcedure
            var procedure = "[Sales by Year]";
            var values = new { Beginning_Date = "1998-1-1", Ending_Date = "1998-12-30" };
            using (var con = new SqlConnection(MyConfig.ConnectionString))
            {
                var reuslts = await con.QueryAsync(procedure, values,commandType: CommandType.StoredProcedure);
                return Ok(reuslts);
            }
        }

　　

//Stored Procedure
using (SqlConnection conn = new SqlConnection(strConnection))
{
	//準備參數
	DynamicParameters parameters = new DynamicParameters();
	parameters.Add("@Param1", "abc",DbType.String, ParameterDirection.Input);
	parameters.Add("@OutPut1", dbType: DbType.Int32,direction: ParameterDirection.Output);
	parameters.Add("@Return1", dbType: DbType.Int32,direction: ParameterDirection.ReturnValue);
	conn.Execute("MyStoredProcedure", parameters, commandType: CommandType.StoredProcedure);
	//接回Output值
	int outputResult = parameters.Get<int> ("@OutPut1");
	//接回Return值
	int returnResult = parameters.Get<int> ("@Return1");
}

　　Stored Procedure會用到的input、output、return都可以用。

//INSERT statement
using (SqlConnection conn = new SqlConnection(strConnection))
{
	string strSql ="INSERT INTO Users(col1,col2) VALUES (@c1,@c2);" ;
	//新增多筆參數
	dynamic datas = new []{ new { c1 = "A", c2 = "A2" }
				, new { c1 = "B", c2 = "B2" }
				, new { c1 = "C", c2 = "C2" }};
	conn.Execute( strSql, datas);
}