/// <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>
/// Returns a Binding object from the database
/// </summary>
/// <param name="bindingId">Id of the binding to be retrieved</param>
/// <returns>Returns a Binding object populated with data</returns>
internal static Binding GetBindingById(int bindingId)
{
Binding binding = null;
try
{
//create and open connection
using (OleDbConnection conn = DAL.GetConnection())
{
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Bindings where BindingID = @bindingId";
cmd.Parameters.AddWithValue("@bindingId", bindingId);
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
List <Binding> bindings = LoadBindingListFromDataReader(dr);
if (bindings.Count >= 1)
{
binding = bindings[0];
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
}
}
catch (Exception exception)
{
log.Error(Utility.GetExceptionWithMethodSignatureDetails(MethodBase.GetCurrentMethod(), exception, bindingId));
throw;
}
return(binding);
}
/// <summary>
/// Returns a ProductCategory object from the database
/// </summary>
/// <param name="productCategoryId">Id of the product category to be retrieved</param>
/// <returns>Returns a ProductCategory object populated with data</returns>
internal static ProductCategory GetProductCategoryById(int productCategoryId)
{
ProductCategory productCategory = null;
//create and open connection
using (OleDbConnection conn = DAL.GetConnection())
{
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from ProductCategories where CategoryID = @productCategoryId";
cmd.Parameters.AddWithValue("@productCategoryId", productCategoryId);
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
List <ProductCategory> categories = LoadProductCategoryListFromDataReader(dr);
if (categories.Count >= 1)
{
productCategory = categories[0];
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
}
return(productCategory);
}
/// <summary>
/// Returns an Country object from the database
/// </summary>
/// <param name="countryId">Id of the country to be retrieved</param>
/// <returns>Returns an Country object populated with data</returns>
internal static Country GetCountryById(int countryId)
{
Country country = null;
try
{
//create and open connection
using (OleDbConnection conn = DAL.GetConnection())
{
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Countries where CountryID = @countryId";
cmd.Parameters.AddWithValue("@countryId", countryId);
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
country = new Country();
country.Id = dr.GetInt32(dr.GetOrdinal("CountryID"));
country.Name = dr.GetStringSafe(dr.GetOrdinal("Country"));
country.Iso2Code = dr.GetStringSafe(dr.GetOrdinal("ISO2"));
country.IsEuropean = dr.GetBoolean(dr.GetOrdinal("European"));
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
}
}
catch (Exception exception)
{
log.Error(Utility.GetExceptionWithMethodSignatureDetails(MethodBase.GetCurrentMethod(), exception, countryId));
throw;
}
return(country);
}
/// <summary>
/// Returns a Supplier object from the database
/// </summary>
/// <param name="supplierId">Id of the supplier to be retrieved</param>
/// <returns>Returns a Supplier object populated with data</returns>
internal static Supplier GetSupplierById(int supplierId)
{
Supplier supplier = null;
//create and open connection
using (OleDbConnection conn = DAL.GetConnection())
{
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = @"select * from Verdelers where VerdelerID = @supplierId";
cmd.Parameters.AddWithValue("@supplierId", supplierId);
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
List <Supplier> suppliers = LoadSupplierListFromDataReader(dr);
if (suppliers.Count >= 1)
{
supplier = suppliers[0];
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
}
return(supplier);
}
/// <summary>
/// Returns an Instrument object from the database
/// </summary>
/// <param name="instrumentId">Id of the instrument to be retrieved</param>
/// <returns>Returns an Instrument object populated with data</returns>
internal static Instrument GetInstrumentById(int instrumentId)
{
Instrument instrument = null;
try
{
//create and open connection
using (OleDbConnection conn = DAL.GetConnection())
{
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Instrumenten where InstrumentID = @instrumentId";
cmd.Parameters.AddWithValue("@instrumentId", instrumentId);
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
List <Instrument> instruments = LoadInstrumentListFromDataReader(dr);
if (instruments.Count >= 1)
{
instrument = instruments[0];
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
}
}
catch (Exception exception)
{
log.Error(Utility.GetExceptionWithMethodSignatureDetails(MethodBase.GetCurrentMethod(), exception, instrumentId));
throw;
}
return(instrument);
}
/// <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);
}
/// <summary>
/// Returns a Customer object from the database
/// </summary>
/// <param name="customerStoreId">Id of the customer to be retrieved</param>
/// <returns>Returns a Customer object populated with data</returns>
internal static Customer GetCustomerById(int customerStoreId)
{
Customer customer = null;
try
{
//create and open connection
using (OleDbConnection conn = DAL.GetConnection())
{
//create command
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from Klanten where KlantID = @customerStoreId";
cmd.Parameters.AddWithValue("@customerStoreId", customerStoreId);
try
{
//execute a datareader, closing the connection when all the data is read from it
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
List <Customer> customers = LoadCustomerListFromDataReader(dr);
if (customers.Count >= 1)
{
customer = customers[0];
}
}
}
catch (Exception exception)
{
throw new Exception("Error while executing the following Sql statement:\n" + cmd.ToStringExtended(), exception);
}
}
}
catch (OleDbException exception)
{
log.Error(Utility.GetExceptionWithMethodSignatureDetails(MethodBase.GetCurrentMethod(), exception, customerStoreId));
throw;
}
return(customer);
}
