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>
/// <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();
}
}
/// <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);
}
private bool TryCommit(AdoHelper db)
{
try
{
PrintLog("MSF DTC({0}) Try Commit..", this.TransIdentity);
db.Commit();
PrintLog("MSF DTC({0}) Try Commit..OK", this.TransIdentity);
return(true);
}
catch (Exception ex)
{
PrintLog("MSF DTC({0}) Try Commit..Error:{1}", this.TransIdentity, ex.Message);
return(false);
}
}
/// <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);
}