public int DeleteLogProperties(Guid componentId, int maxCount, DateTime toDate)
{
using (var connection = Context.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = 0;
string query = string.Format(
@"DELETE FROM LogParameters
WHERE Id IN (SELECT TOP {0} Id FROM LogParameters
WHERE LogId IN (SELECT TOP {0} Id FROM Logs WHERE ComponentId = @ComponentId AND Date < @Date ORDER BY Date) ORDER BY Id)",
maxCount);
command.CommandText = query;
var parameter = command.CreateParameter();
parameter.ParameterName = "@Date";
parameter.Value = toDate;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@ComponentId";
parameter.Value = componentId;
command.Parameters.Add(parameter);
return(command.ExecuteNonQuery());
}
}
}
public int DeleteMetricsHistory(Guid componentId, int maxCount, DateTime toDate)
{
using (var connection = Context.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = 0;
string query = string.Format(
"DELETE FROM [dbo].[MetricHistory] WHERE Id IN (SELECT TOP {0} Id FROM [dbo].[MetricHistory] WHERE ComponentId = @ComponentId AND ActualDate < @ActualDate ORDER BY ActualDate)",
maxCount);
command.CommandText = query;
var parameter = command.CreateParameter();
parameter.ParameterName = "@ActualDate";
parameter.Value = toDate;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@ComponentId";
parameter.Value = componentId;
command.Parameters.Add(parameter);
return(command.ExecuteNonQuery());
}
}
}
public void DeleteBySubscriptionId(Guid subscriptionId)
{
using (var connection = Context.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = 0;
var query = @"
DELETE FROM [dbo].[LastComponentNotifications]
WHERE NotificationId IN (
SELECT Id
FROM [dbo].[Notifications]
WHERE SubscriptionId = @SubscriptionId)
DELETE FROM [dbo].[NotificationsHttp]
WHERE NotificationId IN (
SELECT Id
FROM [dbo].[Notifications]
WHERE SubscriptionId = @SubscriptionId)
DELETE FROM [dbo].[Notifications]
WHERE SubscriptionId = @SubscriptionId";
command.CommandText = query;
var parameter = command.CreateParameter();
parameter.ParameterName = "@SubscriptionId";
parameter.Value = subscriptionId;
command.Parameters.Add(parameter);
SqlCommandHelper.ExecuteNonQuery(command);
}
}
}