/// <summary> /// 新增基本檔資料(使用交易) /// </summary> /// <param name="htParams">放入輸入的參數</param> /// <param name="DBT">交易變數</param> /// <returns>回傳影響筆數</returns> public void doCreate(Hashtable htParams, DbTransaction DBT ) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_ALO_PATTERNSPEC_DETL.ADD_PATTERNSPEC_DETL"); db.AddInParameter(cmd, "ID", DbType.VarNumeric, htParams["ID"]); db.AddInParameter(cmd, "PID", DbType.VarNumeric, htParams["PID"]); db.AddInParameter(cmd, "vCreateDate", DbType.Date, htParams["CREATEDATE"]); db.AddInParameter(cmd, "vCreateUID", DbType.String, htParams["CREATEUID"]); db.AddInParameter(cmd, "vUpdateDate", DbType.Date, htParams["UPDATEDATE"]); db.AddInParameter(cmd, "vUpdateUID", DbType.String, htParams["UPDATEUID"]); db.AddInParameter(cmd, "nSTART_RANKQTY", DbType.VarNumeric, htParams["START_RANKQTY"]); db.AddInParameter(cmd, "nEND_RANKQTY", DbType.VarNumeric, htParams["END_RANKQTY"]); db.AddInParameter(cmd, "nADJ_QTY", DbType.VarNumeric, htParams["ADJ_QTY"]); int ProcessRecord = (DBT == null) ? db.ExecuteNonQuery(cmd) : db.ExecuteNonQuery(cmd, DBT); if (ProcessRecord == 0) { throw new Exception("輸入資料失敗!"); } } catch (Exception ex) { throw new Exception("呼叫VDS_ALO_PATTERNSPEC_DETL_DBO.doCreate()發生錯誤,錯誤訊息:" + OracleExceptionProcess.OracleExceptionStringCut(ex.Message)); } }
public bool Insert(Option Option, Database db, DbTransaction transaction) { DbCommand command = db.GetStoredProcCommand("usp_OptionInsert"); db.AddInParameter(command, "Name", DbType.String, Option.Name); db.AddInParameter(command, "Description", DbType.String, Option.Description); db.AddInParameter(command, "OptionCategoryId", DbType.Int16, Option.OptionCategoryId); db.AddInParameter(command, "ParentOptionId", DbType.Int16, Option.ParentOptionId); db.AddInParameter(command, "IsDeleted", DbType.Boolean, Option.IsDeleted); db.AddInParameter(command, "IsMultiSelect", DbType.Boolean, Option.IsMultiSelect); db.AddInParameter(command, "Points", DbType.Int16, Option.Points); db.AddOutParameter(command, "OptionId", DbType.Int16, 3); if (transaction == null) { db.ExecuteNonQuery(command); } else { db.ExecuteNonQuery(command, transaction); } Option.OptionId = Convert.ToInt16(db.GetParameterValue(command, "OptionId").ToString()); return true; }
public bool DeleteRoleRightsByRoleId(Role roles, Database db, DbTransaction transaction) { DbCommand dbCommand = db.GetStoredProcCommand("usp_RoleRightDelete"); db.AddInParameter(dbCommand, "RoleId", DbType.Guid, roles.RoleId); db.ExecuteNonQuery(dbCommand, transaction); return true; }
public int UpdateBoxDataMain(ArrayList ParameterList, DbTransaction DBT) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_VGR06.UpdateBoxDataMain"); //0.V_BOX_NO //1.V_RETURN_NO //2.V_STATUS //3.D_LAST_DATE //4.V_LAST_ID //5.D_OLD_LAST_DATE //6.V_OLD_LAST_ID db.AddInParameter(cmd, "V_BOX_NO", DbType.String, ParameterList[0]); db.AddInParameter(cmd, "V_RETURN_NO", DbType.String, ParameterList[1]); db.AddInParameter(cmd, "V_STATUS", DbType.String, ParameterList[2]); db.AddInParameter(cmd, "D_LAST_DATE", DbType.Date, ParameterList[3]); db.AddInParameter(cmd, "V_LAST_ID", DbType.String, ParameterList[4]); db.AddInParameter(cmd, "D_OLD_LAST_DATE", DbType.Date, ParameterList[5]); db.AddInParameter(cmd, "V_OLD_LAST_ID", DbType.String, ParameterList[6]); int rtn = (DBT == null) ? db.ExecuteNonQuery(cmd) : db.ExecuteNonQuery(cmd, DBT); return rtn; } catch (Exception ex) { throw new Exception("呼叫PKG_VDS_VGR06.UpdateBoxDataMain() 發生錯誤,錯誤訊息:" + ex.Message); } }
/// <summary> /// ���� ɾ�� /// </summary> /// <param name="id">Id ���</param> /// <returns>ִ�н��</returns> public int CategoryDelete(out string resultMsg, Int32 id, DbTransaction tran = null) { resultMsg = string.Empty; int res = 0; try { //�洢�������� string sql = "usp_category_delete_by_id"; //������� 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 = "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 int CheckStoreOutNo(ArrayList ParameterList, DbTransaction DBT) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_CGD19.CheckStoreOutNO"); db.AddInParameter(cmd, "V_REPORT_TYPE", DbType.String, ParameterList[0]);//單據類別 db.AddInParameter(cmd, "V_OUT_NO", DbType.String, ParameterList[1]);//單號 db.AddOutParameter(cmd, "V_BILL_EXIST", DbType.Int16, Int16.MaxValue); db.AddOutParameter(cmd, "V_ERR_MSG", DbType.String, 100); int ProcessRecord = (DBT == null) ? db.ExecuteNonQuery(cmd) : db.ExecuteNonQuery(cmd, DBT); if (ProcessRecord == 0) { throw new Exception("輸入資料失敗!"); } Int32 PID = 0; Int32.TryParse(db.GetParameterValue(cmd, "V_BILL_EXIST").ToString(), out PID); return PID; } catch (Exception ex) { throw new Exception("呼叫VDS_CGD19_DBO.CheckStoreOutNO() 發生錯誤,錯誤訊息:" + ex.Message); } }
public int AddBookingtrade(Bookingtrade bookingtrade, DbTransaction trans) { string cmdText = "INSERT INTO BookingTrade(bookingshopid,bookingid,bookingname,shopid,shopname,handlerid,handlername,proxyid,proxyname,uid,realname,tradeid,tradename,tradevalue,tradecount,issettle,bookinttime,remarks)VALUES(@bookingshopid,@bookingid,@bookingname,@shopid,@shopname,@handlerid,@handlername,@proxyid,@proxyname,@uid,@realname,@tradeid,@tradename,@tradevalue,@tradecount,@issettle,@bookinttime,@remarks);SELECT @@identity"; DbParameter[] cmdParameters = { DbHelper.MakeInParam("@bookingshopid", (DbType)SqlDbType.Int, 0, bookingtrade.Bookingshopid)/**/, DbHelper.MakeInParam("@bookingid", (DbType)SqlDbType.Int, 0, bookingtrade.Bookingid)/**/, DbHelper.MakeInParam("@bookingname", (DbType)SqlDbType.NChar, 50, bookingtrade.Bookingname)/**/, DbHelper.MakeInParam("@shopid", (DbType)SqlDbType.Int, 0, bookingtrade.Shopid)/**/, DbHelper.MakeInParam("@shopname", (DbType)SqlDbType.NChar, 20, bookingtrade.Shopname)/**/, DbHelper.MakeInParam("@handlerid", (DbType)SqlDbType.Int, 0, bookingtrade.Handlerid)/**/, DbHelper.MakeInParam("@handlername", (DbType)SqlDbType.NChar, 10, bookingtrade.Handlername)/**/, DbHelper.MakeInParam("@proxyid", (DbType)SqlDbType.Int, 0, bookingtrade.Proxyid)/**/, DbHelper.MakeInParam("@proxyname", (DbType)SqlDbType.NChar, 10, bookingtrade.Proxyname)/**/, DbHelper.MakeInParam("@uid", (DbType)SqlDbType.Int, 0, bookingtrade.Uid)/**/, DbHelper.MakeInParam("@realname", (DbType)SqlDbType.NChar, 10, bookingtrade.Realname)/**/, DbHelper.MakeInParam("@tradeid", (DbType)SqlDbType.Int, 0, bookingtrade.Tradeid)/**/, DbHelper.MakeInParam("@tradename", (DbType)SqlDbType.NChar, 20, bookingtrade.Tradename)/**/, DbHelper.MakeInParam("@tradevalue", (DbType)SqlDbType.Decimal, 0, bookingtrade.Tradevalue)/**/, DbHelper.MakeInParam("@tradecount", (DbType)SqlDbType.Int, 0, bookingtrade.Tradecount)/**/, DbHelper.MakeInParam("@issettle", (DbType)SqlDbType.Bit, 0, bookingtrade.Issettle)/**/, DbHelper.MakeInParam("@bookinttime", (DbType)SqlDbType.DateTime, 0, bookingtrade.Bookinttime)/**/, DbHelper.MakeInParam("@remarks", (DbType)SqlDbType.NChar, 100, bookingtrade.Remarks)/**/ }; string s = DbHelper.ExecuteScalar(trans, CommandType.Text, cmdText, cmdParameters).ToString(); int i = (string.IsNullOrEmpty(s) ? 0 : int.Parse(s)); return i; }
/// <summary> /// Resolve at run time the appropriate set of DbParameters for a stored procedure /// </summary> /// <param name="transaction">A valid SqlTransaction object</param> /// <param name="connection">A valid SqlConnection object</param> /// <param name="storedProcedureName">The name of the stored procedure</param> /// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param> /// <returns>The parameter array discovered.</returns> private static IList<SqlParameter> DiscoverSpParameterSet(DbTransaction transaction, DbConnection connection, string storedProcedureName, bool includeReturnValueParameter) { using (SqlCommand cmd = new SqlCommand(storedProcedureName, connection as SqlConnection)) { if (connection.State != ConnectionState.Open) { connection.Open(); } cmd.CommandType = CommandType.StoredProcedure; if (transaction != null) { cmd.Transaction = transaction as SqlTransaction; } SqlCommandBuilder.DeriveParameters(cmd); if (!includeReturnValueParameter) { cmd.Parameters.RemoveAt(0); } SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; cmd.Parameters.CopyTo(discoveredParameters, 0); return discoveredParameters; } }
/// <summary> /// 新增VDS_ITM_TEMPLATE_MAIN(使用交易) /// </summary> /// <param name="ParameterList">放入輸入的參數</param> /// <param name="DBT">交易變數</param> /// <returns>N_ID</returns> public void InsertTemplateMain(ArrayList ParameterList, DbTransaction DBT, out int N_ID ) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_ITM11.InsertTemplateMain"); db.AddOutParameter(cmd, "N_ID", DbType.VarNumeric, 32767); db.AddInParameter(cmd, "V_CODE", DbType.String, ParameterList[0]); db.AddInParameter(cmd, "V_CHAN_NO", DbType.String, ParameterList[1]); db.AddInParameter(cmd, "D_T_DATE", DbType.Date, ParameterList[2]); db.AddInParameter(cmd, "V_REMARK_CONTENT", DbType.String, ParameterList[3]); db.AddInParameter(cmd, "D_CREATEDATE", DbType.DateTime, ParameterList[4]); db.AddInParameter(cmd, "V_CREATEUID", DbType.String, ParameterList[5]); db.AddInParameter(cmd, "D_UPDATEDATE", DbType.DateTime, ParameterList[6]); db.AddInParameter(cmd, "V_UPDATEUID", DbType.String, ParameterList[7]); db.ExecuteNonQuery(cmd, DBT); N_ID = Convert.ToInt32(db.GetParameterValue(cmd, "N_ID").ToString()); } catch (Exception ex) { throw new Exception("呼叫PKG_VDS_ITM11.InsertTemplateMain發生錯誤,錯誤訊息:" + ex.Message); } }
/// <summary> /// 添加审稿单 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool AddReviewBillContent(ReviewBillContentEntity model, DbTransaction trans = null) { StringBuilder sqlCommandText = new StringBuilder(); sqlCommandText.Append(" @CID"); sqlCommandText.Append(", @JournalID"); sqlCommandText.Append(", @ItemID"); sqlCommandText.Append(", @ContentValue"); sqlCommandText.Append(", @IsChecked"); sqlCommandText.Append(", @AddUser"); DbCommand cmd = db.GetSqlStringCommand(String.Format("INSERT INTO dbo.ReviewBillContent ({0},AddDate) VALUES ({1},getdate())", sqlCommandText.ToString().Replace("@", ""), sqlCommandText.ToString())); db.AddInParameter(cmd, "@ItemContentID", DbType.Int64, model.ItemContentID); db.AddInParameter(cmd, "@CID", DbType.Int64, model.CID); db.AddInParameter(cmd, "@JournalID", DbType.Int64, model.JournalID); db.AddInParameter(cmd, "@ItemID", DbType.AnsiString, model.ItemID); db.AddInParameter(cmd, "@ContentValue", DbType.AnsiString, model.ContentValue); db.AddInParameter(cmd, "@IsChecked", DbType.Boolean, model.IsChecked); db.AddInParameter(cmd, "@AddUser", DbType.Int64, model.AddUser); try { bool result = false; if (trans == null) result = db.ExecuteNonQuery(cmd) > 0; else result = db.ExecuteNonQuery(cmd, trans) > 0; if (!result) throw new Exception("新增审稿单失败!"); return result; } catch (Exception ex) { throw ex; } }
/// <summary> /// Instantiate an opened connection enlisted to the Transaction /// if promotable is false, the Transaction wraps a local /// transaction inside and can never be promoted /// </summary> /// <param name="dbResourceAllocator"></param> /// <param name="transaction"></param> /// <param name="wantPromotable"></param> internal SharedConnectionInfo( DbResourceAllocator dbResourceAllocator, Transaction transaction, bool wantPromotable, ManualResetEvent handle) { Debug.Assert((transaction != null), "Null Transaction!"); if (null == handle) throw new ArgumentNullException("handle"); this.handle = handle; if (wantPromotable) { // Enlist a newly opened connection to this regular Transaction this.connection = dbResourceAllocator.OpenNewConnection(); this.connection.EnlistTransaction(transaction); } else { // Make this transaction no longer promotable by attaching our // IPromotableSinglePhaseNotification implementation (LocalTranscaction) // and track the DbConnection and DbTransaction associated with the LocalTranscaction LocalTransaction localTransaction = new LocalTransaction(dbResourceAllocator, handle); transaction.EnlistPromotableSinglePhase(localTransaction); this.connection = localTransaction.Connection; this.localTransaction = localTransaction.Transaction; } }
public void doCloseOrder(ArrayList ParameterList, DbTransaction DBT) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_OUT05.Update_Close_Order"); for (int i = 0; i < ParameterList.Count; i++) { string[] pl = (string[])ParameterList[i]; db.AddInParameter(cmd, "pid", DbType.VarNumeric, pl[0]); // ID db.AddInParameter(cmd, "p_status_reason", DbType.String, pl[1]); // STATUS_REASON db.AddInParameter(cmd, "p_updatedate", DbType.DateTime, System.DateTime.Now); db.AddInParameter(cmd, "p_updateuid", DbType.String, pl[2]); int ProcessRecord = (DBT == null) ? db.ExecuteNonQuery(cmd) : db.ExecuteNonQuery(cmd, DBT); if (ProcessRecord == 0) { throw new Exception("��s��ƥ���!"); } } } catch (Exception ex) { throw new Exception("�I�sPKG_VDS_OUT05.Update_Close_Order()�o�Ϳ��~,���~�T��:" + OracleExceptionProcess.OracleExceptionStringCut(ex.Message) ); } }
/// <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; }
/// <summary> /// �s�W���ɸ��(�ϥΥ��) /// </summary> /// <param name="ParameterList">��J��J���Ѽ�</param> /// <param name="DBT">����ܼ�</param> /// <returns>�^�Ǽv�T����</returns> public int doCreate(ArrayList ParameterList, DbTransaction DBT ) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_OUT05.InsertOne"); for (int i = 0; i < ParameterList.Count; i++) { SQLHelper.ParameterItem pi = ((SQLHelper.ParameterItem)ParameterList[i]); db.AddInParameter(cmd, string.Format("{0}", pi.ParaName), pi.FieldType, pi.FieldValue); } db.AddOutParameter(cmd, "PID", DbType.VarNumeric, Int32.MaxValue); int ProcessRecord = (DBT == null) ? db.ExecuteNonQuery(cmd) : db.ExecuteNonQuery(cmd, DBT); if (ProcessRecord == 0) { throw new Exception("��J��ƥ���!"); } return Convert.ToInt32(db.GetParameterValue(cmd, "PID")); } catch (Exception ex) { throw new Exception("�I�sPKG_VDS_OUT05.InsertOne�o�Ϳ��~,���~�T��:" + OracleExceptionProcess.OracleExceptionStringCut(ex.Message) ); } }
internal void InsertData(ArrayList ParameterList, DbTransaction DBT, out int ID) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_VAM15.InsertData"); db.AddInParameter(cmd, "V_PROMOTE_ID", DbType.String, ParameterList[0]); db.AddInParameter(cmd, "V_VENDOR", DbType.String, ParameterList[1]); db.AddInParameter(cmd, "V_TAX_TYPE", DbType.String, ParameterList[2]); db.AddInParameter(cmd, "V_TAX_REMAIN_AMT", DbType.String, ParameterList[3]); db.AddInParameter(cmd, "V_UTAX_REMAIN_AMT", DbType.String, ParameterList[4]); db.AddInParameter(cmd, "V_TAX_AMT", DbType.String, ParameterList[5]); db.AddInParameter(cmd, "V_UTAX_AMT", DbType.String, ParameterList[6]); db.AddInParameter(cmd, "V_TAX", DbType.String, ParameterList[7]); db.AddInParameter(cmd, "V_UPDATEUID", DbType.String, ParameterList[8]); db.AddInParameter(cmd, "V_UPDATEDATE", DbType.String, ParameterList[9]); db.AddInParameter(cmd, "V_CREATEUID", DbType.String, ParameterList[10]); db.AddInParameter(cmd, "V_CREATEDATE", DbType.String, ParameterList[11]); db.AddOutParameter(cmd, "N_ID", DbType.Int32, 10); db.ExecuteNonQuery(cmd, DBT); ID = int.Parse(db.GetParameterValue(cmd, "N_ID").ToString()); } catch (Exception ex) { throw new Exception("呼叫PKG_VDS_VAM15.InsertData()發生錯誤,錯誤訊息:" + OracleExceptionProcess.OracleExceptionStringCut(ex.Message)); } }
/// <summary> /// /// </summary> /// <param name="criteria"></param> /// <param name="connection"></param> /// <param name="transaction"></param> /// <returns></returns> public MembershipEntity Find(MembershipsCriteria criteria, DbConnection connection, DbTransaction transaction) { var password = criteria.Password; if (password == null) { this.DoNothing(); } else { if (criteria.Password is SecureString) { password = ((SecureString)criteria.Password).GetString(); } var hash = KandaHashAlgorithm.ComputeHash( typeof(SHA512Managed).FullName, (string)password, Encoding.Unicode); password = hash; } criteria.Password = password; var reader = default(KandaDbDataReader); try { reader = MembershipsGateway.Select(criteria, connection, transaction); var found = (reader.Read() ? KandaDbDataMapper.MapToObject<MembershipEntity>(reader) : MembershipEntity.Empty); return found; } finally { if (reader != null) { reader.Close(); } } }
//刪除離島配送費補貼 public string[] DeleteShipSubsidy(ArrayList ParameterList, DbTransaction DBT) { string[] strReturn = new string[2]; try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_VAM32.DeleteShipSubsidy"); db.AddInParameter(cmd, "V_VENDOR", DbType.String, ParameterList[0]); db.AddInParameter(cmd, "V_ITEM", DbType.String, ParameterList[1]); db.AddInParameter(cmd, "V_UID", DbType.String, ParameterList[2]); db.AddOutParameter(cmd, "V_RESULT", DbType.String, 38); db.AddOutParameter(cmd, "V_MSG", DbType.String, 100); db.ExecuteNonQuery(cmd, DBT); strReturn[0] = cmd.Parameters[cmd.Parameters.Count - 2].Value.ToString(); strReturn[1] = cmd.Parameters[cmd.Parameters.Count - 1].Value.ToString(); return strReturn; } catch (Exception ex) { throw new Exception("呼叫PKG_VDS_VAM32.DeleteShipSubsidy()發生錯誤,錯誤訊息:" + OracleExceptionProcess.OracleExceptionStringCut(ex.Message)); } }
public void BeginTransaction() { if (this.transaccion == null) { this.transaccion = this.conexion.BeginTransaction(IsolationLevel.ReadCommitted); } }
/// <summary>Initializes a new instance of the NServiceKit.MiniProfiler.Data.ProfiledDbTransaction class.</summary> /// /// <exception cref="ArgumentNullException">Thrown when one or more required arguments are null.</exception> /// /// <param name="transaction">The transaction.</param> /// <param name="connection"> The connection.</param> public ProfiledDbTransaction(DbTransaction transaction, ProfiledDbConnection connection) { if (transaction == null) throw new ArgumentNullException("transaction"); if (connection == null) throw new ArgumentNullException("connection"); this._trans = transaction; this._conn = connection; }
// update to version 3 private static void UpdateToVersion_1(DbConnection conn, DbTransaction tran) { ExecuteNonQuery(conn, tran, "create table tmp_song_data (songid int, textdata text, dataid int);\ninsert into tmp_song_data (songid, textdata, dataid) select ID, songtext, 1 from song;\ninsert into tmp_song_data (songid, textdata, dataid) select ID, songtext, 3 from song where link_1 is not null;\ninsert into tmp_song_data (songid, textdata, dataid) select ID, songtext, 3 from song where link_2 is not null;\n"); ExecuteNonQuery(conn, tran, "ALTER TABLE \"song\" RENAME TO \"temp_table_7_129075918630300000\""); ExecuteNonQuery(conn, tran, "CREATE TABLE \"song\" ( \n \"ID\" integer primary key NOT NULL, \n \"title\" text NULL, \n \"groupname\" text NULL, \n \"author\" text NULL, \n \"lang\" text NULL, \n \"server_id\" integer NULL, \n \"netID\" integer NULL, \n \"transp\" integer NULL, \n \"published\" datetime NULL, \n \"localmodified\" logical NULL, \n \"remark\" text NULL, \n CONSTRAINT \"FK_song_server_id\" FOREIGN KEY (\"server_id\") REFERENCES \"server\"(\"ID\")\n)"); ExecuteNonQuery(conn, tran, "CREATE INDEX \"IX_song_groupname_ID\" ON \"song\" (\"groupname\",\"ID\")"); ExecuteNonQuery(conn, tran, "INSERT INTO \"song\" (\"ID\", \"title\", \"groupname\", \"author\", \"lang\", \"server_id\", \"netID\", \"transp\", \"published\", \"localmodified\", \"remark\") select \"ID\" AS \"ID\", \"title\" AS \"title\", \"groupname\" AS \"groupname\", \"author\" AS \"author\", \"lang\" AS \"lang\", \"server_id\" AS \"server_id\", \"netID\" AS \"netID\", \"transp\" AS \"transp\", \"published\" AS \"published\", \"localmodified\" AS \"localmodified\", \"remark\" AS \"remark\" FROM \"temp_table_7_129075918630300000\""); ExecuteNonQuery(conn, tran, "DROP TABLE \"temp_table_7_129075918630300000\""); ExecuteNonQuery(conn, tran, "ALTER TABLE \"server\" RENAME TO \"temp_table_8_129075918630380000\""); ExecuteNonQuery(conn, tran, "CREATE TABLE \"server\" ( \n \"ID\" integer primary key NOT NULL, \n \"url\" text NULL, \n \"servertype\" text NULL, \n \"config\" text NULL, \n \"isreadonly\" logical NULL\n)"); ExecuteNonQuery(conn, tran, "INSERT INTO \"server\" (\"ID\", \"url\", \"servertype\", \"config\", \"isreadonly\") select \"ID\" AS \"ID\", \"url\" AS \"url\", \"servertype\" AS \"servertype\", \"config\" AS \"config\", \"isreadonly\" AS \"isreadonly\" FROM \"temp_table_8_129075918630380000\""); ExecuteNonQuery(conn, tran, "DROP TABLE \"temp_table_8_129075918630380000\""); ExecuteNonQuery(conn, tran, "ALTER TABLE \"deletedsong\" RENAME TO \"temp_table_9_129075918630410000\""); ExecuteNonQuery(conn, tran, "CREATE TABLE \"deletedsong\" ( \n \"ID\" integer primary key NOT NULL, \n \"song_netID\" integer NULL, \n \"server_id\" integer NULL, \n CONSTRAINT \"FK_deletedsong_server_id\" FOREIGN KEY (\"server_id\") REFERENCES \"server\"(\"ID\")\n)"); ExecuteNonQuery(conn, tran, "INSERT INTO \"deletedsong\" (\"ID\", \"song_netID\", \"server_id\") select \"ID\" AS \"ID\", \"song_netID\" AS \"song_netID\", \"server_id\" AS \"server_id\" FROM \"temp_table_9_129075918630410000\""); ExecuteNonQuery(conn, tran, "DROP TABLE \"temp_table_9_129075918630410000\""); ExecuteNonQuery(conn, tran, "UPDATE \"info\" SET \"value\"=\'3\' WHERE \"name\"=\'dbversion\'"); ExecuteNonQuery(conn, tran, "CREATE TABLE \"songdata\" ( \n \"ID\" integer primary key NOT NULL, \n \"song_id\" integer NOT NULL, \n \"datatype_id\" integer NOT NULL, \n \"label\" text NULL, \n \"textdata\" text NULL, \n CONSTRAINT \"FK_songdata_song_id\" FOREIGN KEY (\"song_id\") REFERENCES \"song\"(\"ID\"), \n CONSTRAINT \"FK_songdata_datatype_id\" FOREIGN KEY (\"datatype_id\") REFERENCES \"datatype_list\"(\"ID\")\n)"); ExecuteNonQuery(conn, tran, "CREATE INDEX \"IX_songdata_song_id\" ON \"songdata\" (\"song_id\")"); ExecuteNonQuery(conn, tran, "CREATE TABLE \"datatype_list\" ( \n \"ID\" integer NOT NULL, \n \"name\" text NOT NULL, \n CONSTRAINT \"PK_datatype_list\" PRIMARY KEY (\"ID\")\n)"); ExecuteNonQuery(conn, tran, "INSERT INTO \"datatype_list\" (\"ID\", \"name\") VALUES (\'3\', \'link\')"); ExecuteNonQuery(conn, tran, "INSERT INTO \"datatype_list\" (\"ID\", \"name\") VALUES (\'1\', \'songtext\')"); ExecuteNonQuery(conn, tran, "INSERT INTO \"datatype_list\" (\"ID\", \"name\") VALUES (\'2\', \'notation\')"); ExecuteNonQuery(conn, tran, "CREATE TABLE \"songlist\" ( \n \"ID\" integer primary key NOT NULL, \n \"name\" text NOT NULL, \n \"options\" text NULL\n)"); ExecuteNonQuery(conn, tran, "CREATE TABLE \"songlistitem\" ( \n \"ID\" integer primary key NOT NULL, \n \"song_id\" integer NOT NULL, \n \"songlist_id\" integer NOT NULL, \n \"transp\" integer NULL, \n \"position\" integer NULL, \n CONSTRAINT \"FK_songlistitem_song_id\" FOREIGN KEY (\"song_id\") REFERENCES \"song\"(\"ID\"), \n CONSTRAINT \"FK_songlistitem_songlist_id\" FOREIGN KEY (\"songlist_id\") REFERENCES \"songlist\"(\"ID\")\n)"); ExecuteNonQuery(conn, tran, "CREATE INDEX \"IX_songlistitem_song_id\" ON \"songlistitem\" (\"song_id\")"); ExecuteNonQuery(conn, tran, "CREATE INDEX \"IX_songlistitem_songlist_id\" ON \"songlistitem\" (\"songlist_id\")"); ExecuteNonQuery(conn, tran, "insert into songdata (song_id, textdata, datatype_id) \nselect songid, textdata, dataid from tmp_song_data;\ndrop table tmp_song_data;\n\n"); ExecuteNonQuery(conn, tran, "update info set value=\'3\' where name=\'dbversion\'"); }
/// <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; }
public bool AddProductTags(int productId, IList<int> tagIds, DbTransaction tran) { bool flag = false; DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_ProductTag VALUES(@TagId,@ProductId)"); this.database.AddInParameter(sqlStringCommand, "TagId", DbType.Int32); this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32); foreach (int num in tagIds) { this.database.SetParameterValue(sqlStringCommand, "ProductId", productId); this.database.SetParameterValue(sqlStringCommand, "TagId", num); if (tran != null) { flag = this.database.ExecuteNonQuery(sqlStringCommand, tran) > 0; } else { flag = this.database.ExecuteNonQuery(sqlStringCommand) > 0; } if (!flag) { return flag; } } return flag; }
/// <summary> /// 執行轉門市唯一檔 /// </summary> public int[] doCreateMutiStoreData(DbTransaction DBT) { try { int[] intCNT =new int[2]; DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_STM03.InsertMuti"); db.AddOutParameter(cmd, "VCOUNT", DbType.VarNumeric, Int32.MaxValue); db.AddOutParameter(cmd, "V_DIS_SW_COUNT", DbType.VarNumeric, Int32.MaxValue); int ProcessRecord = (DBT == null) ? db.ExecuteNonQuery(cmd) : db.ExecuteNonQuery(cmd, DBT); if (ProcessRecord == 0) { throw new Exception("輸入資料失敗!"); } intCNT[0] = int.Parse((db.GetParameterValue(cmd, "VCOUNT")).ToString()); intCNT[1] = int.Parse((db.GetParameterValue(cmd, "V_DIS_SW_COUNT")).ToString()); return intCNT; } catch (Exception ex) { throw new Exception("呼叫STM_CreateStoreUniqueFileDBO.doCreateMutiStoreData()發生錯誤,錯誤訊息:" + OracleExceptionProcess.OracleExceptionStringCut(ex.Message) ); } }
public static bool Insert(SurveyEntity survey, Database db, DbTransaction transaction) { DbCommand command = db.GetStoredProcCommand("usp_SurveyInsert"); db.AddInParameter(command, "MyUniversity", DbType.String, survey.MyUniversity); db.AddInParameter(command, "UniversityName", DbType.String, survey.UniversityName); db.AddInParameter(command, "UniversityAddress", DbType.String, survey.UniversityAddress); db.AddInParameter(command, "TypeOfResidence", DbType.String, survey.TypeOfResidence); db.AddInParameter(command, "TypeOfResidenceOption", DbType.String, survey.TypeOfResidenceOption); db.AddInParameter(command, "NameOfResidence", DbType.String, survey.NameOfResidence); db.AddInParameter(command, "AddressOfResidence", DbType.String, survey.AddressOfResidence); db.AddInParameter(command, "PropertyOwnerComment", DbType.String, survey.PropertyOwnerComment); db.AddInParameter(command, "Email", DbType.String, survey.Email); if (transaction == null) { db.ExecuteNonQuery(command); } else { db.ExecuteNonQuery(command, transaction); } return true; }
/// <summary> /// 新增基本檔資料(使用交易) /// </summary> /// <param name="ParameterList">放入輸入的參數</param> /// <param name="DBT">交易變數</param> /// <returns>回傳影響筆數</returns> public int doCreate(Int32 PID, DbTransaction DBT, DataRow dRow, ArrayList ParameterList) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_IFM_REQUEST_DETL.InsertOne"); db.AddInParameter(cmd, "vPID", DbType.VarNumeric, PID); db.AddInParameter(cmd, "vCREATEDATE", DbType.Date, ParameterList[17]); db.AddInParameter(cmd, "vCREATEUID", DbType.String, ParameterList[18]); db.AddInParameter(cmd, "vUPDATEDATE", DbType.Date, ParameterList[19]); db.AddInParameter(cmd, "vUPDATEUID", DbType.String, ParameterList[20]); db.AddInParameter(cmd, "vREQ_NO", DbType.String, ParameterList[0]); db.AddInParameter(cmd, "vPHASE_CODE", DbType.String, dRow["PHASE_CODE"]); db.AddInParameter(cmd, "vSTART_DATE", DbType.Date, dRow["START_DATE"]); db.AddInParameter(cmd, "vEND_DATE", DbType.Date, dRow["END_DATE"]); db.AddInParameter(cmd, "vEMP_ID", DbType.String, dRow["EMP_ID"]); db.AddInParameter(cmd, "vPHASE_HOUR", DbType.String, dRow["PHASE_HOUR"]); int ProcessRecord = (DBT == null) ? db.ExecuteNonQuery(cmd) : db.ExecuteNonQuery(cmd, DBT); if (ProcessRecord == 0) { throw new Exception("輸入資料失敗!"); } return int.Parse((db.GetParameterValue(cmd, "vPID")).ToString()); } catch (Exception ex) { throw new Exception("呼叫PKG_VDS_IFM_REQUEST_DETL.InsertOne發生錯誤,錯誤訊息:" + ex.Message); } }
public int AddProduct(ProductInfo product, DbTransaction dbTran) { DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Product_Create"); this.database.AddInParameter(storedProcCommand, "CategoryId", DbType.Int32, product.CategoryId); this.database.AddInParameter(storedProcCommand, "MainCategoryPath", DbType.String, product.MainCategoryPath); this.database.AddInParameter(storedProcCommand, "TypeId", DbType.Int32, product.TypeId); this.database.AddInParameter(storedProcCommand, "ProductName", DbType.String, product.ProductName); this.database.AddInParameter(storedProcCommand, "ProductCode", DbType.String, product.ProductCode); this.database.AddInParameter(storedProcCommand, "ShortDescription", DbType.String, product.ShortDescription); this.database.AddInParameter(storedProcCommand, "Unit", DbType.String, product.Unit); this.database.AddInParameter(storedProcCommand, "Description", DbType.String, product.Description); this.database.AddInParameter(storedProcCommand, "SaleStatus", DbType.Int32, (int) product.SaleStatus); this.database.AddInParameter(storedProcCommand, "AddedDate", DbType.DateTime, product.AddedDate); this.database.AddInParameter(storedProcCommand, "ImageUrl1", DbType.String, product.ImageUrl1); this.database.AddInParameter(storedProcCommand, "ImageUrl2", DbType.String, product.ImageUrl2); this.database.AddInParameter(storedProcCommand, "ImageUrl3", DbType.String, product.ImageUrl3); this.database.AddInParameter(storedProcCommand, "ImageUrl4", DbType.String, product.ImageUrl4); this.database.AddInParameter(storedProcCommand, "ImageUrl5", DbType.String, product.ImageUrl5); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl40", DbType.String, product.ThumbnailUrl40); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl60", DbType.String, product.ThumbnailUrl60); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl100", DbType.String, product.ThumbnailUrl100); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl160", DbType.String, product.ThumbnailUrl160); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl180", DbType.String, product.ThumbnailUrl180); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl220", DbType.String, product.ThumbnailUrl220); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl310", DbType.String, product.ThumbnailUrl310); this.database.AddInParameter(storedProcCommand, "ThumbnailUrl410", DbType.String, product.ThumbnailUrl410); this.database.AddInParameter(storedProcCommand, "MarketPrice", DbType.Currency, product.MarketPrice); this.database.AddInParameter(storedProcCommand, "BrandId", DbType.Int32, product.BrandId); this.database.AddInParameter(storedProcCommand, "HasSKU", DbType.Boolean, product.HasSKU); this.database.AddInParameter(storedProcCommand, "IsfreeShipping", DbType.Boolean, product.IsfreeShipping); this.database.AddInParameter(storedProcCommand, "TaobaoProductId", DbType.Int64, product.TaobaoProductId); this.database.AddOutParameter(storedProcCommand, "ProductId", DbType.Int32, 4); this.database.ExecuteNonQuery(storedProcCommand, dbTran); return (int) this.database.GetParameterValue(storedProcCommand, "ProductId"); }
/// <summary> /// 获取一个DataSet数据集 /// </summary> /// <param name="cmdType">命令类型,SQL文本或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="paras">查询参数</param> /// <returns>DataSet数据集</returns> public static DataSet ExecuteDataSet(Database db, DbTransaction dbTransaction, CommandType cmdType, string cmdText, params DbParameter[] paras) { DbCommand dbcmd = GetDbCommand(db, cmdType, cmdText); PrepareDbCommand(dbcmd, paras); DataSet ds = db.ExecuteDataSet(dbcmd, dbTransaction); return ds; }
public void BeginTransaction() { if (mConnection.State == ConnectionState.Closed) mConnection.Open(); mTransaction = mConnection.BeginTransaction(); }
public int CGR04_SetReAcceptDate(ArrayList ParameterList, DbTransaction DBT) { try { DbCommand cmd = db.GetStoredProcCommand("PKG_VDS_CGR04.CGR04_SetReAcceptDate"); db.AddInParameter(cmd, "V_BDATE", DbType.String, ParameterList[0]); db.AddInParameter(cmd, "V_EDATE", DbType.String, ParameterList[1]); db.AddInParameter(cmd, "V_ACCEPTDATE", DbType.String, ParameterList[2]); db.AddOutParameter(cmd, "RES", DbType.Int16, 10); if (DBT == null) { db.ExecuteNonQuery(cmd); } else { db.ExecuteNonQuery(cmd, DBT); } return int.Parse(cmd.Parameters["RES"].Value.ToString()); } catch (Exception ex) { throw new Exception("呼叫PKG_VDS_CGR04.InsertRow()發生錯誤,錯誤訊息:" + OracleExceptionProcess.OracleExceptionStringCut(ex.Message)); } }
private TransactionResult AddEditMaterial(Database db, System.Data.Common.DbTransaction transaction) { TransactionResult transactionResult; bool bl; int i = 0; System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("spAddEditMaterial"); db.AddInParameter(dbCommand, "MaterialID", System.Data.DbType.Int32, MaterialID); db.AddInParameter(dbCommand, "MaterialName", System.Data.DbType.String, MaterialDescription); db.AddInParameter(dbCommand, "AddEditOption", System.Data.DbType.Int16, AddEditOption); db.AddParameter(dbCommand, "Return Value", System.Data.DbType.Int32, System.Data.ParameterDirection.ReturnValue, "Return Value", System.Data.DataRowVersion.Default, i); db.ExecuteNonQuery(dbCommand, transaction); MaterialID = (int)db.GetParameterValue(dbCommand, "Return Value"); if (MaterialID == -1) { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Failure, "Failure Updating")); } else { return(new TransactionResult(TransactionStatus.Failure, "Failure Adding")); } } else if (MaterialID == -99) { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Success, "Record already exists")); } else { return(new TransactionResult(TransactionStatus.Success, "Record already exists")); } } else { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Success, "Successfully Updated")); } else { return(new TransactionResult(TransactionStatus.Success, "Successfully Added")); } } }
private TransactionResult AddEditEnquiryRegister(Database db, System.Data.Common.DbTransaction transaction) { //TransactionResult transactionResult; bool bl; int i = 0; System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("spAddEditEnquiryRegister"); db.AddInParameter(dbCommand, "EnquiryRegisterID", System.Data.DbType.Int32, _enquiryRegisterID); db.AddInParameter(dbCommand, "EnquiryReviewCheckListID", System.Data.DbType.Int32, _enquiryReviewCheckListID); db.AddInParameter(dbCommand, "RegretLetter", System.Data.DbType.String, _regretLetter); db.AddInParameter(dbCommand, "LegalReqmts", System.Data.DbType.String, _legalReqmts); db.AddInParameter(dbCommand, "Q1Reqmts", System.Data.DbType.String, _q1Reqmts); db.AddInParameter(dbCommand, "API6AReqmts", System.Data.DbType.String, _aPI6AReqmts); db.AddInParameter(dbCommand, "PODetails", System.Data.DbType.String, _pODetails); db.AddInParameter(dbCommand, "Status", System.Data.DbType.String, _status); db.AddInParameter(dbCommand, "AuditDate", System.Data.DbType.DateTime, _auditDate); db.AddInParameter(dbCommand, "AuditID", System.Data.DbType.Int32, _auditID); db.AddInParameter(dbCommand, "AddEditOption", System.Data.DbType.Int16, _addEditOption); db.AddParameter(dbCommand, "Return Value", System.Data.DbType.Int32, System.Data.ParameterDirection.ReturnValue, "Return Value", System.Data.DataRowVersion.Default, i); db.ExecuteNonQuery(dbCommand, transaction); _enquiryRegisterID = (int)db.GetParameterValue(dbCommand, "Return Value"); if (_enquiryRegisterID == -1) { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Failure, "Failure Updating")); } else { return(new TransactionResult(TransactionStatus.Failure, "Failure Adding")); } } else { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Success, "Successfully Updated")); } else { return(new TransactionResult(TransactionStatus.Success, "Successfully Added")); } } }
public bool DeleteDeviceInformation(List <Device> device, List <PointInfo> points, List <LogConfig> log, List <AlarmConfig> alarm, List <DigitalSignature> digital) { //if(_digital.IsExist() using (System.Data.SQLite.SQLiteConnection conn = SQLiteHelper.SQLiteHelper.CreateConn()) { PointTempBLL _point = new PointTempBLL(); LogConfigBLL _log = new LogConfigBLL(); AlarmConfigBLL _alarm = new AlarmConfigBLL(); DigitalSignatureBLL _digital = new DigitalSignatureBLL(); ReportEditorBLL _reportBll = new ReportEditorBLL(); if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } System.Data.Common.DbTransaction tran = conn.BeginTransaction(); try { //删除设备信息 DeleteDevice(device, tran); ////删除温度点信息 _point.DeletePointInfo(points, tran); ////删除log信息 _log.DeleteLogConfig(log, tran); ////删除alarm信息 _alarm.DeleteAlarmConfig(alarm, tran); device.ForEach(p => _reportBll.DeleteReportEditorBySnTn(p.SerialNum, p.TripNum, tran)); _digital.DeleteDigitalSignature(digital, tran); tran.Commit(); } catch (Exception ex) { _tracing.Error(ex, "delete the device info failed"); tran.Rollback(); conn.Close(); return(false); } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } } } return(true); }
public bool DeleteLineItem(string skuId, string orderId, System.Data.Common.DbTransaction dbTran) { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_OrderItems WHERE OrderId=@OrderId AND SkuId=@SkuId "); this.database.AddInParameter(sqlStringCommand, "OrderId", System.Data.DbType.String, orderId); this.database.AddInParameter(sqlStringCommand, "SkuId", System.Data.DbType.String, skuId); bool result; if (dbTran != null) { result = (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) == 1); } else { result = (this.database.ExecuteNonQuery(sqlStringCommand) == 1); } return(result); }
public bool AddActivitiesMember(int ActivitiesId, int Userid, System.Data.Common.DbTransaction dbTran = null) { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_ActivitiesMember (ActivitiesId,Userid)VALUES(@ActivitiesId,@Userid)"); this.database.AddInParameter(sqlStringCommand, "ActivitiesId", System.Data.DbType.Int32, ActivitiesId); this.database.AddInParameter(sqlStringCommand, "Userid", System.Data.DbType.Int32, Userid); bool result; if (dbTran != null) { result = (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) > 0); } else { result = (this.database.ExecuteNonQuery(sqlStringCommand) > 0); } return(result); }
public static bool UpdateAdjustCommssions(string orderId, string itemid, decimal adjustcommssion) { bool flag = false; Database database = DatabaseFactory.CreateDatabase(); bool result; using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { OrderInfo orderInfo = OrderHelper.GetOrderInfo(orderId); if (orderInfo == null) { result = false; return(result); } LineItemInfo lineItemInfo = orderInfo.LineItems[itemid]; lineItemInfo.ItemAdjustedCommssion = adjustcommssion; if (!new LineItemDao().UpdateLineItem(orderId, lineItemInfo, dbTransaction)) { dbTransaction.Rollback(); } if (!new OrderDao().UpdateOrder(orderInfo, dbTransaction)) { dbTransaction.Rollback(); result = false; return(result); } dbTransaction.Commit(); flag = true; } catch (Exception var_6_9F) { dbTransaction.Rollback(); } finally { dbConnection.Close(); } result = flag; } return(result); }
public bool UpdateDiscountStatus(int Id, DiscountStatus status) { bool result = false; using (System.Data.Common.DbConnection dbConnection = this.database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("update Hishop_LimitedTimeDiscount set Status=@Status WHERE LimitedTimeDiscountId = @Id"); this.database.AddInParameter(sqlStringCommand, "Id", System.Data.DbType.Int32, Id); this.database.AddInParameter(sqlStringCommand, "Status", System.Data.DbType.Int32, (int)status); System.Data.Common.DbCommand sqlStringCommand2; if (DiscountStatus.Delete == status) { sqlStringCommand2 = this.database.GetSqlStringCommand("delete Hishop_LimitedTimeDiscountProduct WHERE LimitedTimeDiscountId = @Id"); this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, Id); } else { sqlStringCommand2 = this.database.GetSqlStringCommand("update Hishop_LimitedTimeDiscountProduct set Status=@Status WHERE LimitedTimeDiscountId = @Id"); this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, Id); this.database.AddInParameter(sqlStringCommand2, "Status", System.Data.DbType.Int32, (int)status); } try { this.database.ExecuteNonQuery(sqlStringCommand, dbTransaction); this.database.ExecuteNonQuery(sqlStringCommand2, dbTransaction); dbTransaction.Commit(); result = true; } catch { dbTransaction.Rollback(); result = false; } finally { if (dbTransaction.Connection != null) { dbConnection.Close(); } } } return(result); }
private TransactionResult AddEditPurchaseOrder(Database db, System.Data.Common.DbTransaction transaction) { int i = 0; System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("spAddEditPurchaseOrder"); db.AddInParameter(dbCommand, "PurchaseOrderID", System.Data.DbType.Int32, _purchaseOrderID); db.AddInParameter(dbCommand, "PurchaseOrderNo", System.Data.DbType.String, _purchaseOrderNo); db.AddInParameter(dbCommand, "SendToID", System.Data.DbType.Int32, _sendToID); db.AddInParameter(dbCommand, "PurchaseOrderDate", System.Data.DbType.DateTime, _purchaseOrderDate); db.AddInParameter(dbCommand, "Currency", System.Data.DbType.String, _currency); db.AddInParameter(dbCommand, "Shipment", System.Data.DbType.String, _shipment); db.AddInParameter(dbCommand, "BuyerID", System.Data.DbType.Int32, _buyerID); db.AddInParameter(dbCommand, "TelNo", System.Data.DbType.String, _telNo); db.AddInParameter(dbCommand, "GrandTotal", System.Data.DbType.Decimal, _grandTotal); db.AddInParameter(dbCommand, "AuditDate", System.Data.DbType.DateTime, _auditDate); db.AddInParameter(dbCommand, "AuditID", System.Data.DbType.Int32, _auditID); db.AddInParameter(dbCommand, "AddEditOption", System.Data.DbType.Int16, _addEditOption); db.AddParameter(dbCommand, "Return Value", System.Data.DbType.Int32, System.Data.ParameterDirection.ReturnValue, "Return Value", System.Data.DataRowVersion.Default, i); db.ExecuteNonQuery(dbCommand, transaction); _purchaseOrderID = (int)db.GetParameterValue(dbCommand, "Return Value"); if (_purchaseOrderID == -1) { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Failure, "Failure Updating")); } else { return(new TransactionResult(TransactionStatus.Failure, "Failure Adding")); } } else { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Success, "Successfully Updated")); } else { return(new TransactionResult(TransactionStatus.Success, "Successfully Added")); } } }
public static bool AddGroupBuy(GroupBuyInfo groupBuy) { Globals.EntityCoding(groupBuy, true); Database database = DatabaseFactory.CreateDatabase(); bool result; using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { GroupBuyDao groupBuyDao = new GroupBuyDao(); int num = groupBuyDao.AddGroupBuy(groupBuy, dbTransaction); if (num <= 0) { dbTransaction.Rollback(); result = false; } else { if (!groupBuyDao.AddGroupBuyCondition(num, groupBuy.GroupBuyConditions, dbTransaction)) { dbTransaction.Rollback(); result = false; } else { dbTransaction.Commit(); result = true; } } } catch (Exception var_5_7C) { dbTransaction.Rollback(); result = false; } finally { dbConnection.Close(); } } return(result); }
public override int CreateVoteItem(VoteItemInfo voteItem, System.Data.Common.DbTransaction dbTran) { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO distro_VoteItems(VoteId, VoteItemName, ItemCount) Values(@VoteId, @VoteItemName, @ItemCount)"); this.database.AddInParameter(sqlStringCommand, "VoteId", System.Data.DbType.Int64, voteItem.VoteId); this.database.AddInParameter(sqlStringCommand, "VoteItemName", System.Data.DbType.String, voteItem.VoteItemName); this.database.AddInParameter(sqlStringCommand, "ItemCount", System.Data.DbType.Int32, voteItem.ItemCount); int result; if (dbTran == null) { result = this.database.ExecuteNonQuery(sqlStringCommand); } else { result = this.database.ExecuteNonQuery(sqlStringCommand, dbTran); } return(result); }
public bool DelOrderSplitByOrderID(string oldorderid, System.Data.Common.DbTransaction dbTran = null) { string query = "delete from vshop_OrderSplit where OldOrderID=@OldOrderID"; System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query); this.database.AddInParameter(sqlStringCommand, "OldOrderID", System.Data.DbType.String, oldorderid); bool result; if (dbTran == null) { result = (this.database.ExecuteNonQuery(sqlStringCommand) > 0); } else { result = (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) > 0); } return(result); }
protected virtual DbCommand CreateCommand(System.Data.Common.DbTransaction trans, string sql, params object[] args) { if (trans.Connection.State == ConnectionState.Closed) { trans.Connection.Open(); } var cmd = trans.Connection.CreateCommand(); cmd.CommandText = sql; cmd.Transaction = trans; if (args != null && args.Length > 0) { cmd.Parameters.AddRange(args); cmd.Prepare(); } return(cmd); }
protected virtual async Task <DbCommand> CreateCommandAsync(System.Data.Common.DbTransaction trans, string sql, params object[] args) { if (this.Connection.State == ConnectionState.Closed) { await this.Connection.OpenAsync(); } var cmd = this.Connection.CreateCommand(); cmd.CommandText = GetByIdSql; cmd.Transaction = trans; if (args != null && args.Length > 0) { cmd.Parameters.AddRange(args); await cmd.PrepareAsync(); } return(cmd); }
public static int AddPromotion(PromotionInfo promotion) { Database database = DatabaseFactory.CreateDatabase(); int result; using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { PromotionDao promotionDao = new PromotionDao(); int num = promotionDao.AddPromotion(promotion, dbTransaction); if (num <= 0) { dbTransaction.Rollback(); result = -1; } else { if (!promotionDao.AddPromotionMemberGrades(num, promotion.MemberGradeIds, dbTransaction)) { dbTransaction.Rollback(); result = -2; } else { dbTransaction.Commit(); result = num; } } } catch (Exception var_5_76) { dbTransaction.Rollback(); result = 0; } finally { dbConnection.Close(); } } return(result); }
public static bool AddBundlingProduct(BundlingInfo bundlingInfo) { Database database = DatabaseFactory.CreateDatabase(); bool result; using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { BundlingDao bundlingDao = new BundlingDao(); int num = bundlingDao.AddBundlingProduct(bundlingInfo, dbTransaction); if (num <= 0) { dbTransaction.Rollback(); result = false; } else { if (!bundlingDao.AddBundlingProductItems(num, bundlingInfo.BundlingItemInfos, dbTransaction)) { dbTransaction.Rollback(); result = false; } else { dbTransaction.Commit(); result = true; } } } catch (Exception var_5_74) { dbTransaction.Rollback(); result = false; } finally { dbConnection.Close(); } } return(result); }
private DataTable GetSubjects(System.Data.Common.DbTransaction tran, string WorkflowCode, string FromWorkflowStepCode, string ToWorkflowStepCode, string EntityID, string Comment, string UserName) { DataTable retVal = new DataTable(); DataRow row = null; retVal.Columns.Add("To", typeof(string)); retVal.Columns.Add("Subject", typeof(string)); if (this.SubjectType == DynamicMode.Static) { row = retVal.NewRow(); row["To"] = DBNull.Value; row["Subject"] = Subject; retVal.Rows.Add(row); } else { DataTable data = GetData(tran, this.SubjectDataCommand, WorkflowCode, FromWorkflowStepCode, ToWorkflowStepCode, EntityID, Comment, UserName, SubjectWorkflowCodeParameter, SubjectFromWorkflowStepCodeParameter, SubjectToWorkflowStepCodeParameter, SubjectEntityIDParameter, SubjectCommentParameter, SubjectUsernameParameter); foreach (DataRow record in data.Rows) { row = retVal.NewRow(); row["To"] = record[this.SubjectToAddressField]; row["Subject"] = record[this.SubjectField]; retVal.Rows.Add(row); } } return(retVal); }
public static bool AddGroupBuy(GroupBuyInfo groupBuy) { Database database = DatabaseFactory.CreateDatabase(); bool result; using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { int num = SubsitePromotionsProvider.Instance().AddGroupBuy(groupBuy, dbTransaction); if (num <= 0) { dbTransaction.Rollback(); result = false; } else { if (!SubsitePromotionsProvider.Instance().AddGroupBuyCondition(num, groupBuy.GroupBuyConditions, dbTransaction)) { dbTransaction.Rollback(); result = false; } else { dbTransaction.Commit(); result = true; } } } catch (Exception) { dbTransaction.Rollback(); result = false; } finally { dbConnection.Close(); } } return(result); }
private TransactionResult AddEditWorkOrderDocument(Database db, System.Data.Common.DbTransaction transaction) { //TransactionResult transactionResult; bool bl; int i = 0; DbCommand dbCommand = db.GetStoredProcCommand("spAddEditWorkOrderDocument"); db.AddInParameter(dbCommand, "@WorkOrderDocumentID", DbType.Int32, _workOrderDocumentID); db.AddInParameter(dbCommand, "@ContactID", DbType.Int32, _contactID); db.AddInParameter(dbCommand, "@WorkOrderID", DbType.Int32, _workOrderDocumentID); db.AddInParameter(dbCommand, "@PONo", DbType.String, _pONo); db.AddInParameter(dbCommand, "@FileName", DbType.String, _fileName); db.AddInParameter(dbCommand, "@Fileextension", DbType.String, _fileExtension); db.AddInParameter(dbCommand, "@FilePathandFileName", DbType.String, _filePathandFileName); db.AddInParameter(dbCommand, "@FileDescription", DbType.String, _fileDescription); db.AddInParameter(dbCommand, "@IsActive", DbType.Boolean, _isActive); db.AddInParameter(dbCommand, "@AddEditOption", DbType.Int16, _addEditOption); db.AddParameter(dbCommand, "Return Value", DbType.Int32, ParameterDirection.ReturnValue, "Return Value", DataRowVersion.Default, i); db.ExecuteNonQuery(dbCommand, transaction); _workOrderDocumentID = (int)db.GetParameterValue(dbCommand, "Return Value"); if (_workOrderDocumentID == -1) { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Failure, "Failure Updating")); } else { return(new TransactionResult(TransactionStatus.Failure, "Failure Adding")); } } else { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Success, "Successfully Updated")); } else { return(new TransactionResult(TransactionStatus.Success, "Successfully Added")); } } }
public static int EditPromotion(PromotionInfo promotion) { Database database = DatabaseFactory.CreateDatabase(); int result; using (System.Data.Common.DbConnection dbConnection = database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); try { if (!PromotionsProvider.Instance().EditPromotion(promotion, dbTransaction)) { dbTransaction.Rollback(); result = -1; } else { if (!PromotionsProvider.Instance().AddPromotionMemberGrades(promotion.ActivityId, promotion.MemberGradeIds, dbTransaction)) { dbTransaction.Rollback(); result = -2; } else { dbTransaction.Commit(); result = 1; } } } catch (Exception) { dbTransaction.Rollback(); result = 0; } finally { dbConnection.Close(); } } return(result); }
public ReturnValue CommitTransaction() { ReturnValue _result = new ReturnValue(); if (dbConnect == null || !_inTrans) { throw new DBException("No open transaction exists.", new Exception("")); } try { _transaction.Commit(); _transaction = null; _inTrans = false; ConInfo.Transcation = null; } catch (Exception ex) { RollbackTransaction(); string detail = "Exception occurred while trying to commit transaction.\n" + "See inner exception information for details."; this.SentEmail(detail, ex.ToString()); _result.Code = 1021; _result.Success = false; _result.ErrMessage = "Exception occurred while trying to commit transaction.\n" + "See inner exception information for details.------" + ex.ToString(); } finally { if (dbConnect != null && dbConnect.State != ConnectionState.Closed) { dbConnect.Close(); } } return(_result); }
/// <summary> /// 執行傳入的 SQL 語句 /// </summary> /// <param name="ptran">DBTransaction 物件</param> /// <param name="ps_sql">SQL 語句</param> /// <returns> 正數: 執行成功 負數:執行失敗</returns> public override int OfExecuteSql(System.Data.Common.DbTransaction ptran, string ps_sql) { if (!this.OfOpenDb()) { return(-1); } if (!this.OfOpenDb()) { return(-1); } if (ptran != null && ptran.Connection != null) { return(YR.DAL.DBUtility.MSSqlHelper.ExecuteNonQuery((SqlTransaction)ptran, CommandType.Text, ps_sql)); } else { return(YR.DAL.DBUtility.MSSqlHelper.ExecuteNonQuery((SqlConnection)this.DBConntion, CommandType.Text, ps_sql)); } }
private TransactionResult AddEditCity(Database db, System.Data.Common.DbTransaction transaction) { int i; TransactionResult transactionResult; bool bl; i = 0; System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("spAddEditCity"); db.AddInParameter(dbCommand, "StateID", System.Data.DbType.Int32, this.StateID); db.AddInParameter(dbCommand, "CityID", System.Data.DbType.Int32, this._cityID); db.AddInParameter(dbCommand, "City", System.Data.DbType.String, this._cityName); db.AddInParameter(dbCommand, "AddEditOption", System.Data.DbType.Int16, this.AddEditOption); db.AddParameter(dbCommand, "Return Value", System.Data.DbType.Int32, System.Data.ParameterDirection.ReturnValue, "Return Value", System.Data.DataRowVersion.Default, i); db.ExecuteNonQuery(dbCommand, transaction); this._cityID = (int)db.GetParameterValue(dbCommand, "Return Value"); bl = i != 0; if (!bl) { transactionResult = new TransactionResult(TransactionStatus.Failure, "City Already Exists in the State"); } else { bl = i != -1; if (!bl) { bl = this.AddEditOption != 1; if (!bl) { return(new TransactionResult(TransactionStatus.Failure, "Failure Updated")); } transactionResult = new TransactionResult(TransactionStatus.Failure, "Failure Adding"); } else { bl = this.AddEditOption != 1; transactionResult = !bl ? new TransactionResult(TransactionStatus.Success, "Successfully Updated") : new TransactionResult(TransactionStatus.Success, "Successfully Added"); } } return(transactionResult); }
public bool UpdateVote(VoteInfo vote, System.Data.Common.DbTransaction trans) { if (vote.StartDate > vote.EndDate) { DateTime startDate = vote.StartDate; vote.StartDate = vote.EndDate; vote.EndDate = startDate; } System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE [Hishop_Votes] SET [VoteName] = @VoteName,[MaxCheck] = @MaxCheck,[ImageUrl] = @ImageUrl ,[StartDate] = @StartDate,[EndDate] = @EndDate,[Description] = @Description,[MemberGrades] = @MemberGrades,[DefualtGroup] = @DefualtGroup,[CustomGroup] = @CustomGroup,[IsMultiCheck] = @IsMultiCheck WHERE VoteId = @VoteId ;"); this.database.AddInParameter(sqlStringCommand, "VoteName", System.Data.DbType.String, vote.VoteName); this.database.AddInParameter(sqlStringCommand, "MaxCheck", System.Data.DbType.Int32, vote.MaxCheck); this.database.AddInParameter(sqlStringCommand, "ImageUrl", System.Data.DbType.String, vote.ImageUrl); this.database.AddInParameter(sqlStringCommand, "StartDate", System.Data.DbType.DateTime, vote.StartDate); this.database.AddInParameter(sqlStringCommand, "EndDate", System.Data.DbType.DateTime, vote.EndDate); this.database.AddInParameter(sqlStringCommand, "Description", System.Data.DbType.String, vote.Description); this.database.AddInParameter(sqlStringCommand, "MemberGrades", System.Data.DbType.String, vote.MemberGrades); this.database.AddInParameter(sqlStringCommand, "DefualtGroup", System.Data.DbType.String, vote.DefualtGroup); this.database.AddInParameter(sqlStringCommand, "CustomGroup", System.Data.DbType.String, vote.CustomGroup); this.database.AddInParameter(sqlStringCommand, "IsMultiCheck", System.Data.DbType.Boolean, vote.IsMultiCheck); this.database.AddInParameter(sqlStringCommand, "VoteId", System.Data.DbType.Int64, vote.VoteId); return(this.database.ExecuteNonQuery(sqlStringCommand, trans) > 0); }
public bool AddCouponUseRecord(OrderInfo orderinfo, System.Data.Common.DbTransaction dbTran) { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("update Hishop_CouponItems set userName=@UserName,Userid=@Userid,Orderid=@Orderid,CouponStatus=@CouponStatus,EmailAddress=@EmailAddress,UsedTime=@UsedTime WHERE ClaimCode=@ClaimCode and CouponStatus!=1"); this.database.AddInParameter(sqlStringCommand, "ClaimCode", System.Data.DbType.String, orderinfo.CouponCode); this.database.AddInParameter(sqlStringCommand, "userName", System.Data.DbType.String, orderinfo.Username); this.database.AddInParameter(sqlStringCommand, "userid", System.Data.DbType.Int32, orderinfo.UserId); this.database.AddInParameter(sqlStringCommand, "CouponStatus", System.Data.DbType.Int32, 1); this.database.AddInParameter(sqlStringCommand, "UsedTime", System.Data.DbType.DateTime, DateTime.Now); this.database.AddInParameter(sqlStringCommand, "EmailAddress", System.Data.DbType.String, orderinfo.EmailAddress); this.database.AddInParameter(sqlStringCommand, "Orderid", System.Data.DbType.String, orderinfo.OrderId); bool result; if (dbTran != null) { result = (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) > 0); } else { result = (this.database.ExecuteNonQuery(sqlStringCommand) > 0); } return(result); }
/// <summary> /// Função que retorna um DataTable com um conjunto de dados do banco /// </summary> public DataTable DbGetDataTable(string sql, System.Data.Common.DbTransaction Transaction = null) { while (Transaction == null && IsOpen) { System.Threading.Thread.Sleep(20); } LastSQL = sql; lock (ObjLock) { DbDataAdapter da = null; try { DbCommand cmd = DbConnection.CreateCommand(); cmd.CommandText = sql; cmd.Transaction = Transaction; da = DbCreateDataAdapter(cmd); DataTable tb = new DataTable(); da.Fill(tb); return(tb); } catch (Exception ex) { throw new Exception("Erro ao executar a sql : " + sql, ex); } finally { if (da != null) { da.Dispose(); da = null; } } } }
private Template GetTemplate(System.Data.Common.DbTransaction tran, string WorkflowCode, string FromWorkflowStepCode, string ToWorkflowStepCode, string EntityID, string Comment, string UserName) { Template retVal = null; string TemplateName = null; if (this.ContentType == DynamicMode.Static) { TemplateName = this.ContentTemplate; } else { //dynamic -- execute data command and get template DataTable data = GetData(tran, this.ContentTemplateDataCommand, WorkflowCode, FromWorkflowStepCode, ToWorkflowStepCode, EntityID, Comment, UserName, ContentWorkflowCodeParameter, ContentFromWorkflowStepCodeParameter, ContentToWorkflowStepCodeParameter, ContentEntityIDParameter, ContentCommentParameter, ContentUsernameParameter); if (data.Rows.Count > 0) { if (data.Columns.Contains(this.ContentTemplateField)) { TemplateName = data.Rows[0][this.ContentTemplateField].ToString(); } } } if (!String.IsNullOrEmpty(TemplateName)) { retVal = Template.GetByName(TemplateName); } return(retVal); }
public bool Delete(int Id) { bool result = false; using (System.Data.Common.DbConnection dbConnection = this.database.CreateConnection()) { dbConnection.Open(); System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction(); System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_Activities WHERE ActivitiesId = @Id"); this.database.AddInParameter(sqlStringCommand, "Id", System.Data.DbType.Int32, Id); System.Data.Common.DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand("DELETE FROM Hishop_Activities_Detail WHERE ActivitiesId = @Id"); this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, Id); System.Data.Common.DbCommand sqlStringCommand3 = this.database.GetSqlStringCommand("DELETE FROM Hishop_Activities_Product WHERE ActivitiesId = @Id"); this.database.AddInParameter(sqlStringCommand3, "Id", System.Data.DbType.Int32, Id); try { this.database.ExecuteNonQuery(sqlStringCommand, dbTransaction); this.database.ExecuteNonQuery(sqlStringCommand2, dbTransaction); this.database.ExecuteNonQuery(sqlStringCommand3, dbTransaction); dbTransaction.Commit(); result = true; } catch { dbTransaction.Rollback(); result = false; } finally { if (dbTransaction.Connection != null) { dbConnection.Close(); } } } return(result); }
public bool InsertEmployee(FEPV_Employees_Data emp) { System.Data.Common.DbTransaction tran = gateFEPVOA.BeginTransaction(); try { string[] Columns = new string[] { "[EmployeeID]", "[EmployeeName]", "[EmployeeName_Eng]", "[CostCenter]", "[CardNo]", "[State]", "[DepartmentName_EN]" , "[DepartmentName_CN]", "[Email]", "[PositionName]", "[JoinDate]", "[Gender]", "[CompanyShortName]", "[CompanyFullName]", "[CompanyCode]" , "[LastUpdateDate]", "[Workplace]", "[NumberOfUpdate]", "[Birthday]" }; object[] Values = new object[] { emp.EmployeeID, emp.EmployeeName, emp.EmployeeName_Eng, emp.CostCenter, emp.CardNo, emp.State, emp.DepartmentName_EN , emp.DepartmentName_CN, emp.Email, emp.PositionName, emp.JoinDate, emp.Gender, emp.CompanyShortName, emp.CompanyFullName, emp.CompanyCode , emp.LastUpdateDate, emp.Workplace, emp.NumberOfUpdate, emp.Birthday }; gateFEPVOA.DbHelper.Insert("FEPV_Employees_Data", Columns, Values, tran, "EmployeeID"); tran.Commit(); tran.Dispose(); } catch (Exception ex) { Console.WriteLine(ex.Message); tran.Rollback(); throw new Exception(ex.Message); } return(true); }
public bool AddSendRedpackRecord(SendRedpackRecordInfo sendredpackinfo, System.Data.Common.DbTransaction dbTran) { System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("insert into vshop_SendRedpackRecord(BalanceDrawRequestID,UserID,OpenID,Amount,ActName,Wishing,ClientIP,IsSend,SendTime)values(@BalanceDrawRequestID,@UserID,@OpenID,@Amount,@ActName,@Wishing,@ClientIP,@IsSend,@SendTime)"); this.database.AddInParameter(sqlStringCommand, "BalanceDrawRequestID", System.Data.DbType.Int32, sendredpackinfo.BalanceDrawRequestID); this.database.AddInParameter(sqlStringCommand, "UserID", System.Data.DbType.Int32, sendredpackinfo.UserID); this.database.AddInParameter(sqlStringCommand, "OpenID", System.Data.DbType.String, sendredpackinfo.OpenID); this.database.AddInParameter(sqlStringCommand, "Amount", System.Data.DbType.Int32, sendredpackinfo.Amount); this.database.AddInParameter(sqlStringCommand, "ActName", System.Data.DbType.String, sendredpackinfo.ActName); this.database.AddInParameter(sqlStringCommand, "Wishing", System.Data.DbType.String, sendredpackinfo.Wishing); this.database.AddInParameter(sqlStringCommand, "ClientIP", System.Data.DbType.String, sendredpackinfo.ClientIP); this.database.AddInParameter(sqlStringCommand, "IsSend", System.Data.DbType.Int32, 0); this.database.AddInParameter(sqlStringCommand, "SendTime", System.Data.DbType.DateTime, DBNull.Value); bool result; if (dbTran != null) { result = (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) > 0); } else { result = (this.database.ExecuteNonQuery(sqlStringCommand) > 0); } return(result); }
private TransactionResult AddEditOrderAcceptanceCheckList(Database db, System.Data.Common.DbTransaction transaction) { //TransactionResult transactionResult; bool bl; int i = 0; System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("spAddEditOrderAcceptanceCheckList"); db.AddInParameter(dbCommand, "OrderAcceptanceCheckListID", System.Data.DbType.Int32, _orderAcceptanceCheckListID); db.AddInParameter(dbCommand, "ContactID", System.Data.DbType.Int32, _contactID); db.AddInParameter(dbCommand, "QuotationDate", System.Data.DbType.DateTime, _quotationDate); db.AddInParameter(dbCommand, "PartDescription", System.Data.DbType.String, _partDescription); db.AddInParameter(dbCommand, "PONO", System.Data.DbType.String, _poNO); db.AddInParameter(dbCommand, "PODate", System.Data.DbType.DateTime, _poDate); db.AddInParameter(dbCommand, "POAmendRef", System.Data.DbType.String, _poAmendRef); db.AddInParameter(dbCommand, "QuotationRef", System.Data.DbType.String, _quotationRef); db.AddInParameter(dbCommand, "DrawingNo_Issue", System.Data.DbType.String, _drawingNo_Issue); db.AddInParameter(dbCommand, "NatureOfReview", System.Data.DbType.String, _natureOfReview); db.AddInParameter(dbCommand, "CheckForOption1", System.Data.DbType.Boolean, _checkForOption1); db.AddInParameter(dbCommand, "CheckForOption2", System.Data.DbType.Boolean, _checkForOption2); db.AddInParameter(dbCommand, "CheckForOption3", System.Data.DbType.Boolean, _checkForOption3); db.AddInParameter(dbCommand, "CheckForOption4", System.Data.DbType.Boolean, _checkForOption4); db.AddInParameter(dbCommand, "Comments", System.Data.DbType.String, _comments); db.AddInParameter(dbCommand, "ReviewStatus", System.Data.DbType.String, _reviewStatus); db.AddInParameter(dbCommand, "ReviewedByID", System.Data.DbType.Int32, _reviewedByID); db.AddInParameter(dbCommand, "ReviewedDate", System.Data.DbType.DateTime, _reviewedDate); db.AddInParameter(dbCommand, "ApprovedByID", System.Data.DbType.Int32, _approvedByID); db.AddInParameter(dbCommand, "ApprovedDate", System.Data.DbType.DateTime, _approvedDate); db.AddInParameter(dbCommand, "AuditID", System.Data.DbType.Int32, _auditID); db.AddInParameter(dbCommand, "AuditDate", System.Data.DbType.DateTime, _auditDate); db.AddInParameter(dbCommand, "AddEditOption", System.Data.DbType.Int16, _addEditOption); db.AddParameter(dbCommand, "Return Value", System.Data.DbType.Int32, System.Data.ParameterDirection.ReturnValue, "Return Value", System.Data.DataRowVersion.Default, i); db.ExecuteNonQuery(dbCommand, transaction); _orderAcceptanceCheckListID = (int)db.GetParameterValue(dbCommand, "Return Value"); if (_orderAcceptanceCheckListID == -1) { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Failure, "Failure Updating")); } else { return(new TransactionResult(TransactionStatus.Failure, "Failure Adding")); } } else { if (_addEditOption == 1) { return(new TransactionResult(TransactionStatus.Success, "Successfully Updated")); } else { return(new TransactionResult(TransactionStatus.Success, "Successfully Added")); } } }
private static void PrepareCommand( DbCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters) { if (command == null) throw new ArgumentNullException("command"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); command.Connection = connection; command.CommandText = commandText; command.CommandType = commandType; if (transaction != null) { if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); command.Transaction = transaction; } if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }