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));
}
private string GetExistingYaraRules(SqlKey key)
{
string queryText = $"SELECT TOP 1 [YaraRulesMatched] FROM [{TableName}] WHERE {key.GetWhereClause()} AND [YaraRulesMatched] IS NOT NULL";
return((string)ExecuteScalar(queryText, key.GetSqlParameters()));
}