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()等方法时，使用传入事物对象就可以了。