add a prefix "Brightstar_" to BrightstarDB/BrightstarDB/tree/master/src/azure/sql/Jobs.sql and relative SqlJobQueue.cs

BrightstarDB.Jobs.sql

/**
 * Creates the job queue table and stored procedures in the database
 */

DROP ROLE brightstar_role_gateway
GO

DROP PROCEDURE [dbo].[Brightstar_CompleteJob]
GO
DROP PROCEDURE [dbo].[Brightstar_GetJob]
GO
DROP PROCEDURE [dbo].[Brightstar_GetJobDetail]
GO
DROP PROCEDURE [dbo].[Brightstar_GetStoreJobs]
GO
DROP PROCEDURE [dbo].[Brightstar_JobException]
GO
DROP PROCEDURE [dbo].[Brightstar_NextJob]
GO
DROP PROCEDURE [dbo].[Brightstar_QueueJob]
GO
DROP PROCEDURE [dbo].[Brightstar_StartCommit]
GO
DROP PROCEDURE [dbo].[Brightstar_UpdateStatus]
GO
DROP PROCEDURE [dbo].[Brightstar_ClearAllJobs]
GO
DROP PROCEDURE [dbo].[Brightstar_Cleanup]
GO
DROP PROCEDURE [dbo].[Brightstar_GetActiveJobs]
GO
DROP PROCEDURE [dbo].[Brightstar_ReleaseJob]
GO
DROP PROCEDURE [dbo].[Brightstar_GetLastCommit]
GO
DROP TABLE [dbo].[Brightstar_Jobs]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE ROLE brightstar_role_gateway
GO

CREATE TABLE [dbo].[Brightstar_Jobs](
 [Id] [varchar](255) NOT NULL,
 [StoreId] [nvarchar](255) NOT NULL,
 [JobType] [int] NOT NULL,
 [JobStatus] [int] NOT NULL,
 [StatusMessage] [nvarchar](2000) NULL,
 [JobData] [nvarchar](max) NULL,
 [QueuedTime] [datetime] NOT NULL,
 [ScheduledRunTime] [datetime] NULL,
 [ProcessingStarted] [datetime] NULL,
 [Processor] [varchar](255) NULL,
 [ProcessingCompleted] [datetime] NULL,
 [ProcessingException] [nvarchar](max) NULL,
 [RetryCount] [int] NOT NULL DEFAULT 0
 CONSTRAINT [PK_Brightstar_Jobs] PRIMARY KEY NONCLUSTERED ([Id] ASC))
GO
SET ANSI_PADDING OFF
GO
GRANT INSERT, DELETE, SELECT, UPDATE ON [dbo].[Brightstar_Jobs] TO brightstar_role_gateway
GO

CREATE UNIQUE CLUSTERED INDEX [IX_Brightstar_Jobs_QueuedTime] ON [dbo].[Brightstar_Jobs] 
(
 [QueuedTime] ASC,
 [Id] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_Brightstar_Jobs_StoreId] ON [dbo].[Brightstar_Jobs] 
(
 [StoreId] ASC
)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- QueueJob
--   @id - the new job id. must be unique
--   @storeId - the id of the target store for the job. Empty string for jobs that don't affect individual stores
--   @jobType - the new job type
--   @jobData - input data for the job
--   @scheduledStartTime - the earliest start time for the job.
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_QueueJob]
 @id varchar(255),
 @storeId nvarchar(255),
 @jobType int,
 @jobData nvarchar(max),
 @scheduledRunTime datetime
AS
BEGIN
 SET NOCOUNT OFF;
 SET @scheduledRunTime = COALESCE(@scheduledRunTime, CURRENT_TIMESTAMP)
 INSERT INTO Brightstar_Jobs(Id, StoreId, JobType, JobStatus, JobData, QueuedTime, ScheduledRunTime) 
 VALUES (@id, @storeId, @jobType, 0, @jobData, CURRENT_TIMESTAMP, @scheduledRunTime)
END
GO
GRANT EXECUTE ON [dbo].[Brightstar_QueueJob] TO brightstar_role_gateway
GO

-- ======================================
-- GetActiveJobs
--  @workerId the ID of the processor whose active jobs are to be returned
-- Selects all jobs from the job table where the Processor is @workerId
-- and the ProcessingCompleted timestamp is not set
-- ======================================
CREATE PROCEDURE [dbo].[Brightstar_GetActiveJobs]
 @workerId varchar(255)
