public void UpdateExistingYaraRule(SqlKey key, List <string> newYaraMatchedRules) { string newYaraRulesMatchedValue = YaraHelper.FormatDelimitedRulesString(newYaraMatchedRules); SQLiteParameter yaraMatchedRulesParameter = SqlHelper.GetParameter("YaraRulesMatched", newYaraRulesMatchedValue); List <SQLiteParameter> parameters = key.GetSqlParameters().ToList(); parameters.Add(yaraMatchedRulesParameter); string commandText = string.Format(SqlStrings.UpdateYaraRules, yaraMatchedRulesParameter.ParameterName); ExecuteNonQuery(commandText, parameters); }
private bool InsertIntoDB(FileProperties fileProperties, SqlKey key, List <SqlParameter> sqlParameters) { string updateText = string.Empty; if (!string.IsNullOrWhiteSpace(fileProperties.YaraMatchedRules)) { List <string> newYaraMatchedRules = new List <string>(); string currentYaraRulesMatchedValue = GetExistingYaraRules(key); if (currentYaraRulesMatchedValue != null) { newYaraMatchedRules.AddRange(YaraHelper.ParseDelimitedRulesString(currentYaraRulesMatchedValue)); } newYaraMatchedRules.AddRange(YaraHelper.ParseDelimitedRulesString(fileProperties.YaraMatchedRules)); string newYaraRulesMatchedValue = YaraHelper.FormatDelimitedRulesString(newYaraMatchedRules); SqlParameter yaraMatchedValueParameter = ParameterHelper.GetNewStringParameter("YaraRulesMatched", newYaraRulesMatchedValue); sqlParameters.Add(yaraMatchedValueParameter); updateText = $"UPDATE [{TableName}] SET [YaraRulesMatched] = {yaraMatchedValueParameter.ParameterName} WHERE {key.GetWhereClause()}"; } string columnNames = sqlParameters.AsColumnString(); string values = sqlParameters.AsValuesString(); string insertStatement = $@" INSERT INTO [{TableName}] ({columnNames},[PrevalenceCount],[DateSeen]) VALUES ({values},1,GETDATE())" ; string commandText = $@"DECLARE @PREVALENCECOUNT INT; SET @PREVALENCECOUNT = ( SELECT [PrevalenceCount] FROM [{TableName}] WHERE {key.GetWhereClause()} ) SET @PREVALENCECOUNT = @PREVALENCECOUNT + 1; IF(@PREVALENCECOUNT IS NOT NULL) BEGIN UPDATE [{TableName}] SET [PrevalenceCount] = @PREVALENCECOUNT WHERE {key.GetWhereClause()} {updateText} END ELSE BEGIN {insertStatement} END "; return(ExecNonQuery(commandText, sqlParameters)); }