C# OleDbTransaction.Rollback方法代码示例

        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 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 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 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 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;
        }

        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;
        }

 //执行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 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 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 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;
        }

 /// <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();
     }
 }