/** * */ public static DataTable GetJobBatch(Relativity.API.IDBContext workspaceDBContext, Int32 jobArtifactID, Int32 batchSize) { //Create temp table #TempFileIDs with top (batchSize) records //Update table status using temp table //Select using the temp table //Delete Temp table string sql = String.Format(@" IF OBJECT_ID('tempdb..#TempFileIDs') IS NOT NULL DROP TABLE #TempFileIDs; SELECT TOP({0}) [FileID] INTO #TempFileIDs FROM [REF_POP_{1}] WHERE [Status] = 0 ORDER BY [FileID] ASC; UPDATE REF SET REF.[Status] = 1 FROM [EDDSDBO].[REF_POP_{1}] AS REF INNER JOIN #TempFileIDs AS TFI ON REF.FileID = TFI.FileID; SELECT REF.[FileID], REF.[Filename], REF.[Location], REF.[Status] FROM [EDDSDBO].[REF_POP_{1}] AS REF INNER JOIN #TempFileIDs AS TFI ON REF.FileID = TFI.FileID; IF OBJECT_ID('tempdb..#TempFileIDs') IS NOT NULL DROP TABLE #TempFileIDs;", batchSize.ToString(), jobArtifactID.ToString()); DataTable jobBatch = workspaceDBContext.ExecuteSqlStatementAsDataTable(sql); if (jobBatch.Rows.Count > 0) { return(jobBatch); } else { return(null); } }
public static Dictionary <string, int> GetJob(Relativity.API.IDBContext eddsDBContext, Int32 agentArtifactID) { Int32 workspaceArtifactID; Int32 jobArtifactID; Int32 sourceSearchArtifactID; if (DoesAgentHaveJob(eddsDBContext, agentArtifactID)) { string sql = String.Format(@" SELECT TOP 1 [JobArtifactID], [WorkspaceArtifactID], [SourceSearchArtifactID] FROM [EDDS].[eddsdbo].[ExtensionFixerQueue] WHERE AgentArtifactID = {0} AND [Status] NOT IN (4, 5)", agentArtifactID); DataTable jobInfo = eddsDBContext.ExecuteSqlStatementAsDataTable(sql); DataRow row = jobInfo.Rows[0]; workspaceArtifactID = Int32.Parse(row["WorkspaceArtifactID"].ToString()); jobArtifactID = Int32.Parse(row["JobArtifactID"].ToString()); sourceSearchArtifactID = Int32.Parse(row["SourceSearchArtifactID"].ToString()); Dictionary <string, int> outputDict = new Dictionary <string, int> { { "WorkspaceArtifactID", workspaceArtifactID }, { "JobArtifactID", jobArtifactID }, { "SourceSearchArtifactID", sourceSearchArtifactID } }; return(outputDict); } else if (JobQueueDepth(eddsDBContext) > 0) { string sql = @" SELECT TOP 1 [JobArtifactID], [WorkspaceArtifactID], [SourceSearchArtifactID] FROM [EDDS].[eddsdbo].[ExtensionFixerQueue] WHERE [Status] = 0"; DataTable jobInfo = eddsDBContext.ExecuteSqlStatementAsDataTable(sql); DataRow row = jobInfo.Rows[0]; workspaceArtifactID = Int32.Parse(row["WorkspaceArtifactID"].ToString()); jobArtifactID = Int32.Parse(row["JobArtifactID"].ToString()); sourceSearchArtifactID = Int32.Parse(row["SourceSearchArtifactID"].ToString()); sql = String.Format(@" UPDATE [EDDS].[eddsdbo].[ExtensionFixerQueue] SET [Status] = 2, [AgentArtifactID] = {0} WHERE [WorkspaceArtifactID] = {1} AND [JobArtifactID] = {2}", agentArtifactID, workspaceArtifactID, jobArtifactID); eddsDBContext.ExecuteNonQuerySQLStatement(sql); Dictionary <string, int> outputDict = new Dictionary <string, int> { { "WorkspaceArtifactID", workspaceArtifactID }, { "JobArtifactID", jobArtifactID }, { "SourceSearchArtifactID", sourceSearchArtifactID } }; return(outputDict); } else { return(null); } }