private static void ExecuteDeleteProgramsSqlServerCommand(IEnumerable<ProgramListPartition> deleteProgramRanges,
SqlConnection aConnection,
SqlTransaction aTransaction, int aDelay)
{
int aCounter = 0;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText =
"DELETE FROM Program WHERE idChannel = @idChannel AND ((endTime > @rangeStart AND startTime < @rangeEnd) OR (startTime = endTime AND startTime BETWEEN @rangeStart AND @rangeEnd))";
sqlCmd.Parameters.Add("idChannel", SqlDbType.Int);
sqlCmd.Parameters.Add("rangeStart", SqlDbType.DateTime);
sqlCmd.Parameters.Add("rangeEnd", SqlDbType.DateTime);
try
{
sqlCmd.Connection = aConnection;
sqlCmd.Transaction = aTransaction;
// Prepare the command since we will reuse it quite often
// sqlCmd.Prepare(); <-- this would need exact param field length definitions
}
catch (Exception ex)
{
Log.Info("BusinessLayer: ExecuteDeleteProgramsSqlServerCommand - Prepare caused an Exception - {0}", ex.Message);
}
foreach (ProgramListPartition partition in deleteProgramRanges)
{
sqlCmd.Parameters["idChannel"].Value = partition.IdChannel;
sqlCmd.Parameters["rangeStart"].Value = partition.Start;
sqlCmd.Parameters["rangeEnd"].Value = partition.End;
try
{
// Finally insert all our data
sqlCmd.ExecuteNonQuery();
aCounter++;
// Avoid I/O starving
if (aCounter % 2 == 0)
{
Thread.Sleep(aDelay);
}
}
catch (Exception ex)
{
Log.Error("BusinessLayer: ExecuteDeleteProgramsSqlServerCommand error - {0}, {1}", ex.Message, ex.StackTrace);
}
}
return;
}