public void run() { Exception exp = null; OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); OleDbTransaction txn = null; try { BeginCase("BeginTransaction - connection close"); try { txn = con.BeginTransaction(); } catch (Exception ex) {exp = ex;} Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName ); exp=null; } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} con.Open(); try { BeginCase("BeginTransaction - connection close"); txn = con.BeginTransaction(); Compare(txn == null,false ); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
/// <summary> /// 公有方法,执行一组Sql语句。 /// </summary> /// <param name="SqlStrings">Sql语句组</param> /// <returns>是否成功</returns> public bool ExecuteSQL(ArrayList SqlStrings) { ////System.Windows.Forms.MessageBox.Show("SqlStrings:" + SqlStrings); bool success = true; Open(); OleDbCommand cmd = new OleDbCommand(); OleDbTransaction trans = Connection.BeginTransaction(); cmd.Connection = Connection; cmd.Transaction = trans; try { foreach (String str in SqlStrings) { cmd.CommandText = str; cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { success = false; MessageBox.Show(ex.Message); trans.Rollback(); } finally { Close(); } return(success); }
/// <summary> /// Test OleDbTransaction class /// </summary> public static void Test_Transaction() { using (OleDbConnection conn = new OleDbConnection()) { OleDbTransaction transaction = null; conn.ConnectionString = TestCases.connString; conn.Open(); TestCases.ExecuteSQL("drop table if exists t", conn); transaction = conn.BeginTransaction(); string sql = "create table t(idx integer)"; using (OleDbCommand command = new OleDbCommand(sql, conn)) { command.Transaction = transaction; command.ExecuteNonQuery(); } int tablesCount = TestCases.GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 1); transaction.Rollback(); //Verify the table does not exist tablesCount = GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 0); transaction = conn.BeginTransaction(); sql = "create table t(idx integer)"; using (OleDbCommand command = new OleDbCommand(sql, conn)) { command.Transaction = transaction; command.ExecuteNonQuery(); } tablesCount = GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 1); transaction.Commit(); tablesCount = GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 1); transaction = conn.BeginTransaction(); TestCases.ExecuteSQL("drop table t", conn, transaction); transaction.Commit(); tablesCount = GetTablesCount("t", conn); Assert.IsTrue(tablesCount == 0); } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; OleDbTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.OleDb.OleDbException E) { tx.Rollback(); throw new Exception(E.Message); } } }
public override void Delete() { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); try { OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; cmd.CommandText = @"delete from " + GetDbTableName() + @" where id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); if (GetForeignDocField() != null) { cmd.CommandText = @"update documents set " + GetForeignDocField() + " = NULL where " + GetForeignDocField() + " = " + m_Id.ToString(); cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public int product_service(string service_id, string product_id, int customer_id) { int row=0; cn = new OleDbConnection("Provider=MSDAORA;Data Source=192.168.0.217/orcl;Persist Security Info=True;User ID=hr;Password=hr;"); cmd = cn.CreateCommand(); try { cn.Open(); string s = "update product_services set product_id='" + product_id + "' where service_id=(select service_id from customer_services where customer_id=" + customer_id + " and service_id='" + service_id + "')"; Trans = cn.BeginTransaction(); cmd = new OleDbCommand(s, cn,Trans); cmd.Transaction = Trans; row= cmd.ExecuteNonQuery(); Trans.Commit(); } catch (Exception e) { string s = e.Message; Trans.Rollback(); } finally { cn.Close(); } return row; }
public void exitTopic(TUser tUser) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //Begin Transaction dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE topic_id = " + tUser.topic.id.ToString(); dbCommand.ExecuteNonQuery(); tUser.topic = null; dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public bool Execute(string qry) { ConnectionString cnString = new ConnectionString(); IDbConnection cn = new OleDbConnection(cnString.GetConnString()); IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn); cmd.CommandType = CommandType.StoredProcedure; try { cn.Open(); IDbTransaction tran = cn.BeginTransaction(); cmd.Transaction = tran; int affectedRows = cmd.ExecuteNonQuery(); Console.WriteLine(affectedRows); if (affectedRows > 0) { tran.Commit(); return true; } else { tran.Rollback(); } } catch (Exception e) { Console.WriteLine(e.Message); } finally { cn.Close(); } return false; }
public void run() { OleDbConnection con = null; OleDbTransaction txn; Exception exp = null; try { BeginCase("OleDbTransaction Rollback"); // //prepare data base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); string Result = ""; con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); txn = con.BeginTransaction(); OleDbCommand cmd = new OleDbCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn); cmd.ExecuteNonQuery(); txn.Rollback(); // // cmd = new OleDbCommand("Select LastName From Employees Where EmployeeID = 100", con); Result = cmd.ExecuteScalar().ToString(); Compare(Result,"Last100" ); this.Log(Result); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
public void run() { Exception exp = null; OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); OleDbTransaction txn = con.BeginTransaction(); OleDbCommand cmd = new OleDbCommand("Select * From Employees", con); try { BeginCase("check Transaction property - default"); Compare(cmd.Transaction==null , true); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} cmd.Transaction = txn; try { BeginCase("check Transaction property"); Compare(cmd.Transaction , txn); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
public override bool Delete() { var sqlStatement = "Delete From Path "; using (var conn = new OleDbConnection(this.ConnectionString)) { conn.Open(); var trans = conn.BeginTransaction(); try { AccessHelper.ExecuteNonQuery(trans, sqlStatement); trans.Commit(); return true; } catch (Exception ex) { trans.Rollback(); Logger.Error(ex.Message, ex); return false; } finally { conn.Close(); } } }
public void setMessageDeletedByStudent(string message_id) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { dbCommand.CommandText = "UPDATE Messages SET deletedByStudent = true WHERE id = " + message_id; dbCommand.ExecuteNonQuery(); dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public void finishTopic(TTopic tTopic) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //---Begin Transaction--- tTopic.finishDateTime = DateTime.Now; //UPDATE Topic dbCommand.CommandText = "UPDATE Topics SET finishDateTime = '" + tTopic.finishDateTime.ToString() + "' WHERE id = " + tTopic.id.ToString(); dbCommand.ExecuteNonQuery();; dbTransaction.Commit(); //----End Transaction---- } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated) { var cmds = new List<InsertDelete>(); cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID)); if (archiveTable != null) { cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID)); } var connStr = buildConnString(dbName); var rowCounts = new RowCounts(0, 0); using (var conn = new OleDbConnection(connStr)) { conn.Open(); var trans = conn.BeginTransaction(); foreach (var id in cmds) { id.delete.Transaction = trans; id.delete.Connection = conn; id.delete.CommandTimeout = Config.QueryTimeout; logger.Log(id.delete.CommandText, LogLevel.Trace); int deleted = id.delete.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info); id.insert.Transaction = trans; id.insert.Connection = conn; id.insert.CommandTimeout = Config.QueryTimeout; logger.Log(id.insert.CommandText, LogLevel.Trace); int inserted = id.insert.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info); rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted); } trans.Commit(); } return rowCounts; }
public static void ExecuteSqlTran(Hashtable SQLStringList) { using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString)) { oleDbConnection.Open(); using (System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction()) { System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(); try { foreach (DictionaryEntry dictionaryEntry in SQLStringList) { string cmdText = dictionaryEntry.Key.ToString(); System.Data.OleDb.OleDbParameter[] cmdParms = (System.Data.OleDb.OleDbParameter[])dictionaryEntry.Value; DbHelperOleDb.PrepareCommand(oleDbCommand, oleDbConnection, oleDbTransaction, cmdText, cmdParms); int num = oleDbCommand.ExecuteNonQuery(); oleDbCommand.Parameters.Clear(); oleDbTransaction.Commit(); } } catch { oleDbTransaction.Rollback(); throw; } } } }
public static void ExecuteSqlTran(ArrayList SQLStringList) { using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString)) { oleDbConnection.Open(); System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(); oleDbCommand.Connection = oleDbConnection; System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction(); oleDbCommand.Transaction = oleDbTransaction; try { for (int i = 0; i < SQLStringList.Count; i++) { string text = SQLStringList[i].ToString(); if (text.Trim().Length > 1) { oleDbCommand.CommandText = text; oleDbCommand.ExecuteNonQuery(); } } oleDbTransaction.Commit(); } catch (System.Data.OleDb.OleDbException ex) { oleDbTransaction.Rollback(); throw new Exception(ex.Message); } } }
public void activeTopic(int topic_id) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //---Begin Transaction--- //UPDATE Topic dbCommand.CommandText = "UPDATE Topics SET finishDateTime = NULL WHERE id = " + topic_id; dbCommand.ExecuteNonQuery();; dbTransaction.Commit(); //----End Transaction---- } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public bool DeleteContentType(string id) { String strCmd1 = string.Format("DELETE FROM ContentType WHERE ID = {0}" , id); String strCmd2 = string.Format("DELETE FROM Contents WHERE TypeCode = (SELECT TypeCode FROM ContentType WHERE ID = {0})",id); OleDbConnection conn = new OleDbConnection(StrConn); OleDbTransaction transaction = null; bool flag = false; try { conn.Open(); transaction = conn.BeginTransaction(); OleDbCommand contentCommand = new OleDbCommand(strCmd2, conn, transaction); contentCommand.ExecuteNonQuery(); OleDbCommand typeCommand = new OleDbCommand(strCmd1, conn,transaction); typeCommand.ExecuteNonQuery(); transaction.Commit(); flag = true; } catch (Exception ex) { flag = false; transaction.Rollback(); } finally { conn.Close(); conn.Dispose(); } return flag; }
protected void BtnInsertJjd_Click(object sender, EventArgs e) { string jjdNo_; string psQty_; string reqObjid_; string reqVer_ = ""; string reqMsg_; jjdNo_ = TxtJjdNo.Text; //�ӵ����� jjd_line,�� ����jjd_no����jjd_line using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString)) { conn.Open(); OleDbTransaction tr = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand(); cmd.Transaction = tr; cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; if (jjdNo_ == "") { Misc.Message(this.GetType(),ClientScript, "err01 �������ӵ�ʧ��!"); return; } cmd.CommandText = "jp_jjd_line_api.new_"; cmd.Parameters.Clear(); cmd.Parameters.Add("v_jjd_no", OleDbType.VarChar).Value = jjdNo_; cmd.Parameters.Add("v_req_objid", OleDbType.VarChar); cmd.Parameters.Add("v_req_rowversion", OleDbType.VarChar); cmd.Parameters.Add("v_qty", OleDbType.Single); cmd.Parameters.Add("v_msg", OleDbType.VarChar, 1000).Direction = ParameterDirection.Output; foreach (GridViewRow grv in GVReqData.Rows) { psQty_ = ((TextBox)(grv.FindControl("TxtJjdLineQty"))).Text; if (psQty_ == "") continue; reqObjid_ = grv.Cells[13].Text; reqVer_ = grv.Cells[14].Text; cmd.Parameters["v_req_objid"].Value = reqObjid_; cmd.Parameters["v_req_rowversion"].Value = reqVer_; cmd.Parameters["v_qty"].Value = Convert.ToSingle(psQty_); cmd.ExecuteNonQuery(); reqMsg_ = cmd.Parameters["v_msg"].Value.ToString(); if (reqMsg_ != "1") { Misc.Message(this.GetType(),ClientScript, string.Format("err02 �������ӵ�ʧ�ܣ�{0}", reqMsg_)); return; } } tr.Commit(); } DisplayJjd(jjdNo_); }
/// <summary> /// Initializes a new instance. /// </summary> /// <param name="connectionString">OleDB Connection string</param> /// <param name="useTransaction">True if you want to use a transaction</param> public SqlHelper(string connectionString, bool useTransaction) { _connection = new OleDbConnection(connectionString); _connection.Open(); if (useTransaction) { _transaction = _connection.BeginTransaction(); } }
/// <summary> /// 增加一条数据,及其子表数据 /// </summary> public int Add(Model.manager_role model) { int newId; using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into dt_manager_role("); strSql.Append("role_name,role_type)"); strSql.Append(" values ("); strSql.Append("@role_name,@role_type)"); OleDbParameter[] parameters = { new OleDbParameter("@role_name", OleDbType.VarChar,100), new OleDbParameter("@role_type", OleDbType.TinyInt,1)}; parameters[0].Value = model.role_name; parameters[1].Value = model.role_type; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); StringBuilder strSql2; foreach (Model.manager_role_value models in model.manager_role_values) { strSql2 = new StringBuilder(); strSql2.Append("insert into dt_manager_role_value("); strSql2.Append("role_id,channel_name,channel_id,action_type)"); strSql2.Append(" values ("); strSql2.Append("@role_id,@channel_name,@channel_id,@action_type)"); OleDbParameter[] parameters2 = { new OleDbParameter("@role_id", OleDbType.Integer,4), new OleDbParameter("@channel_name", OleDbType.VarChar,255), new OleDbParameter("@channel_id", OleDbType.Integer,4), new OleDbParameter("@action_type", OleDbType.VarChar,100)}; parameters2[0].Value = newId; parameters2[1].Value = models.channel_name; parameters2[2].Value = models.channel_id; parameters2[3].Value = models.action_type; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } trans.Commit(); } catch { trans.Rollback(); return -1; } } } return newId; }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.manager_role model) { using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "manager_role("); strSql.Append("role_name,role_type,is_sys)"); strSql.Append(" values ("); strSql.Append("@role_name,@role_type,@is_sys)"); OleDbParameter[] parameters = { new OleDbParameter("@role_name", OleDbType.VarChar,100), new OleDbParameter("@role_type", OleDbType.Integer,4), new OleDbParameter("@is_sys", OleDbType.Integer,4)}; parameters[0].Value = model.role_name; parameters[1].Value = model.role_type; parameters[2].Value = model.is_sys; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID model.id = GetMaxId(conn, trans); StringBuilder strSql2; if (model.manager_role_values != null) { foreach (Model.manager_role_value modelt in model.manager_role_values) { strSql2 = new StringBuilder(); strSql2.Append("insert into " + databaseprefix + "manager_role_value("); strSql2.Append("role_id,nav_name,action_type)"); strSql2.Append(" values ("); strSql2.Append("@role_id,@nav_name,@action_type)"); OleDbParameter[] parameters2 = { new OleDbParameter("@role_id", OleDbType.Integer,4), new OleDbParameter("@nav_name", OleDbType.VarChar,100), new OleDbParameter("@action_type", OleDbType.VarChar,50)}; parameters2[0].Value = model.id; parameters2[1].Value = modelt.nav_name; parameters2[2].Value = modelt.action_type; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } } trans.Commit(); } catch { trans.Rollback(); return -1; } } } return model.id; }
void Generate(OleDbConnection connection, DateTime startDate, int daysCount) { using(var transaction = connection.BeginTransaction()) { for(int n = 0; n < daysCount; n++) { Console.Write("{0} of {1}\r", n + 1, daysCount); GenerateDay(connection, transaction, n, startDate.AddDays(n)); RaiseProgress((double)n / (double)daysCount); } transaction.Commit(); } }
protected void BtnSave_Click(object sender, EventArgs e) { string strObjid; if (DdlProject.SelectedValue == "0") { Misc.Message(this.GetType(), ClientScript, "����ʧ�ܣ���ѡ����Ŀ��"); return; } if (TxtPackageNo.Text.Trim() == "") { Misc.Message(this.GetType(), ClientScript, " ����ʧ�ܣ������������ʱ��롣"); } if (TxtPackageName.Text.Trim() == "") { Misc.Message(this.GetType(), ClientScript, " ����ʧ�ܣ�������������������"); } using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString)) { if (conn.State != ConnectionState.Open) conn.Open(); OleDbTransaction tr = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand("gen_part_package_api.new_", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Transaction = tr; cmd.Parameters.Clear(); cmd.Parameters.Add("v_project_id", OleDbType.VarChar, 20).Value = DdlProject.SelectedValue; cmd.Parameters.Add("v_Package_no", OleDbType.VarChar, 20).Value = this.TxtPackageNo.Text; cmd.Parameters.Add("v_Package_name", OleDbType.VarChar, 500).Value = this.TxtPackageName.Text; cmd.Parameters.Add("v_objid", OleDbType.VarChar, 50).Direction = ParameterDirection.Output; try { cmd.ExecuteNonQuery(); tr.Commit(); strObjid = cmd.Parameters["v_objid"].Value.ToString(); GVBaleEditDataBind_(strObjid); } catch (Exception ex) { tr.Rollback(); if (Misc.CheckIsDBCustomException(ex)) { Misc.Message(this.GetType(), ClientScript, Misc.GetDBCustomException(ex)); } else { throw; } } finally { conn.Close(); } } }
/// <summary>執行 Transaction</summary> /// <param name="alSQL">欲執行交易的 ArrayList (內含 SQL 指令)</param> /// <param name="OleDbConn">OleDbConnection連線物件</param> /// <returns>Transaction是否成功</returns> /// <remarks></remarks> public static bool raiseOleDbTransaction(ArrayList alSQL, System.Data.OleDb.OleDbConnection OleDbConn) { if (alSQL == null) { return(true); } if (alSQL.Count == 0) { return(true); } if (OleDbConn == null) { OleDbConn = createOleDbConnection(); } System.Data.OleDb.OleDbTransaction OleDbTrans = null; if (!(OleDbConn.State == ConnectionState.Open)) { OleDbConn.Open(); } System.Data.OleDb.OleDbCommand cmd = OleDbConn.CreateCommand(); StringBuilder strSQL = new StringBuilder(""); OleDbTrans = OleDbConn.BeginTransaction(); try { cmd.Transaction = OleDbTrans; for (int i = 0; i <= alSQL.Count - 1; i++) { if (!string.IsNullOrEmpty(alSQL[i].ToString())) { strSQL.AppendLine(alSQL[i].ToString()); } } cmd.CommandText = strSQL.ToString(); cmd.ExecuteNonQuery(); OleDbTrans.Commit(); return(true); } catch (Exception Ex) { if ((OleDbTrans != null)) { OleDbTrans.Rollback(); } //Message.alertMessage("C0002", null, Ex.Message.ToString(), null); return(false); } finally { if (!(OleDbConn.State == ConnectionState.Closed)) { OleDbConn.Close(); } } }
protected void BtnSave_Click(object sender, EventArgs e) { if (!ReqQtyValidation()) return; TextBox txtBox_ = new TextBox(); DropDownList ddl_lack_type_ = new DropDownList(); //ming.li ����ȱ���������� double reqQty; using (OleDbConnection conn = new OleDbConnection(Lib.DBHelper.OleConnectionString)) { if (conn.State != ConnectionState.Open) conn.Open(); OleDbTransaction tr = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand("jp_demand_api.new_", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Transaction = tr; cmd.Parameters.Clear(); cmd.Parameters.Add("v_demand_id", OleDbType.VarChar,20).Direction = ParameterDirection.Output; cmd.Parameters.Add("v_matr_seq_no", OleDbType.VarChar); cmd.Parameters.Add("v_matr_seq_line_no", OleDbType.Numeric); cmd.Parameters.Add("v_require_qty", OleDbType.Numeric); cmd.Parameters.Add("v_project_block", OleDbType.VarChar); cmd.Parameters.Add("v_project_system", OleDbType.VarChar).Value=TxtSystem.Text; cmd.Parameters.Add("v_work_content", OleDbType.VarChar).Value = TxtWorkContent.Text; cmd.Parameters.Add("v_place", OleDbType.VarChar).Value = DdlProdSite.SelectedValue; cmd.Parameters.Add("v_receiver", OleDbType.VarChar).Value = DdlReceiptPerson.SelectedItem.Text; cmd.Parameters.Add("v_receiver_ic", OleDbType.VarChar).Value = TxtIC.Text; cmd.Parameters.Add("v_receive_date", OleDbType.VarChar).Value = TxtDate.Text; cmd.Parameters.Add("v_receiver_contact", OleDbType.VarChar).Value = TxtContact.Text; cmd.Parameters.Add("v_receipt_dept", OleDbType.VarChar).Value = DdlReceiptDept.SelectedValue; cmd.Parameters.Add("v_crane", OleDbType.VarChar).Value = ChkDz.Checked == true ? "1" : "0"; cmd.Parameters.Add("v_recorder", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).UserID; cmd.Parameters.Add("v_req_group", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).GroupID; cmd.Parameters.Add("v_lack_type", OleDbType.VarChar); foreach (GridViewRow gvr in GVRation.Rows) { txtBox_ = (TextBox)(gvr.FindControl("TxtReqQty")); if (txtBox_.Text == "" || txtBox_.Text == null) continue; reqQty = Convert.ToDouble(txtBox_.Text); ddl_lack_type_ = (DropDownList)(gvr.FindControl("DDL_QH")); //ming.li ����ȱ���������� if (reqQty > 0) { cmd.Parameters["v_matr_seq_no"].Value = gvr.Cells[1].Text; cmd.Parameters["v_matr_seq_line_no"].Value = gvr.Cells[2].Text; cmd.Parameters["v_require_qty"].Value = reqQty; cmd.Parameters["v_project_block"].Value = gvr.Cells[13].Text; cmd.Parameters["v_lack_type"].Value = ddl_lack_type_.SelectedValue; cmd.ExecuteNonQuery(); ReqIDSessionHandler("ADD", cmd.Parameters["v_demand_id"].Value.ToString());//ming.li 20130327 } } tr.Commit(); } GVRationDataBind(); RecoverPageView(); GVDataBind(); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.point_log model) { int newId; using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into dt_point_log("); strSql.Append("user_id,user_name,[value],remark,add_time)"); strSql.Append(" values ("); strSql.Append("@user_id,@user_name,@value,@remark,@add_time)"); OleDbParameter[] parameters = { new OleDbParameter("@user_id", OleDbType.Integer,4), new OleDbParameter("@user_name", OleDbType.VarChar,100), new OleDbParameter("@value", OleDbType.Integer,4), new OleDbParameter("@remark", OleDbType.VarChar,500), new OleDbParameter("@add_time", OleDbType.Date)}; parameters[0].Value = model.user_id; parameters[1].Value = model.user_name; parameters[2].Value = model.value; parameters[3].Value = model.remark; parameters[4].Value = model.add_time; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); StringBuilder strSql2 = new StringBuilder(); strSql2.Append("update dt_users set point=point+" + model.value); if (model.value > 0) { strSql2.Append(",exp=exp+" + model.value); } strSql2.Append(" where id=@id"); OleDbParameter[] parameters2 = { new OleDbParameter("@id", OleDbType.Integer,4)}; parameters2[0].Value = model.user_id; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); trans.Commit(); } catch { trans.Rollback(); return -1; } } } return newId; }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.user_recharge model) { int newId; using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql=new StringBuilder(); strSql.Append("insert into " + databaseprefix + "user_recharge("); strSql.Append("user_id,user_name,recharge_no,payment_id,amount,status,add_time,complete_time)"); strSql.Append(" values ("); strSql.Append("@user_id,@user_name,@recharge_no,@payment_id,@amount,@status,@add_time,@complete_time)"); OleDbParameter[] parameters = { new OleDbParameter("@user_id", OleDbType.Integer,4), new OleDbParameter("@user_name", OleDbType.VarChar,100), new OleDbParameter("@recharge_no", OleDbType.VarChar,100), new OleDbParameter("@payment_id", OleDbType.Integer,4), new OleDbParameter("@amount", OleDbType.Decimal,5), new OleDbParameter("@status", OleDbType.Integer,4), new OleDbParameter("@add_time", OleDbType.Date), new OleDbParameter("@complete_time", OleDbType.Date)}; parameters[0].Value = model.user_id; parameters[1].Value = model.user_name; parameters[2].Value = model.recharge_no; parameters[3].Value = model.payment_id; parameters[4].Value = model.amount; parameters[5].Value = model.status; parameters[6].Value = model.add_time; if (model.complete_time != null) { parameters[7].Value = model.complete_time; } else { parameters[7].Value = DBNull.Value; } DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); trans.Commit(); } catch { trans.Rollback(); return -1; } } } return newId; }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.user_message model) { int newId; using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "user_message("); strSql.Append("[type],post_user_name,accept_user_name,is_read,title,content,post_time,read_time)"); strSql.Append(" values ("); strSql.Append("@type,@post_user_name,@accept_user_name,@is_read,@title,@content,@post_time,@read_time)"); OleDbParameter[] parameters = { new OleDbParameter("@type", OleDbType.Integer,4), new OleDbParameter("@post_user_name", OleDbType.VarChar,100), new OleDbParameter("@accept_user_name", OleDbType.VarChar,100), new OleDbParameter("@is_read", OleDbType.Integer,4), new OleDbParameter("@title", OleDbType.VarChar,100), new OleDbParameter("@content", OleDbType.VarChar), new OleDbParameter("@post_time", OleDbType.Date), new OleDbParameter("@read_time", OleDbType.Date)}; parameters[0].Value = model.type; parameters[1].Value = model.post_user_name; parameters[2].Value = model.accept_user_name; parameters[3].Value = model.is_read; parameters[4].Value = model.title; parameters[5].Value = model.content; parameters[6].Value = model.post_time; if (model.read_time != null) { parameters[7].Value = model.read_time; } else { parameters[7].Value = DBNull.Value; } DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); trans.Commit(); } catch { trans.Rollback(); return -1; } } } return newId; }
public DbTransaction GetTransction() { //throw new NotImplementedException(); if (_connection != null) { if (_connection.State == ConnectionState.Closed) { _connection.Open(); } return((DbTransaction)_connection.BeginTransaction()); } return(null); }
public override int Create() { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"insert into users (Login, Password, FirstName, LastName, Email, Role) values (?,?,?,?,?,?)"; cmd.Parameters.Add(new OleDbParameter("Login", m_Login)); cmd.Parameters.Add(new OleDbParameter("Password", m_Password)); cmd.Parameters.Add(new OleDbParameter("FirstName", m_FirstName)); cmd.Parameters.Add(new OleDbParameter("LastName", m_LastName)); cmd.Parameters.Add(new OleDbParameter("Email", m_Email)); cmd.Parameters.Add(new OleDbParameter("Role", m_Role)); cmd.ExecuteNonQuery(); cmd.CommandText = "select @@identity"; Decimal oid = (Decimal)cmd.ExecuteScalar(); m_Id = Convert.ToInt32(oid); if (m_Groups != null) { cmd.CommandText = @"insert into usergroups (UserId, GroupId) values (?,?)"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("UserId", typeof(int))); cmd.Parameters.Add(new OleDbParameter("GroupId", typeof(int))); //cmd.Prepare(); foreach (int groupId in m_Groups) { cmd.Parameters["UserId"].Value = m_Id; cmd.Parameters["GroupId"].Value = groupId; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } return(m_Id); }
protected void ButtSave_Click(object sender, EventArgs e) { string v_out; using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString)) { if (conn.State != ConnectionState.Open) conn.Open(); OleDbCommand cmd = new OleDbCommand("gen_part_package_item_api.Modify_", conn); cmd.CommandType = CommandType.StoredProcedure; OleDbTransaction tr = conn.BeginTransaction(); cmd.Transaction = tr; cmd.Parameters.Clear(); cmd.Parameters.Add("v_objid", OleDbType.VarChar, 20).Value = HiddenObjId.Value; cmd.Parameters.Add("v_rowversion", OleDbType.VarChar, 20).Value = HiddenRowversion.Value; cmd.Parameters.Add("v_Part_name", OleDbType.VarChar, 500).Value = TxtPartName.Text; cmd.Parameters.Add("v_Part_name_e", OleDbType.VarChar, 500).Value = TxtPartNameE.Text; cmd.Parameters.Add("v_Part_spec", OleDbType.VarChar, 100).Value = TxtPartSpec.Text; cmd.Parameters.Add("v_UNIT", OleDbType.VarChar, 20).Value = DdlUnit.SelectedValue =="0"?"":DdlUnit.SelectedValue; cmd.Parameters.Add("v_Dec_no", OleDbType.VarChar, 100).Value = TxtDecNo.Text; cmd.Parameters.Add("v_Contract_no", OleDbType.VarChar, 100).Value = TxtContractNo.Text; cmd.Parameters.Add("v_po_no", OleDbType.VarChar).Value = TxtPO.Text; cmd.Parameters.Add("v_pay_flag", OleDbType.VarChar).Value = ChkPayFlag.Checked ? "1" : "0"; try { cmd.ExecuteNonQuery(); tr.Commit(); Misc.RegisterClientScript(this.GetType(),"part_refresh",ClientScript, "<script type='text/javascript'>alert('���ݸ��³ɹ���');window.dialogArguments.refresh();window.close();</script>"); } catch (Exception ex) { tr.Rollback(); if (Misc.CheckIsDBCustomException(ex)) { Misc.Message(this.GetType(), ClientScript, Misc.GetDBCustomException(ex)); } else { throw; } //Misc.Message(this.GetType(),ClientScript,string.Format("���ݸ���ʧ�ܣ�{0}",ex.Message)); //return; } finally { conn.Close(); } } }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.manager model) { int newId; using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "manager("); strSql.Append("role_id,role_type,user_name,[password],salt,real_name,telephone,email,is_lock,add_time)"); strSql.Append(" values ("); strSql.Append("@role_id,@role_type,@user_name,@password,@salt,@real_name,@telephone,@email,@is_lock,@add_time)"); OleDbParameter[] parameters = { new OleDbParameter("@role_id", OleDbType.Integer,4), new OleDbParameter("@role_type", OleDbType.Integer,4), new OleDbParameter("@user_name", OleDbType.VarChar,100), new OleDbParameter("@password", OleDbType.VarChar,100), new OleDbParameter("@salt", OleDbType.VarChar,20), new OleDbParameter("@real_name", OleDbType.VarChar,50), new OleDbParameter("@telephone", OleDbType.VarChar,30), new OleDbParameter("@email", OleDbType.VarChar,30), new OleDbParameter("@is_lock", OleDbType.Integer,4), new OleDbParameter("@add_time", OleDbType.Date)}; parameters[0].Value = model.role_id; parameters[1].Value = model.role_type; parameters[2].Value = model.user_name; parameters[3].Value = model.password; parameters[4].Value = model.salt; parameters[5].Value = model.real_name; parameters[6].Value = model.telephone; parameters[7].Value = model.email; parameters[8].Value = model.is_lock; parameters[9].Value = model.add_time; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); trans.Commit(); } catch { trans.Rollback(); return -1; } } } return newId; }
public override void Update() { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"update users set Login=?, Password=?, FirstName=?, LastName=?, Email=?, Role=? where id=" + m_Id.ToString(); cmd.Parameters.Add(new OleDbParameter("Login", m_Login)); cmd.Parameters.Add(new OleDbParameter("Password", m_Password)); cmd.Parameters.Add(new OleDbParameter("FirstName", m_FirstName)); cmd.Parameters.Add(new OleDbParameter("LastName", m_LastName)); cmd.Parameters.Add(new OleDbParameter("Email", m_Email)); cmd.Parameters.Add(new OleDbParameter("Role", m_Role)); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from usergroups where userid=" + m_Id.ToString(); cmd.ExecuteNonQuery(); if (m_Groups != null) { cmd.CommandText = @"insert into usergroups (UserId, GroupId) values (?,?)"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("UserId", typeof(int))); cmd.Parameters.Add(new OleDbParameter("GroupId", typeof(int))); //cmd.Prepare(); foreach (int groupId in m_Groups) { cmd.Parameters["UserId"].Value = m_Id; cmd.Parameters["GroupId"].Value = groupId; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public bool Execute(DbUser alteredUser) { // update user details GetStreamID id = new GetStreamID(); string stream_id = id.Read(alteredUser.Stream); GetTStatusID id2 = new GetTStatusID(); string tstatus_id = id2.Read(alteredUser.TStatus); ConnectionString cnString = new ConnectionString(); IDbConnection cn = new OleDbConnection(cnString.GetConnString()); IDbCommand cmd = new OleDbCommand("sp_update_profile", (OleDbConnection)cn); cmd.CommandType = CommandType.StoredProcedure; try { cn.Open(); IDbTransaction tran = cn.BeginTransaction(); cmd.Transaction = tran; // Add job cmd.Parameters.Add(new OleDbParameter("@userLocation", alteredUser.Location)); cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId)); cmd.Parameters.Add(new OleDbParameter("@userStream", stream_id)); cmd.Parameters.Add(new OleDbParameter("@tstatID", tstatus_id)); cmd.Parameters.Add(new OleDbParameter("@userDegree", alteredUser.Degree)); cmd.Parameters.Add(new OleDbParameter("@userModules", alteredUser.Modules)); int affectedRows = cmd.ExecuteNonQuery(); if (affectedRows > 0) { tran.Commit(); return true; } else { tran.Rollback(); } } catch (Exception e) { Console.WriteLine(e.Message); } finally { cn.Close(); } return false; }
/// <summary> /// opens a new connection then starts transaction on that connection /// </summary> public bool BeginNewTransaction() { try { _conn = getNewConnection(); _tran = _conn.BeginTransaction(); return true; } catch (Exception e) { Debug.WriteLine(e.Message); return false; } }
public void run() { Exception exp = null; OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); /********************************************************* * OLEDB Provider for SQL Server does not allow nested transactions * http://support.microsoft.com/kb/177138/EN-US/ * http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q316872& */ if ((ConnectedDataProvider.GetDbType(con) == DataBaseServer.SQLServer) || (ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle) || (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) || (ConnectedDataProvider.GetDbType(con) == DataBaseServer.DB2)) { Log(string.Format("Test skipped, nested transactions are not supported in {0}", ConnectedDataProvider.GetDbType(con))); return; } // How To Implement Nested Transactions with Oracle // http://support.microsoft.com/kb/187289/EN-US/ OleDbTransaction txnOuter = null; OleDbTransaction txnInner = null; try { BeginCase("Check Outer Transaction Isoloation Level"); txnOuter = con.BeginTransaction(); txnInner = txnOuter.Begin(); Compare(txnOuter.IsolationLevel,IsolationLevel.ReadCommitted); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("Check Inner Transaction Isoloation Level"); Compare(txnOuter.IsolationLevel,IsolationLevel.RepeatableRead); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.payment model) { int newId; using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into dt_payment("); strSql.Append("title,img_url,remark,[type],poundage_type,poundage_amount,sort_id,is_lock,api_path)"); strSql.Append(" values ("); strSql.Append("@title,@img_url,@remark,@type,@poundage_type,@poundage_amount,@sort_id,@is_lock,@api_path)"); OleDbParameter[] parameters = { new OleDbParameter("@title", OleDbType.VarChar,100), new OleDbParameter("@img_url", OleDbType.VarChar,255), new OleDbParameter("@remark", OleDbType.VarChar,500), new OleDbParameter("@type", OleDbType.TinyInt,1), new OleDbParameter("@poundage_type", OleDbType.TinyInt,1), new OleDbParameter("@poundage_amount", OleDbType.Decimal,5), new OleDbParameter("@sort_id", OleDbType.Integer,4), new OleDbParameter("@is_lock", OleDbType.TinyInt,1), new OleDbParameter("@api_path", OleDbType.VarChar,100)}; parameters[0].Value = model.title; parameters[1].Value = model.img_url; parameters[2].Value = model.remark; parameters[3].Value = model.type; parameters[4].Value = model.poundage_type; parameters[5].Value = model.poundage_amount; parameters[6].Value = model.sort_id; parameters[7].Value = model.is_lock; parameters[8].Value = model.api_path; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); trans.Commit(); } catch { trans.Rollback(); return -1; } } } return newId; }
public bool GetCommand(int recommender_id, int recommended_id, int job_id, string reason) { if (DetermineIfExists(recommender_id, recommended_id, job_id)) { return true; } else { ConnectionString cnString = new ConnectionString(); IDbConnection cn = new OleDbConnection(cnString.GetConnString()); IDbCommand cmd = new OleDbCommand("sp_add_recommendation", (OleDbConnection)cn); cmd.CommandType = CommandType.StoredProcedure; try { cn.Open(); IDbTransaction tran = cn.BeginTransaction(); cmd.Transaction = tran; cmd.Parameters.Add(new OleDbParameter("@userId", recommender_id)); cmd.Parameters.Add(new OleDbParameter("@user_Id", recommended_id)); cmd.Parameters.Add(new OleDbParameter("@jobId", job_id)); cmd.Parameters.Add(new OleDbParameter("@reason", reason)); int affectedRows = cmd.ExecuteNonQuery(); if (affectedRows > 0) { tran.Commit(); return true; } else { tran.Rollback(); } } catch (Exception e) { Console.WriteLine(e.Message); return false; } finally { cn.Close(); } return true; } }
public int CreateCustomer(Customer newCustomer) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbCustomer(CCode,CName,Address,PRVID,Phone,Discount)"); sb.Append(" VALUES (@CCode,@CName,@Address,@PRVID,@Phone,@Discount)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@CCode", OleDbType.VarChar).Value = newCustomer.CCode; com.Parameters.Add("@CName", OleDbType.VarChar).Value = newCustomer.CName; com.Parameters.Add("@Address", OleDbType.VarChar).Value = newCustomer.Address; com.Parameters.Add("@PRVID", OleDbType.VarChar).Value = newCustomer.PRVID; com.Parameters.Add("@Phone", OleDbType.VarChar).Value = newCustomer.Phone; com.Parameters.Add("@Discount", OleDbType.VarChar).Value = newCustomer.Discount; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.user_groups model) { int newId; using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into dt_user_groups("); strSql.Append("title,grade,upgrade_exp,amount,point,discount,is_default,is_upgrade,is_lock)"); strSql.Append(" values ("); strSql.Append("@title,@grade,@upgrade_exp,@amount,@point,@discount,@is_default,@is_upgrade,@is_lock)"); OleDbParameter[] parameters = { new OleDbParameter("@title", OleDbType.VarChar,100), new OleDbParameter("@grade", OleDbType.Integer,4), new OleDbParameter("@upgrade_exp", OleDbType.Integer,4), new OleDbParameter("@amount", OleDbType.Decimal,5), new OleDbParameter("@point", OleDbType.Integer,4), new OleDbParameter("@discount", OleDbType.Integer,4), new OleDbParameter("@is_default", OleDbType.TinyInt,1), new OleDbParameter("@is_upgrade", OleDbType.TinyInt,1), new OleDbParameter("@is_lock", OleDbType.TinyInt,1)}; parameters[0].Value = model.title; parameters[1].Value = model.grade; parameters[2].Value = model.upgrade_exp; parameters[3].Value = model.amount; parameters[4].Value = model.point; parameters[5].Value = model.discount; parameters[6].Value = model.is_default; parameters[7].Value = model.is_upgrade; parameters[8].Value = model.is_lock; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); trans.Commit(); } catch { trans.Rollback(); return -1; } } } return newId; }
public int CreateSupiler(Supiler newSupiler) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbSupiler(SPCode,SPName,Address,PersonContact,Phone,Fax)"); sb.Append(" VALUES (@SPCode,@SPName,@Address,@PersonContact,@Phone,@Fax)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@SPCode", OleDbType.VarChar).Value = newSupiler.SPCode; com.Parameters.Add("@SPName", OleDbType.VarChar).Value = newSupiler.SPName; com.Parameters.Add("@Address", OleDbType.VarChar).Value = newSupiler.Address; com.Parameters.Add("@PersonContact", OleDbType.VarChar).Value = newSupiler.PersonContact; com.Parameters.Add("@Phone", OleDbType.VarChar).Value = newSupiler.Phone; com.Parameters.Add("@Fax", OleDbType.VarChar).Value = newSupiler.Fax; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public TTopic createTopic(TUser tUserStarter, TCourse tCourse, string title) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbDataReader dbDataReader; System.Data.OleDb.OleDbTransaction dbTransaction; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; TTopic tTopic = new TTopic(tCourse, title); try { //Begin Transaction tTopic.startDateTime = DateTime.Now; tTopic.starterTeacher = tUserStarter; dbCommand.CommandText = "INSERT INTO Topics (course_id, group_id, title, startDateTime, starterTeacher) VALUES('" + tTopic.tCourse.id + "', '" + tTopic.tCourse.groupId + "', '" + tTopic.title + "', '" + tTopic.startDateTime.ToString() + "', '" + tUserStarter.id + "')"; dbCommand.ExecuteNonQuery(); dbCommand.CommandText = "SELECT * FROM Topics WHERE (course_id = '" + tTopic.tCourse.id + "') AND (group_id = '" + tTopic.tCourse.groupId + "') AND (title = '" + tTopic.title + "') AND (startDateTime = '" + tTopic.startDateTime.ToString() + "')"; dbDataReader = dbCommand.ExecuteReader(); dbDataReader.Read(); tTopic.id = Convert.ToInt32(dbDataReader["id"]); dbDataReader.Close(); dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); return(null); } dbCommand.Transaction = null; dbTransaction = null; dbDataReader.Close(); dbConnection.Close(); return(tTopic); }
public void enterTopic(TUser tUser, TTopic tTopic) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; System.Data.OleDb.OleDbDataReader dbDataReader; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //Begin Transaction //Verify if the user is already on-line dbCommand.CommandText = "SELECT * FROM User_Topic WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL"; dbDataReader = dbCommand.ExecuteReader(); if (dbDataReader.HasRows) { dbDataReader.Close(); dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL"; dbCommand.ExecuteNonQuery(); } dbDataReader.Close(); dbCommand.CommandText = "INSERT INTO User_Topic (user_id, topic_id, startDateTime) VALUES ('" + tUser.id + "', '" + tTopic.id + "', '" + DateTime.Now.ToString() + "')"; dbCommand.ExecuteNonQuery(); tUser.topic = tTopic; dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public void setPositionOfNewContentItem(string contentItemGroup) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; System.Data.OleDb.OleDbDataReader dbDataReader; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //Begin Transaction //Verify if the user is already on-line dbCommand.CommandText = "SELECT MAX(id) FROM ContentItems WHERE item_group = '" + contentItemGroup + "'"; dbDataReader = dbCommand.ExecuteReader(); if (dbDataReader.HasRows) { dbDataReader.Read(); Int64 maxId = dbDataReader.GetInt32(0); //MAX id is the new item dbDataReader.Close(); dbCommand.CommandText = "UPDATE ContentItems SET [position] = " + maxId.ToString() + " WHERE (id = " + maxId.ToString() + ") AND (item_group = 'A')"; dbCommand.ExecuteNonQuery(); } dbTransaction.Commit(); //End Transaction } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public override void Delete() { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"delete from usergroups where userid = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from users where id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public void DbDelete() { if (m_Id < 1) { throw new BipFatalException("Internal Error"); } System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"delete from DocGroups where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from DocRefRelated where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from UserFavoriteDocs where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from UserReadDocs where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"update documents set ParentId = null where ParentId =" + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"update documents set PreviousVersionId = null where PreviousVersionId =" + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from Documents where Id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public int DbCreate(out OleDbTransaction trans) { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { CmdParams cp = new CmdParams(cmd); m_CreationTime = DateTime.Now; cmd.CommandText = @" insert into documents ( CreationTime, FileType, [FileName], DateReceived, DocumentDate, IncomingNumber, OutgoingNumber, Subject, Header, ArchiveFileNames, DocTypeId, DocSourceId, DocCategoryId, ParentId, PreviousVersionId, OwnerUserId, IsPublic )values ( " + cp.Add(m_CreationTime) + cp.Add(m_FileTypeId) + cp.Add(m_FileName) + cp.Add(m_DateReceived) + cp.Add(m_DocumentDate) + cp.Add(m_IncomingNumber) + cp.Add(m_OutgoingNumber) + cp.Add(m_Subject) + cp.Add(m_Header) + cp.Add(m_ArchiveFileNames) + cp.Add(m_DocTypeId) + cp.Add(m_DocSourceId) + cp.Add(m_DocCategoryId) + cp.Add(m_ParentId) + cp.Add(m_PreviousVersionId) + cp.Add(m_OwnerUserId) + cp.Add(m_IsPublic) + " ) "; cmd.ExecuteNonQuery(); cmd.CommandText = "select @@identity"; Decimal oid = (Decimal)cmd.ExecuteScalar(); m_Id = Convert.ToInt32(oid); StoreDocRefs(trans, "DocGroups", "GroupId", Groups); StoreDocRefs(trans, "DocRefRelated", "RelatedDocId", RefDocuments); cmd.Parameters.Clear(); cmd.CommandText = "update Documents set StorageFileName = '" + StorageFileName + "' where Id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); //trans.Commit(); //.DEV. isRead } catch (Exception ex) { trans.Rollback(); trans = null; throw ex; } MarkAsRead(); return(m_Id); }
public static bool ImportReportsFromDB(string fileName) { if (File.Exists(fileName))// Open the input file for input { System.Data.OleDb.OleDbConnection connImport = new System.Data.OleDb.OleDbConnection(VWA4Common.VWACommon.GetConnectionString(fileName)); System.Data.OleDb.OleDbConnection connTransaction = new System.Data.OleDb.OleDbConnection(VWA4Common.AppContext.WasteConnectionString); connTransaction.Open(); System.Data.OleDb.OleDbTransaction transaction = connTransaction.BeginTransaction(); try { Hashtable OldToNewReports = new Hashtable(); // import all reports DataTable dtReports = VWA4Common.DB.Retrieve("SELECT * FROM ReportMemorized", connImport, null); if (dtReports.Rows.Count > 0) { foreach (DataRow rowReport in dtReports.Rows) { string date_time = VWA4Common.VWACommon.DateToString(DateTime.Now); string oldReportID = rowReport["ID"].ToString(); string title = rowReport["Title"].ToString(); if (VWA4Common.DB.Retrieve("SELECT * FROM ReportMemorized WHERE Title = '" + title + "'").Rows.Count > 0) { title = "Imported " + date_time + " " + title; } string newReportID = VWA4Common.DB.Insert("INSERT INTO ReportMemorized(Title, ReportType, ConfigXML, CreatedDate, ModifiedDate) " + " VALUES('" + title + "','" + rowReport["ReportType"].ToString() + "','" + rowReport["ConfigXML"].ToString() + "', #" + date_time + "#, #" + date_time + "#)", connTransaction, transaction).ToString(); OldToNewReports[oldReportID] = newReportID; //System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(); //cmd.CommandText = "INSERT INTO ReportMemorized (ConfigXML, ReportType, Title, CreatedDate, ModifiedDate) " + // "VALUES(@ConfigXML, @ReportType, @Title, @CreatedDate, @ModifiedDate)"; //cmd.Parameters.Add("@ConfigXML", OleDbType.Binary); //cmd.Parameters.Add("@ReportType", OleDbType.VarChar, 50, "ReportType"); //cmd.Parameters.Add("@Title", OleDbType.VarChar, 255, "Title"); //cmd.Parameters.Add("@CreatedDate", OleDbType.Date, 50, "CreatedDate"); //cmd.Parameters.Add("@ModifiedDate", OleDbType.Date, 50, "ModifiedDate"); //cmd.Parameters["@ConfigXML"].Value = System.Text.Encoding.UTF8.GetBytes(arr); //cmd.Parameters["@ReportType"].Value = reportType; //cmd.Parameters["@Title"].Value = name; //cmd.Parameters["@CreatedDate"].Value = DateTime.Now; //cmd.Parameters["@ModifiedDate"].Value = DateTime.Now; //cmd.Connection = conn; //if (cmd.ExecuteNonQuery() <= 0) // MessageBox.Show(null, "Error saving report - report was not saved", "Error saving report", MessageBoxButtons.OK, MessageBoxIcon.Error); //if (isNew) // cmd.CommandText = "SELECT @@Identity"; //else // cmd.CommandText = "SELECT ID FROM ReportMemorized WHERE ReportMemorized.Title = '" + name + "'"; //id = (int)cmd.ExecuteScalar(); // import all report parameters DataTable dtReportParameters = VWA4Common.DB.Retrieve("SELECT * FROM ReportParam WHERE ReportMemorized = " + oldReportID, connImport, null); if (dtReportParameters.Rows.Count > 0) { foreach (DataRow rowReportParam in dtReportParameters.Rows) { VWA4Common.DB.Insert("INSERT INTO ReportParam(ParamName, ParamValue, ParamDisplayValue, ParamType, " + "ParamValueType, AssignType, GlobalName, ReportMemorized) " + " VALUES('" + rowReportParam["ParamName"].ToString().Replace("'", "''") + "', '" + rowReportParam["ParamValue"].ToString().Replace("'", "''") + "', '" + rowReportParam["ParamDisplayValue"].ToString().Replace("'", "''") + "', '" + rowReportParam["ParamType"].ToString().Replace("'", "''") + "', '" + rowReportParam["ParamValueType"].ToString().Replace("'", "''") + "', '" + rowReportParam["AssignType"].ToString().Replace("'", "''") + "', '" + rowReportParam["GlobalName"].ToString().Replace("'", "''") + "', " + newReportID + ")", connTransaction, transaction).ToString(); } } } } // import all series DataTable dtSeries = VWA4Common.DB.Retrieve("SELECT * FROM ReportSeries", connImport, null); if (dtSeries.Rows.Count > 0) { foreach (DataRow rowSerie in dtSeries.Rows) { string date_time = VWA4Common.VWACommon.DateToString(DateTime.Now); string oldSerieID = rowSerie["ID"].ToString(); // Mila todo: what to do with SiteID? string newSerieID = VWA4Common.DB.Insert("INSERT INTO ReportSeries(SerieName, SiteID, CreatedDate, ModifiedDate) " + " VALUES('" + rowSerie["SerieName"].ToString() + "', " + rowSerie["SiteID"].ToString() + ", #" + date_time + "#, #" + date_time + "#)", connTransaction, transaction).ToString(); // import all report sets DataTable dtReportSets = VWA4Common.DB.Retrieve("SELECT * FROM ReportSet WHERE SerieID = " + oldSerieID, connImport, null); if (dtReportSets.Rows.Count > 0) { foreach (DataRow rowReportSet in dtReportSets.Rows) { string oldReportSetID = rowSerie["ID"].ToString(); string newReportSetID = VWA4Common.DB.Insert("INSERT INTO ReportSet(ReportMemorized, [Order], Expression, SerieID) " + " VALUES(" + OldToNewReports[rowReportSet["ReportMemorized"].ToString()] + ", " + rowReportSet["Order"] + ", '" + rowReportSet["Expression"].ToString().Replace("'", "''") + "', " + rowReportSet["SerieID"] + ")", connTransaction, transaction).ToString(); } } } } transaction.Commit(); return(true); } catch (Exception ex) { MessageBox.Show("Error occured during importing Reports with message : " + ex.Message, "VWA Import Reports Error", MessageBoxButtons.OK, MessageBoxIcon.Error); transaction.Rollback(); } finally { if (connTransaction != null && connTransaction.State != ConnectionState.Closed) { connTransaction.Close(); } } } return(false); }
public void changePositionOfContentItems(int oldPositionIndex, int newPositionIndex, string owner) { //--Data Base Access Variables-- System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand(); dbCommand.Connection = dbConnection; System.Data.OleDb.OleDbTransaction dbTransaction; System.Data.OleDb.OleDbDataReader dbDataReader; //----------------------------- dbConnection.Close(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Transaction = dbTransaction; try { //--Begin Transaction-- //ID of the oldPosition dbCommand.CommandText = "SELECT id, [position] FROM ContentItems WHERE (item_group = 'A') AND (owner = '" + owner + "') ORDER BY [position]"; dbDataReader = dbCommand.ExecuteReader(); for (int count = 0; count <= oldPositionIndex; count++) { dbDataReader.Read(); } Int64 oldPositionId = dbDataReader.GetInt32(0); //id of the oldPosition Int64 oldPosition = dbDataReader.GetInt32(1); //position of the oldPositionIndex dbDataReader.Close(); //ID of the newPosition dbCommand.CommandText = "SELECT id, [position] FROM ContentItems WHERE (item_group = 'A') AND (owner = '" + owner + "') ORDER BY [position]"; dbDataReader = dbCommand.ExecuteReader(); for (int count = 0; count <= newPositionIndex; count++) { dbDataReader.Read(); } Int64 newPositionId = dbDataReader.GetInt32(0); //id of the newPosition Int64 newPosition = dbDataReader.GetInt32(1); //position of the newPositionIndex dbDataReader.Close(); //UPDATE of the oldPosition dbCommand.CommandText = "UPDATE ContentItems SET [position] = " + newPosition.ToString() + " WHERE (id = " + oldPositionId.ToString() + ") AND (item_group = 'A')"; dbCommand.ExecuteNonQuery(); //UPDATE of the newPosition dbCommand.CommandText = "UPDATE ContentItems SET [position] = " + oldPosition.ToString() + " WHERE (id = " + newPositionId.ToString() + ") AND (item_group = 'A')"; dbCommand.ExecuteNonQuery(); dbTransaction.Commit(); //--End Transaction-- } catch { dbTransaction.Rollback(); dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); } dbCommand.Transaction = null; dbTransaction = null; dbConnection.Close(); }
public void DbUpdate() { UserIdentity user = UserIdentity.Current; Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { CmdParams cp = new CmdParams(cmd, false); m_CreationTime = DateTime.Now; cmd.CommandText = @" update documents set FileType = " + cp.Add(m_FileTypeId) + ", [FileName] = " + cp.Add(m_FileName) + ", DateReceived = " + cp.Add(m_DateReceived) + ", DocumentDate = " + cp.Add(m_DocumentDate) + ", IncomingNumber = " + cp.Add(m_IncomingNumber) + ", OutgoingNumber = " + cp.Add(m_OutgoingNumber) + ", Subject = " + cp.Add(m_Subject) + ", Header = " + cp.Add(m_Header) + ", ArchiveFileNames = " + cp.Add(m_ArchiveFileNames) + ", DocTypeId = " + cp.Add(m_DocTypeId) + ", DocSourceId = " + cp.Add(m_DocSourceId) + ", DocCategoryId = " + cp.Add(m_DocCategoryId) + ", ParentId = " + cp.Add(m_ParentId) + ", PreviousVersionId = " + cp.Add(m_PreviousVersionId) + ", IsPublic = " + cp.Add(m_IsPublic) + ", StorageFileName = '" + StorageFileName + "' " + " where id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); StoreDocRefs(trans, "DocGroups", "GroupId", Groups); StoreDocRefs(trans, "DocRefRelated", "RelatedDocId", RefDocuments); cmd.Parameters.Clear(); cmd.CommandText = "delete from UserReadDocs where DocId=" + m_Id.ToString() + " and UserId=" + user.UserId.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = "delete from UserFavoriteDocs where DocId=" + m_Id.ToString() + " and UserId=" + user.UserId.ToString(); cmd.ExecuteNonQuery(); if (IsRead) { cmd.CommandText = "insert into UserReadDocs (DocId, UserId) values (" + m_Id.ToString() + ", " + user.UserId.ToString() + " ) "; cmd.ExecuteNonQuery(); } if (IsFavorite) { cmd.CommandText = "insert into UserFavoriteDocs (DocId, UserId) values (" + m_Id.ToString() + ", " + user.UserId.ToString() + " ) "; cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }