/// <summary> /// Initializes a new instance of the <see cref="DataBaseFunctions" /> class. /// </summary> /// <param name="theDatabase">The database object <see cref="DataBase.Database" /> which is used to access the database.</param> /// <exception cref="System.Exception">Database connection failed! Terminating.</exception> public DataBaseFunctions(Database theDatabase) { Database = theDatabase; while (true) { if (!theDatabase.IsAvailable()) { int waitingTime = 5000; System.Threading.Thread.Sleep(waitingTime); FileOperation.LogError($"Database is not available. Retry in {waitingTime} ms", 128 * 1024 * 1024); } else { break; } } FileOperation.MaxLogFileSize = GetConfigValue <int>("MaxLogFileSize"); this.timerReorganizeOrRebuildIndices.Elapsed += this.OnReorganizeOrRebuildIndicesTimerElapsed; this.timerReorganizeOrRebuildIndices.AutoReset = false; this.timerReorganizeOrRebuildIndices.Start(); }
/// <summary> /// Query that returns no data but number of affected rows. This query works transactional, changes done are will be /// rolled back in case of errors. /// </summary> /// <param name="sqlStatement">The SQL statement.</param> /// <returns> /// The result of NonQuery. -4 general exception, -3 exception on rollback, -2 rollback done, all other depend on the /// SQL /// statement itself. /// </returns> public int NonQueryWithTransaction(string sqlStatement) { int retryCount = 0; int result = 0; if (this.ConnectionType == ConnType.MSSQL) { while (true) { try { using (var con = new SqlConnection(this.connectionStringBuilderForSql.ConnectionString)) { con.Open(); var adapter = new SqlDataAdapter(); SqlTransaction transaction = con.BeginTransaction(); try { adapter.SelectCommand = new SqlCommand(sqlStatement, con); adapter.SelectCommand.CommandTimeout = SqlCmdTimeout; adapter.SelectCommand.Transaction = transaction; result = adapter.SelectCommand.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { if (ex is SqlException && Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) && retryCount < MaxRetry) { retryCount++; Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait); continue; } else { result = -2; this.WriteErrorLog(ex.ToString(), sqlStatement); // Attempt to roll back the transaction. try { transaction.Rollback(); throw; } catch (Exception ex2) { result = -3; // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. FileOperation.LogError(ex2, FileOperation.MaxLogFileSize); throw; } } } } break; } catch (Exception ex) { if (ex is SqlException && Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) && retryCount < MaxRetry) { retryCount++; Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait); continue; } this.WriteErrorLog(ex.ToString(), sqlStatement); throw; } } } else if (this.ConnectionType == ConnType.ODBC) { using (var con = new OdbcConnection(this.connectionStringBuilderForOdbc.ConnectionString)) { con.Open(); var adapter = new OdbcDataAdapter(); OdbcTransaction transaction = con.BeginTransaction(); try { adapter.SelectCommand = new OdbcCommand(sqlStatement, con); adapter.SelectCommand.CommandTimeout = SqlCmdTimeout; adapter.SelectCommand.Transaction = transaction; result = adapter.SelectCommand.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { result = -2; this.WriteErrorLog(ex.ToString(), sqlStatement); // Attempt to roll back the transaction. try { transaction.Rollback(); throw; } catch (Exception ex2) { result = -3; // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. FileOperation.LogError(ex2, FileOperation.MaxLogFileSize); throw; } } } } else if (this.ConnectionType == ConnType.OLEDB) { using (var con = new OleDbConnection(this.connectionStringBuilderForOledb.ConnectionString)) { con.Open(); var adapter = new OleDbDataAdapter(); OleDbTransaction transaction = con.BeginTransaction(); try { adapter.SelectCommand = new OleDbCommand(sqlStatement, con); adapter.SelectCommand.CommandTimeout = SqlCmdTimeout; adapter.SelectCommand.Transaction = transaction; result = adapter.SelectCommand.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { result = -2; this.WriteErrorLog(ex.ToString(), sqlStatement); // Attempt to roll back the transaction. try { transaction.Rollback(); throw; } catch (Exception ex2) { result = -3; // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. FileOperation.LogError(ex2, FileOperation.MaxLogFileSize); throw; } } } } return(result); }
/// <summary> /// Writes an error into 'ErrorLog.txt' text file. /// </summary> /// <param name="message"> /// The Error message. /// </param> /// <param name="strCommand"> /// SQL command. /// </param> private void WriteErrorLog(string message, string strCommand) { FileOperation.LogError("Message: \r\n" + message + "\r\n Command: \r\n" + strCommand, FileOperation.MaxLogFileSize); }
/// <summary> /// Logs the error. /// </summary> /// <param name="ex">The ex.</param> /// <param name="maxFileSize">Maximum size of the file.</param> public static void LogError(Exception ex, int maxFileSize = 128 * 1024 * 1024) { FileOperation.WriteTextToFile(ApplicationPath + @"\LogFiles\", "Errorlog.txt", DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss.fff") + "\r\n" + ex.ToString() + "\r\n", maxFileSize); }
/// <summary> /// Logs the event data. /// </summary> /// <param name="orderNumber">The order number.</param> /// <param name="messageText">The message text.</param> /// <param name="orderInformation">The order information.</param> /// <param name="maxFileSize">Maximum size of the file.</param> public static void LogOrderData(int orderNumber, string messageText, string orderInformation = "", int maxFileSize = 128 * 1024 * 1024) { FileOperation.WriteTextToFile(ApplicationPath + @"\OrderLog\", orderNumber + ".txt", DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss.fff") + "\r\n" + messageText + "\r\n" + orderInformation + "\r\n\r\n", maxFileSize); FileOperation.WriteTextToFile(ApplicationPath + @"\OrderLog\", orderNumber + "_Short.txt", DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss.fff") + "\r\n" + messageText + "\r\n\r\n", maxFileSize); }
/// <summary> /// Logs the event data. /// </summary> /// <param name="path">The path.</param> /// <param name="filename">The filename.</param> /// <param name="text">The text.</param> /// <param name="maxFileSize">Maximum size of the file.</param> public static void LogEventData(string path, string filename, string text, int maxFileSize = 128 * 1024 * 1024) { FileOperation.WriteTextToFile(path, filename, text, maxFileSize); }
/// <summary> /// Logs the io interface error. /// </summary> /// <param name="interfaceDeviceId">The interface device identifier.</param> /// <param name="text">The text.</param> /// <param name="maxFileSize">Maximum size of the file.</param> public static void LogIoInterfaceError(int interfaceDeviceId, string text, int maxFileSize = 128 * 1024 * 1024) { FileOperation.WriteTextToFile(ApplicationPath + @"\InterfaceLog\", "LOG_IoDevice_" + interfaceDeviceId + ".txt", DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss.fff") + "\r\n" + text + "\r\n", maxFileSize); }
/// <summary> /// Logs the interface error. /// </summary> /// <param name="logfileName">Name of the logfile.</param> /// <param name="interfaceName">Name of the interface.</param> /// <param name="ex">The ex.</param> /// <param name="additionalText">The additional text.</param> /// <param name="maxFileSize">Maximum size of the file.</param> public static void LogInterfaceError(string logfileName, string interfaceName, Exception ex, string additionalText, int maxFileSize = 128 * 1024 * 1024) { FileOperation.WriteTextToFile(ApplicationPath + @"\InterfaceLog\", "LOG_" + logfileName + ".txt", DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss.fff") + "\r\n" + interfaceName + "\r\n" + ex.ToString() + "\r\n" + additionalText + "\r\n", maxFileSize); }
/// <summary> /// Logs the interface error. /// </summary> /// <param name="interfaceName">Name of the interface.</param> /// <param name="ex">The ex.</param> /// <param name="additionalText">The additional text.</param> /// <param name="maxFileSize">Maximum size of the file.</param> public static void LogInterfaceError(string interfaceName, Exception ex, string additionalText, int maxFileSize = 128 * 1024 * 1024) { FileOperation.LogInterfaceError(interfaceName, interfaceName, ex, additionalText, maxFileSize); }
/// <summary> /// Logs the interface data. /// </summary> /// <param name="interfaceName">Name of the interface.</param> /// <param name="text">The text.</param> /// <param name="maxFileSize">Maximum size of the file.</param> public static void LogInterfaceData(string interfaceName, string text, int maxFileSize = 128 * 1024 * 1024) { FileOperation.LogInterfaceData(interfaceName, interfaceName, text, maxFileSize); }