private void Read(SqlConnection connection, SqlTransaction transaction, CalculationDayDB result, DateTime date)
{
SqlCommand command = new SqlCommand("[outlook].[GetLastCalculationDay]");
command.Connection = connection;
command.Transaction = transaction;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("Date", date));
SqlDataReader sqlDataReader = command.ExecuteReader();
if (sqlDataReader.Read())
{
result.CalculateEmailsId = (int)sqlDataReader["CalculateEmailsId"];
result.Date = (DateTime)sqlDataReader["Date"];
result.MailCountAdd = (int)sqlDataReader["MailCountAdd"];
result.MailCountSent = (int)sqlDataReader["MailCountSent"];
result.MailCountProcessed = (int)sqlDataReader["MailCountProcessed"];
result.TaskCountAdded = (int)sqlDataReader["TaskCountAdded"];
result.TaskCountRemoved = (int)sqlDataReader["TaskCountRemoved"];
result.TaskCountFinished = (int)sqlDataReader["TaskCountFinished"];
sqlDataReader.Close();
connection.Close();
}
else
{
throw new Exception("No record retrieved");
}
}
public void UpdateCalculationDay(Action <CalculationDayDB> updateAction, DateTime date)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
CalculationDayDB result = new CalculationDayDB();
Read(connection, transaction, result, date);
updateAction(result);
Write(connection, transaction, result);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
public List <CalculationDayDB> GetCalculationDays(DateTime startDate, DateTime endDay)
{
startDate = ReplaceMinDateWithSQLMinDate(startDate);
endDay = ReplaceMinDateWithSQLMaxDate(endDay);
List <CalculationDayDB> result = new List <CalculationDayDB>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("[outlook].[GetCalculationDay]");
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("startDate", startDate));
command.Parameters.Add(new SqlParameter("endDate", endDay));
connection.Open();
SqlDataReader sqlDataReader = command.ExecuteReader();
while (sqlDataReader.Read())
{
var item = new CalculationDayDB();
result.Add(item);
item.CalculateEmailsId = (int)sqlDataReader["CalculateEmailsId"];
item.Date = (DateTime)sqlDataReader["Date"];
item.MailCountAdd = (int)sqlDataReader["MailCountAdd"];
item.MailCountSent = (int)sqlDataReader["MailCountSent"];
item.MailCountProcessed = (int)sqlDataReader["MailCountProcessed"];
item.TaskCountAdded = (int)sqlDataReader["TaskCountAdded"];
item.TaskCountRemoved = (int)sqlDataReader["TaskCountRemoved"];
item.TaskCountFinished = (int)sqlDataReader["TaskCountFinished"];
}
connection.Close();
}
return(result);
}
public CalculationDay GetLastCalculationDay(DateTime date)
{
var mapper = mapperConfiguration.CreateMapper();
CalculationDayDB resultDB = FillTodaysCalculationDetails(date);
CalculationDay result = mapper.Map <CalculationDayDB, CalculationDay>(resultDB);
return(result);
}
private void SaveDetailList(CalculationDayDB TodayCalculationDetails)
{
lock (padloc)
{
// WriteToLog($"TodayCalculationDetails.MailCountAdd: {TodayCalculationDetails.MailCountAdd}");
DBManager.SaveTodayCalculationDay(TodayCalculationDetails);
}
}
private void Write(SqlConnection connection, SqlTransaction transaction, CalculationDayDB calcualtionDay)
{
SqlCommand command = new SqlCommand("[outlook].[UpdateLastCalculationDay]");
command.Connection = connection;
command.Transaction = transaction;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@calculateEmailsId", calcualtionDay.CalculateEmailsId));
command.Parameters.Add(new SqlParameter("@MailCountAdd", calcualtionDay.MailCountAdd));
command.Parameters.Add(new SqlParameter("@MailCountSent", calcualtionDay.MailCountSent));
command.Parameters.Add(new SqlParameter("@MailCountProcessed", calcualtionDay.MailCountProcessed));
command.Parameters.Add(new SqlParameter("@TaskCountAdded", calcualtionDay.TaskCountAdded));
command.Parameters.Add(new SqlParameter("@TaskCountRemoved", calcualtionDay.TaskCountRemoved));
command.Parameters.Add(new SqlParameter("@TaskCountFinished", calcualtionDay.TaskCountFinished));
var recordAffected = command.ExecuteNonQuery();
if (recordAffected != 1)
{
throw new Exception("No record updated");
}
}
public void SaveTodayCalculationDay(CalculationDayDB calcualtionDay)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("[outlook].[UpdateLastCalculationDay]");
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Date", calcualtionDay.Date));
command.Parameters.Add(new SqlParameter("@MailCountAdd", calcualtionDay.MailCountAdd));
command.Parameters.Add(new SqlParameter("@MailCountSent", calcualtionDay.MailCountSent));
command.Parameters.Add(new SqlParameter("@MailCountProcessed", calcualtionDay.MailCountProcessed));
command.Parameters.Add(new SqlParameter("@TaskCountAdded", calcualtionDay.TaskCountAdded));
command.Parameters.Add(new SqlParameter("@TaskCountRemoved", calcualtionDay.TaskCountRemoved));
command.Parameters.Add(new SqlParameter("@TaskCountFinished", calcualtionDay.TaskCountFinished));
connection.Open();
var recordAffected = command.ExecuteNonQuery();
if (recordAffected != 1)
{
throw new Exception("No record updated");
}
connection.Close();
}
}
