public void UpdateServiceLocationStatus(string regionID, string serviceLocationID, string error, string status, out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; int retry = 1; do { try { ExecuteNonQuery( SQLStrings.UPDATE_STAGED_SERVICE_LOCATION_STATUS(regionID, serviceLocationID.Replace("'", "''"), error, status), "Update Service Location Status for Service Location " + serviceLocationID + " status from New to Completed"); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.ErrorFormat("Update Service Location {0} Status SQl Transaction Deadlocked, {1} retrys left.", serviceLocationID, retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); }
public void DeleteDuplicatedOrders(out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; int retry = 1; do { try { //Edit this ExecuteNonQuery( SQLStrings.DELETE_DUPLICATE_ORDERS(), "Delete Duplicate ORDERS from STAGED_ORDERS Table"); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.ErrorFormat("Delete Duplicate ORDERS from STAGED_ORDERS Table SQl Transaction Deadlocked, {0} retrys left.", retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); }
public void InsertStagedUnassignedOrders(string regionID, string orderId, string staged, string error, string status, out string databaseError, out bool errorCaught) { databaseError = string.Empty; errorCaught = false; int retry = 1; do { try { ExecuteNonQuery( SQLStrings.INSERT_STAGED_ROUTES_UNASSIGNED_ORDER(regionID, orderId, staged, status), "Insert Staged UnnasignedOrder " + orderId + " into Staged Route table for Region " + regionID); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; errorCaught = true; _Logger.ErrorFormat("Insert Staged Unassigned Order {0} SQl Transaction Deadlocked, {1} retrys left.", orderId, retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; errorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); }
public void InsertStagedRoute(string orderNumber, string regionID, string routeId, string routeStartTime, string RouteDescr, string stopSeq, string staged, string error, string status) { int retry = 1; do { try { ExecuteNonQuery( SQLStrings.INSERT_STAGED_ROUTES(orderNumber, regionID, routeId, routeStartTime, RouteDescr, stopSeq, staged, error, status), "Insert Route " + routeId + " into Staged Route table for Region " + regionID); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { _Logger.ErrorFormat("Insert Route {0} from Region {1} SQl Transaction Deadlocked, {1} retrys left.", routeId, regionID, retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); }
public List <StagedServiceLocationRecord> SelectAllStagedServiceLocationsStatus(string status, out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; List <StagedServiceLocationRecord> stagedStagedServiceLocationList = null; try { stagedStagedServiceLocationList = GetList( SQLStrings.SELECT_ALL_STAGED_SERVICE_LOCATIONS_STATUS(status), new StagedServiceLocationRecord(), "Select Staged Service Location with Status(" + status + ")" ).Cast <StagedServiceLocationRecord>().ToList(); } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); } _Logger.DebugFormat("Sucessfully Retrieved Service Locations with status {0} from Staged_Service_Location Table", status); return(stagedStagedServiceLocationList); }
public List <StagedOrderRecord> RetrievedStagedOrders(string regionID) { List <StagedOrderRecord> stagedOrderRecordList = null; try { stagedOrderRecordList = GetList( SQLStrings.SELECT_NEW_ORDERS(regionID), new StagedOrderRecord(), "Select Staged Orders (" + regionID + ")" ).Cast <StagedOrderRecord>().ToList(); } catch (DatabaseException ex) { _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); } return(stagedOrderRecordList); }
public List <StagedOrderRecord> SelectAllStagedOrdersStatus(string status, out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; List <StagedOrderRecord> stagedOrderRecords = null; int retry = 1; do { try { //Edit this stagedOrderRecords = GetList( SQLStrings.SELECT_ALL_STAGED_ORDERS_STATUS(status), new StagedOrderRecord(), "Select all staged orders based on Status: " + status + " from STAGED_ORDERS Table" ).Cast <StagedOrderRecord>().ToList(); return(stagedOrderRecords); } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.ErrorFormat("Select All Staged Order with status {0} SQl Transaction Deadlocked, {1} retrys left.", status, retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); return(stagedOrderRecords); }
public List <StagedRouteRecord> SelectStagedRoutesStatus(string status, out string databaseError, out bool databaseErrorCaught) { List <StagedRouteRecord> stagedRouteRecordList = null; databaseError = string.Empty; databaseErrorCaught = false; try { stagedRouteRecordList = GetList( SQLStrings.SELECT_ALL_STAGED_ROUTES_STATUS(status), new StagedRouteRecord(), "Select Staged routes (" + status + ")" ).Cast <StagedRouteRecord>().ToList(); } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); } return(stagedRouteRecordList); }
public List <StagedServiceLocationRecord> SelectNewStagedServiceLocations(string regionID) { List <StagedServiceLocationRecord> stagedStagedServiceLocationList = null; try { stagedStagedServiceLocationList = GetList( SQLStrings.SELECT_ALL_NEW_STAGED_SERVICE_LOCATIONS(regionID), new StagedServiceLocationRecord(), "Select Staged Service Location (" + regionID + ")" ).Cast <StagedServiceLocationRecord>().ToList(); } catch (DatabaseException ex) { _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); } _Logger.DebugFormat("Sucessfully Retrieved {0} Service Locations from Staged_Service_Location Table", stagedStagedServiceLocationList.Count); return(stagedStagedServiceLocationList); }
public void DeleteExpiredStagedRouteOrder(string regionID, string orderId, string status, string staged, out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; int retry = 1; do { try { //Edit this _Logger.DebugFormat("Executing Delete of Route Table order {0} ", orderId); ExecuteNonQuery( SQLStrings.DELETE_EXPIRED_STAGED_ROUTE_TABLE_ORDER(regionID, status, staged, orderId), "Delete expired Route Order" + orderId + " from STAGED_ROUTE Table"); _Logger.DebugFormat(" Delete of Route Table order {0} successful ", orderId); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.ErrorFormat("Delete expired Route Order {0} Status SQl Transaction Deadlocked, {1} retrys left.", orderId, retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); }
public void DeleteExpiredSLSQL(out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; int retry = 1; do { try { //Edit this _Logger.DebugFormat("Executing Delete of Error/Complete Service Location older than {0}", Config.ARCHIVE_DAYS); ExecuteNonQuery( SQLStrings.DELETE_ERROR_COMPLETE_EXP_SL(), "Delete expired Service Location from Staged Service Locations Table"); _Logger.DebugFormat("Delete Successful"); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.ErrorFormat("Delete expired Service Location Status SQl Transaction Deadlocked, {0} retrys left.", retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); }
public void DeleteExpiredStagedServiceLocation(string regionID, string serviceLocationId, string staged, out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; int retry = 1; do { try { //Edit this _Logger.DebugFormat("Executing Delete of Service Location {0}", serviceLocationId); ExecuteNonQuery( SQLStrings.DELETE_EXPIRED_STAGED_SERVICE_LOCATION(regionID, serviceLocationId.Replace("'", "''"), staged), "Delete expired Service Location" + serviceLocationId + " from STAGED_SERVICE_LOCATION Table"); _Logger.DebugFormat("Delete of Service Location {0} Successful", serviceLocationId); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.ErrorFormat("Delete expired Service Location {0} Status SQl Transaction Deadlocked, {1} retrys left.", serviceLocationId, retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } } while (retry <= Config.SQLTransactionRetry); }
public void UpdateOrderStatus(string regionID, string OrderId, string error, string status, out string databaseError, out bool databaseErrorCaught) { databaseError = string.Empty; databaseErrorCaught = false; int retry = 1; do { try { //Edit this ExecuteNonQuery( SQLStrings.UPDATE_STAGED_ORDERS_STATUS(regionID, OrderId, error, status), "Update Staged Orders Status for Order " + OrderId + " status from New to Completed"); break; } catch (DatabaseException ex) when(ex.Message.ToUpper().Contains("DEADLOCKED")) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.ErrorFormat("Update Staged Order {0} Status SQl Transaction Deadlocked, {1} retrys left.", OrderId, retry); System.Threading.Thread.Sleep(retry * 1000); retry++; } catch (DatabaseException ex) { databaseError = ex.Message; databaseErrorCaught = true; _Logger.Error("IntegrationDBAccessor | " + ex.Message, ex); System.Threading.Thread.Sleep(retry * 1000); retry++; } }while (retry <= Config.SQLTransactionRetry); }