AS
BEGIN
 SELECT Id, StoreId, JobType, JobStatus, JobData, StatusMessage, RetryCount 
 FROM Brightstar_Jobs
 WHERE Processor = @workerId AND ProcessingCompleted IS NULL
END
GO
GRANT EXECUTE ON [dbo].[Brightstar_GetActiveJobs] TO brightstar_role_gateway
GO

-- =============================================
-- UpdateStatus
--  @jobId the ID of the job to update
--  @statusMessage - the new status message for the job
-- Updates the user-friendly status message on the job
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_UpdateStatus]
 @jobId varchar(255),
 @statusMessage nvarchar(2000)
AS
BEGIN
 SET NOCOUNT ON;
 UPDATE Brightstar_Jobs SET StatusMessage=@statusMessage WHERE Id=@jobId
END
GO
GRANT EXECUTE ON [dbo].[Brightstar_UpdateStatus] TO brightstar_role_gateway
GO

-- =============================================
-- StartCommit
--  @jobId - the ID of the job to update
--  @statusMessage - the user-friendly status message for the job
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_StartCommit]
 @jobId varchar(255),
 @statusMessage nvarchar(2000)
AS
BEGIN
 SET NOCOUNT ON;
 UPDATE Brightstar_Jobs SET JobStatus=2, StatusMessage=@statusMessage WHERE Id=@jobId
END
GO
GRANT EXECUTE ON [dbo].[Brightstar_StartCommit] to brightstar_role_gateway
GO

-- =============================================
-- NextJob
-- Acquires a job for processing by a specific worker
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_NextJob] 
 @workerId varchar(255),
 @storeId nvarchar(255)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @jobId varchar(50)
 DECLARE @now datetime
 SET @now = CURRENT_TIMESTAMP

 -- First check to see if there is a Job already running for the worker
 -- If so, this job needs to be re-run
 SET @jobId = (
  SELECT TOP 1 Id FROM Brightstar_Jobs j WHERE
	j.Processor=@workerId AND
	(j.JobStatus = 1 OR j.JobStatus = 2)
 )
 IF (@jobId IS NULL)
 BEGIN
	 -- If storeId is NULL, get next scheduled pending job that is not
	 -- on a store that has an active job on it already
	 IF (@storeId IS NULL)
	 BEGIN
	  SET @jobId = (
	   SELECT TOP 1 Id FROM
	   Brightstar_Jobs j WHERE 
		(j.ScheduledRunTime IS NULL OR j.ScheduledRunTime <= @now) AND 
		j.JobStatus=0 
		AND NOT EXISTS(
		 SELECT Id FROM Brightstar_Jobs w 
		 WHERE 
		  w.StoreId=j.StoreId 
		  AND w.JobStatus > 0
		  AND w.ProcessingCompleted IS NULL)
	   ORDER BY QueuedTime ASC
	  )
	 END
	 ELSE 
	 BEGIN
	  SET @jobId = (
	   SELECT TOP 1 Id
	   FROM Brightstar_Jobs
	   WHERE StoreId=@storeId AND Processor=@workerId AND ProcessingCompleted IS NULL
	   ORDER BY QueuedTime ASC
	  )
	  IF @jobId IS NULL AND NOT EXISTS (SELECT Id FROM Brightstar_Jobs WHERE Processor IS NOT NULL AND ProcessingCompleted IS NULL AND StoreId=@storeId)
	   SET @jobId = (
		SELECT TOP 1 Id 
		FROM Brightstar_Jobs 
		WHERE JobStatus = 0 AND StoreId=@storeId 
		ORDER BY QueuedTime ASC
	   )
	 END
 END
 
 IF (@jobId IS NOT NULL)
 BEGIN
  UPDATE Brightstar_Jobs SET JobStatus = 1, ProcessingStarted=CURRENT_TIMESTAMP, Processor=@workerId WHERE Id=@jobId
  SELECT Id, StoreId, JobType, JobStatus, JobData, RetryCount FROM Brightstar_Jobs WHERE Id=@jobId
 END
END

GRANT EXECUTE ON [dbo].[Brightstar_NextJob] to brightstar_role_gateway
GO

