public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE EXTERNAL_NOTIFICATION_QUEUE SET
TRIES = TRIES + 1,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids);
}
public static void DeleteSentNotifications(DbConnection connection)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $"DELETE FROM SYSTEM_NOTIFICATION_QUEUE WHERE SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public static void UpdateUnsentNotifications(DbConnection connection, IEnumerable <int> ids, string lastExceptionMessage = null)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
TRIES = TRIES + 1,
LastExceptionMessage = @lastExceptionMessage,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids, lastExceptionMessage);
}
public static void UpdateSentNotifications(DbConnection connection, IEnumerable <int> ids)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $@"UPDATE SYSTEM_NOTIFICATION_QUEUE SET
SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)},
LastExceptionMessage = NULL,
MODIFIED = {SqlQuerySyntaxHelper.Now(dbType)}
WHERE ID IN (SELECT Id FROM {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})" ;
ExecuteIdsQuery(connection, query, ids);
}
public static void InsertNotifications(DbConnection connection, string notificationsXml)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = dbType == DatabaseType.Postgres ? PgInsertNotificationsQuery : SqlInsertNotificationsQuery;
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@notifications", notificationsXml, dbType));
cmd.ExecuteNonQuery();
}
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids, string lastExceptionMessage = null)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
cmd.Parameters.AddWithValue("@lastExceptionMessage", lastExceptionMessage);
cmd.ExecuteNonQuery();
}
}
internal static void CreateFrontendViews(int contentId)
{
using (var scope = new QPConnectionScope())
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(scope.DbConnection);
if (dbType == DatabaseType.Postgres)
{
Common.CreateContentViews(scope.DbConnection, contentId, false);
}
else
{
Common.CreateFrontendViews(scope.DbConnection, contentId);
}
}
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseTypeHelper.ResolveDatabaseType(connection)));
cmd.ExecuteNonQuery();
}
}
