C# OracleConnection.BeginTransaction方法代码示例

        public void Execute(string name, Action<OracleConnection> a, bool useTransaction = false)
        {
            ConnectionString cs = Database.GetConnectionString(name);

            OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder
            {
                DataSource = cs.DataSource,
                UserID = cs.UserID,
                Password = cs.Password,
                PersistSecurityInfo = cs.PersistSecurityInfo
            };

            using (OracleConnection conn = new OracleConnection(builder.ConnectionString))
            {
                conn.Open();

                if (useTransaction)
                {
                    using (OracleTransaction trans = conn.BeginTransaction())
                    {
                        a(conn);
                        trans.Commit();
                    }
                }
                else
                    a(conn);

                conn.Close();
            }
        }

 public static OracleConnection GetConnection()
 {
     if (conn != null) {
         if (conn.State == System.Data.ConnectionState.Open) {
             return conn;
         }
     }
     string DBName = "RA00C000";
     string DBUser = "voshod";
     string DBPass = "voshod";
     string[] args = Environment.GetCommandLineArgs();
     if (args.Length > 3) {
         DBName = args[1];
         DBUser = args[2];
         DBPass = args[3];
     }
     oradb = "Data Source=" + DBName + ";User Id=" + DBUser + ";Password=" + DBPass + ";";
     try {
         conn = new OracleConnection(oradb);
         conn.Open();
         transaction = conn.BeginTransaction();
         return conn;
     } catch (Exception) {
         return null;
     }
 }

 /// <summary>
 /// Connects to the database.
 /// </summary>
 /// <param name="connDetails"></param>
 public SqlRunner(ConnectionDetails connDetails)
 {
     this.connDetails = connDetails;
     string connStr = GetConnectionString();
     log.Info("connecting to [" + connDetails.Name + "] with connection string " + connStr);
     conn = new OracleConnection(connStr);
     conn.Open();
     currentTransaction = conn.BeginTransaction();
 }

        public bool Fill()
        {
            try {
                conn = new OracleConnection(GetConnectionString());
                conn.Open();
                transaction = conn.BeginTransaction();
            } catch (Exception e) {
                MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message);
                conn.Close();
                conn.Dispose();
                return false;
            }

            cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)";
            try {
                dr = cmd.ExecuteReader();
                while (dr.Read()) {
                    if (dr.IsDBNull(0) || dr.IsDBNull(1)) {
                        MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n");
                        break;
                    } else {
                        UsersGrid.Rows.Add();
                        UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0);
                        UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1);
                    }

                }
                dr.Close();
                dr.Dispose();
            } catch (Exception e) {
                MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message);
            }
            if (UsersGrid.Rows.Count == 0) {
                MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information);
                ConvertButton.Enabled = false;
            }
            FillDeloDepartmentsColumn();
            return true;
        }

		public IDatabaseQuery StartQuery(bool withTransaction)
		{
			var connection = new OracleConnection(ConnectionString);
			OracleTransaction transaction = null;
			if (withTransaction)
			{
				try
				{
					connection.Open();
				}
				catch (Exception ex)
				{
					TraceSource.TraceEvent(TraceEventType.Error, 5010, "{0}", ex);
					try { connection.Close(); }
					catch { }
					OracleConnection.ClearAllPools();
					connection = new OracleConnection(ConnectionString);
					connection.Open();
				}
				transaction = connection.BeginTransaction();
			}
			IDatabaseQuery query;
			try
			{
				query = QueryFactory(connection, transaction);
			}
			catch (Exception ex)
			{
				TraceSource.TraceEvent(TraceEventType.Critical, 5101, "{0}", ex);
				TraceSource.TraceEvent(TraceEventType.Information, 5101, "Transactions: {0}, connections: {1}", OpenTransactions.Count, OpenConnections.Count);
				throw;
			}
			if (withTransaction)
				OpenTransactions.TryAdd(query, transaction);
			OpenConnections.TryAdd(query, connection);
			return query;
		}

 /// <summary>
 ///打开(如果必要的话),指定一个命令、连接、事务、参数类型和参数      
 /// </summary>
 /// <param name="command">OracleCommand</param>
 /// <param name="connection">OracleConnection</param>
 /// <param name="transaction">OracleTransaction 或  'null'</param>
 /// <param name="commandType"> CommandType (stored procedure, text, etc.)</param>
 /// <param name="commandText">存储过程名称或SQL语句</param> 
 /// <param name="commandParameters">OracleParameter[]</param>
 private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
 {
     if (connection.State != ConnectionState.Open)
     {
         connection.Open();
     }
     command.Connection = connection;
     command.CommandText = commandText;
     if (transaction != null)
     {
         //command.Transaction = transaction;
         transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
     }
     command.CommandType = commandType;
     if (commandParameters != null)
     {
         AttachParameters(command, commandParameters);
     }
     return;
 }

        /// <summary>
        /// 通过事务执行SQL语句命令
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="SQLStringList"></param>
        public static void ExecuteSQLTransaction(OracleConnection connection, List<string> SQLStringList)
        {
            connection.Open();
            OracleCommand command = new OracleCommand();
            command.Connection = connection;
            OracleTransaction transaction = connection.BeginTransaction();
            if (transaction != null)
            {
                //command.Transaction = transaction;
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            try
            {
                for (int i = 0; i < SQLStringList.Count; i++)
                {
                    string str = SQLStringList[i].ToString();
                    if (str.Trim().Length > 1)
                    {
                        command.CommandText = str;
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }
            catch (OracleException exception)
            {
                transaction.Rollback();
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }

        }

        public JsonResult UpdateRecords(string id, string records)
        {
            OutgoingNotice notice = repository.FindByNotCache(id);
            if (notice == null)
            {
                return Json(new { success = false, message = "未找到指定的发料通知单" });
            }

            if (string.IsNullOrEmpty(records))
            {
                return Json(new { success = false, message = "发料记录不能为空" });
            }

            IPipeOutgoingRecordRepository porRepository = RepositoryFactory.GetRepository<IPipeOutgoingRecordRepository, PipeOutgoingRecord>(unitOfWork);
            IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork);

            JavaScriptSerializer serializer = new JavaScriptSerializer();
            Account onlineAccount = UserSession.OnlineAccount;

            IList<PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord> recordContracts = serializer.Deserialize<IList<PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord>>(records);
            IList<PipeOutgoingRecord> existsRecords = notice.Records;
            IList<long> pipeIds = new List<long>();
            float totalWorkLength = 0f;
            float totalBusinessLength = 0f;
            float totalWeight = 0f;
            int count = 0;

            OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
            conn.Open();
            OracleTransaction tran = conn.BeginTransaction();

            try
            {
                foreach (PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord rc in recordContracts)
                {
                    PDCPMS.Model.Common.Pipe pipe = null;
                    PipeOutgoingRecord record = null;
                    if (string.IsNullOrEmpty(rc.Key))
                    {
                        pipe = pipeRepository.FindBy(rc.PipeId);
                        if (pipe == null ||
                           !PipePolicy.CanSentout(pipe) ||
                            pipeIds.Contains(rc.PipeId))
                        {
                            continue;
                        }

                        if ((pipe.State & PipeState.SentingOut) != PipeState.SentingOut)
                        {
                            pipe.State |= PipeState.SentingOut;
                        }

                        //pipeRepository[pipe.Key] = pipe;
                        pipeRepository.UpdatedPipe(pipe, conn, tran);

                        record = new PipeOutgoingRecord(Guid.NewGuid().ToString("N"), notice, pipe, DateTime.Now);
                        //porRepository.Add(record);

                        porRepository.AddPipeOutgoingRecord(record, conn, tran);

                        pipeIds.Add(rc.PipeId);
                    }
                    else
                    {
                        record = existsRecords.FirstOrDefault(r => r.Key.ToString() == rc.Key);
                        if (record == null)
                        {
                            continue;
                        }
                        pipe = record.Pipe;

                        pipeIds.Add((long)pipe.Key);
                        existsRecords.Remove(record);
                    }

                    count += 1;
                    totalBusinessLength += pipe.BusinessLength.HasValue ? pipe.BusinessLength.Value : 0;
                    totalWorkLength += pipe.WorkLength.HasValue ? pipe.WorkLength.Value : 0;
                    totalWeight += pipe.Weight.HasValue ? pipe.Weight.Value : 0;
                }
                // 已删除
                foreach (PipeOutgoingRecord record in existsRecords)
                {
                    PDCPMS.Model.Common.Pipe pipe = record.Pipe;
                    if ((pipe.State & PipeState.SentingOut) == PipeState.SentingOut)
                    {
                        pipe.State ^= PipeState.SentingOut;
                    }
                    //pipeRepository[pipe.Key] = pipe;
                    pipeRepository.UpdatedPipe(pipe, conn, tran);

                    //porRepository.Remove(record);
                    porRepository.RemovePipeOutgoingRecord(record, conn, tran);
                }

                notice.SentBusinessLength = totalBusinessLength;
                notice.SentCount = count;
                notice.SentWeight = totalWeight / 1000;
                notice.SentWorkLength = totalWorkLength;
                notice.Sender = onlineAccount;
//.........这里部分代码省略.........

        public OracleTransaction BeginTransaction(OracleConnection conn)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            return conn.BeginTransaction();
        }

        /// <summary>
        /// Na Update wordt het aantal bijgewerkte rijen teruggegeven.
        /// </summary>
        /// <param name="oldBusinessObject"></param>
        /// <param name="newBusinessObject"></param>
        /// <returns></returns>
        public int Update(BeheerContextEntity oldBusinessObject, BeheerContextEntity newBusinessObject)
        {
            int rowsAffected = 0;
            using (OracleConnection conn = new OracleConnection(_oradb))
            {
                string sqlUpdate = "update {0} set {1} ='{2}' where {1} = '{3}'";
                string tableName = oldBusinessObject.Tablename;
                string columnName = oldBusinessObject.DataKeyName;
                sqlUpdate = string.Format(sqlUpdate,
                    tableName,
                    columnName,
                    newBusinessObject.DataKeyValue,
                    oldBusinessObject.DataKeyValue);
                conn.Open();

                var trans = conn.BeginTransaction();
                try
                {
                    var cmd = new OracleCommand
                            {
                                Connection = conn,
                                CommandText = sqlUpdate,
                                CommandType = CommandType.Text
                            };
                    cmd.Connection = conn;

                    rowsAffected = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
            }
            return rowsAffected;
        }

        /// <summary>
        /// Na Delete wordt het aantal verwijderde rijen teruggegeven.
        /// </summary>
        /// <param name="businessObject"></param>
        /// <returns></returns>
        public int Delete(BeheerContextEntity businessObject)
        {
            int rowsaffected = 0;
            using (OracleConnection conn = new OracleConnection(_oradb))
            {
                conn.Open();
                var trans = conn.BeginTransaction();

                try
                {
                    string sqlUpdate =
                @"
                        DELETE FROM {0}
                        WHERE {1} = '{2}'
                    ";
                    sqlUpdate = string.Format(sqlUpdate, businessObject.Tablename,
                        businessObject.DataKeyName, businessObject.DataKeyValue);
                    var cmd = new OracleCommand
                    {
                        Connection = conn,
                        CommandText = sqlUpdate,
                        CommandType = CommandType.Text
                    };
                    cmd.Connection = conn;

                    rowsaffected = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }

            }
            return rowsaffected;
        }

        public JsonResult Approve(string code, string ReportNO)
        {
            NormalCheckTask task = repository.FindByCode(code);
            Account onlineAccount = UserSession.OnlineAccount;

            if (task == null)
            {
                return Json(new { success = false, message = "未找到指定常检任务" });
            }

            if (task.Approver != null)
            {
                return Json(new { success = false, message = string.Format("该批常检任务已完成批准,报告号为{0}，请不要重复批准！", ReportNO) });
            }

            Regex codePattern = new Regex(@"^[-|\.|a-z|A-Z|0-9]+$");
            if (!codePattern.IsMatch(ReportNO))
            {
                return Json(new { success = false, message = "报告号格式不正确" });
            }

            if (null != repository.FindByReportNO(ReportNO,onlineAccount))
            {
                return Json(new { success = false, message = string.Format("报告号{0}已经存在，请尝试使用其它报告号", ReportNO) });
            }

            #region 回滚事务
            OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
            conn.Open();
            OracleTransaction tran = conn.BeginTransaction();

            try
            {
            #endregion

                task.ReportNO = ReportNO;
                task.Approver = UserSession.OnlineAccount;
                task.DateApproved = DateTime.Now;
                //repository[task.Key] = task;
                repository.UpdatedTask(task, conn, tran);

                // Create Records
                INormalCheckRecordRepository recordRepository = RepositoryFactory.GetRepository<INormalCheckRecordRepository, NormalCheckRecord>(unitOfWork);

                TransferTask transferTask = task.TransferTask;
                int orederNO = 0;
                DateTime now = DateTime.Now;

                transferTask.Items.ForEach(transferItem =>
                {
                    transferItem.Records.ForEach(transferRecord =>
                    {
                        orederNO++;

                        NormalCheckRecord r = new NormalCheckRecord(Guid.NewGuid().ToString("N"),
                            task,
                            transferRecord.Pipe,
                            now,
                            orederNO);

                        //recordRepository.Add(r);
                        recordRepository.AddRecord(r, conn, tran);
                    });
                });
                //unitOfWork.Commit();

                #region 回滚事务
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return Json(new { success = true, message = "没有成功批准检测任务，请重新批准！" + ex });
            }
                #endregion

            return Json(new { success = true, message = "成功批准检测任务！" });
        }

        public JsonResult UpdateCheckResults(string code)
        {
            NormalCheckTask task = repository.FindByCode(code);
            if (task == null)
            {
                return Json(new { success = false, message = "未找到指定的常检任务" });
            }

            if ((int)task.State < (int)NormalCheckTaskState.Received)
            {
                return Json(new { success = false, message = "当前常检任务沿不允许更新检测数据" });
            }
            if (task.State == NormalCheckTaskState.Verified)
            {
                return Json(new { success = false, message = "当前常检任务已审核存档，不允许被编辑" });
            }

            JavaScriptSerializer serializer = new JavaScriptSerializer();
            IList<PDCPMS.DataContracts.NormalCheck.NormalCheckRecord> recordContracts = null;
            try
            {
                recordContracts = serializer.Deserialize<IList<PDCPMS.DataContracts.NormalCheck.NormalCheckRecord>>(Request.Form["Records"]);
            }
            catch
            {
                return Json(new { success = false, message = "请求数据有误" });
            }

            INormalCheckRecordRepository recordRepository = RepositoryFactory.GetRepository<INormalCheckRecordRepository, NormalCheckRecord>(unitOfWork);
            IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork);

            IList<String> codes = new List<string>();

            #region 回滚事务
            OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString);
            conn.Open();
            OracleTransaction tran = conn.BeginTransaction();

            try
            {
            #endregion

            foreach (PDCPMS.DataContracts.NormalCheck.NormalCheckRecord rc in recordContracts)
            {
                NormalCheckRecord record = recordRepository.FindBy(rc.Key);
                if (record == null)
                    continue;

                record.IsBodyValid = rc.IsBodyValid;
                record.IsCheckBlindCrackValid = rc.IsCheckBlindCrackValid;
                record.IsCheckBodyCrackValid = rc.IsCheckBodyCrackValid;
                record.IsClosenessValid = rc.IsClosenessValid;
                record.IsEndValid = rc.IsEndValid;
                record.IsPressureValid = rc.IsPressureValid;
                record.IsScrewValid = rc.IsScrewValid;
                record.IsStraightValid = rc.IsStraightValid;
                record.IsThicknessValid = rc.IsThicknessValid;
                record.IsUnBlockValid = rc.IsUnBlockValid;
                record.IsValid = rc.IsValid;
                record.MaleP = rc.MaleP;
                record.FemaleA = rc.FemaleA;
                record.PressureDetectValue = rc.PressureDetectValue;
                record.PressureHoldValue = rc.PressureHoldValue;

                //2010-11-12 by huncherby
                record.OutDiameter = rc.OutDiameter;
                record.ThicknessValid = rc.ThicknessValid;
                record.CheckUnBlock = rc.CheckUnBlock;
                record.MinOutDiameter = rc.MinOutDiameter;
                record.Comment = rc.Comment;

                //recordRepository[record.Key] = record;
                recordRepository.UpdateRecord(record,conn,tran);

                if (!string.IsNullOrEmpty(rc.Code))
                {
                    rc.Code = rc.Code.ToUpper().Trim();

                    if (codes.Contains(rc.Code))
                    {
                        return Json(new { success = false, message = "存在重复的工序号" });
                    }

                    codes.Add(rc.Code);

                    record.Pipe.Code = task.WorkNO + rc.Code;
                }
                else
                {
                    record.Pipe.Code = string.Empty;
                }
                record.Pipe.WorkLength = rc.WorkLength;
                //pipeRepository[record.Pipe.Key] = record.Pipe;
                pipeRepository.UpdatedPipe(record.Pipe,conn,tran);
            }

            task.ReportComment = Request.Form["ReportComment"];
            task.CheckConclusion = Request.Form["CheckConclusion"];
            task.Finisher = UserSession.OnlineAccount;
            task.DateFinished = DateTime.Now;
//.........这里部分代码省略.........

        /**
         * @brief Adds a user to the database
         * @param User user The user to add
         **/
        public static int RegisterNewUser(User user)
        {
            int lastInsertedId = -1;

            bool error = false;
            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
            OracleTransaction transaction = null;
            try
            {
                //Open the connection
                connection.Open();
                //Start a local transaction
                transaction = connection.BeginTransaction();

                //First we add the user to the database
                //Setup the command to run
                OracleCommand command = new OracleCommand();
                command.Connection = connection;
                command.Transaction = transaction;

                //Get the next id
                command.CommandText = DatabaseSchema.SELECT_USERS_NEXT_ID;
                int user_id = Convert.ToInt32(command.ExecuteScalar());

                if (user_id > 0)
                {
                    command.Parameters.Clear();
                    command.CommandText = INSERT_USER;

                    //First we set the parameters
                    command.Parameters.Add("user_id", user_id);
                    command.Parameters.Add("password", user.Password);
                    command.Parameters.Add("role_id", (int)user.Role_Id);
                    command.Parameters.Add("name", user.Name);
                    command.Parameters.Add("email", user.Email);
                    command.Parameters.Add("registered", DatabaseSchema.USER_NOT_REGISTERED);
                    command.Parameters.Add("mobile", user.Mobile);
                    command.Parameters.Add("address", user.Address);
                    command.Parameters.Add("paypal_id", user.Paypal_Id);
                    command.Parameters.Add("betfair_id", user.Betfair_Id);

                    //Run the command
                    command.ExecuteNonQuery();
                    lastInsertedId = user_id;
                }
            }
            catch (Exception exception)
            {
                error = true;
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                if (transaction != null)
                {
                    if (error)
                    {
                        transaction.Rollback();
                    }
                    else
                    {
                        transaction.Commit();
                    }
                }
                //Closes the datareader and connection to the database
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return lastInsertedId;
        }

        /**
         * @brief Adds a user to the database
         * @param User user The user to add
         **/
        public static int UpdateUserSettings(User user)
        {
            int result = -1;

            bool error = false;
            OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE));
            OracleTransaction transaction = null;
            try
            {
                //Open the connection
                connection.Open();
                //Start a local transaction
                transaction = connection.BeginTransaction();

                //First we add the user to the database
                //Setup the command to run
                OracleCommand command = new OracleCommand();
                command.Connection = connection;
                command.Transaction = transaction;

                if (user.User_Id > 0)
                {
                    command.Parameters.Clear();
                    command.BindByName = true;
                    if (user.Password != null && user.Password.Length > 0)
                    {
                        command.CommandText = UPDATE_USER_PASSWORD;
                        command.Parameters.Add(":password", user.Password);
                    }
                    else
                    {
                        command.CommandText = UPDATE_USER_NO_PASSWORD;
                    }

                    //First we set the parameters
                    command.Parameters.Add(":user_id", user.User_Id);
                    command.Parameters.Add(":name", user.Name);
                    command.Parameters.Add(":mobile", user.Mobile);
                    command.Parameters.Add(":address", user.Address);
                    command.Parameters.Add(":paypal_id", user.Paypal_Id);
                    command.Parameters.Add(":betfair_id", user.Betfair_Id);

                    Tools.printDebug(command.CommandText.ToString());
                    Tools.printDebug(command.Parameters.ToString());

                    //Run the command
                    result = command.ExecuteNonQuery();
                    Tools.printDebug("UPDATE USER RESULT: " + result);
                }
            }
            catch (Exception exception)
            {
                error = true;
                Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception);
                throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception);
            }
            finally
            {
                if (transaction != null)
                {
                    if (error)
                    {
                        transaction.Rollback();
                    }
                    else
                    {
                        transaction.Commit();
                    }
                }
                //Closes the datareader and connection to the database
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return result;
        }