-- =============================================
-- JobException
--  @jobId - the ID of the job that failed
--  @statusMessage - user friendly error message
--  @processingException - error stack trace for admin/devs
--
--  This store proc should be invoked when the processing of a job
--  fails with an exception. It makes the following changes to the
--  job record:
--    Set JobStatus to 0 (pending)
--    Set StatusMessage to @statusMessage
--    Append @processingException to ProcessingException
--    Increment RetryCount
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_JobException]
 @jobId varchar(255),
 @statusMessage nvarchar(2000),
 @processingException nvarchar(max)
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @linebreak as varchar(2)
 SET @linebreak = CHAR(13) + CHAR(10)
 
 UPDATE Brightstar_Jobs 
 SET JobStatus=0, 
  StatusMessage=@statusMessage, 
  ProcessingException=COALESCE(ProcessingException, '') +  '-----' + @linebreak + COALESCE(@processingException, '') + @linebreak,
  RetryCount=RetryCount+1
 WHERE
  Id=@jobId and RetryCount <= 3

 UPDATE Brightstar_Jobs 
 SET JobStatus=99,
	ProcessingCompleted=CURRENT_TIMESTAMP
 WHERE
 Id=@jobId and RetryCount > 3

END
GO
GRANT EXECUTE ON [dbo].[Brightstar_JobException] to brightstar_role_gateway
GO

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_GetJob]
 @jobId varchar(255),
 @storeId nvarchar(255)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 SELECT TOP 1 Id, StoreId, JobType, JobStatus, StatusMessage, ScheduledRunTime, ProcessingStarted, ProcessingCompleted FROM Brightstar_Jobs WHERE Id=@jobId AND StoreId=@storeId
END
GO

GRANT EXECUTE ON [dbo].[Brightstar_GetJob] to brightstar_role_gateway
GO

CREATE PROCEDURE [dbo].[Brightstar_GetJobDetail]
  @jobId varchar(255)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT TOP 1 Id, StoreId, JobType, JobStatus, StatusMessage, ScheduledRunTime, ProcessingStarted, Processor, ProcessingCompleted, ProcessingException, RetryCount
	FROM [Brightstar_Jobs]
	WHERE Id = @jobId
END
GO

GRANT EXECUTE ON [dbo].[Brightstar_GetJob] to brightstar_role_gateway
GO

-- =======================================
-- GetStoreJobs
-- Returns the details for all jobs targetting the specified store
-- @storeId - The ID of the target store
-- ========================================
CREATE PROCEDURE [dbo].[Brightstar_GetStoreJobs]
	@storeId nvarchar(255)
AS
BEGIN
SELECT Id, StoreId, JobType, JobStatus, StatusMessage, ScheduledRunTime, ProcessingStarted, ProcessingCompleted FROM Brightstar_Jobs WHERE StoreId=@storeId ORDER BY ScheduledRunTime
END
GO

GRANT EXECUTE ON [dbo].[Brightstar_GetStoreJobs] to brightstar_role_gateway
GO

-- ==================================
-- GetLastCommit
-- Returns the details for the last job that
-- committed to a particular store
-- 
-- @storeId - the id of the store
-- ===================================
CREATE PROCEDURE [dbo].[Brightstar_GetLastCommit]
	@storeId nvarchar(255)
AS
BEGIN
	SELECT TOP 1 Id, StoreId, JobType, JobStatus, StatusMessage, ProcessingCompleted 
	FROM Brightstar_Jobs
	WHERE ProcessingCompleted IS NOT NULL
	ORDER BY ProcessingCompleted DESC
END
GO

GRANT EXECUTE ON [dbo].[Brightstar_GetLastCommit] to brightstar_role_gateway
GO

-- =============================================
-- ReleaseJob
--  @jobId - the ID of the job to be released
-- Returns the specified job to the queue
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_ReleaseJob]
  @jobId varchar(255)
AS
BEGIN
 SET NOCOUNT ON
 UPDATE Brightstar_Jobs 
 SET Processor=NULL, JobStatus=0 
 WHERE Id=@jobId
END
GO

GRANT EXECUTE ON [dbo].[Brightstar_ReleaseJob] to brightstar_role_gateway
GO

