.NET Core Dapper(ORM) 执行sql语句和事物(Transaction)

1、Dapper中执行SQL语句

public IDbConnection dbConn
{
    get
    {
	return new SqlConnection(_config.GetConnectionString("SConnectionString"));
    }
}

1）执行INSERT语句

public void Add(Sale item)
{
  string sQuery = "INSERT INTO Sales (firstname, lastname, middlename, address1, address2, city, state, zipcode, dateofbirth, phonenumber, phonenumberalt, insurancename, insuranceid, binnumber, pcnnumber, groupid, offerid, offercodes, timestamp, otherfields)"
          + " VALUES(@FirstName, @LastName, @MiddleName, @Address1, @Address2, @City, @State, @ZipCode, @DateOfBirth, @PhoneNumber, @PhoneNumberAlt, @InsuranceName, @InsuranceId, @BinNumber, @PcnNumber, @GroupId, @OfferId, @OfferCodes, @Timestamp, '@OtherFields')";
  using(IDbConnection active = dbConn)
  {
    active.Open();
    active.Execute(sQuery, item);
  }
}

2）执行SELECT语句

 public List<Sale> Add(string sql,int state)
    {
      using (IDbConnection active = dbConn)
      {
        active.Open();
        var result = active.Query<Sale>(sql, new { state = state });//查询条件参数
        return result.ToList();
      }
    }

3）执行UPDATE语句

 public int Update(string sql,Sale item)
    {
      using (IDbConnection active = dbConn)
      {
        active.Open();
        return active.Execute(sql, item);//查询条件参数,返回影响行数
      }
    }

4）执行DELETE语句

 public int Delete(string sql,int state)
    {
      using (IDbConnection active = dbConn)
      {
        active.Open();
       return active.Execute(sql, new { state = state });//删除条件参数，返回影响行数
      }
    }

2、Dapper中使用事物(Transaction)

public void Execute(IEnumerable<Action<IDbConnection, IDbTransaction>> actions)
  {
    using (IDbConnection connection = OpenConnection())
    using (IDbTransaction transaction = connection.BeginTransaction())
    {
      try
      {
        foreach (var action in actions)
        {
          action(transaction.Connection, transaction);
        }
        transaction.Commit();
      }
      catch
      {
        transaction.Rollback();
        throw;
      }
    }
  }

Action是.Net中的委托，在委托方法中执行Dapper的具体操作，并且使用传入的连接和事物。在执行Dapper中Execute()等方法时，使用传入事物对象就可以了。

相关文档：ASP.NET Core 2.1 中异步使用Dapper总结

.NET Core Dapper返回存储过程输出(output)参数