目之瞬间-专注于互联网平台

.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)参数