C# AdoJobStore.ConnectionAndTransactionHolder类代码示例

        public TriggerPropertyBundle LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
        {
            using (IDbCommand cmd = CommandAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, TablePrefix, SchedNameLiteral)))
            {
                CommandAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
                CommandAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);

                using (IDataReader rs = cmd.ExecuteReader())
                {
                    if (rs.Read())
                    {
                        int repeatCount = rs.GetInt32(AdoConstants.ColumnRepeatCount);
                        long repeatInterval = rs.GetInt64(AdoConstants.ColumnRepeatInterval);
                        int timesTriggered = rs.GetInt32(AdoConstants.ColumnTimesTriggered);

                        SimpleScheduleBuilder sb = SimpleScheduleBuilder.Create()
                            .WithRepeatCount(repeatCount)
                            .WithInterval(TimeSpan.FromMilliseconds(repeatInterval));

                        string[] statePropertyNames = {"timesTriggered"};
                        object[] statePropertyValues = {timesTriggered};

                        return new TriggerPropertyBundle(sb, statePropertyNames, statePropertyValues);
                    }
                }
                throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, TablePrefix, SchedNameLiteral));
            }
        }

		/// <summary> 
		/// Grants a lock on the identified resource to the calling thread (blocking
		/// until it is available).
		/// </summary>
		/// <returns>True if the lock was obtained.</returns>
		public virtual bool ObtainLock(DbMetadata metadata, ConnectionAndTransactionHolder conn, string lockName)
		{
			lock (this)
			{
				lockName = String.Intern(lockName);

				if (!IsLockOwner(conn, lockName))
				{
					while (locks.Contains(lockName))
					{
						try
						{
							Monitor.Wait(this);
						}
						catch (ThreadInterruptedException)
						{
							
						}
					}

					ThreadLocks.Add(lockName);
					locks.Add(lockName);
				}

				return true;
			}
		}

        /// <summary>
        /// Execute the SQL that will lock the proper database row.
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="lockName"></param>
        /// <param name="expandedSQL"></param>
        /// <param name="expandedInsertSQL"></param>
        protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL, string expandedInsertSQL)
        {
            try
            {
                using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
                {
                    AdoUtil.AddCommandParameter(cmd, "lockName", lockName);

                    int numUpdate = cmd.ExecuteNonQuery();

                    if (numUpdate < 1)
                    {
                        using (IDbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSQL))
                        {
                            AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);

                            int res = cmd2.ExecuteNonQuery();

                            if (res != 1)
                            {
                                throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
                                    "No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
                                    " for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
                            }
                        }
                    }
                }
            }
            catch (Exception sqle)
            {
                throw new LockException(
                    "Failure obtaining db row lock: " + sqle.Message, sqle);
            }
        }

    /**
     * Execute the SQL select for update that will lock the proper database row.
     */
    protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL)
    {
        try {
            using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
            {
                AdoUtil.AddCommandParameter(cmd, 1, "lockName", lockName);

                using (IDataReader rs = cmd.ExecuteReader())
                {
                    if (Log.IsDebugEnabled)
                    {
                        Log.Debug("Lock '" + lockName + "' is being obtained: " + Thread.CurrentThread.Name);
                    }
                    
                    if (!rs.Read())
                    {
                        throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix("No row exists in table " + TablePrefixSubst + TableLocks + " for lock named: " + lockName, TablePrefix));
                    }
                }
            }
        } catch (Exception sqle) {

            if (Log.IsDebugEnabled) {
                Log.Debug(
                    "Lock '" + lockName + "' was not obtained by: " + 
                    Thread.CurrentThread.Name);
            }
            
            throw new LockException("Failure obtaining db row lock: "
                    + sqle.Message, sqle);
        } 
    
    }

        /// <summary>
        /// Execute the SQL that will lock the proper database row.
        /// </summary>
        protected override async Task ExecuteSQL(Guid requestorId, ConnectionAndTransactionHolder conn, string lockName, string expandedSql, string expandedInsertSql)
        {
            Exception lastFailure = null;
            for (int i = 0; i < RetryCount; i++)
            {
                try
                {
                    if (!await LockViaUpdate(requestorId, conn, lockName, expandedSql).ConfigureAwait(false))
                    {
                        await LockViaInsert(requestorId, conn, lockName, expandedInsertSql).ConfigureAwait(false);
                    }
                    return;
                }
                catch (Exception e)
                {
                    lastFailure = e;
                    if (i + 1 == RetryCount)
                    {
                        Log.DebugFormat("Lock '{0}' was not obtained by: {1}", lockName, requestorId);
                    }
                    else
                    {
                        Log.DebugFormat("Lock '{0}' was not obtained by: {1} - will try again.", lockName, requestorId);
                    }

                    await Task.Delay(TimeSpan.FromSeconds(1)).ConfigureAwait(false);
                }
            }
            if (lastFailure != null)
            {
                throw new LockException("Failure obtaining db row lock: " + lastFailure.Message, lastFailure);
            }
        }

        public TriggerPropertyBundle LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
        {
            using (IDbCommand cmd = CommandAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral)))
            {
                CommandAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
                CommandAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);

                using (IDataReader rs = cmd.ExecuteReader())
                {
                    if (rs.Read())
                    {
                        string cronExpr = rs.GetString(AdoConstants.ColumnCronExpression);
                        string timeZoneId = rs.GetString(AdoConstants.ColumnTimeZoneId);

                        CronScheduleBuilder cb = CronScheduleBuilder.CronSchedule(cronExpr);
  
                        if (timeZoneId != null)
                        {
                            cb.InTimeZone(TimeZoneInfo.FindSystemTimeZoneById(timeZoneId));
                        }

                        return new TriggerPropertyBundle(cb, null, null);
                    }
                }

                throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral));
            }
        }

 public IDbCommand PrepareCommand(ConnectionAndTransactionHolder cth, string commandText)
 {
     IDbCommand cmd = dbProvider.CreateCommand();
     cmd.CommandText = commandText;
     cmd.Connection = cth.Connection;
     cmd.Transaction = cth.Transaction;
     return cmd;
 }

        public int DeleteExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
        {
            using (IDbCommand cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlDeleteCronTrigger, TablePrefix, SchedNameLiteral)))
            {
                DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
                DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);

                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Grants a lock on the identified resource to the calling thread (blocking
        /// until it is available).
        /// </summary>
        /// <returns>True if the lock was obtained.</returns>
        public virtual Task<bool> ObtainLock(Guid requestorId, ConnectionAndTransactionHolder conn, string lockName)
        {
            if (log.IsDebugEnabled())
            {
                log.Debug($"Lock '{lockName}' is desired by: {requestorId}");
            }

            lock (syncRoot)
            {
                if (!IsLockOwner(requestorId, lockName))
                {
                    if (log.IsDebugEnabled())
                    {
                        log.Debug($"Lock '{lockName}' is being obtained: {requestorId}");
                    }

                    while (locks.Contains(lockName))
                    {
                        try
                        {
                            Monitor.Wait(syncRoot);
                        }
                        catch (ThreadInterruptedException)
                        {
                            if (log.IsDebugEnabled())
                            {
                                log.Debug($"Lock '{lockName}' was not obtained by: {requestorId}");
                            }
                        }
                    }

                    HashSet<string> requestorLocks;
                    if (!threadLocks.TryGetValue(requestorId, out requestorLocks))
                    {
                        requestorLocks = new HashSet<string>();
                        threadLocks[requestorId] = requestorLocks;
                    }
                    requestorLocks.Add(lockName);
                    locks.Add(lockName);

                    if (log.IsDebugEnabled())
                    {
                        log.Debug($"Lock '{lockName}' given to: {requestorId}");
                    }
                }
                else if (log.IsDebugEnabled())
                {
                    log.DebugException($"Lock '{lockName}' already owned by: {requestorId} -- but not owner!", new Exception("stack-trace of wrongful returner"));
                }

                return Task.FromResult(true);
            }
        }

        /// <summary>
        /// Execute the SQL select for update that will lock the proper database row.
        /// </summary>
        protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL, string expandedInsertSQL)
        {
            try
            {
                using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
                {
                    AdoUtil.AddCommandParameter(cmd, "lockName", lockName);

                    bool found = false;
                    using (IDataReader rs = cmd.ExecuteReader())
                    {
                        if (Log.IsDebugEnabled)
                        {
                            Log.Debug("Lock '" + lockName + "' is being obtained: " + Thread.CurrentThread.Name);
                        }

                        found = rs.Read();
                    }

                    if (!found)
                    {
                        Log.Debug(
                            "Inserting new lock row for lock: '" + lockName + "' being obtained by thread: " +
                            Thread.CurrentThread.Name);

                        using (IDbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSQL))
                        {
                            AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
                            int res = cmd2.ExecuteNonQuery();

                            if (res != 1)
                            {
                                throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
                                    "No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
                                    " for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
                            }
                        }
                    }
                }
            }
            catch (Exception sqle)
            {
                if (Log.IsDebugEnabled)
                {
                    Log.Debug(
                        "Lock '" + lockName + "' was not obtained by: " +
                        Thread.CurrentThread.Name);
                }

                throw new LockException("Failure obtaining db row lock: "
                                        + sqle.Message, sqle);
            }
        }

        public DbCommand PrepareCommand(ConnectionAndTransactionHolder cth, string commandText)
        {
            DbCommand cmd = dbProvider.CreateCommand();
            cmd.CommandText = commandText;
            cth.Attach(cmd);

            if (log.IsDebugEnabled())
            {
                log.DebugFormat("Prepared SQL: {0}", cmd.CommandText);
            }

            return cmd;
        }

        public int InsertExtendedTriggerProperties(ConnectionAndTransactionHolder conn, IOperableTrigger trigger, string state, IJobDetail jobDetail)
        {
            ICronTrigger cronTrigger = (ICronTrigger) trigger;

            using (IDbCommand cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlInsertCronTrigger, TablePrefix, SchedNameLiteral)))
            {
                DbAccessor.AddCommandParameter(cmd, "triggerName", trigger.Key.Name);
                DbAccessor.AddCommandParameter(cmd, "triggerGroup", trigger.Key.Group);
                DbAccessor.AddCommandParameter(cmd, "triggerCronExpression", cronTrigger.CronExpressionString);
                DbAccessor.AddCommandParameter(cmd, "triggerTimeZone", cronTrigger.TimeZone.Id);

                return cmd.ExecuteNonQuery();
            }
        }

        public int InsertExtendedTriggerProperties(ConnectionAndTransactionHolder conn, IOperableTrigger trigger, string state, IJobDetail jobDetail)
        {
            ISimpleTrigger simpleTrigger = (ISimpleTrigger) trigger;

            using (IDbCommand cmd = commandAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlInsertSimpleTrigger, tablePrefix, schedNameLiteral)))
            {
                commandAccessor.AddCommandParameter(cmd, "triggerName", trigger.Key.Name);
                commandAccessor.AddCommandParameter(cmd, "triggerGroup", trigger.Key.Group);
                commandAccessor.AddCommandParameter(cmd, "triggerRepeatCount", simpleTrigger.RepeatCount);
                commandAccessor.AddCommandParameter(cmd, "triggerRepeatInterval", simpleTrigger.RepeatInterval.TotalMilliseconds);
                commandAccessor.AddCommandParameter(cmd, "triggerTimesTriggered", simpleTrigger.TimesTriggered);

                return cmd.ExecuteNonQuery();
            }
        }

		/// <summary> 
		/// Grants a lock on the identified resource to the calling thread (blocking
		/// until it is available).
		/// </summary>
		/// <returns>True if the lock was obtained.</returns>
		public virtual bool ObtainLock(DbMetadata metadata, ConnectionAndTransactionHolder conn, string lockName)
		{
			lock (this)
			{
				lockName = String.Intern(lockName);

				if (log.IsDebugEnabled)
					log.Debug("Lock '" + lockName + "' is desired by: " + Thread.CurrentThread.Name);

				if (!IsLockOwner(conn, lockName))
				{
					if (log.IsDebugEnabled)
					{
						log.Debug("Lock '" + lockName + "' is being obtained: " + Thread.CurrentThread.Name);
					}
					
					while (locks.Contains(lockName))
					{
						try
						{
							Monitor.Wait(this);
						}
						catch (ThreadInterruptedException)
						{
							if (log.IsDebugEnabled)
							{
								log.Debug("Lock '" + lockName + "' was not obtained by: " + Thread.CurrentThread.Name);
							}
						}
					}

					if (log.IsDebugEnabled)
					{
						log.Debug(string.Format(CultureInfo.InvariantCulture, "Lock '{0}' given to: {1}", lockName, Thread.CurrentThread.Name));
					}
					ThreadLocks.Add(lockName);
					locks.Add(lockName);
				}
				else if (log.IsDebugEnabled)
				{
					log.Debug(string.Format(CultureInfo.InvariantCulture, "Lock '{0}' already owned by: {1} -- but not owner!", lockName, Thread.CurrentThread.Name), new Exception("stack-trace of wrongful returner"));
				}

				return true;
			}
		}

        public void TestSelectBlobTriggerWithNoBlobContent()
        {
            var dbProvider = MockRepository.GenerateMock<IDbProvider>();
            var connection = MockRepository.GenerateMock<IDbConnection>();
            var transaction = MockRepository.GenerateMock<IDbTransaction>();
            var command = (IDbCommand) MockRepository.GenerateMock<StubCommand>();
            var dbMetadata = new DbMetadata();
            dbProvider.Stub(x => x.Metadata).Repeat.Any().Return(dbMetadata);

            dbProvider.Stub(x => x.CreateCommand()).Return(command);

            var dataReader = MockRepository.GenerateMock<IDataReader>();
            command.Stub(x => x.ExecuteReader()).Return(dataReader);
            command.Stub(x => x.Parameters).Repeat.Any().Return(new StubParameterCollection());
            command.Stub(x => x.CommandText).Return("").Repeat.Any();
            command.Stub(x => x.CreateParameter()).Repeat.Any().Return(new StubDataParameter());

            var adoDelegate = new StdAdoDelegate();

            var delegateInitializationArgs = new DelegateInitializationArgs
                                             {
                                                 TablePrefix = "QRTZ_",
                                                 InstanceId = "TESTSCHED",
                                                 InstanceName = "INSTANCE",
                                                 TypeLoadHelper = new SimpleTypeLoadHelper(),
                                                 UseProperties = false,
                                                 InitString = "",
                                                 Logger = LogManager.GetLogger(GetType()),
                                                 DbProvider = dbProvider
                                             };
            adoDelegate.Initialize(delegateInitializationArgs);

            var conn = new ConnectionAndTransactionHolder(connection, transaction);

            // First result set has results, second has none
            dataReader.Stub(x => x.Read()).Return(true).Repeat.Once();
            dataReader.Stub(x => x.Read()).Return(false);
            dataReader.Stub(x => x[AdoConstants.ColumnTriggerType]).Return(AdoConstants.TriggerTypeBlob);

            IOperableTrigger trigger = adoDelegate.SelectTrigger(conn, new TriggerKey("test"));
            Assert.That(trigger, Is.Null);
        }