/// <summary>
/// 快速删除数据，内部根据实体类的主键进行删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
public void QuickDelete <T>(List <T> list) where T : EntityBase, new()
{
List <object> idList = new List <object>();
T entity0 = list[0];
string tableName = entity0.GetTableName();
string pkName = entity0.PrimaryKeys[0];
foreach (T entity in list)
{
idList.Add(entity[pkName]);
}
List <DeletedPKIDEntity> dteList = new List <DeletedPKIDEntity>();
foreach (T entity in list)
{
object pkValue = entity[pkName];
DeletedPKIDEntity idEntity = new DeletedPKIDEntity();
if (pkValue is string)
{
idEntity.TargetID = 0;
idEntity.TargetStringID = pkValue.ToString();
}
else
{
idEntity.TargetID = Convert.ToInt64(pkValue);
idEntity.TargetStringID = "";
}
idEntity.TargetTableName = tableName;
idEntity.DeletedTime = DateTime.Now;
dteList.Add(idEntity);
}
OQL deleteQ = OQL.From(entity0)
.Delete()
.Where(cmp => cmp.Comparer(entity0[pkName], OQLCompare.CompareType.IN, idList.ToArray()))
.END;
AdoHelper db = this.OptDbContext.CurrentDataBase;
EntityQuery <DeletedPKIDEntity> eq = new EntityQuery <DeletedPKIDEntity>(db);
db.BeginTransaction();
try
{
int delCount = eq.ExecuteOql(deleteQ);
int insCount = eq.QuickInsert(dteList);
if (delCount != insCount)
{
throw new Exception("插入和删除的数据记录数不一样，删除数：" + delCount + ",插入数：" + insCount);
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (this.dataGridView1.CurrentRow != null)
{
User user = this.dataGridView1.CurrentRow.DataBoundItem as User;
if (user != null)
{
//事务的例子
AdoHelper db = MyDB.GetDBHelperByConnectionName("AccessConn");
db.BeginTransaction();
try
{
EntityQuery <User> .Instance.Update(user, db);
db.Commit();
MessageBox.Show("标识为" + user.UserID.ToString() + " 的对象修改成功！");
}
catch (Exception ex)
{
db.Rollback();
MessageBox.Show("修改数据失败：" + ex.Message);
}
}
}
}
/// <summary>
/// 更新一个实体类集合（内部采用事务方式）
/// </summary>
/// <param name="entityList">实体类集合</param>
/// <returns>受影响的行数</returns>
public int Update(List <T> entityList)
{
T temp = new T();
//List<string> list = new List<string>();
int count = 0;
AdoHelper db = DefaultDataBase;
db.BeginTransaction();
try
{
foreach (T entity in entityList)
{
//list.Clear();
//foreach (string key in entity.PropertyList.Keys)
//{
// //只有不等于默认值的字段才需要更新
// if (entity.PropertyList[key] != null && !entity.PropertyList[key].Equals(temp.PropertyList[key]))
// list.Add(key);
//}
//foreach (string key in entity.PropertyList.Keys)
// if (entity.PropertyChangedList[key])//只修改属性更改过的字段
// list.Add(key);
count += UpdateInner(entity, entity.PropertyChangedList, db);
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
return(count);
}
/// <summary>
/// 插入一个实体对象集合（使用事务方式）
/// </summary>
/// <param name="entityList">实体对象集合</param>
/// <returns>受影响的行数</returns>
public int Insert(List <T> entityList)
{
int count = 0;
if (entityList.Count > 0)
{
//List<string> list = new List<string>();
AdoHelper db = DefaultDataBase;
db.BeginTransaction();
try
{
foreach (T entity in entityList)
{
//list.Clear();
//foreach (string key in entity.PropertyList.Keys)
// if(entity.PropertyChangedList [key ])//只插入属性更改过的字段
// list.Add(key);
count += InsertInner(entity, entity.PropertyChangedList, db);
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
}
return(count);
}
public int Save(KeHoachCT keHoachCT)
{
const string storeName = "st_UpdateKeHoachCT";
const string storeNameThamDu = "st_InsertThamDu";
const string storeNameDeleteThamDu = "st_DeleteThamDu";
int idKeHoach = 0;
using (var conn = new AdoHelper())
{
conn.BeginTransaction();
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@IdKeHoach", keHoachCT.IdKeHoach),
new SqlParameter("@TenKeHoach", keHoachCT.TenKeHoach),
new SqlParameter("@UserName", keHoachCT.UserName),
new SqlParameter("@TuNgay", keHoachCT.TuNgayModel),
new SqlParameter("@TuGio", keHoachCT.TuGio),
new SqlParameter("@DenGio", keHoachCT.DenGio),
new SqlParameter("@DenNgay", keHoachCT.DenNgayModel),
new SqlParameter("@KeHoachCongTac", keHoachCT.KeHoachCongTac),
new SqlParameter("@DiaDiem", keHoachCT.DiaDiem),
new SqlParameter("@MucDo", keHoachCT.MucDo),
new SqlParameter("@LapLai", keHoachCT.LapLai),
new SqlParameter("@Deadline", keHoachCT.Deadline),
};
conn.ExecNonQueryProc(storeName, objectParam);
SqlParameter[] objectDeleteThamDu = new SqlParameter[] {
new SqlParameter("@IdKeHoach", keHoachCT.IdKeHoach),
};
conn.ExecNonQueryProc(storeNameDeleteThamDu, objectDeleteThamDu);
if (keHoachCT.ThamDu != null && (keHoachCT.ThamDu.Count() > 0))
{
foreach (var item in keHoachCT.ThamDu)
{
SqlParameter[] objectThamDu = new SqlParameter[] {
new SqlParameter("@IdKeHoach", keHoachCT.IdKeHoach),
new SqlParameter("@NguoiThamDu", item),
new SqlParameter("@Kieu", 1),//Tham du
};
conn.ExecNonQueryProc(storeNameThamDu, objectThamDu);
}
}
if (keHoachCT.ChuTri != null && (keHoachCT.ChuTri.Count() > 0))
{
foreach (var item in keHoachCT.ChuTri)
{
SqlParameter[] objectThamDu = new SqlParameter[] {
new SqlParameter("@IdKeHoach", keHoachCT.IdKeHoach),
new SqlParameter("@NguoiThamDu", item),
new SqlParameter("@Kieu", 2),//Chu Tri
};
conn.ExecNonQueryProc(storeNameThamDu, objectThamDu);
}
}
conn.Commit();
}
return(idKeHoach);
}
/// <summary>
/// 3阶段分布式事务请求函数，执行完本地事务操作后，请求线程将继续工作，处理分布式提交的问题
/// </summary>
/// <typeparam name="T">本地事务操作函数的返回类型</typeparam>
/// <param name="client">分布式事务服务的代理客户端</param>
/// <param name="dbHelper">数据访问对象</param>
/// <param name="transFunction">事务操作函数</param>
/// <returns>返回事务操作函数的结果</returns>
public T DistTrans3PCRequest <T>(Proxy client, AdoHelper dbHelper, Func <AdoHelper, T> transFunction)
{
string transIdentity = this.TransIdentity;
ServiceRequest request = new ServiceRequest();
request.ServiceName = "DTCService";
request.MethodName = "AttendTransaction";
request.Parameters = new object[] { transIdentity };
DateTime dtcReqTime = DateTime.Now;
ResourceServerState = DistTrans3PCState.CanCommit;
System.Threading.CancellationTokenSource cts = new System.Threading.CancellationTokenSource();
var tcs = new TaskCompletionSource <T>();
//可以在外部开启事务，以方便出错，回滚事务，这里检查下是否开启了事务
if (dbHelper.TransactionCount <= 0)
{
dbHelper.BeginTransaction();
}
DataType resultDataType = MessageConverter <T> .GetResponseDataType();
client.ErrorMessage += client_ErrorMessage;
client.RequestService <bool, DistTrans3PCState, DistTrans3PCState>(request.ServiceUrl, resultDataType,
r =>
{
PrintLog("MSF DTC({0}) Controller Process Reuslt:{1},Receive time:{2}", transIdentity, r, DateTime.Now.ToString("HH:mm:ss.fff"));
client.Close();
},
s => {
var DTR_State = ProcessDistTrans3PCState <T>(client, dbHelper, transFunction, s, tcs, transIdentity);
if (DTR_State == DistTrans3PCState.Completed)
{
PrintLog("MSF DTC({0}) 3PC Request Completed,use time:{1} seconds.", transIdentity, DateTime.Now.Subtract(dtcReqTime).TotalSeconds);
}
return(DTR_State);
}
);
try
{
tcs.Task.Wait();
return(tcs.Task.Result);
}
catch (Exception ex)
{
PrintLog("MSF DTC({0}) Task Error:{1}", transIdentity, ex.Message);
TryRollback(dbHelper);
}
return(default(T));
}
/// <summary>
/// 将实体类集合中实体类的数据插入或者修改到数据库中，适用于更新数据，如果需要大批量导入数据，请考虑使用 ImportData 方法。
/// </summary>
/// <param name="entityList">实体类集合</param>
/// <returns>操作受影响的行数</returns>
public int InsertOrUpdate(List <EntityBase> entityList)
{
int count = 0;
AdoHelper db = DefaultNewDataBase;
db.BeginTransaction();
try
{
count = InsertOrUpdateInner(entityList, db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
return(count);
}
/// <summary>
/// 删除一个实体类集合（内部采用事务方式）
/// </summary>
/// <param name="entityList">实体类集合</param>
/// <returns>受影响的行数</returns>
public int Delete(List <T> entityList)
{
int count = 0;
AdoHelper db = DefaultDataBase;
db.BeginTransaction();
try
{
foreach (T entity in entityList)
{
count += DeleteInnerByDB(entity, db);
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
return(count);
}
private static void TestTransaction()
{
AdoHelper db = MyDB.GetDBHelper();
EntityQuery <AuctionOperationLog> query = new EntityQuery <AuctionOperationLog>(db);
AuctionOperationLog optLog = new AuctionOperationLog();
optLog.OperaterID = 1000;
optLog.Module = "Login";
optLog.Operation = "登录成功1";
optLog.LogSource = "PC";
db.BeginTransaction();
try
{
query.Insert(optLog);
//必须设置为全部属性已经修改，否则仅会更新 Operation 字段
optLog.ResetChanges(true);
optLog.Operation = "退出登录";
query.Insert(optLog);
//optLog.Module = "Login";
//OQL q = OQL.From(optLog).Select().Where(optLog.Module).END;
OQL q = new OQL(optLog);
//q.Select().Where(q.Condition.AND(optLog.Operation, "like", "%登录%"));
q.Select().Count(optLog.OperaterID, "");//使用空字符串参数，这样统计的值将放到 OperaterID 属性中
//必须指定db参数，否则不再一个事务中，无法进行统计查询
optLog = EntityQuery <AuctionOperationLog> .QueryObject(q, db);
int allCount = optLog.OperaterID;
//optLog 已经使用过，在生成OQL的查询前，必须使用新的实体对象，
// 否则下面的查询仅会使用OperaterID 字段从而导致分页查询出错
optLog = new AuctionOperationLog();
q = new OQL(optLog);
q.Select().OrderBy(optLog.Module, "asc").OrderBy(optLog.AtDateTime, "desc");
q.Limit(10, 2);
q.PageEnable = true;
q.PageWithAllRecordCount = allCount;
//查询列表并更新到数据库
List <AuctionOperationLog> list = EntityQuery <AuctionOperationLog> .QueryList(q, db);
foreach (AuctionOperationLog logItem in list)
{
logItem.AtDateTime = DateTime.Now;
}
query.Update(list);
db.Commit();
Console.WriteLine("事务操作成功。");
}
catch (Exception ex)
{
Console.WriteLine("Error:" + ex.Message);
db.Rollback();
}
}
/// <summary>
/// 将实体集合中的所有数据导入数据库，如果数据已经存在则修改（先删除再插入）否则直接插入。如果实体中的数据只包含部分字段的数据，请勿使用该方法。
/// </summary>
/// <param name="entityList">同一实体类集合</param>
/// <param name="bulkCopyModel">是否使用批量插入的方式更新，只支持SQLSERVER。
/// 取值含义：0，不使用批量复制，1，批量复制前删除数据库中对应的重复记录，2，不检查重复，直接批量插入
/// </param>
/// <returns>操作受影响的行数</returns>
public int ImportData(List <EntityBase> entityList, int bulkCopyModel)
{
int count = 0;
if (entityList == null || entityList.Count == 0)
{
return(0);
}
AdoHelper db = DefaultNewDataBase;
#if (CMD_FAST)
//如果是SQLSERVER，考虑批量复制的方式
if (bulkCopyModel > 0 && db is SqlServer)
{
if (bulkCopyModel == 1)
{
//将目标数据库中对应的数据删除
db.BeginTransaction();
try
{
count = DeleteDataInner(entityList, db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
}
//执行大批量复制
DataTable source = EntitysToDataTable <EntityBase>(entityList);
SqlServer.BulkCopy(source, db.ConnectionString, source.TableName, 500);
return(entityList.Count);
}
else
{
db.BeginTransaction();
try
{
count = ImportDataInner(entityList, db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
}
#else
db.BeginTransaction();
try
{
count = ImportDataInner(entityList, db);
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
#endif
return(count);
}
/// <summary>
/// 保存销售信息
/// </summary>
/// <param name="customer">客户信息</param>
/// <param name="integral">要增加的积分</param>
/// <returns></returns>
private GoodsSellNote SaveSalesInfo(Customer customer, int integral)
{
GoodsSellNote note = new GoodsSellNote();
note.CustomerID = customer.CustomerID;
note.ManchinesNumber = this.CurrCRManchines.CashRegisterNo;
note.SalesmanID = this.CurrCashier.WorkNumber;
note.SalesType = "店内销售";
note.SellDate = DateTime.Now;
note.GoodsSellDetails = new List <GoodsSellDetail>();
AdoHelper db = MyDB.GetDBHelper();
db.BeginTransaction();
try
{
EntityQuery <GoodsSellNote> query = new EntityQuery <GoodsSellNote>(db);
if (query.Insert(note) > 0)
{
foreach (Goods goods in customer.Goodss)
{
if (goods.GoodsNumber > 0)
{
//处理详单
GoodsSellDetail detail = new GoodsSellDetail();
detail.GoodsPrice = goods.GoodsPrice;
detail.NoteID = note.NoteID;
detail.SellNumber = goods.GoodsNumber;
detail.SerialNumber = goods.SerialNumber;
note.GoodsSellDetails.Add(detail);
//更新库存
SuperMarketDAL.Entitys.GoodsStock stock = new SuperMarketDAL.Entitys.GoodsStock();
stock.GoodsID = goods.GoodsID;
stock.Stocks = goods.GoodsNumber;
OQL q = OQL.From(stock)
.UpdateSelf('-', stock.Stocks)
.Where(stock.GoodsID)
.END;
EntityQuery <SuperMarketDAL.Entitys.GoodsStock> .ExecuteOql(q, db);
}
}
EntityQuery <GoodsSellDetail> queryDetail = new EntityQuery <GoodsSellDetail>(db);
queryDetail.Insert(note.GoodsSellDetails);
//更新会员的积分
if (integral > 0)
{
SuperMarketDAL.Entitys.CustomerContactInfo ccInfo = new CustomerContactInfo();
ccInfo.CustomerID = customer.CustomerID;
ccInfo.Integral = integral;
OQL qc = OQL.From(ccInfo)
.UpdateSelf('+', ccInfo.Integral)
.Where(ccInfo.CustomerID)
.END;
EntityQuery <SuperMarketDAL.Entitys.GoodsStock> .ExecuteOql(qc, db);
}
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
throw new Exception("插入销售记录失败，内部错误原因：" + ex.Message);
}
return(note);
}
/// <summary>
/// 3阶段分布式事务请求函数，执行完本地事务操作后，请求线程将继续工作，处理分布式提交的问题
/// </summary>
/// <typeparam name="T">本地事务操作函数的返回类型</typeparam>
/// <param name="client">分布式事务服务的代理客户端</param>
/// <param name="dbHelper">数据访问对象</param>
/// <param name="transFunction">事务操作函数</param>
/// <returns>返回事务操作函数的结果</returns>
public T DistTrans3PCRequest <T>(Proxy client, AdoHelper dbHelper, Func <AdoHelper, T> transFunction)
{
string transIdentity = this.TransIdentity;
ServiceRequest request = new ServiceRequest();
request.ServiceName = "DTCService";
request.MethodName = "AttendTransaction";
request.Parameters = new object[] { transIdentity };
DateTime dtcReqTime = DateTime.Now;
ResourceServerState = DistTrans3PCState.CanCommit;
System.Threading.CancellationTokenSource cts = new System.Threading.CancellationTokenSource();
var tcs = new TaskCompletionSource <T>();
//可以在外部开启事务，以方便出错，回滚事务，这里检查下是否开启了事务
if (dbHelper.TransactionCount <= 0)
{
dbHelper.BeginTransaction();
}
DataType resultDataType = MessageConverter <T> .GetResponseDataType();
client.ErrorMessage += client_ErrorMessage;
client.RequestService <bool, DistTrans3PCState, DistTrans3PCState>(request.ServiceUrl, resultDataType,
r =>
{
PrintLog("MSF DTC({0}) Controller Process Reuslt:{1},Receive time:{2}", transIdentity, r, DateTime.Now.ToString("HH:mm:ss.fff"));
client.Close();
},
s =>
{
PrintLog("MSF DTC({0}) Resource at {1} receive DTC Controller state:{2}", transIdentity, DateTime.Now.ToString("HH:mm:ss.fff"), s);
if (s == DistTrans3PCState.CanCommit)
{
try
{
T t = transFunction(dbHelper);
ResourceServerState = DistTrans3PCState.Rep_Yes_1PC;
tcs.SetResult(t);
}
catch (Exception ex)
{
PrintLog(ex.Message);
ResourceServerState = DistTrans3PCState.Rep_No_1PC;
tcs.SetException(ex);
}
//警告：如果自此之后，很长时间没有收到协调服务器的任何回复，本地应回滚事务
new Task(() =>
{
DateTime currOptTime = DateTime.Now;
PrintLog("MSF DTC({0}) 1PC,Child moniter task has started at time:{1}", transIdentity, currOptTime.ToString("HH:mm:ss.fff"));
while (ResourceServerState != DistTrans3PCState.Completed)
{
System.Threading.Thread.Sleep(10);
if (ResourceServerState != DistTrans3PCState.Rep_Yes_1PC && ResourceServerState != DistTrans3PCState.Rep_No_1PC)
{
//在1阶段，只要发现通信中断，就应该回滚事务
if (ResourceServerState == DistTrans3PCState.CommunicationInterrupt)
{
TryRollback(dbHelper);
client.Close();
PrintLog("** MSF DTC({0}) 1PC,Child moniter task check Communication Interrupt ,Rollback Transaction,task break!", transIdentity);
}
else
{
PrintLog("MSF DTC({0}) 1PC,Child moniter task find DistTrans3PCState has changed,Now is {1},task break!", transIdentity, ResourceServerState);
}
break;
}
else
{
//在1阶段回复消息后，超过一分钟，资源服务器没有收到协调服务器的任何响应，回滚本地事务
if (DateTime.Now.Subtract(currOptTime).TotalSeconds > 60)
{
TryRollback(dbHelper);
client.Close();
PrintLog("** MSF DTC({0}) 1PC,Child moniter task check Opreation timeout,Rollback Transaction,task break!", transIdentity);
break;
}
}
}
}, TaskCreationOptions.None).Start();
return(ResourceServerState);
}
else if (s == DistTrans3PCState.PreCommit)
{
ResourceServerState = DistTrans3PCState.ACK_Yes_2PC;
//警告：如果自此之后，如果成功确认资源服务器进入第二阶段，但是很长时间没有收到协调服务器的任何回复，本地应提交事务
new Task(() =>
{
DateTime currOptTime = DateTime.Now;
PrintLog("MSF DTC({0}) 2PC,Child moniter task has started at time:{1}", transIdentity, currOptTime.ToString("HH:mm:ss.fff"));
while (ResourceServerState != DistTrans3PCState.Completed)
{
System.Threading.Thread.Sleep(10);
if (ResourceServerState != DistTrans3PCState.ACK_Yes_2PC)
{
//在2阶段，如果在1秒内就检测到通信已经中断，事务控制器可能难以收到预提交确认信息，考虑回滚本地事务
if (ResourceServerState == DistTrans3PCState.CommunicationInterrupt)
{
if (DateTime.Now.Subtract(currOptTime).TotalMilliseconds < 1000)
{
TryRollback(dbHelper);
PrintLog("** MSF DTC({0}) 2PC,Child moniter find Communication Interrupt ,task break!", transIdentity);
}
else
{
//否则，1秒后才发现连接已经断开，预提交确认信号大概率已经发送过去，不用再等，提交本地事务
TryCommit(dbHelper);
PrintLog("MSF DTC({0}) 2PC,Child moniter find Communication Interrupt,but ACK_Yes_2PC send ok,tansaction Commit ,task break!", transIdentity);
}
//已经结束事务，关闭通信连接
client.Close();
}
else
{
//如果通信未中断且已经是其它状态，退出当前子任务
PrintLog("MSF DTC({0}) 2PC,Child moniter task find DistTrans3PCState has changed,Now is {1},task break!", transIdentity, ResourceServerState);
}
break;
}
else
{
//在2阶段，通信未中断，超过30秒，资源服务器没有收到协调服务器的任何响应，提交本地事务
if (DateTime.Now.Subtract(currOptTime).TotalSeconds > 30)
{
TryCommit(dbHelper);
client.Close();
PrintLog("** MSF DTC({0}) 2PC,Child moniter task check Opreation timeout,Commit Transaction,task break!", transIdentity);
break;
}
}
}
}, TaskCreationOptions.None).Start();
return(ResourceServerState);
}
else if (s == DistTrans3PCState.Abort)
{
TryRollback(dbHelper);
ResourceServerState = DistTrans3PCState.ACK_No_2PC;
return(ResourceServerState);
}
else if (s == DistTrans3PCState.DoCommit)
{
if (TryCommit(dbHelper))
{
ResourceServerState = DistTrans3PCState.Rep_Yes_3PC;
}
else
{
ResourceServerState = DistTrans3PCState.Rep_No_3PC;
}
return(ResourceServerState);
}
else
{
//其它参数，原样返回
ResourceServerState = s;
if (s == DistTrans3PCState.Completed)
{
PrintLog("MSF DTC({0}) 3PC Request Completed,use time:{1} seconds.", transIdentity, DateTime.Now.Subtract(dtcReqTime).TotalSeconds);
}
return(s);
}
});
try
{
tcs.Task.Wait();
return(tcs.Task.Result);
}
catch (Exception ex)
{
PrintLog("MSF DTC({0}) Task Error:{1}", transIdentity, ex.Message);
TryRollback(dbHelper);
}
return(default(T));
}
