1:注意方法是 Execute
2:方法参数是 lists 对象
3:新增数据返回的是 bool类型
4:Execute 里面的参数 是sql ,对象数组 跟 事务
一:新增方法 实例:
/// <summary>
/// 批量新增房型房量Log
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public bool BatchInsertYiBonRmTypeAvailLog(List<YiBonRmTypeAvailLogEntity> entities)
{
int rows = 0;
string sql = @"INSERT INTO [YiBonRmTypeAvailLog](HotelCode,RoomClassCode,HotelDate,TotalRms,AvailableRms,RmtypeNum,LimitNum,SureBookNum,UnsureBookNum,OooNum,RmType,BatchNo,YiBonSyncTime,IsSync,SyncTime,CreateTime,SyncType)
VALUES(@HotelCode,@RoomClassCode,@HotelDate,@TotalRms,@AvailableRms,@RmtypeNum,@LimitNum,@SureBookNum,@UnsureBookNum,@OooNum,@RmType,@BatchNo,@YiBonSyncTime,@IsSync,@SyncTime,@CreateTime,@SyncType)";
using (var conn = GetYiBonSqlConnection())
{
conn.Open();
IDbTransaction trans = conn.BeginTransaction();
try
{
rows = conn.Execute(sql, entities.ToArray(), trans);
trans.Commit();
}
catch (Exception ex)
{
rows = 0;
trans.Rollback();
throw;
}
finally
{
if (trans != null)
trans.Dispose();
if (conn != null)
conn.Dispose();
}
}
return rows > 0 ? true : false;
}
二 修改方法实例:
/// <summary>
/// 根据主键更新逸柏房型同步状态
/// </summary>
/// <param name="entity"></param>
/// <param name="hasMap"></param>
/// <returns></returns>
public bool ModifyYiBonRoomTypeMapByID(long id, string roomClassCode, bool hasMap)
{
int rows = 0;
string sql = string.Empty;
//更新同步时未找到映射关系
if (!hasMap)
{
sql = @"UPDATE dbo.YiBonRmTypeLog SET RoomClassCode =@RoomClassCode, IsSync = 1 ,SyncTime = GETDATE() WHERE ID = @ID";
}
else
{
sql = @"UPDATE dbo.YiBonRmTypeLog SET IsSync = 1 ,SyncTime = GETDATE() WHERE ID = @ID";
}
using (var conn = GetYiBonSqlConnection())
{
rows = conn.Execute(sql, new { ID = id, RoomClassCode = roomClassCode });
conn.Dispose();
}
return rows > 0 ? true : false;
}
三:查询直接放回对象 SQL语句直接返回对象
/// <summary>
/// 查询逸柏房型Log信息
/// </summary>
/// <returns></returns>
public List<YiBonRmTypeAvailLogEntity> QueryYiBonRmTypeAvailLogList()
{
List<YiBonRmTypeAvailLogEntity> list = null;
string sql = @"SELECT TOP 1000 ID,HotelCode,RoomClassCode,HotelDate,TotalRms,AvailableRms,RmtypeNum,LimitNum,SureBookNum,UnsureBookNum,OooNum,RmType,BatchNo,YiBonSyncTime,IsSync,SyncTime,CreateTime FROM [YiBonRmTypeAvailLog] WITH(NOLOCK) WHERE IsSync = 0 ORDER BY CreateTime";
using (var conn = GetYiBonSqlConnection())
{
list = conn.Query<YiBonRmTypeAvailLogEntity>(sql).ToList();
conn.Dispose();
}
return list;
}
四:底层的ADO.net代码:
protected SqlConnection GetYiBonSqlConnection() { return new SqlConnection(_yiBonConnStr); }
//YiBon
private static readonly string _yiBonConnStr = ConfigurationUtil.GetConnValue("YiBonConnStr");