-- =============================================
-- CompleteJob
-- @jobId - the ID of the job to be marked as completed
-- @finalStatus - the status code to record for the completed job
-- @finalStatusMessage - the user friendly message to record for the completed job
-- =============================================
CREATE PROCEDURE [dbo].[Brightstar_CompleteJob]
 @jobId varchar(255),
 @finalStatus int,
 @finalStatusMessage nvarchar(2000)
AS
BEGIN
 SET NOCOUNT ON;
 UPDATE Brightstar_Jobs 
 SET JobStatus=@finalStatus, StatusMessage=@finalStatusMessage, ProcessingCompleted=CURRENT_TIMESTAMP
 WHERE Id=@jobId
END
GO

GRANT EXECUTE ON [dbo].[Brightstar_CompleteJob] to brightstar_role_gateway
GO

/**
 * Deletes all jobs (regardless of their status) from the queue.
 * This is intended only for testing purposes.
 */
CREATE PROCEDURE [dbo].[Brightstar_ClearAllJobs]
AS
BEGIN
 DELETE FROM Brightstar_Jobs
END
GO

GRANT EXECUTE ON [dbo].[Brightstar_ClearAllJobs] to brightstar_role_gateway
GO

/**
 * Deletes all jobs that have a ProcessingCompleted timestamp
 * that is earlier than the current date/time less @maxJobAge (in seconds)
 */
CREATE PROCEDURE [dbo].[Brightstar_Cleanup]
 @maxJobAge int
AS
BEGIN
 DECLARE @cutOffDate datetime
 SET @cutOffDate = DATEADD(second, 0 - @maxJobAge, CURRENT_TIMESTAMP)
 DELETE FROM Brightstar_Jobs WHERE 
  ProcessingCompleted IS NOT NULL AND
  ProcessingCompleted < @cutOffDate
END

GRANT EXECUTE ON [dbo].[Brightstar_Cleanup] to brightstar_role_gateway
GO

SqlJobQueue.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using BrightstarDB.Azure.Common.Logging;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.StorageClient;

namespace BrightstarDB.Azure.Common
{
    public class SqlJobQueue : IJobQueue
    {
        private readonly string _connectionString;
        private readonly string _workerId;

        public SqlJobQueue(string connectionString, string workerId)
        {
            _connectionString = connectionString;
            _workerId = workerId;
        }

        #region Implementation of IJobQueue

        public string QueueJob(string storeId, JobType jobType, string jobData, DateTime? scheduledRunTime)
        {
            string jobId = Guid.NewGuid().ToString("N");
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_QueueJob";
                        cmd.Parameters.AddWithValue("id", jobId);
                        cmd.Parameters.AddWithValue("storeId", storeId);
                        cmd.Parameters.AddWithValue("jobType", (short) jobType);
                        cmd.Parameters.AddWithValue("jobData", jobData);
                        cmd.Parameters.AddWithValue("scheduledRunTime",
                                                    scheduledRunTime.HasValue
                                                        ? (object) scheduledRunTime.Value
                                                        : DBNull.Value);
                        var rowCount = cmd.ExecuteNonQuery();
                        if (rowCount == 1) return jobId;
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
            return null;
        }


