/// <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);
}
