private string PG_ReceiveMessagesScript(int messageCount) { DatabaseQueue queue = Settings.DatabaseSettings.DatabaseQueue; string tableName = queue.TableName; string field1 = queue.Fields.Where(f => f.Property == "МоментВремени").FirstOrDefault()?.Name; string field2 = queue.Fields.Where(f => f.Property == "Идентификатор").FirstOrDefault()?.Name; string field3 = queue.Fields.Where(f => f.Property == "ДатаВремя").FirstOrDefault()?.Name; string field4 = queue.Fields.Where(f => f.Property == "Отправитель").FirstOrDefault()?.Name; string field5 = queue.Fields.Where(f => f.Property == "Получатели").FirstOrDefault()?.Name; string field6 = queue.Fields.Where(f => f.Property == "ТипОперации").FirstOrDefault()?.Name; string field7 = queue.Fields.Where(f => f.Property == "ТипСообщения").FirstOrDefault()?.Name; string field8 = queue.Fields.Where(f => f.Property == "ТелоСообщения").FirstOrDefault()?.Name; //DELETE FROM _reference39 WHERE _idrref IN //(SELECT _idrref FROM _reference39 ORDER BY _code ASC, _idrref ASC LIMIT 10) //RETURNING //_code as "Код", _idrref as "Ссылка", _version as "ВерсияДанных", //_fld135 as "ДатаВремя", _fld136 as "Отправитель", //_fld137 as "Получатели", _fld138 as "ТипОперации", //_fld139 as "ТипСообщения", _fld140 as "ТелоСообщения"; StringBuilder script = new StringBuilder(); script.AppendLine("WITH cte AS"); script.AppendLine($"(SELECT {field1}, {field2} FROM {tableName} ORDER BY {field1} ASC, {field2} ASC LIMIT {messageCount})"); script.AppendLine($"DELETE FROM {tableName} t USING cte"); script.AppendLine($"WHERE t.{field1} = cte.{field1} AND t.{field2} = cte.{field2}"); script.AppendLine("RETURNING"); script.AppendLine($"t.{field1} AS \"МоментВремени\", t.{field2} AS \"Идентификатор\","); script.AppendLine($"t.{field3} AS \"ДатаВремя\", CAST(t.{field4} AS varchar) AS \"Отправитель\","); script.AppendLine($"CAST(t.{field5} AS varchar) AS \"Получатели\", CAST(t.{field6} AS varchar) AS \"ТипОперации\","); script.AppendLine($"CAST(t.{field7} AS varchar) AS \"ТипСообщения\", CAST(t.{field8} AS text) AS \"ТелоСообщения\";"); return(script.ToString()); }
private string MS_ReceiveMessagesScript(int messageCount) { DatabaseQueue queue = Settings.DatabaseSettings.DatabaseQueue; string tableName = queue.TableName; string field1 = queue.Fields.Where(f => f.Property == "МоментВремени").FirstOrDefault()?.Name; string field2 = queue.Fields.Where(f => f.Property == "Идентификатор").FirstOrDefault()?.Name; string field3 = queue.Fields.Where(f => f.Property == "ДатаВремя").FirstOrDefault()?.Name; string field4 = queue.Fields.Where(f => f.Property == "Отправитель").FirstOrDefault()?.Name; string field5 = queue.Fields.Where(f => f.Property == "Получатели").FirstOrDefault()?.Name; string field6 = queue.Fields.Where(f => f.Property == "ТипОперации").FirstOrDefault()?.Name; string field7 = queue.Fields.Where(f => f.Property == "ТипСообщения").FirstOrDefault()?.Name; string field8 = queue.Fields.Where(f => f.Property == "ТелоСообщения").FirstOrDefault()?.Name; StringBuilder script = new StringBuilder(); script.AppendLine("WITH [CTE] AS"); script.AppendLine("("); script.AppendLine($"SELECT TOP ({messageCount})"); script.AppendLine($"[{field1}] AS [МоментВремени],"); script.AppendLine($"[{field2}] AS [Идентификатор],"); script.AppendLine($"[{field3}] AS [ДатаВремя],"); script.AppendLine($"[{field4}] AS [Отправитель],"); script.AppendLine($"[{field5}] AS [Получатели],"); script.AppendLine($"[{field6}] AS [ТипОперации],"); script.AppendLine($"[{field7}] AS [ТипСообщения],"); script.AppendLine($"[{field8}] AS [ТелоСообщения]"); script.AppendLine("FROM"); script.AppendLine($"[{tableName}] WITH (ROWLOCK)"); script.AppendLine("ORDER BY"); script.AppendLine($"[{field1}] ASC, [{field2}] ASC"); script.AppendLine(")"); script.AppendLine("DELETE [CTE]"); script.AppendLine("OUTPUT deleted.[МоментВремени], deleted.[Идентификатор],"); script.AppendLine("deleted.[ДатаВремя], deleted.[Отправитель], deleted.[Получатели],"); script.AppendLine("deleted.[ТипОперации], deleted.[ТипСообщения], deleted.[ТелоСообщения];"); return(script.ToString()); }