        public JobInfo NextJob(string preferredStoreId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_NextJob";
                        cmd.Parameters.AddWithValue("storeId",
                                                    String.IsNullOrEmpty(preferredStoreId)
                                                        ? (object) DBNull.Value
                                                        : preferredStoreId);
                        cmd.Parameters.AddWithValue("workerId", _workerId);
                        using (var reader = cmd.ExecuteReader())
                        {
                            JobInfo ret = null;
                            if (reader.Read())
                            {
                                var jobId = reader.GetString(0);
                                var storeId = reader.GetString(1);
                                ret = new JobInfo(jobId, storeId)
                                          {
                                              JobType = (JobType) reader.GetInt32(2),
                                              Status = (JobStatus) reader.GetInt32(3),
                                              Data = reader.IsDBNull(4) ? null : reader.GetString(4),
                                              RetryCount = reader.GetInt32(5)
                                          };
                            }
                            return ret;
                        }
                    }
                } finally
                {
                    conn.Close();
                }
            }
        }

        public void UpdateStatus(string jobId, string statusMessage)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_UpdateStatus";
                        cmd.Parameters.AddWithValue("jobId", jobId);
                        cmd.Parameters.AddWithValue("statusMessage", StatusMessageValue(statusMessage));
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public void StartCommit(string jobId, string statusMessage)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_StartCommit";
                        cmd.Parameters.AddWithValue("jobId", jobId);
                        cmd.Parameters.AddWithValue("statusMessage", StatusMessageValue(statusMessage));
                        cmd.ExecuteNonQuery();
                    }
                } finally
                {
                    conn.Close();
                }
            }
        }

        public void CompleteJob(string jobId, JobStatus finalStatus, string finalStatusMessage)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_CompleteJob";
                        cmd.Parameters.AddWithValue("jobId", jobId);
                        cmd.Parameters.AddWithValue("finalStatus", (int) finalStatus);
                        cmd.Parameters.AddWithValue("finalStatusMessage", StatusMessageValue(finalStatusMessage));
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
            LogJob(jobId);
        }

        public void FailWithException(string jobId, string failureMessage, Exception ex)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_JobException";
                        cmd.Parameters.AddWithValue("jobId", jobId);
                        cmd.Parameters.AddWithValue("statusMessage", StatusMessageValue(failureMessage));
                        cmd.Parameters.AddWithValue("processingException", ex.ToString());
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    conn.Close();
                }
            }

            LogJob(jobId);
        }

        public void ReleaseJob(string jobId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_ReleaseJob";
                        cmd.Parameters.AddWithValue("jobId", jobId);
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        // Not currently used
        //public IEnumerable<JobInfo> GetActiveJobs()
        //{
        //    using (var conn = new SqlConnection(_connectionString))
        //    {
        //        conn.Open();
        //        try
        //        {
        //            using (var cmd = conn.CreateCommand())
        //            {
        //                cmd.CommandType = CommandType.StoredProcedure;
        //                cmd.CommandText = "GetActiveJobs";
        //                cmd.Parameters.AddWithValue("workerId", _workerId);
        //                using (var reader = cmd.ExecuteReader())
        //                {
        //                    while (reader.Read())
        //                    {
        //                        yield return new JobInfo(reader.GetString(0), reader.GetString(1))
        //                                         {
        //                                             JobType = (JobType) reader.GetInt32(2),
        //                                             Status = (JobStatus) reader.GetInt32(3),
        //                                             StatusMessage =
        //                                                 reader.IsDBNull(4) ? String.Empty : reader.GetString(4),
        //                                             RetryCount = reader.GetInt32(5)
        //                                         };
        //                    }
        //                }
        //            }
        //        }
        //        finally
        //        {
        //            conn.Close();
        //        }
        //    }
        //}

        public JobInfo GetJob(string storeId, string jobId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.CommandText = "Brightstar_GetJob";
                        cmd.Parameters.AddWithValue("jobId", jobId);
                        cmd.Parameters.AddWithValue("storeId", storeId);
                        using (var reader = cmd.ExecuteReader())
                        {
                            JobInfo ret = null;
                            if (reader.Read())
                            {
                                ret = new JobInfo(jobId, storeId)
                                          {
                                              JobType = (JobType) reader.GetInt32(2),
                                              Status = (JobStatus) reader.GetInt32(3),
                                              StatusMessage = reader.IsDBNull(4) ? String.Empty : reader.GetString(4)
                                          };
                            }
                            return ret;
                        }
                    }
                } finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Cleans up the store by deleting all completed jobs that were finished
        /// before the current date/time less maxJobAge
        /// </summary>
        /// <param name="maxJobAge"></param>
        public void Cleanup(TimeSpan maxJobAge)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_Cleanup";
                        cmd.Parameters.AddWithValue("maxJobAge", (int) maxJobAge.TotalSeconds);
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Removes all jobs (completed and uncompleted) from the queue
        /// </summary>
        public void ClearAll()
        {
            using(var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_ClearAllJobs";
                        cmd.ExecuteNonQuery();
                    }
                } finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Returns the info for the last job that committed to the specified store
        /// </summary>
        /// <param name="storeId"></param>
        /// <returns></returns>
        public JobInfo GetLastCommit(string storeId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_GetLastCommit";
                        cmd.Parameters.AddWithValue("storeId", storeId);
                        using (var reader = cmd.ExecuteReader())
                        {
                            JobInfo ret = null;
                            if (reader.Read())
                            {
                                var jobId = reader.GetString(0);
                                ret = new JobInfo(jobId, storeId)
                                          {
                                              JobType = (JobType) reader.GetInt32(2),
                                              Status = (JobStatus) reader.GetInt32(3),
                                              StatusMessage = reader.IsDBNull(4) ? String.Empty : reader.GetString(4),
                                          };
                                if (!reader.IsDBNull(5)) ret.ProcessingCompleted = reader.GetDateTime(5);
                            }
                            return ret;
                        }
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Returns an enumeration over all jobs in the store that are to work on the specified store
        /// </summary>
        /// <param name="storeId"></param>
        /// <returns></returns>
        public IEnumerable<JobInfo> GetJobs(string storeId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                try
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Brightstar_GetStoreJobs";
                        cmd.Parameters.AddWithValue("storeId", storeId);
                        using (var reader = cmd.ExecuteReader())
                        {
                            var ret = new List<JobInfo>();
                            while (reader.Read())
                            {
                                ret.Add(ReadJobInfo(reader));
                            }
                            return ret;
                        }
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        #endregion

        private void LogJob(string jobId)
        {
            try
            {
                JobInfo jobInfo = null;
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    try
                    {
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = "Brightstar_GetJobDetail";
                            cmd.Parameters.AddWithValue("jobId", jobId);
                            using (var reader = cmd.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    jobInfo = new JobInfo(reader.GetString(0), reader.GetString(1));
                                    jobInfo.JobType = (JobType) reader.GetInt32(2);
                                    jobInfo.Status = (JobStatus) reader.GetInt32(3);
                                    jobInfo.StatusMessage = reader.IsDBNull(4) ? String.Empty : reader.GetString(4);
                                    jobInfo.ScheduledRunTime =
                                        reader.IsDBNull(5) ? (DateTime?) null : reader.GetDateTime(5);
                                    jobInfo.StartTime = reader.IsDBNull(6) ? (DateTime?) null : reader.GetDateTime(6);
                                    jobInfo.ProcessorId = reader.IsDBNull(7) ? String.Empty : reader.GetString(7);
                                    jobInfo.ProcessingCompleted =
                                        reader.IsDBNull(8) ? (DateTime?) null : reader.GetDateTime(8);
                                    jobInfo.ProcessingException =
                                        reader.IsDBNull(9) ? String.Empty : reader.GetString(9);
                                    jobInfo.RetryCount = reader.GetInt32(10);
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Trace.TraceError("Error retrieving job detail for job {0}. Cause: {1}", jobId, ex);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
                if (jobInfo != null)
                {
                    var connectionString =
                        RoleEnvironment.GetConfigurationSettingValue(AzureConstants.DiagnosticsConnectionStringName);
                    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
                    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
                    tableClient.CreateTableIfNotExist("jobs");
                    TableServiceContext serviceContext = tableClient.GetDataServiceContext();
                    JobLogEntity jobLogEntity = new JobLogEntity(jobInfo);
                    serviceContext.AddObject("jobs", jobLogEntity);
                    serviceContext.SaveChangesWithRetries();
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError("Error logging detail for job {0}. Cause: {1}", jobId, ex);
            }
        }

        private static JobInfo ReadJobInfo(IDataRecord reader)
        {
            return new JobInfo(reader.GetString(0), reader.GetString(1))
                       {
                           JobType = (JobType) reader.GetInt32(2),
                           Status = (JobStatus) reader.GetInt32(3),
                           StatusMessage = reader.IsDBNull(4) ? String.Empty : reader.GetString(4),
                           ScheduledRunTime = reader.IsDBNull(5) ? (DateTime?) null : reader.GetDateTime(5),
                           StartTime = reader.IsDBNull(6) ? (DateTime?) null : reader.GetDateTime(6),
                           ProcessingCompleted = reader.IsDBNull(7) ? (DateTime?) null : reader.GetDateTime(7)
                       };
        }

        private object StatusMessageValue(string statusMessage)
        {
            return statusMessage == null
                       ? (object) DBNull.Value
                       : statusMessage.Length <= 2000 ? statusMessage : statusMessage.Substring(0, 2000);
        }
    }
}