/// <summary> /// ���� ɾ�� /// </summary> /// <param name="orgid">Orgid ���</param> /// <returns>ִ�н��</returns> public int OrganizationDelete(out string resultMsg, Int32 orgid, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "usp_organization_delete"; //������� IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "Id", ParameterValue = orgid, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String }); //����ִ�� res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); foreach (var item in parm) { //��ȡ�������ֵ if (item.ParameterName == "resultMsg") { resultMsg = item.ParameterValue.ToString(); break; } } } catch (Exception ex) { if(tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
/// <summary> /// ���� ɾ�� /// </summary> /// <param name="id">Id ���</param> /// <returns>ִ�н��</returns> public int ArticleDelete(out string resultMsg, string authorizeInfo, Int32 id, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "usp_article_Delete_by_id"; //������� IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); //����ִ�� res = DBHelper.ExecuteNonQuery(authorizeInfo, sql, true, parm, tran); } catch (AuthorizeException ex) { throw ex; } catch (Exception ex) { if (tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
/// <summary> /// 数据 删除 /// </summary> /// <param name="djbm">Djbm 单据编码</param> /// <param name="spxh">Spxh 商品序号</param> /// <returns>执行结果</returns> public int DetaildataDelete(out string resultMsg, String djbm, Int32 spxh, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //存储过程名称 string sql = " USP_DETAILDATA_DELETE "; //参数添加 IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "DJBM", ParameterValue = djbm, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "SPXH", ParameterValue = spxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String }); //更新执行 res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); foreach (var item in parm) { //获取输出参数值 if (item.ParameterName == "resultMsg") { resultMsg = item.ParameterValue.ToString(); break; } } } catch (Exception ex) { if (tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
protected void btnDiscard_Click(object sender, EventArgs e) { if (Page.IsValid) { string pipelineStart = this.txtDiscardStart.Text.Trim(); string pipelineEnd = this.txtDiscardEnd.Text.Trim(); string[] arrangedNumber = Common.GetArranged(pipelineStart, pipelineEnd); if (arrangedNumber.Length == 0) { this.lblErrorDiscard.Text = "操作失败,请正确填写起止单证号!"; return; } int count = 0, countEffect = 0; System.Data.Common.DbTransaction tran = Common.DB.BeginTransaction(); foreach (string number in arrangedNumber) { countEffect = Common.DB.Delete(Tables.t_Serial) .Where(Tables.t_Serial.caseNo == number) .Where(Tables.t_Serial.caseOwner == User.Identity.Name) .SetTransaction(tran) .Execute(); if (countEffect > 0) { count++; } else { tran.Rollback(); this.lblErrorDiscard.Text = string.Format("销毁单证号 {0} 发生错误!请确认您是否拥有该单证号。", number); return; } } if (count > 0) { Common.DB.Insert(Tables.t_Log) .AddColumn(Tables.t_Log.LogContent, "销毁 " + pipelineStart + "-" + pipelineEnd + " 共 " + count + " 个单证号") .AddColumn(Tables.t_Log.LogType, "销毁") .AddColumn(Tables.t_Log.LogOwner, User.Identity.Name) .AddColumn(Tables.t_Log.datetime, DateTime.Now) .SetTransaction(tran) .Execute(); tran.Commit(); string scriptSucc = this.ClientScriptFactory.PopAlert("成功销毁 " + count.ToString() + " 个单证号!"); this.ClientScript.RegisterClientScriptBlock(GetType(), "", scriptSucc, true); this.gvLog.DataBind(); } else { this.lblErrorDiscard.Text = "没有销毁任何单证号!"; tran.Commit(); } } }
public bool AddPO(PO po) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_PO_Insert); db.AddInParameter(dbCommand, "@sPOCode", DbType.String, po.POCode); db.AddInParameter(dbCommand, "@mPOAmount", DbType.Currency, po.POAmount); db.AddInParameter(dbCommand, "@mBalanceAmount", DbType.Currency, po.BalanceAmount); db.AddInParameter(dbCommand, "@iPOCreatedUser", DbType.Int32, po.POCreatedUser); db.AddInParameter(dbCommand, "@iSupId", DbType.Int32, po.SupId); db.AddInParameter(dbCommand, "@dtPODate", DbType.DateTime, po.PODate); db.AddInParameter(dbCommand, "@iRequestedBy", DbType.Int32, po.RequestedBy.Value); db.AddInParameter(dbCommand, "@sPOComment", DbType.String, po.POComment); db.AddOutParameter(dbCommand, "@iPOId", DbType.Int32, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int32 newPOID = (Int32)db.GetParameterValue(dbCommand, "@iPOId"); if (newPOID > 0) { po.POId = newPOID; if (this.UpdatePOItems(po, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddPO(PO po)"); transaction.Rollback(); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public bool AddPR(PurchaseReturns pr) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_PR_Insert); db.AddInParameter(dbCommand, "@iGRNId", DbType.Int64, pr.GRNId); db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, pr.CreatedBy); db.AddInParameter(dbCommand, "@mTotalReturns", DbType.Currency, pr.TotalReturns); db.AddInParameter(dbCommand, "@sComment", DbType.String, pr.Comment); db.AddInParameter(dbCommand, "@dReturnDate", DbType.Date, pr.ReturnDate); db.AddOutParameter(dbCommand, "@iPRId", DbType.Int32, 4); db.AddOutParameter(dbCommand, "@sPRCode", DbType.String, 10); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int32 newPRID = (Int32)db.GetParameterValue(dbCommand, "@iPRId"); string prCode = (string)db.GetParameterValue(dbCommand, "@sPRCode"); if (newPRID > 0) { pr.PRId = newPRID; pr.PRCode = prCode; if (this.UpdatePRItems(pr, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddPR(PurchaseReturns pr)"); transaction.Rollback(); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
private void Rollback(DbTransaction tx) { try { tx.Rollback(); } catch (Exception ex) { _log.Error(ex); } }
public ActionResult DeleteOrder(int id) { try { db.Connection.Open(); transaction = db.Connection.BeginTransaction(); var resShipTo = (from i in db.shipTo where i.ID == id select i).FirstOrDefault(); var resItCart = from i in db.itCart where i.shipToID == id select i; var resDB = from i in db.OrderDashBoards where i.shipToID == id select i; if (resShipTo.getOState.Descr.Contains("Создан заказ")) { if (resDB != null) { foreach (var i in resDB) { db.DeleteObject(i); } } if (resItCart != null) { foreach (var i in resItCart) { db.DeleteObject(i); } } if (resShipTo != null) { db.DeleteObject(resShipTo); } db.SaveChanges(); transaction.Commit(); } } catch { if (transaction != null) { transaction.Rollback(); } } return RedirectToAction("Index", new { parUser = User.Identity.Name }); }
public bool AddGetPass(GetPass getPass) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GetPass_Add); db.AddInParameter(cmd, "@sGPCode", DbType.String, getPass.GPCode); db.AddInParameter(cmd, "@iInvoiceId", DbType.Int32, getPass.InvoiceId); db.AddInParameter(cmd, "@iCreatedBy", DbType.Int32, getPass.CreatedBy); db.AddOutParameter(cmd, "@biGPId", DbType.Int64, 8); if (db.ExecuteNonQuery(cmd, transaction) > 0) { Int32 newGatePassID = Convert.ToInt32(db.GetParameterValue(cmd, "@biGPId")); if (newGatePassID > 0) { getPass.GPId = newGatePassID; if (this.UpdateGetPassDetails(getPass, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { transaction.Rollback(); result = false; throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public void Add(Order order) { owner = GetOwner(); vendor = GetVendor(order.VendorID); using (cn = dbContext.Database.Connection) { try { cn.Open(); tran = cn.BeginTransaction(); DbCommand cmd = cn.CreateCommand(); cmd.Transaction = tran; cmd.CommandText = @"INSERT INTO tblSO (SOCode, SODate, BranchId, CurrencyId, EmployeeId, VendorID, TermID, TaxID, SOGrossAmt, SONetAmt, StatusID, Operator, Computer, LastUpdate) VALUES (@SOCode, @SODate, @BranchId, @CurrencyId, @EmployeeId, @VendorId, @TermId, 0, @SOGrossAmt, @SONetAmt, 0, @Operator, @Computer, @LastUpdate); SELECT IDENT_CURRENT('tblSO');"; cmd.Parameters.Add(new SqlParameter("@SOCode", order.SOCode)); cmd.Parameters.Add(new SqlParameter("@SODate", order.SODate)); cmd.Parameters.Add(new SqlParameter("@BranchId", order.BranchID)); cmd.Parameters.Add(new SqlParameter("@CurrencyId", order.CurrencyId)); cmd.Parameters.Add(new SqlParameter("@EmployeeId", owner.EmployeeId)); cmd.Parameters.Add(new SqlParameter("@VendorId", vendor.Id)); cmd.Parameters.Add(new SqlParameter("@TermId", owner.DefTerm)); cmd.Parameters.Add(new SqlParameter("@SOGrossAmt", order.SOGrossAmt)); cmd.Parameters.Add(new SqlParameter("@SONetAmt", order.SONetAmt)); cmd.Parameters.Add(new SqlParameter("@Operator", order.Username)); cmd.Parameters.Add(new SqlParameter("@Computer", System.Environment.MachineName)); cmd.Parameters.Add(new SqlParameter("@LastUpdate", DateTime.Now)); soId = Convert.ToInt32(cmd.ExecuteScalar()); foreach (OrderItem item in order.Items) { AddOrderItem(item); } tran.Commit(); } catch (Exception ex) { if (tran != null) tran.Rollback(); throw ex; } finally { cn.Close(); } } }
/// <summary> /// Creates the cheque book and saves the cheques in tha dataset dsCheques property /// </summary> /// <param name="chqBook"></param> /// <returns></returns> public bool Create(ChequeBook chqBook) { bool success = true; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Cheque_Insert); db.AddInParameter(dbCommand, "@iNoOfCheques", DbType.Int32, chqBook.NoOfCheques); db.AddInParameter(dbCommand, "@biFirstChqNo", DbType.Int64, chqBook.FirstChqNo); db.AddInParameter(dbCommand, "@biLastChqNo", DbType.Int64, chqBook.LastChqNo); db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, chqBook.CreatedBy); db.AddInParameter(dbCommand, "@sBankName", DbType.String, chqBook.BankName); db.AddInParameter(dbCommand, "@sBankBranch", DbType.String, chqBook.BankBranch); db.AddOutParameter(dbCommand, "@iChqBookId", DbType.Int32, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int32 newChqBookId = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iChqBookId")); if (newChqBookId > 0) { chqBook.ChqBookId = newChqBookId; if (this.UpdateCheques(chqBook, db, transaction)) { transaction.Commit(); success = true; } } } } catch (Exception ex) { success = false; transaction.Rollback(); ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool Create(ChequeBook chqBook)"); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return success; }
public bool AddItemTransfer(ItemTransfer itemTransfer) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Item_Transfer_Insert); db.AddInParameter(dbCommand, "@iInvoiceId", DbType.Int32, DBNull.Value); db.AddInParameter(dbCommand, "@iBranchFrom", DbType.Int32, itemTransfer.BranchFrom); db.AddInParameter(dbCommand, "@iBranchTo", DbType.Int32, itemTransfer.BranchTo); db.AddInParameter(dbCommand, "@iTransferQty", DbType.Int32, itemTransfer.TransferQty); db.AddInParameter(dbCommand, "@iTransferBy", DbType.Int32, itemTransfer.TransferBy); db.AddOutParameter(dbCommand, "@iTransferId", DbType.Int32, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int32 newTransferID = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iTransferId")); if (newTransferID > 0) { itemTransfer.TransferId = newTransferID; if (this.AddItemTransferInvoice(itemTransfer, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { transaction.Rollback(); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public bool AddGroups(Group group) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Groups_Insert); db.AddInParameter(dbCommand, "@sGroupCode", DbType.String, group.GroupCode); db.AddInParameter(dbCommand, "@sGroupName", DbType.String, group.GroupName); db.AddInParameter(dbCommand, "@sDescription", DbType.String, group.Description); db.AddInParameter(dbCommand, "@tiItemCount", DbType.Int16, group.ItemCount); db.AddInParameter(dbCommand, "@bIsActive", DbType.Boolean, group.IsActive); db.AddOutParameter(dbCommand, "@tiGroupId", DbType.Int16, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int16 newGroupId = Convert.ToInt16(db.GetParameterValue(dbCommand, "@tiGroupId")); if (newGroupId > 0) { group.GroupId = newGroupId; if (this.UpdateGroupItems(group, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { transaction.Rollback(); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public bool AddBrands(Brand brand) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Brands_Insert); db.AddInParameter(dbCommand, "@sBrandName", DbType.String, brand.BrandName); db.AddOutParameter(dbCommand, "@iBrandId", DbType.Int32, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int32 newBrandID = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iBrandId")); if (newBrandID > 0) { brand.BrandId = newBrandID; if (this.UpdateBrandCategories(brand, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { transaction.Rollback(); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public static Boolean delFeeByPatientId(string patient_id,string admiss_times,string charge_code) { DbConnection con = db.open(); command = con.CreateCommand(); trans = con.BeginTransaction(); command.Transaction = trans; try { sql = string.Format("insert into zy_detail_charge_zzdelete(patient_id,admiss_times,ledger_sn,detail_sn,occ_time,confirm_time,happen_date,charge_code,bill_item_code,audit_code,orig_price,charge_price,charge_amount,charge_group,apply_opera,confirm_opera,charge_status,infant_flag,self_flag,separate_flag,suprice_flag,drug_flag,emergency_flag,ope_flag,apply_status,pay_self,serial,ward_sn,dept_sn,order_no,exec_unit,group_no,page_no,page_type,report_date,adt_dept_no,back_flag,orig_dept,orig_ward,cash_date,account_date,doctor_code,samp_id,occ_page_no,fit_flag,samp_barcode,samp_type,charge_fee,parent_detail_sn) select patient_id,admiss_times,ledger_sn,detail_sn,occ_time,confirm_time,happen_date,charge_code,bill_item_code,audit_code,orig_price,charge_price,charge_amount,charge_group,apply_opera,confirm_opera,charge_status,infant_flag,self_flag,separate_flag,suprice_flag,drug_flag,emergency_flag,ope_flag,apply_status,pay_self,serial,ward_sn,dept_sn,order_no,exec_unit,group_no,page_no,page_type,report_date,adt_dept_no,back_flag,orig_dept,orig_ward,cash_date,account_date,doctor_code,samp_id,occ_page_no,fit_flag,samp_barcode,samp_type,charge_fee,parent_detail_sn FROM view_zy_detail_charge_c where patient_id ='{0}' and admiss_times = {1} and charge_code = '{2}' ", patient_id, admiss_times, charge_code); //insert into zy_detail_charge_zzdelete select [patient_id] ,[admiss_times],[ledger_sn],[detail_sn],[occ_time],[confirm_time],[happen_date],[charge_code] ,[bill_item_code] ,[audit_code] ,[orig_price] ,[charge_price],[charge_amount],[charge_group] ,[apply_opera] ,[confirm_opera],[charge_status] ,[infant_flag] ,[self_flag],[separate_flag] ,[suprice_flag],[drug_flag] ,[emergency_flag],[ope_flag],[apply_status] ,[pay_self] ,[serial] ,[ward_sn],[dept_sn] ,[order_no] ,[exec_unit] ,[group_no] ,[page_no] ,[page_type],[report_date],[adt_dept_no] ,[back_flag],[orig_dept] ,[orig_ward],[cash_date] ,[account_date] ,[doctor_code] ,[samp_id] ,[occ_page_no],[fit_flag] ,[samp_barcode] ,[samp_type],[charge_fee] ,[parent_detail_sn] ,[exec_time],[exec_status],[charge_sort] from view_zy_detail_charge_c if (db.ExecuteNonQueryByTrans(sql, null,command) > 0) { sql = string.Format("delete from zy_detail_charge where patient_id ='{0}' and admiss_times ={1} and charge_code ={2} ", patient_id, admiss_times, charge_code); if (db.ExecuteNonQueryByTrans(sql, null,command) > 0) isDelete = true; sql = string.Format("delete from zy_detail_charge_b where patient_id ='{0}' and admiss_times ={1} and charge_code ={2} ", patient_id, admiss_times, charge_code); if (db.ExecuteNonQueryByTrans(sql, null,command)>0) isDelete = true; sql = string.Format("delete from zy_detail_charge_c where patient_id ='{0}' and admiss_times ={1} and charge_code ={2} ", patient_id, admiss_times, charge_code); if (db.ExecuteNonQueryByTrans(sql, null,command) > 0) isDelete = true; } trans.Commit(); }catch(Exception e) { try { trans.Rollback(); } catch (SqlException ex) { if (con != null) con.Close(); if (trans.Connection != null) { trans.Connection.Close(); } } } return isDelete; }
internal static void RollbackTransaction(DbTransaction dbTrans) { if (dbTrans == null) { throw new ArgumentNullException("dbTrans"); } // Get transaction connection for closing before rollback DbConnection dbConn = dbTrans.Connection; try { // Rollback transaction dbTrans.Rollback(); } finally { // Close connection if (dbConn != null) { dbConn.Close(); } } }
/// <summary> /// /// </summary> /// <param name="DbFilePath">数据库相对URL</param> /// <param name="ID"></param> /// <returns></returns> public static DataTable GetLogData(string DbFilePath, string ID) { string DbPath = LogDbPathRoot + "\\" + DbFilePath; SQLiteConnection conn = new SQLiteConnection("Data Source=" + DbPath + ";Pooling=true;Max Pool Size=100;"); conn.Open(); string sql = @"SELECT ID,Time,Level,Type,UserID,UserName,UserIP,Message,Data FROM Logs where ID=@ID"; using (SQLiteCommand cmd = new SQLiteCommand(sql, conn)) { System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction(); try { cmd.Parameters.Add(new SQLiteParameter("@ID", ID)); SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); DataTable data = new DataTable(); adapter.Fill(data); transaction.Commit(); adapter.Dispose(); return(data); } catch (Exception exp) { transaction.Rollback(); throw exp; } finally { transaction.Dispose(); cmd.Connection.Close(); } } }
/// <summary> /// 执行多个存储过程 /// </summary> /// <param name="args">存储过程列表</param> /// <returns></returns> public bool ExecuteNoQueryTranPro(List<CommandList> args) { bool bResult = false; try { Database db = GetDataBase(); _cmd = db.DbProviderFactory.CreateCommand(); _cmd.Connection = db.CreateConnection(); _cmd.Connection.Open(); _tran = _cmd.Connection.BeginTransaction(); _cmd.Transaction = _tran; _cmd.CommandTimeout = TimeOut; foreach (CommandList command in args) { _cmd.CommandType = command.Type; _cmd.CommandText = command.strCommandText; _cmd.Parameters.Clear(); if (null != command.Params) { _cmd.Parameters.AddRange(command.Params); } db.ExecuteNonQuery(_cmd, _tran); } _tran.Commit(); bResult = true; } catch (Exception ex) { if(null != _tran) { _tran.Rollback(); } throw ex; } finally { Dispose(); } return bResult; }
/// <summary> /// 执行多条SQL脚本 /// </summary> /// <param name="strCmdTexts"></param> /// <returns></returns> public bool ExecuteNoQueryTran(List<string> strCmdTexts) { bool bResult = false; try { Database db = GetDataBase(); _cmd = db.DbProviderFactory.CreateCommand(); _cmd.Connection = db.CreateConnection(); _cmd.Connection.Open(); _tran = _cmd.Connection.BeginTransaction(); _cmd.CommandTimeout = TimeOut; _cmd.CommandType = CommandType.Text; foreach (string strCommandText in strCmdTexts) { _cmd.CommandText = strCommandText; db.ExecuteNonQuery(_cmd, _tran); } _tran.Commit(); bResult = true; } catch (Exception ex) { if (null != _tran) { _tran.Rollback(); } throw ex; } finally { Dispose(); } return bResult; }
public bool UpdateItemsInBulk(DataSet dsItems) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); //todo:Insert,Delete if required! DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Items_UpdateAllById); db.AddInParameter(updCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current); db.AddInParameter(updCommand, "@biQuantityInHand", DbType.String, "QuantityInHand", DataRowVersion.Current); db.AddInParameter(updCommand, "@bIsActive", DbType.Boolean, "IsActive", DataRowVersion.Current); db.UpdateDataSet(dsItems, dsItems.Tables[0].TableName, null, updCommand, null, transaction); result = true; transaction.Commit(); } catch (System.Exception ex) { transaction.Rollback(); result = false; throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public bool UpdateAllCustomers(DataSet dsCustomers) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Customers_UpdateAll_ByID); db.AddInParameter(updCommand, "@iCustomerID", DbType.Int32, "CustomerID", DataRowVersion.Current); db.AddInParameter(updCommand, "@mCus_CreditTotal", DbType.Currency, "Cus_CreditTotal", DataRowVersion.Current); db.UpdateDataSet(dsCustomers, dsCustomers.Tables[0].TableName, null, updCommand, null, transaction); result = true; transaction.Commit(); } catch (System.Exception ex) { transaction.Rollback(); result = false; throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public bool UpdateVoucher(VoucherRecievable voucher) { bool result = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Voucher_InsVoucher); db.AddInParameter(cmd, "@mPaymentAmount", DbType.Currency, voucher.PaymentAmount); db.AddInParameter(cmd, "@dtPaymentDate", DbType.DateTime, voucher.PaymentDate); db.AddInParameter(cmd, "@iPaymentTypeId", DbType.Int32, (int)voucher.PaymentTypeId); switch (voucher.PaymentTypeId) { case Structures.PaymentTypes.CASH: db.AddInParameter(cmd, "@sChequeNo", DbType.String, DBNull.Value);//null db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);//null db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);//null db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value); break; case Structures.PaymentTypes.CHEQUE: db.AddInParameter(cmd, "@sChequeNo", DbType.String, voucher.ChequeNo); db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, voucher.ChequeDate); db.AddInParameter(cmd, "@sCardType", DbType.String, DBNull.Value);//null db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, DBNull.Value);//null break; case Structures.PaymentTypes.CREDIT_CARD: db.AddInParameter(cmd, "@sChequeNo", DbType.String,voucher.ChequeNo); db.AddInParameter(cmd, "@dtChequeDate", DbType.DateTime, DBNull.Value);//null db.AddInParameter(cmd, "@sCardType", DbType.String, voucher.CardType); db.AddInParameter(cmd, "@dCardCommisionRate", DbType.Decimal, voucher.CardCommisionRate); break; default: break; } db.AddInParameter(cmd, "@sComment", DbType.String, voucher.Comment); db.AddInParameter(cmd, "@iCreatedBy", DbType.Int32, voucher.CreatedBy); db.AddInParameter(cmd, "@inewPaymentID", DbType.Int64, voucher.PaymentID); if (db.ExecuteNonQuery(cmd, transaction) > 0) { Int64 newVoucherID = 0; Int64.TryParse(db.GetParameterValue(cmd, "@inewPaymentID").ToString().Trim(), out newVoucherID); if (newVoucherID > 0) { voucher.PaymentID = newVoucherID; voucher.PaymentCode = db.GetParameterValue(cmd, "@snewPaymentID").ToString().Trim(); if (this.UpdateVoucherDetails(voucher, db, transaction)) { transaction.Commit(); result = true; } } } } catch (System.Exception ex) { transaction.Rollback(); result = false; throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
public static void RollbackTransaction(DbTransaction dbTransaction) { dbTransaction.Rollback(); }
/// <summary> /// ���� ���/���� /// </summary> /// <param name="user">ʵ��</param> /// <returns>ִ�н��</returns> public int UserInsertUpdate(out string resultMsg, ModelUser user, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "USP_USER_INSERT_UPDATE"; //������� IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = user.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "NAME", ParameterValue = user.Name, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "ACCOUNT", ParameterValue = user.Account, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "PASSWORD", ParameterValue = user.Password, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "ORGANIZATION", ParameterValue = user.Organization, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = user.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "PERMISSIONS", ParameterValue = user.Permissions, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String }); //����/����ִ�� res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); foreach (var item in parm) { //��ȡ�������ֵ if (item.ParameterName == "resultMsg") { resultMsg = item.ParameterValue.ToString(); if (resultMsg.Contains(BaseDict.ErrorPrefix)) res = -1; break; } } } catch (Exception ex) { if (tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
/// <summary> /// ���� ���/���� /// </summary> /// <param name="article">ʵ��</param> /// <returns>ִ�н��</returns> public int ArticleInsertUpdate(out string resultMsg,ModelArticle article,DbTransaction tran =null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "USP_ARTICLE_INSERT_UPDATE"; //������� IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = article.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "TITLE", ParameterValue = article.Title, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "AUTHOR", ParameterValue = article.Author, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "SOURCE", ParameterValue = article.Source, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "CONTEXT", ParameterValue = article.Context, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "ABSTRACT", ParameterValue = article.Abstract, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "THUMBNAILS", ParameterValue = article.Thumbnails, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "CATEGORYID", ParameterValue = article.Categoryid, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "Categorytwo", ParameterValue = article.CategoryTwo, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "KEYWORD", ParameterValue = article.Keyword, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "ISRECOMMEND", ParameterValue = article.Isrecommend, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "TITLECOLOR", ParameterValue = article.Titlecolor, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "TITLEISBOLD", ParameterValue = article.Titleisbold, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = article.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); //����/����ִ�� res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); } catch (Exception ex) { if(tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
/// <summary> /// 数据 添加/更新 /// </summary> /// <param name="detaildata">实体</param> /// <returns>执行结果</returns> public int DetaildataInsertUpdate(out string resultMsg, ModelDetaildata detaildata, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //存储过程名称 string sql = "USP_DETAILDATA_INSERT_UPDATE"; //参数添加 IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "DJBM", ParameterValue = detaildata.Djbm, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "SPXH", ParameterValue = detaildata.Spxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "BAXH", ParameterValue = detaildata.Baxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "SPBH", ParameterValue = detaildata.Spbh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "FJBH", ParameterValue = detaildata.Fjbh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "SPMC", ParameterValue = detaildata.Spmc, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "GGXH", ParameterValue = detaildata.Ggxh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "CJSL", ParameterValue = detaildata.Cjsl, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object }); parm.Add(new DBParameter() { ParameterName = "CJDW", ParameterValue = detaildata.Cjdw, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "CJDJ", ParameterValue = detaildata.Cjdj, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object }); parm.Add(new DBParameter() { ParameterName = "CJZJ", ParameterValue = detaildata.Cjzj, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object }); parm.Add(new DBParameter() { ParameterName = "BIZHI", ParameterValue = detaildata.Bizhi, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "FDSL", ParameterValue = detaildata.Fdsl, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object }); parm.Add(new DBParameter() { ParameterName = "FDDW", ParameterValue = detaildata.Fddw, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "BBH", ParameterValue = detaildata.Bbh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "HUOH", ParameterValue = detaildata.Huoh, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "SCCJ", ParameterValue = detaildata.Sccj, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "DESL", ParameterValue = detaildata.Desl, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object }); parm.Add(new DBParameter() { ParameterName = "DEDW", ParameterValue = detaildata.Dedw, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "MDD", ParameterValue = detaildata.Mdd, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "ZM", ParameterValue = detaildata.Zm, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "GJF", ParameterValue = detaildata.Gjf, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Object }); parm.Add(new DBParameter() { ParameterName = "YT", ParameterValue = detaildata.Yt, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String }); //新增/更新执行 res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); foreach (var item in parm) { //获取输出参数值 if (item.ParameterName == "resultMsg") { resultMsg = item.ParameterValue.ToString(); break; } } } catch (Exception ex) { if (tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
public bool InsertRequesition(Entrusted _Entrusted) { bool NeedDraft = false; // if (_Entrusted.DraftID.Trim().Length < 11) if (_Entrusted.DraftID == null || _Entrusted.DraftID == "") { int count = draftCount(_Entrusted.VoucherID.Trim()); if (count < 0) { return(false); } else { // draftID = voucherID.Substring(1, 8) + count.ToString().PadLeft(3, '0'); _Entrusted.DraftID = _Entrusted.VoucherID.Substring(1, 8) + count.ToString().PadLeft(4, '0'); // = Draft; } NeedDraft = true; } // bool result = false; string createDraft = @"INSERT INTO Draft VALUES(@DraftID,@Owner,GETDATE())"; // string createDraftCommand = @"INSERT INTO ReqInDraft VALUES(@DraftID,@VoucherID,@Stamp)"; // string createRequCommand = @"INSERT INTO Requisition (VoucherID,SampleName,LOT_NO,Purpose,SampleFrom,RequireDate,SendDate,GetDate,State,Remark) VALUES(@VoucherID,@SampleName,@LOT_NO,@Purpose,@SampleFrom,@RequireDate,@SendDate,@GetDate,@State,@Remark)"; System.Data.Common.DbTransaction tran = gate.BeginTransaction(); // try { if (NeedDraft) { gate.ExecuteNonQuery(createDraft, new object[] { _Entrusted.DraftID, _Entrusted.UserID }); } gate.ExecuteNonQuery(createRequCommand, new object[] { _Entrusted.VoucherID, _Entrusted.SampleName, _Entrusted.Material, _Entrusted.purpose, _Entrusted.sampleFrom, _Entrusted.needDate, _Entrusted.sendDate, _Entrusted.pickTime, "D", "D" }); gate.ExecuteNonQuery(createDraftCommand, new object[] { _Entrusted.DraftID, _Entrusted.VoucherID, System.DateTime.Now }); // int i = 1; foreach (string propertyName in _Entrusted.Properties) { string sqlProfile = @"INSERT INTO Profile(VoucherID,Item,SampleName,PropertyName,Result,State,OverRange) VALUES(@VoucherID,@Item,@SampleName,@PropertyName,@Result,@State,@OverRange)"; gate.ExecuteNonQuery(sqlProfile, new object[] { _Entrusted.VoucherID, i, _Entrusted.SampleName, propertyName, DBNull.Value, "N", "0" }); i++; } tran.Commit(); result = true; } catch (Exception e) { tran.Rollback(); throw e; } return(result); }
/// <summary> /// ���� ���/���� /// </summary> /// <param name="pictures">ʵ��</param> /// <returns>ִ�н��</returns> public int PicturesInsertUpdate(out string resultMsg,ModelPictures pictures,DbTransaction tran =null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "USP_PICTURES_INSERT_UPDATE"; //������� IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = pictures.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "NAME", ParameterValue = pictures.Name, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "CATEGORYID", ParameterValue = pictures.Categoryid, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "OWNER", ParameterValue = pictures.Owner, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "PICURI", ParameterValue = pictures.Picuri, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "PICLINK", ParameterValue = pictures.Piclink, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = pictures.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); //����/����ִ�� res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); } catch (Exception ex) { if(tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
public static bool UpdateUserNameCoupon(Member member, string recemmendCode, out bool IsSendCoupon) { IsSendCoupon = false; bool flag = false; Database database = DatabaseFactory.CreateDatabase(); using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { // 判断是否有邀请码 if (!string.IsNullOrWhiteSpace(recemmendCode)) { string sourcechars = ConfigurationManager.AppSettings["sourcechars"]; string newsourcechars = ConfigurationManager.AppSettings["newsourcechars"]; if (!string.IsNullOrWhiteSpace(sourcechars) && !string.IsNullOrWhiteSpace(newsourcechars)) { string currcode = BaseConvertHelper.BaseConvert(recemmendCode, newsourcechars, sourcechars); string useredId = MemberHelper.GetUserIdByRecommendCode(currcode); if (!string.IsNullOrWhiteSpace(useredId) && !string.IsNullOrWhiteSpace(currcode)) { // 插入到邀请码记录表 if (!MemberHelper.AddRecommendCodeRecord(member.UserId, Convert.ToInt32(useredId), currcode, recemmendCode)) { dbTransaction.Rollback(); flag = false; return(flag); } } } } bool CouponFlag = true; // 判断该手机号码是否有openid if (!UserHelper.IsExistOpendByUserName(member.Username)) { CouponFlag = false; } //if (Users.UpdateUser(member)) if (!UserHelper.UpdateUserUserNameByCellPhone(member.UserId, member.Username, member.CellPhone, member.Password, member.OpenId, 1, UserHelper.CreateSalt())) { dbTransaction.Rollback(); flag = false; return(flag); } if (CouponFlag) { // 判断是否有优惠卷 int count = CouponHelper.GetCountCouponItemed(member.UserId, 5); if (count == 0) { // 获取有效时间内注册赠劵的数量 IList <CouponInfo> couponList = CouponHelper.GetCouponsBySendType(5); IList <CouponItemInfo> couponItemList = new List <CouponItemInfo>(); string claimCode = string.Empty; if (couponList != null && couponList.Count > 0) { foreach (CouponInfo coupon in couponList) { CouponItemInfo item = new CouponItemInfo(); claimCode = System.Guid.NewGuid().ToString().Replace("-", "").Substring(0, 15); if (coupon.Validity > 0 && member.CreateDate >= coupon.StartTime) { coupon.StartTime = member.CreateDate; coupon.ClosingTime = member.CreateDate.AddDays(coupon.Validity); } else if (coupon.Validity > 0 && member.CreateDate < coupon.StartTime) { coupon.StartTime = DateTime.Now; coupon.ClosingTime = DateTime.Now.AddDays(coupon.Validity); } item = new CouponItemInfo(coupon.CouponId, claimCode, new int?(member.UserId), member.Username, member.Email, System.DateTime.Now, coupon.StartTime.Date, coupon.ClosingTime.Date.AddDays(1).AddSeconds(-1), coupon.Amount, coupon.DiscountValue); couponItemList.Add(item); } } if (couponItemList.Count > 0) { // 发劵 if (!CouponHelper.SendClaimCodes(couponItemList)) { dbTransaction.Rollback(); flag = false; return(flag); } else { IsSendCoupon = true; } } } } dbTransaction.Commit(); flag = true; } catch { dbTransaction.Rollback(); flag = false; } finally { dbConnection.Close(); } } return(flag); }
/// <summary> /// ���� ���/���� /// </summary> /// <param name="category">ʵ��</param> /// <returns>ִ�н��</returns> public int CategoryInsertUpdate(out string resultMsg, ModelCategory category, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "USP_CATEGORY_INSERT_UPDATE"; //������� IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = category.Id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "NAME", ParameterValue = category.Name, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "NAMEEN", ParameterValue = category.Nameen, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "THUMBNAILS", ParameterValue = category.Thumbnails, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "INTRODUCTION", ParameterValue = category.Introduction, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String }); parm.Add(new DBParameter() { ParameterName = "PARENTCATEG", ParameterValue = category.Parentcateg, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "SORT", ParameterValue = category.Sort, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "ISNAV", ParameterValue = category.Isnav, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "ISINDEX", ParameterValue = category.Isindex, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = category.Status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); //parm.Add(new DBParameter() { ParameterName = "CREATEDATE", ParameterValue = category.Createdate, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.DateTime }); //parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String }); //����/����ִ�� res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); } catch (Exception ex) { if (tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
/// <summary> /// /// </summary> /// <param name="DbFilePath">数据库相对URL</param> /// <returns></returns> public static DataTable GetLogs(string DbFilePath , string bTime = "", string eTime = "", string Type = "", string UserID = "" , string UserName = "", string UserIP = "", string Message = "", string Data = "" ) { string DbPath = LogDbPathRoot + "\\" + DbFilePath; SQLiteConnection conn = new SQLiteConnection("Data Source=" + DbPath + ";Pooling=true;Max Pool Size=100;"); conn.Open(); string sql = @"SELECT ID,Time,Level,Type,UserID,UserName,UserIP,Message FROM Logs"; List <string> whereList = new List <string>(); List <SQLiteParameter> parList = new List <SQLiteParameter>(); #region where par if (!string.IsNullOrEmpty(bTime)) { whereList.Add(@" Time >= @bTime "); parList.Add(new SQLiteParameter("@bTime", bTime)); } if (!string.IsNullOrEmpty(eTime)) { whereList.Add(@" Time <= @eTime "); parList.Add(new SQLiteParameter("@eTime", eTime)); } if (!string.IsNullOrEmpty(Type)) { whereList.Add(@" Type like '%'||@Type||'%' "); parList.Add(new SQLiteParameter("@Type", Type)); } if (!string.IsNullOrEmpty(UserID)) { whereList.Add(@" UserID=@UserID "); parList.Add(new SQLiteParameter("@UserID", UserID)); } if (!string.IsNullOrEmpty(UserName)) { whereList.Add(@" UserName like '%'||@UserName||'%' "); parList.Add(new SQLiteParameter("@UserName", UserName)); } if (!string.IsNullOrEmpty(UserIP)) { whereList.Add(@" UserIP like '%'||@UserIP||'%' "); parList.Add(new SQLiteParameter("@UserIP", UserIP)); } if (!string.IsNullOrEmpty(Message)) { whereList.Add(@" Message like '%'||@Message||'%' "); parList.Add(new SQLiteParameter("@Message", Message)); } if (!string.IsNullOrEmpty(Data)) { whereList.Add(@" Data like '%'||@Data||'%' "); parList.Add(new SQLiteParameter("@Data", Data)); } #endregion if (whereList.Count > 0) { sql += @" where " + string.Join(" and ", whereList); } using (SQLiteCommand cmd = new SQLiteCommand(sql, conn)) { System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction(); try { if (parList.Count > 0) { cmd.Parameters.Clear(); cmd.Parameters.AddRange(parList.ToArray()); } SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); DataTable data = new DataTable(); adapter.Fill(data); transaction.Commit(); adapter.Dispose(); return(data); } catch (Exception exp) { transaction.Rollback(); throw exp; } finally { transaction.Dispose(); cmd.Connection.Close(); } } }
public static bool Insert(Order order, string executedBy) { bool rslt = false; try { Database db = DatabaseFactory.CreateDatabase(Constants.DBConnection); DbCommand cmd = db.GetStoredProcCommand(Constants.SP_Order_Insert); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); db.AddInParameter(cmd, "CustomerID", DbType.Int32, order.CustomerID); db.AddInParameter(cmd, "BranchID", DbType.Int32, order.BranchID); db.AddInParameter(cmd, "OrderDate", DbType.DateTime, order.OrderDate); db.AddInParameter(cmd, "OrderStatusID", DbType.Int32, order.OrderStatusID); db.AddInParameter(cmd, "TotalValue", DbType.Decimal, order.TotalValue); db.AddInParameter(cmd, "IsPaid", DbType.Boolean, order.IsPaid); db.AddInParameter(cmd, "CreatedBy", DbType.Boolean, order.CreatedBy); db.AddOutParameter(cmd, "NewID", DbType.Int32, 4); db.ExecuteNonQuery(cmd, transaction); int newID = 0; int.TryParse(db.GetParameterValue(cmd, "NewID").ToString(), out newID); if (newID > 0) { bool dtlInserted; foreach (OrderDetail orDtl in order.OrderItems) { dtlInserted = InsertDetail(orDtl, transaction, executedBy); if (dtlInserted == false) { throw new Exception("Failed to insert order detail/s"); } } transaction.Commit(); rslt = true; } } catch (Exception ex) { transaction.Rollback(); rslt = false; throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return rslt; }
public void RollbackTransaction(System.Data.Common.DbTransaction trans) { trans.Rollback(); }
private bool ExecuteTest() { this.errorMsgs = new System.Collections.Generic.List <string>(); System.Data.Common.DbTransaction transaction = null; System.Data.Common.DbConnection connection = null; string str; try { if (this.ValidateConnectionStrings(out str)) { System.Data.Common.DbConnection dbConnection; connection = (dbConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString())); try { connection.Open(); System.Data.Common.DbCommand command = connection.CreateCommand(); transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; command.CommandText = "CREATE TABLE installTest(Test bit NULL)"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE installTest"; command.ExecuteNonQuery(); transaction.Commit(); connection.Close(); goto IL_129; } finally { if (dbConnection != null) { ((System.IDisposable)dbConnection).Dispose(); } } } this.errorMsgs.Add(str); } catch (System.Exception exception) { this.errorMsgs.Add(exception.Message); if (transaction != null) { try { transaction.Rollback(); } catch (System.Exception exception2) { this.errorMsgs.Add(exception2.Message); } } if (connection != null && connection.State != System.Data.ConnectionState.Closed) { connection.Close(); connection.Dispose(); } } IL_129: if (!Install.TestFolder(base.Request.MapPath(Globals.ApplicationPath + "/config/test.txt"), out str)) { this.errorMsgs.Add(str); } try { Configuration configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(base.Request.ApplicationPath); if (configuration.ConnectionStrings.ConnectionStrings["KingWooSqlServer"].ConnectionString == "none") { configuration.ConnectionStrings.ConnectionStrings["KingWooSqlServer"].ConnectionString = "required"; } else { configuration.ConnectionStrings.ConnectionStrings["KingWooSqlServer"].ConnectionString = "none"; } configuration.Save(); } catch (System.Exception exception3) { this.errorMsgs.Add(exception3.Message); } if (!Install.TestFolder(base.Request.MapPath(Globals.ApplicationPath + "/storage/test.txt"), out str)) { this.errorMsgs.Add(str); } return(this.errorMsgs.Count == 0); }
public void RollbackTransaction(DbTransaction transaction) { transaction.Rollback(); }
/// <summary> /// Handles the Click event of the _btnCreateRoom control. /// </summary> /// <param name = "sender">The source of the event.</param> /// <param name = "e">The <see cref = "System.EventArgs" /> instance containing the event data.</param> protected void _btnCreateRoom_Click(object sender, EventArgs e) { if (!Page.IsValid) return; var db = new UrbanDataContext(); //Verify All sections from user are valid var validationCheck = VerifyRequiredFieldsSelected(); switch (validationCheck) { case -1: { WriteFeedBackMaster(FeedbackType.Warning, "Please fill in required fields"); return; } case -2: { WriteFeedBackMaster(FeedbackType.Warning, "Building already exists with this address information"); return; } case -3: { WriteFeedBackMaster(FeedbackType.Warning, "Room already exists for this building and room number"); return; } case 1: { break; } } try { db.Connection.Open(); _transac = db.Connection.BeginTransaction(); db.Transaction = _transac; //Building Creation var buildingCreateParams = GetBuildingCreateParams(); var buidlingResult = BuildingUtilities.ProcessBuildingCreation(ref db, Cu.Id, buildingCreateParams); if (buidlingResult != -1) { //Room Creation var roomCreateParams = GetRoomCreateParams(); var roomResult = RoomUtilities.ProcessRoomCreation(ref db, buidlingResult, Cu.Id, AvailabilityList, roomCreateParams); if (roomResult == -1) { WriteFeedBackMaster(FeedbackType.Warning, "Room already exists with this number for this building please select another."); _transac.Rollback(); } else { //_transac.Rollback(); _transac.Commit(); //Move every temp image to full image foreach (var file in ImageList) { var filesTemp = Server.MapPath("~/FilesTemp"); var imageToCopy = Path.Combine(filesTemp, file.ImageUrl); var files = Server.MapPath("~/Files"); var imagePathToCopy = Path.Combine(files, file.ImageUrl); File.Copy(imageToCopy, imagePathToCopy); var f = new Files { Extension = file.Extension, FilesName = file.FileName, FileSubType = "Image", ServerFileName = file.ImageUrl }; db.Files.InsertOnSubmit(f); db.SubmitChanges(); var link = new RoomImageLink { ImageDescription = file.Description, Title = file.Title, RoomID = roomResult, FileID = f.Id }; db.RoomImageLink.InsertOnSubmit(link); db.SubmitChanges(); } RadAjaxManager.GetCurrent(Page).Redirect(String.Format("~/App/Pages/MyAccount.aspx?message={0}", ("Room " + _txtRoomNumber.Text + " created"))); } } else { WriteFeedBackMaster(FeedbackType.Warning, "This address information already eixsts for a different user. Please modify and resubmit"); _transac.Rollback(); } } catch (Exception) { if (_transac != null) _transac.Rollback(); throw; } finally { if (db.Connection.State == ConnectionState.Open) db.Connection.Close(); } }
/// <summary> /// Creates a new user account in both MembershipReboot database and in the MapHive meta database; /// sends out a confirmation email if email account and template are provided /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TAccount"></typeparam> /// <param name="userAccountService"></param> /// <param name="dbCtx"></param> /// <param name="emailAccount"></param> /// <param name="emailTemplate"></param> /// <returns></returns> protected internal virtual async Task <T> CreateAsync <T, TAccount>(DbContext dbCtx, UserAccountService <TAccount> userAccountService, IEmailAccount emailAccount = null, IEmailTemplate emailTemplate = null) where T : MapHiveUserBase where TAccount : RelationalUserAccount { T output; //need to validate the model first await ValidateAsync(dbCtx); //make sure the email is ALWAYS lower case Email = Email.ToLower(); //check if the email is already used or not; throw validation feedback exception if so //Note - could do it in the mh meta, but both dbs must be in sync anyway var emailInUse = userAccountService.GetByEmail(Email) != null; if (emailInUse) { throw Validation.Utils.GenerateValidationFailedException(nameof(Email), ValidationErrors.EmailInUse); } //user account exists in two places - mbr and mh databases. Therefore need to handle them both properly wrapped into transactions DbContext mbrDbCtx = GetMembershipRebootDbCtx(userAccountService); System.Data.Common.DbTransaction mbrTrans = null; System.Data.Common.DbTransaction mhTrans = null; //since this method wraps the op on 2 dbs into transactions, it must handle connections manually and take care of closing it aftwerwards //it is therefore required to clone contexts with independent conns so the base contexts can be reused var clonedMhDbCtx = dbCtx.Clone(contextOwnsConnection: false); var clonedMbrDbCtx = mbrDbCtx.Clone(false); try { //open the connections as otherwise will not be able to begin transaction await clonedMbrDbCtx.Database.Connection.OpenAsync(); await clonedMhDbCtx.Database.Connection.OpenAsync(); //begin the transaction and set the transaction object back on the db context so it uses it mbrTrans = clonedMbrDbCtx.Database.Connection.BeginTransaction(); clonedMbrDbCtx.Database.UseTransaction(mbrTrans); mhTrans = clonedMhDbCtx.Database.Connection.BeginTransaction(); clonedMhDbCtx.Database.UseTransaction(mhTrans); //first create a membership reboot account //wire up evt too, to intercept what mbr is trying to say... AccountCreatedEvent <TAccount> e = null; userAccountService.Configuration.AddEventHandler(new MembershipRebootEventHandlers.AccountCreatedEventHandler <TAccount>( (evt) => { e = evt; })); var newMbrAccount = userAccountService.CreateAccount(this.Email, Cartomatic.Utils.Crypto.Generator.GenerateRandomString(10), this.Email); //so can next pass some data to the mh meta user object this.Uuid = newMbrAccount.ID; //mbr work done, so can create the user within the mh metadata db output = await base.CreateAsync <T>(clonedMhDbCtx); //looks like we're good to go, so can commit mbrTrans.Commit(); mhTrans.Commit(); var opFeedback = new Dictionary <string, object> { { nameof(e.InitialPassword), e.InitialPassword }, { nameof(e.VerificationKey), e.VerificationKey } }; //if email related objects have been provided, send the account created email if (emailAccount != null && emailTemplate != null) { EmailSender.Send( emailAccount, emailTemplate.Prepare(opFeedback), Email ); } //finally the user created event UserCreated?.Invoke( this, new Events.OpFeedbackEventArgs { OperationFeedback = opFeedback } ); } catch (Exception ex) { mbrTrans?.Rollback(); mhTrans?.Rollback(); throw Validation.Utils.GenerateValidationFailedException(ex); } finally { //try to close the connections as they were opened manually and therefore may not have been closed! clonedMhDbCtx.Database.Connection.CloseConnection(dispose: true); clonedMbrDbCtx.Database.Connection.CloseConnection(dispose: true); mbrTrans?.Dispose(); mhTrans?.Dispose(); } return(output); }
/// <summary> /// ����״̬ ���� /// </summary> /// <param name="id">Id ���</param> /// <param name="status">״̬</param> /// <returns>ִ�н��</returns> public int CategoryUpdateStatus(out string resultMsg, Int32 id, Int32 status, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "USP_Category_UPDATE_STATUS"; //������� IList<DBParameter> parm = new List<DBParameter>(); parm.Add(new DBParameter() { ParameterName = "ID", ParameterValue = id, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "STATUS", ParameterValue = status, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.Int32 }); parm.Add(new DBParameter() { ParameterName = "resultMsg", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String }); //����ִ�� res = DBHelper.ExecuteNonQuery(sql, true, parm, tran); foreach (var item in parm) { //��ȡ�������ֵ if (item.ParameterName == "resultMsg") { resultMsg = item.ParameterValue.ToString(); break; } } } catch (Exception ex) { if (tran != null) tran.Rollback(); resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, ex.ToString()); } return res; }
public static bool AccepteDraw(long journalNumber, string managerRemark) { Database database = DatabaseFactory.CreateDatabase(); bool result; using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { ReferralDao referralDao = new ReferralDao(); if (!referralDao.AccepteDraw(journalNumber, managerRemark)) { dbTransaction.Rollback(); result = false; } else { SplittinDrawInfo splittinDraw = referralDao.GetSplittinDraw(journalNumber); if (splittinDraw == null) { dbTransaction.Rollback(); result = false; } else { if (!referralDao.AddSplittinDetail(new SplittinDetailInfo { OrderId = string.Empty, UserId = splittinDraw.UserId, UserName = splittinDraw.UserName, IsUse = true, TradeDate = DateTime.Now, TradeType = SplittingTypes.DrawRequest, Expenses = new decimal?(splittinDraw.Amount), Balance = referralDao.GetUserUseSplittin(splittinDraw.UserId) - splittinDraw.Amount, Remark = managerRemark })) { dbTransaction.Rollback(); result = false; } else { dbTransaction.Commit(); result = true; } } } } catch (Exception var_6_11F) { dbTransaction.Rollback(); result = false; } finally { dbConnection.Close(); } } return(result); }