/// <summary> /// Updates the user's [Edds].[Eddsdbo].[User].[HasAgreedToTermsOfUse] for all users. If a users in not eligible to be /// presented with a disclaimer their value is set to 0, other wise it is set to 1. This same logic is uses to present /// disclaimers to users only in specific groups /// </summary> /// <param name="eddsDbContext">Admin level DB Context</param> /// <param name="alldisclaimers">List of enabled disclaimers</param> private void UpdateUserAcceptance(IDBContext eddsDbContext, IEnumerable <Models.Disclaimer> alldisclaimers) { var usersWhoNeedToAccept = new List <Int32>(); var allRelativityUserIds = Functions.GetAllUserIds(Helper); var enabledDisclaimers = alldisclaimers.Where(x => x.Enabled).ToList(); //figure out which users need to accept each disclaimer and add their ArtifactIds to a list foreach (var disclaimer in enabledDisclaimers) { usersWhoNeedToAccept.AddRange(Functions.FindUsersWhoNeedToAcceptDisclaimer(eddsDbContext, disclaimer)); } //The users collected from above will be flagged for disclaimer acceptance if (usersWhoNeedToAccept.Any()) { var formattedUsersWhoNeedToAccept = String.Join(",", usersWhoNeedToAccept.Distinct()); var updateStatmentForUsersWhoNeedToAccept = String.Format(SQL.FlagUsersWhoNeedToAccept, formattedUsersWhoNeedToAccept); eddsDbContext.ExecuteNonQuerySQLStatement(updateStatmentForUsersWhoNeedToAccept); } //every other user is reset and are exempt var exemptUsers = allRelativityUserIds.Except(usersWhoNeedToAccept).ToList(); if (exemptUsers.Any()) { var formattedUsersWhoAreExempt = String.Join(",", exemptUsers); var updateStatmentForExemptUsers = String.Format(SQL.FlagUsersWhoAlreadyAccepted, formattedUsersWhoAreExempt); eddsDbContext.ExecuteNonQuerySQLStatement(updateStatmentForExemptUsers); } }
public void InsertRowIntoErrorLog(IDBContext eddsDbContext, Int32 workspaceArtifactId, String queueTableName, Int32 queueRecordId, Int32 agentId, String errorMessage) { var sql = String.Format(@" INSERT INTO EDDSDBO.{0} ( [TimeStampUTC] ,WorkspaceArtifactID ,ApplicationName ,ApplicationGuid ,QueueTableName ,QueueRecordID ,AgentID ,[Message] ) VALUES ( GetUTCDate() ,@workspaceArtifactId ,@applicationName ,@applicationGuid ,@queueTableName ,@queueRecordID ,@agentID ,@message )" , Constant.Tables.ErrorLog); var sqlParams = new List <SqlParameter> { new SqlParameter("@workspaceArtifactId", SqlDbType.Int) { Value = workspaceArtifactId }, new SqlParameter("@applicationName", SqlDbType.VarChar) { Value = Constant.Names.ApplicationName }, new SqlParameter("@applicationGuid", SqlDbType.UniqueIdentifier) { Value = Constant.Guids.ApplicationGuid }, new SqlParameter("@queueTableName", SqlDbType.VarChar) { Value = queueTableName }, new SqlParameter("@queueRecordID", SqlDbType.Int) { Value = queueRecordId }, new SqlParameter("@agentID", SqlDbType.Int) { Value = agentId }, new SqlParameter("@message", SqlDbType.NVarChar) { Value = errorMessage } }; eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); }
public void CreateWorkerQueueTable(IDBContext eddsDbContext) { var sql = String.Format(@" IF OBJECT_ID('EDDSDBO.{0}') IS NULL BEGIN CREATE TABLE EDDSDBO.{0} ( [ID] INT IDENTITY(1,1) PRIMARY KEY ,[TimeStampUTC] DATETIME ,[WorkspaceArtifactID] INT ,[QueueStatus] INT ,[AgentID] INT ,[ExtractorSetArtifactID] INT ,[DocumentArtifactID] INT ,[ExtractorProfileArtifactID] INT ,[SourceLongTextFieldArtifactID] INT ) END IF NOT EXISTS (SELECT * FROM sys.sysindexes WHERE id = OBJECT_ID('{0}') AND name = 'i_{0}') BEGIN CREATE NONCLUSTERED INDEX [i_{0}] ON [EDDSDBO].{0} ( [WorkspaceArtifactID] ASC ,[ExtractorSetArtifactID] ASC ,[DocumentArtifactID] ASC ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] END" , Constant.Tables.WorkerQueue); eddsDbContext.ExecuteNonQuerySQLStatement(sql); }
public bool insertJob(IDBContext dbContext, string currentUser, DTOs.RDO rdo) { bool result = true; DateTime date = DateTime.Now; string INSERT_JOB_QUERY = $@"IF NOT EXISTS(SELECT TOP 1 * FROM [EDDSDBO].commentJob where comment_artifactid = @commentArtifactId) BEGIN insert into [EDDSDBO].commentJob (cojob_comment, cojob_createdBy, comment_artifactId, cojob_createdOn) Values (@comment, @user, @commentArtifactId, @createdOn ) END"; System.Data.SqlClient.SqlParameter comment = new System.Data.SqlClient.SqlParameter("@comment", System.Data.SqlDbType.VarChar); comment.Value = rdo.Fields[0].Value; System.Data.SqlClient.SqlParameter user = new System.Data.SqlClient.SqlParameter("@user", System.Data.SqlDbType.VarChar); user.Value = currentUser; System.Data.SqlClient.SqlParameter commentArtifactId = new System.Data.SqlClient.SqlParameter("@commentArtifactId", System.Data.SqlDbType.Int); commentArtifactId.Value = rdo.ArtifactID; System.Data.SqlClient.SqlParameter createdOn = new System.Data.SqlClient.SqlParameter("@createdOn", System.Data.SqlDbType.DateTime); createdOn.Value = DateTime.Now; try { dbContext.ExecuteNonQuerySQLStatement(INSERT_JOB_QUERY, new System.Data.SqlClient.SqlParameter[] { commentArtifactId, comment, user, createdOn }); result = true; } catch (Exception e) { System.Console.WriteLine($"There was a problem in a Query: {e.Message}"); result = false; } return(result); }
public void DeleteRecordsInWorkerQueueForCancelledExtractorSetAndAgentId(IDBContext eddsDbContext, Int32 workspaceArtifactId, Int32 extractorSetArtifactId, Int32 agentId) { var sql = String.Format(@" BEGIN TRAN DELETE FROM EDDSDBO.[{0}] WHERE [AgentID] = @agentId AND [WorkspaceArtifactID] = @workspaceArtifactId AND [ExtractorSetArtifactID] = @extractorSetArtifactId COMMIT " , Constant.Tables.WorkerQueue); var sqlParams = new List <SqlParameter> { new SqlParameter("@agentId", SqlDbType.Int) { Value = agentId }, new SqlParameter("@workspaceArtifactId", SqlDbType.Int) { Value = workspaceArtifactId }, new SqlParameter("@extractorSetArtifactId", SqlDbType.Int) { Value = extractorSetArtifactId } }; eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); }
private void ToggleSolution(IDBContext eddsDbContext, bool?enabled, bool?allowAccessOnError) { var currentRelativityVersion = Functions.GetRelativityVersion(typeof(kCura.EventHandler.Application)).ToString(); var relativityVersion = new Version(currentRelativityVersion); var supportedRelativityVersion = new Version(KCD_1042192.Utility.Constants.OtherConstants.RelativityVersion.September94Release); if (enabled == true) { var allowAccessInsert = allowAccessOnError.GetValueOrDefault(false).ToString().ToLower(); //THe html has many curly braces that confuse format, so String.replace was used var loginPageHtml = HTML.LoginPage.Replace("{0}", Utility.Constants.Guids.Applications.DisclaimerAcceptanceLog.ToString()); loginPageHtml = loginPageHtml.Replace("{1}", allowAccessInsert); var parameters = new List <SqlParameter> { new SqlParameter { ParameterName = "@HTML", SqlDbType = SqlDbType.NVarChar, Value = loginPageHtml } }; //var currentRelativityVersion = Functions.GetAssemblyVersion(typeof(kCura.EventHandler.Application)).ToString(); if (relativityVersion >= supportedRelativityVersion) { eddsDbContext.ExecuteNonQuerySQLStatement(SQL.EnableDisclaimerSolutionSept, parameters); } else { eddsDbContext.ExecuteNonQuerySQLStatement(SQL.EnableDisclaimerSolution, parameters); } //eddsDbContext.ExecuteNonQuerySQLStatement(SQL.EnableDisclaimerSolution, parameters); } else { if (relativityVersion >= supportedRelativityVersion) { eddsDbContext.ExecuteNonQuerySQLStatement(SQL.DisableDisclaimerSolutionSept); } else { eddsDbContext.ExecuteNonQuerySQLStatement(SQL.DisableDisclaimerSolution); } //eddsDbContext.ExecuteNonQuerySQLStatement(SQL.DisableDisclaimerSolution); } }
public void DropTable(IDBContext dbContext, String tableName) { var sql = String.Format(@" IF NOT OBJECT_ID('EDDSDBO.{0}') IS NULL BEGIN DROP TABLE EDDSDBO.{0} END" , tableName); dbContext.ExecuteNonQuerySQLStatement(sql); }
public void RemoveBatchFromQueue(IDBContext eddsDbContext, String uniqueTableName) { var sql = String.Format(@" DELETE EDDSDBO.{0} FROM EDDSDBO.{0} S INNER JOIN EDDSDBO.{1} B ON B.ID = S.ID " , Constant.Tables.WorkerQueue, uniqueTableName); eddsDbContext.ExecuteNonQuerySQLStatement(sql); }
public void InsertRowIntoManagerQueue(IDBContext eddsDbContext, Int32 workspaceArtifactId, Int32?savedSearchId, Int32 extractorSetArtifactId, Int32?extractorProfileArtifactId, Int32?sourceLongTextFieldArtifactId) { var sql = String.Format(@" INSERT INTO EDDSDBO.{0} ( [TimeStampUTC] ,WorkspaceArtifactID ,QueueStatus ,AgentID ,SavedSearchArtifactID ,ExtractorSetArtifactID ,ExtractorProfileArtifactID ,SourceLongTextFieldArtifactID ) VALUES ( GetUTCDate() ,@workspaceArtifactId ,@queueStatus ,NULL ,@savedSearchId ,@extractorSetArtifactID ,@extractorProfileArtifactID ,@sourceLongTextFieldArtifactID )" , Constant.Tables.ManagerQueue); var sqlParams = new List <SqlParameter> { new SqlParameter("@workspaceArtifactId", SqlDbType.Int) { Value = workspaceArtifactId }, new SqlParameter("@queueStatus", SqlDbType.VarChar) { Value = Constant.QueueStatus.NotStarted }, new SqlParameter("@savedSearchId", SqlDbType.Int) { Value = savedSearchId }, new SqlParameter("@extractorSetArtifactID", SqlDbType.Int) { Value = extractorSetArtifactId }, new SqlParameter("@extractorProfileArtifactID", SqlDbType.Int) { Value = extractorProfileArtifactId }, new SqlParameter("@sourceLongTextFieldArtifactID", SqlDbType.Int) { Value = sourceLongTextFieldArtifactId } }; eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); }
public dynamic ExecuteSql(IDBContext dbContext, Action action, DapiJson dapiJson) { List <SqlParameter> sqlParameters = new List <SqlParameter>(); foreach (Parameter param in action.Parameters) { SqlParameter sqlParam = new SqlParameter(param.Name, Mapper.Map <SqlDbType>(param.DataType)); if (param.Length > 0) { sqlParam.Size = param.Length ?? sqlParam.Size; } var dParam = dapiJson.Parameters.Find(d => d.name.Equals(param.Name, StringComparison.OrdinalIgnoreCase)); sqlParam.Value = dParam?.value; sqlParameters.Add(sqlParam); } dynamic result; try { switch (action.Outputs.OutputMode) { case OutputMode.NoReturnValue: result = dbContext.ExecuteNonQuerySQLStatement(action.SQL, sqlParameters); result = ""; break; case OutputMode.Scalar: result = dbContext.ExecuteSqlStatementAsScalar <dynamic>(action.SQL, sqlParameters); break; case OutputMode.Rowset: result = dbContext.ExecuteSqlStatementAsDataTable(action.SQL, sqlParameters); break; default: result = ""; break; } DidActionExecute = true; } catch (Exception ex) { DidActionExecute = false; result = ex.Message; throw; } return(result); }
public void auditComment(CORE.DAL.Entities.Comment comment, bool isNew, IDBContext dbContext) { string sql = string.Empty; int value = 0; if (isNew) { sql = $@"INSERT INTO [EDDSDBO].[AuditComment] ([CommentId] ,[CreatedOn] ,[CreateByUserId] ,[CreatedByUserName] ,[ReplysAmount] ,[comment] ,[type]) VALUES ({comment.ArtifactId}, GETDATE(), {comment.CreatedBy.ArtifactId}, '{comment.CreatedBy.Name}', 0, '{comment.Name}', '{comment.TypeChoosed}' )" ; } else { sql = $@"INSERT INTO [EDDSDBO].[AuditComment] ([CommentId] ,[ModifiedOn] ,[ModifiedByUserId] ,[ModifiedByUserName] ,[ReplysAmount] ,[comment] ,[type]) VALUES ({comment.ArtifactId}, GETDATE(), {comment.CreatedBy.ArtifactId}, '{comment.CreatedBy.Name}', 0, '{comment.Name}', '{comment.TypeChoosed}' )" ; } value = dbContext.ExecuteNonQuerySQLStatement(sql); }
public void RemoveRecordFromTableByAgentId(IDBContext eddsDbContext, String queueTableName, Int32 agentId) { var sql = String.Format(@" DELETE FROM EDDSDBO.[{0}] WHERE [AgentID] = @agentID" , queueTableName); var sqlParams = new List <SqlParameter> { new SqlParameter("@agentID", SqlDbType.Int) { Value = agentId } }; eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); }
private Boolean MoveDisclaimerToArchive(IDBContext workspaceDb, Int32 currentArtifactId) { var disclaimerObjTableName = GetDisclaimerObjTableName(workspaceDb, Utility.Constants.Guids.Objects.Disclaimer); var moveDisclaimerSqlCommand = String.Format(SQL.MoveDisclaimerToArchive, disclaimerObjTableName); var parameters = new List <SqlParameter> { new SqlParameter { ParameterName = "@CurrentArtifactID", SqlDbType = SqlDbType.Int, Value = currentArtifactId } }; var affectedRows = workspaceDb.ExecuteNonQuerySQLStatement(moveDisclaimerSqlCommand, parameters); return(affectedRows > 0); }
public void Delete(int agentArtifactId) { string SQL = @" UPDATE [Artifact] SET [DeleteFlag] = 1 WHERE ArtifactID = @ArtifactID"; SqlParameter agentArtifactIdParam = new SqlParameter("@ArtifactID", System.Data.SqlDbType.Int) { Value = agentArtifactId }; _eddsDbContext.ExecuteNonQuerySQLStatement(SQL, new SqlParameter[] { agentArtifactIdParam }); /* No need to audit the delete. * Relativity does it for you */ }
public Boolean RemoveRecordFromWorkerQueue(IDBContext eddsDbContext, String queueTableName, Int32 queueId) { var sql = String.Format(@" DELETE FROM EDDSDBO.[{0}] WHERE [ID] = @queueId" , queueTableName); var sqlParams = new List <SqlParameter> { new SqlParameter("@queueId", SqlDbType.Int) { Value = queueId } }; var rowsAffected = eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); return(rowsAffected > 0); }
public int changeTheme(string value) { int data; using (IRSAPIClient proxy = _helper.GetServicesManager().CreateProxy <IRSAPIClient>(ExecutionIdentity.System)) { int workspace = _workspaceID; IDBContext dbContext = _helper.GetDBContext(-1); string sql = $@"UPDATE [eddsdbo].[InstanceSetting] SET [Value] = '{value}' WHERE [Name] LIKE '%Theme UI (light/dark)%'"; data = dbContext.ExecuteNonQuerySQLStatement(sql); } return(data); }
public void UpdateStatusInWorkerQueue(IDBContext eddsDbContext, Int32 statusId, String uniqueTableName) { var sql = String.Format(@" UPDATE S SET QueueStatus = @statusId FROM EDDSDBO.{1} B INNER JOIN EDDSDBO.{0} S ON B.ID = S.ID" , Constant.Tables.WorkerQueue, uniqueTableName); var sqlParams = new List <SqlParameter> { new SqlParameter("@statusId", SqlDbType.Int) { Value = statusId } }; eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); }
public override kCura.EventHandler.Response Execute() { // Update Security Protocol ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; // Get logger Relativity.API.IAPILog _logger = this.Helper.GetLoggerFactory().GetLogger().ForContext <PostWorkspaceCreateEventHandlerBase>(); // Init general response kCura.EventHandler.Response response = new kCura.EventHandler.Response() { Success = true, Message = "" }; // Get current Workspace ID int workspaceId = this.Helper.GetActiveCaseID(); _logger.LogDebug("Audit Log Elastic Search, current Workspace ID: {workspaceId}", workspaceId.ToString()); // Get database context of the instance IDBContext instanceContext = Helper.GetDBContext(-1); // Add line to the application management table for current workspace try { // Insert to the application management table SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId); instanceContext.ExecuteNonQuerySQLStatement("INSERT INTO [eddsdbo].[" + this.TableName + "] ([CaseArtifactID], [AuditRecordID], [Status], [LastUpdated]) VALUES (@workspaceId, 0, 1, CURRENT_TIMESTAMP)", new SqlParameter[] { workspaceIdParam }); } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Post Workspace Create EventHandler application management table insert error"); response.Success = false; response.Message = "Post Workspace Create EventHandler application management table insert error"; return(response); } // Log end of Post Workspace Create EventHandler _logger.LogDebug("Audit Log Elastic Search, Post Workspace Create EventHandler successfully finished"); return(response); }
public override kCura.EventHandler.Response Execute() { // Update Security Protocol ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; // Get logger Relativity.API.IAPILog _logger = this.Helper.GetLoggerFactory().GetLogger().ForContext <PreUninstallEventHandler>(); // Init general response kCura.EventHandler.Response response = new kCura.EventHandler.Response() { Success = true, Message = "" }; // Get current Workspace ID int workspaceId = this.Helper.GetActiveCaseID(); _logger.LogDebug("Audit Log Elastic Search, current Workspace ID: {workspaceId}", workspaceId.ToString()); // Get database context of the instance IDBContext instanceContext = Helper.GetDBContext(-1); // Update line of the application management and set current workspace to disabled try { // Update the application management table SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId); instanceContext.ExecuteNonQuerySQLStatement("UPDATE [eddsdbo].[" + this.TableName + "] SET [Status] = 0, [LastUpdated] = CURRENT_TIMESTAMP WHERE [CaseArtifactID] = @workspaceId", new SqlParameter[] { workspaceIdParam }); } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Pre Uninstall EventHandler application management table update error"); response.Success = false; response.Message = "Pre Uninstall EventHandler application management table update error"; return(response); } // Log end of Pre Uninstall EventHandler _logger.LogDebug("Audit Log Elastic Search, Pre Uninstall EventHandler successfully finished"); return(response); }
public void UpdateManagerRecordStatus(IDBContext eddsDbContext, Int32 statusId, Int32 recordID) { var sql = String.Format(@" UPDATE EDDSDBO.[{0}] SET [QueueStatus] = @statusId WHERE [ID] = @ID" , Constant.Tables.ManagerQueue); var sqlParams = new List <SqlParameter> { new SqlParameter("@statusId", SqlDbType.Int) { Value = statusId }, new SqlParameter("@ID", SqlDbType.Int) { Value = recordID } }; eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); }
public void CreateErrorLogTable(IDBContext eddsDbContext) { var sql = String.Format(@" IF OBJECT_ID('EDDSDBO.{0}') IS NULL BEGIN CREATE TABLE EDDSDBO.{0} ( ID INT IDENTITY(1,1) ,[TimeStampUTC] DATETIME ,WorkspaceArtifactID INT ,ApplicationName VARCHAR(500) ,ApplicationGuid uniqueidentifier ,QueueTableName NVARCHAR(MAX) ,QueueRecordID INT ,AgentID INT ,[Message] NVARCHAR(MAX) ) END" , Constant.Tables.ErrorLog); eddsDbContext.ExecuteNonQuerySQLStatement(sql); }
private void SetAgentOffHours(string agentOffHoursType, string timeValue) { string sql = $@" UPDATE [EDDSDBO].[Configuration] SET [Value] = '{timeValue}' WHERE Section = '{TestConstants.AGENT_OFF_HOURS_SECTION_NAME}' AND [Name] = '{agentOffHoursType}' " ; try { _eddsDbContext.ExecuteNonQuerySQLStatement(sql); } catch (Exception ex) { throw new Exception($"An error occured when setting the Agent OffHours time of type: {agentOffHoursType}.", ex); } }
public void ResetUnfishedJobs(IDBContext eddsDbContext, Int32 agentId, String queueTableName) { var sql = String.Format(@" UPDATE [EDDSDBO].[{0}] SET [AgentID] = NULL, [QueueStatus] = @notStartedQueueStatus WHERE [AgentID] = @agentID" , queueTableName); var sqlParams = new List <SqlParameter> { new SqlParameter("@agentID", SqlDbType.Int) { Value = agentId }, new SqlParameter("@notStartedQueueStatus", SqlDbType.Int) { Value = Constant.QueueStatus.NotStarted } }; eddsDbContext.ExecuteNonQuerySQLStatement(sql, sqlParams); }
private void InsertAuditRecord(string agentName) { string SQL = @" INSERT INTO [AuditRecord] ([ArtifactID], [Action], [Details], [UserID], [TimeStamp], [RequestOrigination], [RecordOrigination]) VALUES (@ArtifactID, @Action, '<auditElement>' + @AgentName + ' created with Agent Agent.</auditElement>', 777, Getutcdate(), '<auditElement></auditElement>', '<auditElement></auditElement>') "; //Gather values to input into above script SqlParameter agentArtifactIdParam = new SqlParameter("@ArtifactID", System.Data.SqlDbType.Char) { Value = _agentArtifactId }; SqlParameter agentNameParam = new SqlParameter("@AgentName", System.Data.SqlDbType.Char) { Value = agentName }; SqlParameter actionParam = new SqlParameter("@Action", System.Data.SqlDbType.Char) { //2 is create Value = 2 }; _eddsDbContext.ExecuteNonQuerySQLStatement(SQL, new SqlParameter[] { agentArtifactIdParam, agentNameParam, actionParam }); }
public bool deleteJob(IDBContext dbcontext, int artifactId) { bool result = true; string DELETE_JOB_QUERY = "IF EXISTS(SELECT TOP 1 * FROM [EDDSDBO].commentJob where comment_artifactid = @artifactId)" + " BEGIN" + " DELETE from [EDDSDBO].commentJob" + " WHERE comment_artifactId = @artifactId" + " END"; System.Data.SqlClient.SqlParameter artifact = new System.Data.SqlClient.SqlParameter("@artifactId", System.Data.SqlDbType.Int); artifact.Value = artifactId; try { dbcontext.ExecuteNonQuerySQLStatement(DELETE_JOB_QUERY, new SqlParameter[] { artifact }); } catch (Exception e) { throw; } return(result); }
public override void Execute() { // Update Security Protocol ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; // Get logger Relativity.API.IAPILog _logger = this.Helper.GetLoggerFactory().GetLogger().ForContext <Agent>(); // Get current Agent ID int agentArtifactId = this.AgentID; _logger.LogDebug("Audit Log Elastic Search, current Agent ID: {agentArtifactId}", agentArtifactId.ToString()); // Display initial message this.RaiseMessageNoLogging("Getting Instance Settings.", 10); // Get ES URI Instance Settings List <Uri> elasticUris = new List <Uri>(); try { string[] uris = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchUris").Split(';'); foreach (string uri in uris) { if (Uri.IsWellFormedUriString(uri, UriKind.Absolute)) { elasticUris.Add(new Uri(uri)); } else { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchUri), single URI error ({uri})", agentArtifactId.ToString(), uri); this.RaiseMessageNoLogging(string.Format("Instance Settings error (ElasticSearchUri), single URI error ({0}).", uri), 1); return; } } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchUri)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchUri).", 1); return; } // Get ES authentication API Key Instance Settings string[] elasticApiKey = new string[] { "", "" }; try { string apiKey = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchApiKey"); if (apiKey.Length > 0) { if (apiKey.Split(':').Length == 2) { elasticApiKey = apiKey.Split(':'); } else { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchApiKey), API Key format error ({apiKey})", agentArtifactId.ToString(), apiKey); this.RaiseMessageNoLogging(string.Format("Instance Settings error (ElasticSearchApiKey), API Key format error ({0}).", apiKey), 1); return; } } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchApiKey)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchApiKey).", 1); return; } // Get ES index prefix Instance Settings (must by lowercase) string elasticIndexPrefix = ""; try { elasticIndexPrefix = this.Helper.GetInstanceSettingBundle().GetString("Relativity.AuditLogElasticSearch", "ElasticSearchIndexPrefix").ToLower(); } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexPrefix)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexPrefix).", 1); return; } // Get ES index number of replicas Instance Settings int elasticIndexReplicas = 1; try { elasticIndexReplicas = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchIndexReplicas").Value; if (elasticIndexReplicas < 0) { elasticIndexReplicas = 1; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexReplicas)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexReplicas).", 1); return; } // Get ES index number of shards Instance Settings int elasticIndexShards = 1; try { elasticIndexShards = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchIndexShards").Value; if (elasticIndexShards < 0) { elasticIndexShards = 1; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchIndexShards)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchIndexShards).", 1); return; } // Get ES synchronization threshold for one agent run int elasticSyncSize = 1000; try { elasticSyncSize = this.Helper.GetInstanceSettingBundle().GetInt("Relativity.AuditLogElasticSearch", "ElasticSearchSyncSize").Value; if (elasticSyncSize < 1000) { elasticSyncSize = 1000; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), Instance Settings error (ElasticSearchSyncSize)", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Instance Settings error (ElasticSearchSyncSize).", 1); return; } // Get database context of the instance IDBContext instanceContext = Helper.GetDBContext(-1); // Check if management table exists try { int exists = instanceContext.ExecuteSqlStatementAsScalar <int>("IF OBJECT_ID('[eddsdbo].[" + this.tableName + "]', 'U') IS NOT NULL SELECT 1 ELSE SELECT 0"); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table " + (exists == 1 ? "exists" : "does not exist"), agentArtifactId.ToString()); if (exists != 1) { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table does not exist", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Application management table does not exist.", 1); return; } } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table existence check error", agentArtifactId.ToString()); } _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), selecting Workspace", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Selecting Workspace.", 10); // Check what needs to be done int workspaceId = -1; long auditRecordId = -1; int status = -1; instanceContext.BeginTransaction(); try { // Get workspace that was synchronized latest DataTable dataTable = instanceContext.ExecuteSqlStatementAsDataTable(@" SELECT TOP(1) [CaseArtifactID], [AuditRecordID], [Status] FROM [eddsdbo].[" + this.tableName + @"] WHERE [AgentArtifactID] IS NULL ORDER BY [Status] ASC, [LastUpdated] ASC "); // If there is no workspace check if table is empty and if it is, delete it _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Workspace selection row count: {count}", agentArtifactId.ToString(), dataTable.Rows.Count.ToString()); if (dataTable.Rows.Count == 0) { int count = instanceContext.ExecuteSqlStatementAsScalar <int>("SELECT COUNT(*) FROM [eddsdbo].[" + this.tableName + "]"); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table row count: {count}", agentArtifactId.ToString(), count.ToString()); // If there are no rows in the application management table better to drop it if (count == 0) { instanceContext.ExecuteNonQuerySQLStatement("DROP TABLE [eddsdbo].[" + this.tableName + "]"); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), application management table was deleted", agentArtifactId.ToString()); } } // Else we have workspace to work with else { DataRow dataRow = dataTable.Rows[0]; workspaceId = Convert.ToInt32(dataRow["CaseArtifactID"]); auditRecordId = Convert.ToInt64(dataRow["AuditRecordID"]); status = Convert.ToInt32(dataRow["Status"]); // Update the application management table with Agent ID lock SqlParameter agentArtifactIdParam = new SqlParameter("@agentArtifactId", agentArtifactId); SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId); instanceContext.ExecuteNonQuerySQLStatement("UPDATE [eddsdbo].[" + this.tableName + "] SET [AgentArtifactID] = @agentArtifactId WHERE [CaseArtifactID] = @workspaceId", new SqlParameter[] { agentArtifactIdParam, workspaceIdParam }); } instanceContext.CommitTransaction(); } catch (Exception e) { instanceContext.RollbackTransaction(); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table querying error", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Application management table querying error.", 1); return; } // If we have Workspace ID we have to do something if (workspaceId > 0) { // Construct ES index name string elasticIndexName = elasticIndexPrefix + workspaceId.ToString(); // Construct connector to ES cluster Nest.ElasticClient elasticClient = null; try { Elasticsearch.Net.StaticConnectionPool pool = new Elasticsearch.Net.StaticConnectionPool(elasticUris, true); elasticClient = new Nest.ElasticClient(new Nest.ConnectionSettings(pool).DefaultIndex(elasticIndexName).ApiKeyAuthentication(elasticApiKey[0], elasticApiKey[1]).EnableHttpCompression()); } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search connection call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search connection call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } // Check ES cluster connection Nest.PingResponse pingResponse = elasticClient.Ping(); if (pingResponse.IsValid) { _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Ping succeeded ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Ping failed, check cluster health and connection settings ({elasticUris}, {indexName}, {elasticError})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, pingResponse.DebugInformation); this.RaiseMessageNoLogging(string.Format("Elastic Search ping failed, check cluster health and connection settings ({0}, {1}, {2}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, pingResponse.DebugInformation), 1); return; } switch (status) { // If the status is 0 we will be deleting ES index case 0: _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), deleting ES index ({indexName})", agentArtifactId.ToString(), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Deleting ES index ({0}).", elasticIndexName), 10); // Delete ES index try { Nest.DeleteIndexResponse deleteIndexResponse = elasticClient.Indices.Delete(elasticIndexName); if (deleteIndexResponse.Acknowledged) { _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index deleted ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index deletion error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search index deletion error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search deletion call error ({indexName})", agentArtifactId.ToString(), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search deletion call error ({0}).", elasticIndexName), 1); return; } // Delete related row from the application management table try { SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId); instanceContext.ExecuteNonQuerySQLStatement("DELETE FROM [eddsdbo].[" + this.tableName + "] WHERE [Status] = 0 AND [CaseArtifactID] = @workspaceId", new SqlParameter[] { workspaceIdParam }); } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}), application management table delete error", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Application management table delete error.", 1); return; } break; // If the status is 1 we will be synchronizing Audit Log with ES index case 1: _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), synchronizing Audit Log of Workspace ({workspaceId}) to ES index ({indexName})", agentArtifactId.ToString(), workspaceId.ToString(), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Synchronizing Audit Log of Workspace ({0}) to ES index ({1})", workspaceId.ToString(), elasticIndexName), 10); // If there is no records synchronized yet, we have to create ES index first if (auditRecordId == 0) { // Create ES index try { Nest.CreateIndexResponse createIndexResponse = elasticClient.Indices.Create(elasticIndexName, c => c.Settings(s => s.NumberOfShards(elasticIndexShards).NumberOfReplicas(elasticIndexReplicas)).Map <AuditRecord>(m => m.AutoMap())); if (createIndexResponse.Acknowledged) { _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index created ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Search index creation error ({elasticUris}, {indexName}, {serverError})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName, createIndexResponse.ServerError.ToString()); this.RaiseMessageNoLogging(string.Format("Elastic Search index creation error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search index creation call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search index creation call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } // Get database context of the given workspace IDBContext workspaceContext = Helper.GetDBContext(workspaceId); // Synchronize until threshold is reached int syncCount = 0; while (syncCount < elasticSyncSize) { try { // Get Audit Log to synchronize SqlParameter auditRecordIdParam = new SqlParameter("@auditRecordId", auditRecordId); DataTable dataTable = workspaceContext.ExecuteSqlStatementAsDataTable(@" SELECT TOP (1000) [AuditRecord].[ID], [AuditRecord].[TimeStamp], [AuditRecord].[ArtifactID], [AuditRecord].[Action] AS [ActionID], [AuditAction].[Action], [AuditRecord].[UserID], [AuditUser].[FullName] AS [User], [AuditRecord].[ExecutionTime], [AuditRecord].[Details], [AuditRecord].[RequestOrigination], [AuditRecord].[RecordOrigination] FROM [EDDSDBO].[AuditRecord] WITH (NOLOCK) JOIN [EDDSDBO].[AuditUser] WITH (NOLOCK) ON [AuditRecord].[UserID] = [AuditUser].[UserID] JOIN [EDDSDBO].[AuditAction] WITH (NOLOCK) ON [AuditRecord].[Action] = [AuditAction].[AuditActionID] WHERE [AuditRecord].[ID] > @auditRecordId ORDER BY [AuditRecord].[ID] ASC ", new SqlParameter[] { auditRecordIdParam }); // If there is nothing to synchronize end _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), Audit Log row count to synchronize: {count}", agentArtifactId.ToString(), dataTable.Rows.Count.ToString()); if (dataTable.Rows.Count == 0) { // Log end of Agent execution this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), completed, nothing to synchronize", agentArtifactId.ToString()); this.RaiseMessageNoLogging("Completed.", 10); return; } // Else synchronize workspace Audit Log with ES index else { // Synchronizing workspace Audit Log with ES index List <AuditRecord> auditRecords = new List <AuditRecord>(); long newAuditRecordId = auditRecordId; for (int i = 0; i < dataTable.Rows.Count; i++) { // Read Audit Log data AuditRecord auditRecord = new AuditRecord(); DataRow dataRow = dataTable.Rows[i]; auditRecord.AuditRecordId = Convert.ToInt64(dataRow["ID"]); auditRecord.TimeStamp = Convert.ToDateTime(dataRow["TimeStamp"]); auditRecord.ArtifactId = Convert.ToInt32(dataRow["ArtifactID"]); auditRecord.ActionId = Convert.ToInt32(dataRow["ActionID"]); auditRecord.Action = Convert.ToString(dataRow["Action"]); auditRecord.UserId = Convert.ToInt32(dataRow["UserID"]); auditRecord.User = Convert.ToString(dataRow["User"]); auditRecord.ExecutionTime = dataRow["ExecutionTime"] is DBNull ? default : Convert.ToInt32(dataRow["ExecutionTime"]); auditRecord.Details = dataRow["Details"] is DBNull ? default : Convert.ToString(dataRow["Details"]); auditRecord.RequestOrigination = dataRow["RequestOrigination"] is DBNull ? default : Convert.ToString(dataRow["RequestOrigination"]); auditRecord.RecordOrigination = dataRow["RecordOrigination"] is DBNull ? default : Convert.ToString(dataRow["RecordOrigination"]); auditRecords.Add(auditRecord); // Record last Audit Log ID if (newAuditRecordId < auditRecord.AuditRecordId) { newAuditRecordId = auditRecord.AuditRecordId; } // Index data in threshold is reached or we are at the last row if (auditRecords.Count >= 500 || i + 1 >= dataTable.Rows.Count) { try { Nest.BulkResponse bulkResponse = elasticClient.Bulk(b => b.Index(elasticIndexName).IndexMany(auditRecords, (descriptor, s) => descriptor.Id(s.AuditRecordId.ToString()))); if (!bulkResponse.Errors) { auditRecords.Clear(); _logger.LogDebug("Audit Log Elastic Search, Agent ({agentArtifactId}), documents synchronized to Elastic Serach index ({indexName})", agentArtifactId.ToString(), elasticIndexName); } else { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); foreach (Nest.BulkResponseItemBase itemWithError in bulkResponse.ItemsWithErrors) { _logger.LogError("Audit Log Elastic Search, Agent ({agentArtifactId}), Elastic Serach bulk index error to index {indexName} ({elasticUris}) on document {docIs}:{docError}", agentArtifactId.ToString(), elasticIndexName, string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), itemWithError.Id, itemWithError.Error.ToString()); } this.RaiseMessageNoLogging(string.Format("Elastic Serach bulk index error to index {0} ({1}).", elasticIndexName, string.Join(";", elasticUris.Select(x => x.ToString()).ToArray())), 1); return; } } catch (Exception e) { this.releaseAgentLock(agentArtifactId, auditRecordId, workspaceId); _logger.LogError(e, "Audit Log Elastic Search, Agent ({agentArtifactId}) Elastic Search bulk index call error ({elasticUris}, {indexName})", agentArtifactId.ToString(), string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName); this.RaiseMessageNoLogging(string.Format("Elastic Search bulk index call error ({0}, {1}).", string.Join(";", elasticUris.Select(x => x.ToString()).ToArray()), elasticIndexName), 1); return; } } } // After successful indexing assign new Audit Log ID auditRecordId = newAuditRecordId; } }
public override kCura.EventHandler.Response Execute() { // Update Security Protocol ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; // Get logger Relativity.API.IAPILog _logger = this.Helper.GetLoggerFactory().GetLogger().ForContext <PostInstallEventHandler>(); // Init general response kCura.EventHandler.Response response = new kCura.EventHandler.Response() { Success = true, Message = "" }; // Get current Workspace ID int workspaceId = this.Helper.GetActiveCaseID(); _logger.LogDebug("Audit Log Elastic Search, current Workspace ID: {workspaceId}", workspaceId.ToString()); // Get database context of the instance IDBContext instanceContext = Helper.GetDBContext(-1); // Existing application management table name string tableExisting = ""; try { // Get application management table tableExisting = instanceContext.ExecuteSqlStatementAsScalar("SELECT ISNULL((SELECT '" + this.TableName + "' FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_SCHEMA] = 'eddsdbo' AND [TABLE_NAME] = '" + this.TableName + "'), '')").ToString(); } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Post Install EventHandler application management table error"); response.Success = false; response.Message = "Post Install EventHandler management application table error"; return(response); } // If application management table not present, create it if (tableExisting != this.TableName) { instanceContext.BeginTransaction(); try { // Create application management table instanceContext.ExecuteNonQuerySQLStatement("CREATE TABLE [eddsdbo].[" + this.TableName + "] ([CaseArtifactID] [int] NOT NULL, [AuditRecordID] [bigint] NOT NULL, [Status] [bit] NOT NULL, [LastUpdated] [datetime] NOT NULL, [AgentArtifactID] [int] NULL)"); instanceContext.CommitTransaction(); } catch (Exception e) { instanceContext.RollbackTransaction(); _logger.LogError(e, "Audit Log Elastic Search, Post Install EventHandler application management table creation error"); response.Success = false; response.Message = "Post Install EventHandler application management table creation error"; return(response); } } // Add line to the application management table for current workspace try { // Insert to the application management table SqlParameter workspaceIdParam = new SqlParameter("@workspaceId", workspaceId); instanceContext.ExecuteNonQuerySQLStatement("INSERT INTO [eddsdbo].[" + this.TableName + "] ([CaseArtifactID], [AuditRecordID], [Status], [LastUpdated]) VALUES (@workspaceId, 0, 1, CURRENT_TIMESTAMP)", new SqlParameter[] { workspaceIdParam }); } catch (Exception e) { _logger.LogError(e, "Audit Log Elastic Search, Post Install EventHandler application management table insert error"); response.Success = false; response.Message = "Post Install EventHandler application management table insert error"; return(response); } // Log end of Post Install EventHandler _logger.LogDebug("Audit Log Elastic Search, Post Install EventHandler successfully finished"); return(response); }
public override void Execute() { try { var indexObjList = new List <IndexObj>(); using (var proxy = Helper.GetServicesManager() .CreateProxy <IRSAPIClient>(ExecutionIdentity.System)) { #region Query for workspace artifact IDs and create a list RaiseMessage("Proxy Initiated", 1); var workspaceList = new List <int>(); proxy.APIOptions.WorkspaceID = -1; Query <Workspace> query = new Query <Workspace>(); query.Condition = new WholeNumberCondition("Artifact ID", NumericConditionEnum.IsSet); RaiseMessage("Querying DB", 1); var results = proxy.Repositories.Workspace.Query(query); RaiseMessage("DB Queried, moving to foreach statements", 1); foreach (var workspaces in results.Results) { var workspace = workspaces.Artifact; workspaceList.Add(workspace.ArtifactID); RaiseMessage(String.Format("Added " + workspace), 1); } //workspaceList now has a full list of workspaces //Now need to query each workspace for dtIndexes with status New #endregion #region Find Indexes with Status of "New" in each workspace. foreach (var workspace in workspaceList) { IDBContext eddsDBContext = this.Helper.GetDBContext(workspace); var sqlWorkspaceName = "[EDDS" + workspace + "].[EDDSDBO].[dtSearchIndex]"; var sqlQueryString = @"SELECT [ID],[Name] FROM " + sqlWorkspaceName + " WHERE STATUS = 'NEW'"; var reader = eddsDBContext.ExecuteSQLStatementAsReader(sqlQueryString); while (reader.Read()) { indexObjList.Add(new IndexObj { WorkspaceArtifactID = workspace, SetID = reader.GetInt32(0), Name = reader.GetString(1) }); } reader.Close(); } #endregion #region Create and run the insert to dtSearchIndexQueue statement for each index found foreach (var dataValue in indexObjList) //We have a list of indexes with status == new //Use this info to create the insert statement for the queue table. { RaiseMessage(String.Format("Creating entry for: " + dataValue.Name), 1); var time = DateTime.Now; IDBContext eddsDBContext = this.Helper.GetDBContext(-1); string sqlInsertString = @"INSERT INTO [EDDS].[EDDSDBO].[dtSearchIndexQueue] VALUES('" + dataValue.WorkspaceArtifactID + "','" + dataValue.SetID + "','1015200','1015208',' ','1','" + time + "','9','" + time + "',NULL,'1','0','0')"; eddsDBContext.ExecuteNonQuerySQLStatement(sqlInsertString); } #endregion } } catch (Exception ex) { RaiseMessage(ex.ToString(), 1); } }