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 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); } } }
/// <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(); } } }
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; }
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 int CreateProduct(Product newProduct) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbProduct(PCode,PBarCode,PName,CategoryCode,UCode,Status)"); sb.Append(" VALUES (@PCode,@PBarCode,@PName,@CategoryCode,@UCode,@Status)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@PCode", OleDbType.VarChar).Value = newProduct.PCode; com.Parameters.Add("@PBarCode", OleDbType.VarChar).Value = newProduct.PBarCode; com.Parameters.Add("@PName", OleDbType.VarChar).Value = newProduct.PName; com.Parameters.Add("@CategoryCode", OleDbType.VarChar).Value = newProduct.CategoryCode; com.Parameters.Add("@UCode", OleDbType.VarChar).Value = newProduct.UCode; com.Parameters.Add("@Status", OleDbType.VarChar).Value = newProduct.Status; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public int CreateProductOnWareHouse(ProductOnWareHouse newProductOnWareHouse) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbProductOnWareHouse(PCode,WHCode,UCode,QtyWithUnit,RealQty)"); sb.Append(" VALUES (@PCode,@WHCode,@UCode,@QtyWithUnit,@RealQty )"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@PCode", OleDbType.VarChar).Value = newProductOnWareHouse.PCode; com.Parameters.Add("@WHCode", OleDbType.VarChar).Value = newProductOnWareHouse.WHCode; com.Parameters.Add("@UCode", OleDbType.VarChar).Value = newProductOnWareHouse.UCode; com.Parameters.Add("@QtyWithUnit", OleDbType.VarChar).Value = newProductOnWareHouse.QtyWithUnit; com.Parameters.Add("@RealQty", OleDbType.VarChar).Value = newProductOnWareHouse.RealQty; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public int CreateProductOnSupiler(ProductOnSupiler newProductOnSupiler) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbProductOnSupiler(PCode,SPCode,Cost,RecordDate)"); sb.Append(" VALUES (@PCode,@SPCode,@Cost,@RecordDate)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@PCode", OleDbType.VarChar).Value = newProductOnSupiler.PCode; com.Parameters.Add("@SPCode", OleDbType.VarChar).Value = newProductOnSupiler.SPCode; com.Parameters.Add("@Cost", OleDbType.VarChar).Value = newProductOnSupiler.Cost; string dateRecord = String.Format("{0:dd/MM/yyyy}", newProductOnSupiler.RecordDate); com.Parameters.Add("@RecordDate", OleDbType.Date).Value = dateRecord; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
private void buttonRollback_Click(object sender, EventArgs e) { try { trnNew.Rollback(); MessageBox.Show("Rolled back"); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { this.oleDbConnection.Close(); } }
public int CreateUnit(Unit newUnit) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbUnit(UCode,UName,QtyUnit)"); sb.Append(" VALUES (@UCode,@UName,@QtyUnit)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@UCode", OleDbType.VarChar).Value = newUnit.UCode; com.Parameters.Add("@UName", OleDbType.VarChar).Value = newUnit.UName; com.Parameters.Add("@QtyUnit", OleDbType.BigInt).Value = newUnit.QtyUnit; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public int CreateWareHouse(WareHouse newWareHouse) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbWareHouse(WHCode,WHName,WHLocation)"); sb.Append(" VALUES (@WHCode,@WHName,@WHLocation)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@WHCode", OleDbType.VarChar).Value = newWareHouse.WHCode; com.Parameters.Add("@WHName", OleDbType.VarChar).Value = newWareHouse.WHName; com.Parameters.Add("@WHLocation", OleDbType.VarChar).Value = newWareHouse.WHLocation; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public int CreateStUser(StUser newStUser) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbUser(SUsername,SPassword,SPriority)"); sb.Append(" VALUES ('"+newStUser.Username+"','"+newStUser.Password +"','"+newStUser.Priority+"')"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public static bool ExecuteNonQuery(System.Data.OleDb.OleDbCommand[] sqlCmdArray) { if (Connection1.State == ConnectionState.Closed) { Connection1.Open(); } Transaction = Connection.BeginTransaction(); for (int i = 0; i < sqlCmdArray.Length; i++) { if (!ExecuteNonQuery(sqlCmdArray[i])) { Transaction.Rollback(); Connection1.Close(); return(false); } } Transaction.Commit(); Connection1.Close(); return(true); }
//执行SQL 无返回 public static void AExecuteNonQuerySql(String sql) { try { Console.WriteLine(sql); conn = new OleDbConnection(constrA); conn.Open(); OTrans = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans); cmd.ExecuteNonQuery(); OTrans.Commit(); } catch (Exception e1) { OTrans.Rollback(); MessageBox.Show("" + e1, "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } finally { conn.Close(); } }
public int CreateRent(Rent newRent,string _whCode) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbRent(RentCode,PRCode,PCompany,PAddress,PPhone,PObjective,DateRent,DateReturn)"); sb.Append(" VALUES (@RentCode,@PRCode,@PCompany,@PAddress,@PPhone,@PObjective,@DateRent,@DateReturn)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@RentCode", OleDbType.VarChar).Value = newRent.RentCode; com.Parameters.Add("@PRCode", OleDbType.VarChar).Value = newRent.PRCode; com.Parameters.Add("@PCompany", OleDbType.VarChar).Value = newRent.PCompany; com.Parameters.Add("@PAddress", OleDbType.VarChar).Value = newRent.PAddress; com.Parameters.Add("@PPhone", OleDbType.VarChar).Value = newRent.PPhone; com.Parameters.Add("@PObjective", OleDbType.VarChar).Value = newRent.PObjective; string rentDate = String.Format("{0:dd/MM/yyyy}", newRent.DateRent); string returnDate = String.Format("{0:dd/MM/yyyy}", newRent.DateReturn); com.Parameters.Add("@DateRent", OleDbType.Date).Value = rentDate; com.Parameters.Add("@DateReturn", OleDbType.Date).Value = returnDate; com.ExecuteNonQuery(); foreach (RentDetail r in newRent.rentDetails) { sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbRentDetail(RentCode,PCode,PName,UCode,UName,NumberRent,NumberReturn,Physical,Penalty)"); sb.Append(" VALUES (@RentCode,@PCode,@PName,@UCode,@UName,@NumberRent,@NumberReturn,@Physical,@Penalty)"); string sqlSaveDetail; sqlSaveDetail = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSaveDetail; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@RentCode", OleDbType.VarChar).Value = r.RentCode; com.Parameters.Add("@PCode", OleDbType.VarChar).Value = r.PCode; com.Parameters.Add("@PName", OleDbType.VarChar).Value = r.PName; com.Parameters.Add("@UCode", OleDbType.VarChar).Value = r.UCode; com.Parameters.Add("@UName", OleDbType.VarChar).Value = r.UName; com.Parameters.Add("@NumberRent", OleDbType.VarChar).Value = r.NumberRent; com.Parameters.Add("@NumberReturn", OleDbType.VarChar).Value = "0"; com.Parameters.Add("@Physical", OleDbType.VarChar).Value = ""; com.Parameters.Add("@Penalty", OleDbType.VarChar).Value = "0"; com.ExecuteNonQuery(); ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _whCode); if (pOnwarehouse != null) { Product p = serviceProduct.getByCode(r.PCode); pOnwarehouse.UCode = p.UCode; Unit u = serviceUnit.getByCode(p.UCode); pOnwarehouse.QtyWithUnit = u.QtyUnit; int oldblance = pOnwarehouse.RealQty; int newbalnce = oldblance - r.NumberRent; pOnwarehouse.RealQty = newbalnce; int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse); if (tmp > 0) { Console.WriteLine("update pOnwarehouse Complate"); } } } tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public Boolean UpdateTable(string pTable, DataRow pRow, string pListColumn, object[] pValueKey) { Boolean flag = false; string Parameter = ""; string sql; int i, count; string[] ListColumn = pListColumn.Split(';'); if (pRow != null) { count = pRow.Table.Columns.Count; for (i = 0; i < count - 1; i++) { Parameter = Parameter + "[" + pRow.Table.Columns[i].ColumnName + "] = ?,"; } Parameter = Parameter + pRow.Table.Columns[i].ColumnName + " = ?"; string sWhere = ""; for (i = 0; i < ListColumn.Length; i++) { sWhere += "[" + ListColumn[i] + "] = ? and "; } sWhere = sWhere.Substring(0, sWhere.Length - 4); sql = "Update " + pTable + " Set " + Parameter + " Where " + sWhere; try { OpenConnect(); glbTransaction = glbConnection.BeginTransaction(); glbCommand.Transaction = glbTransaction; glbCommand.Connection = glbConnection; glbCommand.CommandType = CommandType.Text; glbCommand.CommandText = sql; glbCommand.Parameters.Clear(); for (i = 0; i < count; i++) { glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]); } for (i = 0; i < pValueKey.Length; i++) { glbCommand.Parameters.AddWithValue("@ValueKey0" + i.ToString(), pValueKey[i]); } glbCommand.ExecuteNonQuery(); glbTransaction.Commit(); CloseConnect(); flag = true; } catch { glbTransaction.Rollback(); CloseConnect(); } } return flag; }
public int CreateSale(Sale newSale, string _whCode) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbSale(SaleCode,CCode,PQutation,DateSale,SaleBy,NetDc,NetVat,NetTotal)"); sb.Append(" VALUES (@SaleCode,@CCode,@PQutation,@DateSale,@SaleBy,@NetDc,@NetVat,@NetTotal)"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@SaleCode", OleDbType.VarChar).Value = newSale.SaleCode; com.Parameters.Add("@CCode", OleDbType.VarChar).Value = newSale.CustomerForSale.CCode; com.Parameters.Add("@PQutation", OleDbType.VarChar).Value = newSale.PQutation; string dateSale = String.Format("{0:dd/MM/yyyy}", newSale.DateSale); com.Parameters.Add("@DateSale", OleDbType.Date).Value = dateSale; com.Parameters.Add("@SaleBy", OleDbType.VarChar).Value = newSale.SaleBy; com.Parameters.Add("@NetDc", OleDbType.VarChar).Value = newSale.NetDc; com.Parameters.Add("@NetVat", OleDbType.VarChar).Value = newSale.NetVat; com.Parameters.Add("@NetTotal", OleDbType.VarChar).Value = newSale.NetTotal; com.ExecuteNonQuery(); foreach (SaleDetail r in newSale.saleDetails) { sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbSaleDetail(SaleCode,PCode,PName,UCode,UName,Amount,SalePrice,NetTotal)"); sb.Append(" VALUES (@SaleCode,@PCode,@PName,@UCode,@UName,@Amount,@SalePrice,@NetTotal)"); string sqlSaveDetail; sqlSaveDetail = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSaveDetail; com.Transaction = tr; com.Parameters.Clear(); com.Parameters.Add("@SaleCode", OleDbType.VarChar).Value = r.SaleCode; com.Parameters.Add("@PCode", OleDbType.VarChar).Value = r.PCode; com.Parameters.Add("@PName", OleDbType.VarChar).Value = r.PName; com.Parameters.Add("@UCode", OleDbType.VarChar).Value = r.UCode; com.Parameters.Add("@UName", OleDbType.VarChar).Value = r.UName; com.Parameters.Add("@Amount", OleDbType.VarChar).Value = r.Amount; com.Parameters.Add("@SalePrice", OleDbType.VarChar).Value = r.SalePrice; com.Parameters.Add("@NetTotal", OleDbType.VarChar).Value = r.NetTotal; com.ExecuteNonQuery(); ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _whCode); if (pOnwarehouse != null) { Product p = serviceProduct.getByCode(r.PCode); pOnwarehouse.UCode = p.UCode; Unit u = serviceUnit.getByCode(p.UCode); pOnwarehouse.QtyWithUnit = u.QtyUnit; int oldblance = pOnwarehouse.RealQty; int newbalnce = oldblance - r.Amount; pOnwarehouse.RealQty = newbalnce; int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse); if (tmp > 0) { Console.WriteLine("update pOnwarehouse Complate"); } } } tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public Boolean InsertTable(string pTable, DataRow pRow, string pIdentityColumn) { Boolean flag = false; string mColumn = ""; string Parameter = ""; string sql; int i, count; if (pRow != null) { count = pRow.Table.Columns.Count; for (i = 0; i < count - 1; i++) { if (pIdentityColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper()) { mColumn = mColumn + "[" + pRow.Table.Columns[i].ColumnName + "],"; Parameter = Parameter + "?,"; } } mColumn = mColumn + "[" + pRow.Table.Columns[i].ColumnName + "]"; Parameter = Parameter + "?"; sql = "Insert into " + pTable + " (" + mColumn + ") values (" + Parameter + ")"; try { OpenConnect(); glbTransaction = glbConnection.BeginTransaction(); glbCommand.Transaction = glbTransaction; glbCommand.Connection = glbConnection; glbCommand.CommandType = CommandType.Text; glbCommand.CommandText = sql; glbCommand.Parameters.Clear(); for (i = 0; i < count; i++) { if (pIdentityColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper()) { glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]); } } glbCommand.ExecuteNonQuery(); glbTransaction.Commit(); CloseConnect(); flag = true; } catch { glbTransaction.Rollback(); CloseConnect(); } } return flag; }
public Boolean UpdateTable(string pTable, DataRow pRow, string pColumn, object pValueKey, bool pIsUpdateKey) { Boolean flag = false; string Parameter = ""; string sql; int i, count; if (pRow != null) { count = pRow.Table.Columns.Count; for (i = 0; i < count - 1; i++) { if (pIsUpdateKey == true) { Parameter = Parameter + "[" + pRow.Table.Columns[i].ColumnName + "] = ?,"; } else { if (pColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper()) { Parameter = Parameter + "[" + pRow.Table.Columns[i].ColumnName + "] = ?,"; } } } Parameter = Parameter + pRow.Table.Columns[i].ColumnName + " = ?"; sql = "Update " + pTable + " Set " + Parameter + " Where [" + pColumn + "] = ?"; try { OpenConnect(); glbTransaction = glbConnection.BeginTransaction(); glbCommand.Transaction = glbTransaction; glbCommand.Connection = glbConnection; glbCommand.CommandType = CommandType.Text; glbCommand.CommandText = sql; glbCommand.Parameters.Clear(); for (i = 0; i < count; i++) { if (pIsUpdateKey == true) { glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]); } else { if (pColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper()) { glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]); } } } glbCommand.Parameters.AddWithValue("@ValueKey", pValueKey); glbCommand.ExecuteNonQuery(); glbTransaction.Commit(); CloseConnect(); flag = true; } catch { glbTransaction.Rollback(); CloseConnect(); } } return flag; }
public bool CreateQuestion(Question _question) { bool result = false; try { Conn = db.openConn(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbQuestion(ProgramID,Subject,Created,MemberType,UserName)"); sb.Append(" VALUES ("); sb.Append(" '" + _question.ProgramID + "',"); sb.Append(" '" + _question .Subject + "',"); sb.Append(" now(),"); sb.Append(" '" + _question.MemberType + "',"); sb.Append(" '" + _question.UserName + "')"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.ExecuteNonQuery(); tr.Commit(); result = true; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public Boolean DeleteTable(string pTable, string pListColumn, object[] pValues, ref string ErrorCode) { Boolean flag = false; string sql = ""; try { string[] ListColumn = pListColumn.Split(';'); for (int i = 0; i < pValues.Length; i++) { sql += "[" + ListColumn[i] + "] = ? and "; } sql = sql.Substring(0, sql.Length - 4); sql = "Delete From [" + pTable + "] where " + sql; OpenConnect(); glbTransaction = glbConnection.BeginTransaction(); glbCommand.Transaction = glbTransaction; glbCommand.Connection = glbConnection; glbCommand.CommandType = CommandType.Text; glbCommand.CommandText = sql; glbCommand.Parameters.Clear(); for (int i = 0; i < pValues.Length; i++) { glbCommand.Parameters.AddWithValue("@Par0" + i.ToString(), pValues[i]); } glbCommand.ExecuteNonQuery(); glbTransaction.Commit(); CloseConnect(); flag = true; } catch (OleDbException ex) { ErrorCode = ex.ErrorCode.ToString(); glbTransaction.Rollback(); CloseConnect(); } return flag; }
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 int UpdateSupiler(Supiler updateSupiler) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append(" UPDATE tbSupiler "); sb.Append(" SET SPName='" + updateSupiler.SPName +"',"); sb.Append(" Address='" + updateSupiler.Address + "',"); sb.Append(" PersonContact='" + updateSupiler.PersonContact + "',"); sb.Append(" Phone='" + updateSupiler.Phone + "',"); sb.Append(" Fax='" + updateSupiler.Fax + "'"); sb.Append(" WHERE (SPCode='" + updateSupiler.SPCode + "')"); string sqlUpdate; sqlUpdate = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlUpdate; com.Transaction = tr; com.Parameters.Clear(); com.ExecuteNonQuery(); tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public static void RollbackTransaction() { Transaction.Rollback(); Transaction = null; }
/// <summary> /// 执行SQL 无返回 /// </summary> /// <param name="sql">SQL语句</param> public static void ExecuteNonQuerySql(String sql) { try { Console.WriteLine(sql); conn = new OleDbConnection(constr); conn.Open(); OTrans = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans); cmd.ExecuteNonQuery(); OTrans.Commit(); } catch (Exception ex) { OTrans.Rollback(); throw ex; } finally { conn.Close(); } }
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; }
/// <summary> /// 执行存储过程返回参数集合,只输入存储过程名即可不能带EXEC /// </summary> /// <param name="sql">存储过程名</param> /// <param name="pars">参数数组</param> /// <returns>存储过程参数集合</returns> public static OleDbParameterCollection ExecuteScalarProcedure(String sql, OleDbParameter[] pars) { try { Console.WriteLine(sql); conn = new OleDbConnection(constr); conn.Open(); OTrans = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(pars); cmd.ExecuteNonQuery(); OTrans.Commit(); return cmd.Parameters; } catch (Exception ex) { OTrans.Rollback(); throw ex; } finally { conn.Close(); } }
public bool CreateViewWebSite(ViewWebSite _view) { bool result = false; try { Conn = db.openConn(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbView(IP,UserName,Page,VDate)"); sb.Append(" VALUES ("); sb.Append(" '" + _view.IP + "',"); sb.Append(" '" + _view.User + "',"); sb.Append(" '" + _view.Page + "',"); sb.Append(" now() )"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.ExecuteNonQuery(); tr.Commit(); result = true; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public int UpdateRent(IList<RentDetail> updateRentDetails,string _whCode) { int result = -1; try { Conn = db.openConnAccess(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); foreach (RentDetail r in updateRentDetails) { sb.Remove(0, sb.Length); sb.Append(" UPDATE tbRentDetail "); sb.Append(" SET NumberReturn='" + r.NumberReturn + "',"); sb.Append(" Physical='" + r.Physical + "',"); sb.Append(" Penalty='" + r.Penalty + "'"); sb.Append(" WHERE (RentCode='" + r.RentCode + "')"); sb.Append(" AND (PCode='" + r.PCode + "')"); string sqlUpdate; sqlUpdate = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlUpdate; com.Transaction = tr; com.Parameters.Clear(); com.ExecuteNonQuery(); ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _whCode); if (pOnwarehouse != null) { Product p = serviceProduct.getByCode(r.PCode); pOnwarehouse.UCode = p.UCode; Unit u = serviceUnit.getByCode(p.UCode); pOnwarehouse.QtyWithUnit = u.QtyUnit; int oldblance = pOnwarehouse.RealQty; int newbalnce = oldblance + r.NumberRent; pOnwarehouse.RealQty = newbalnce; int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse); if (tmp > 0) { Console.WriteLine("update pOnwarehouse Complate"); } } } tr.Commit(); result = 1; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public Boolean DeleteTable(string pTable, string pColumn, object pValueKey, ref string ErrorCode) { Boolean flag = false; string sql; try { sql = "Delete From [" + pTable + "] where [" + pColumn + "] = ?"; OpenConnect(); glbTransaction = glbConnection.BeginTransaction(); glbCommand.Transaction = glbTransaction; glbCommand.Connection = glbConnection; glbCommand.CommandType = CommandType.Text; glbCommand.CommandText = sql; glbCommand.Parameters.Clear(); glbCommand.Parameters.AddWithValue("@Par01", pValueKey); glbCommand.ExecuteNonQuery(); glbTransaction.Commit(); CloseConnect(); flag = true; } catch (OleDbException ex) { ErrorCode = ex.ErrorCode.ToString(); glbTransaction.Rollback(); CloseConnect(); } return flag; }
public bool CreateMember(Member _member) { bool result = false; try { Conn = db.openConn(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbMember(UserName,PasswordMember,Email,MemberType)"); sb.Append(" VALUES ("); sb.Append(" '" + _member.UserName + "',"); sb.Append(" '" + _member.Password + "',"); sb.Append(" '" + _member.Email + "',"); sb.Append(" '" + _member.MemmerType + "')"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.ExecuteNonQuery(); tr.Commit(); result = true; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
/// <summary> /// 执行SQL返回表第一行第一列 /// </summary> /// <param name="sql">SQL语句</param> public static object ExecuteScalarSql(String sql) { try { Console.WriteLine(sql); conn = new OleDbConnection(constr); conn.Open(); OTrans = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans); object ores = cmd.ExecuteScalar(); OTrans.Commit(); return ores; } catch (Exception ex) { OTrans.Rollback(); throw ex; } finally { conn.Close(); } }
public bool CreateQuestionDetail(QuestionDetail _questionDetail) { bool result = false; try { Conn = db.openConn(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbQuestionDetail(QID,AnsQus,AnsDate,AnsBy)"); sb.Append(" VALUES ("); sb.Append(" '" + _questionDetail.QID + "',"); sb.Append(" '" + _questionDetail.AnsQus + "',"); sb.Append(" now(),"); sb.Append(" '" + _questionDetail.AnsBy + "')"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.ExecuteNonQuery(); tr.Commit(); result = true; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
/// <summary> /// 执行SQL返回OleDbDataReader /// </summary> /// <param name="sql">SQL语句</param> /// <returns>返回OleDbDataReader</returns> public static OleDbDataReader DrFillSql(String sql) { try { Console.WriteLine(sql); conn = new OleDbConnection(constr); conn.Open(); OTrans = conn.BeginTransaction(); OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans); OleDbDataReader dr = cmd.ExecuteReader(); OTrans.Commit(); return dr; } catch (Exception ex) { OTrans.Rollback(); throw ex; } finally { conn.Close(); } }
public bool CreateComment(Comment _comment) { bool result = false; try { Conn = db.openConn(); tr = Conn.BeginTransaction(); sb = new StringBuilder(); sb.Remove(0, sb.Length); sb.Append("INSERT INTO tbComment(UserType,IP,Subject,CommentDecription)"); sb.Append(" VALUES ("); sb.Append(" '" + _comment.UserType + "',"); sb.Append(" '" + _comment.IP + "',"); sb.Append(" '" + _comment.Subject + "',"); sb.Append(" '" + _comment.CommentDecription + "')"); string sqlSave; sqlSave = sb.ToString(); com = new OleDbCommand(); com.Connection = Conn; com.CommandText = sqlSave; com.Transaction = tr; com.Parameters.Clear(); com.ExecuteNonQuery(); tr.Commit(); result = true; } catch (Exception ex) { tr.Rollback(); Conn.Close(); return result; throw ex; } finally { Conn.Close(); } return result; }
public void ExecuteBatch(ArrayList sqlQueryList) { string[] queryParts = null; string[] separator = { "GO", "Go", "gO", "go" }; try { Connect(); foreach (string query in sqlQueryList) { queryParts = query.Split(separator, 2, StringSplitOptions.RemoveEmptyEntries); _dbTransacao = _dbConexao.BeginTransaction(); try { foreach (string queryPart in queryParts) { _dbComando.Transaction = _dbTransacao; _dbComando.CommandText = queryPart; Execute(query); } _dbTransacao.Commit(); } catch (Exception e) //debug { _dbTransacao.Rollback(); } } Disconnect(); } catch (Exception e) { throw e; } }