public static void WriteResult(int sessionId, Enums.UpdateActions action, int resultSuccess, int resultFailed, int?resultNew, Enums.ProductType?productType)
{
//check if anything needs to be logged
if (resultSuccess + resultFailed == 0)
{
return;
}
try
{
//create and open connection
using (OleDbConnection conn = DAL.GetConnection())
{
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into SyncAgentSessionResults " +
"(" +
"SyncSessionId, " +
"[Timestamp], " +
"SyncAction, " +
"SyncResultSuccess, " +
"SyncResultFailed, " +
"SyncResultNew, " +
"ProductType" +
") " +
"values " +
"(" +
"@sync_session_id, " +
"@timestamp, " +
"@sync_action, " +
"@sync_result_success, " +
"@sync_result_failed, " +
"@sync_result_new, " +
"@product_type" +
")";
cmd.Parameters.AddWithValue("@sync_session_id", sessionId);
cmd.Parameters.AddWithValue("@timestamp", DateTime.Parse(DateTime.Now.ToString()));
cmd.Parameters.AddWithValue("@sync_action", action.ToString());
cmd.Parameters.AddWithValue("@sync_result_success", resultSuccess);
cmd.Parameters.AddWithValue("@sync_result_failed", resultFailed);
cmd.Parameters.AddWithValue("@sync_result_new", resultNew != 0 ? resultNew ?? (object)DBNull.Value : DBNull.Value);
cmd.Parameters.AddWithValue("@product_type", productType != null ? (int)productType : (object)DBNull.Value);
cmd.ExecuteNonQuery();
}
}
catch (Exception exception)
{
log.Error(Utility.GetExceptionWithMethodSignatureDetails(MethodBase.GetCurrentMethod(), exception, action, resultSuccess, resultFailed, resultNew, productType));
throw;
}
}
/// <summary>
/// Retrieve a list of product categories whose data has changed or been deleted in a given time frame
/// </summary>
/// <param name="timestampStart">Time stamp of the oldest record to be synchronized</param>
/// <param name="timestampEnd">Time stamp of the most recent record to be synchronized</param>
/// <param name="conn">Active connection</param>
/// <param name="action">Identifying name of the synchronization action</param>
/// <returns>Returns a list of product categories</returns>
private static List <ProductCategory> GetProductCategoryChangesByTimestamp(DateTime timestampStart, DateTime timestampEnd, OleDbConnection conn, Enums.UpdateActions action)
{
bool bLocalConnection = false;
var categories = new List <ProductCategory>();
//create a connection if none exists
if (conn == null)
{
conn = DAL.GetConnection();
bLocalConnection = true;
}
else if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string sqlWhere = "";
switch (action)
{
case Enums.UpdateActions.productCategory_update:
{
sqlWhere = "SyncWS Is Null " +
"and ((CreateDttm between @timestampStart and @timestampEnd) or " +
"(UpdateDttm between @timestampStart and @timestampEnd)) ";
break;
}
case Enums.UpdateActions.productCategory_delete:
{
sqlWhere = "SyncWS Is Null " +
"and (DeleteDttm between @timestampStart and @timestampEnd) ";
break;
}
}
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from ProductCategories " +
"where CategoryName <> \"[ Nieuwe categorie ]\" and " + sqlWhere +
"order by CategoryID";
cmd.Parameters.AddWithValue("@timestampStart", timestampStart.ToString());
cmd.Parameters.AddWithValue("@timestampEnd", timestampEnd.ToString());
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader())
{
if (dr != null && dr.HasRows)
{
categories = LoadProductCategoryListFromDataReader(dr);
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
if (bLocalConnection)
{
conn.Close();
}
return(categories);
}
/// <summary>
/// Retrieve a list of customers whose data has been changed or deleted in a given time frame
/// </summary>
/// <param name="timestampStart">Time stamp of the oldest record to be synchronized</param>
/// <param name="timestampEnd">Time stamp of the most recent record to be synchronized</param>
/// <param name="conn">Active database connection</param>
/// <param name="action">Identifying name of the synchronization action</param>
/// <returns>List of customers</returns>
private static List <Customer> GetCustomerChangesByTimestamp(DateTime timestampStart, DateTime timestampEnd, OleDbConnection conn, Enums.UpdateActions action)
{
bool bLocalConnection = false;
var customers = new List <Customer>();
//create a connection if none exists
if (conn == null)
{
conn = DAL.GetConnection();
bLocalConnection = true;
}
else if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string sqlWhere = "";
switch (action)
{
case Enums.UpdateActions.customer_SMS2WS_update:
sqlWhere = "(SyncWS Is Null " +
"or UpdateDttm > SyncWS) " +
"and Email Is Not Null " +
"and ((CreateDttm between @timestampStart and @timestampEnd) or " +
"(UpdateDttm between @timestampStart and @timestampEnd)) ";
break;
case Enums.UpdateActions.customer_SMS2WS_delete:
sqlWhere = "(SyncWS Is Null " +
"or UpdateDttm > SyncWS) " +
"and Email Is Not Null " +
"and (DeleteDttm between @timestampStart and @timestampEnd) ";
break;
case Enums.UpdateActions.customer_SMS2WS_password_reset:
sqlWhere = "ForcePasswordReset = true " +
"and Email Is Not Null ";
break;
case Enums.UpdateActions.customer_SMS2WS_teacher_approval:
sqlWhere = "SendTeacherConfirmedEmail = true " +
"and Email Is Not Null ";
break;
}
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Klanten " +
"where " + sqlWhere +
"order by KlantID";
cmd.Parameters.AddWithValue("@timestampStart", timestampStart.ToString());
cmd.Parameters.AddWithValue("@timestampEnd", timestampEnd.ToString());
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader())
{
if (dr != null && dr.HasRows)
{
customers = LoadCustomerListFromDataReader(dr);
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
if (bLocalConnection)
{
conn.Close();
}
return(customers);
}
public static void WriteResult(int sessionId, Enums.UpdateActions action, int resultSuccess, int resultFailed)
{
WriteResult(sessionId, action, resultSuccess, resultFailed, null, null);
}
