/// <summary>
/// Removes a thread definition from the thread list (-> thread has finished)
/// </summary>
/// <param name="threadDef"></param>
private void RemoveThread(ThreadDefiniton threadDef)
{
lock (_threadList)
{
_threadList.Remove(threadDef);
}
}
/// <summary>
/// Reacton DoWork event:
///
/// While waiting for bulk copys or thread definition list is not empty (and no error occured) this method will not be left.
/// If thread definition list is not empty a new database command will be executed
/// </summary>
/// <param name="sender">evetn sender</param>
/// <param name="e"></param>
private void ThreadDbCommand_DoWork(object sender, DoWorkEventArgs e)
{
while ((_waitForBulkCopy || _threadList.Count > 0) && !HasError)
{
if (_threadList.Count > 0)
{
ThreadDefiniton currentThread = _threadList[0];
ExecDbCommand(currentThread);
RemoveThread(currentThread);
IncreaseFinishedDbCommands();
}
else
{
Thread.Sleep(180);
}
}
}
/// <summary>
/// Drops temporary table after database command has finished
/// </summary>
/// <param name="comm">Sql command</param>
/// <param name="threadDef">Thread definition</param>
private void DropTemporaryTable(SqlCommand comm, ThreadDefiniton threadDef)
{
try
{
comm.CommandText = "drop table " + threadDef.TempTableName;
comm.ExecuteNonQuery();
string message = string.Format("[Exec DbCommand Thread {0} : {1}]: {2} has been dropped. ({3})",
threadDef.ThreadNr, _sqlCommandType.ToString(), threadDef.TempTableName, DateTime.Now.ToString());
_messageList.AddMessage(message, IsagEvents.IsagEventType.Sql);
}
catch (Exception ex)
{
SetError();
_errorMessages.Add(string.Format("DbCommand {0} failed, unable to drop {1}. [{2}]", threadDef.ThreadNr,
threadDef.TempTableName, DateTime.Now.ToString()) + ex.ToString());
throw ex;
}
}
/// <summary>
/// Executes database command (using a thread deinition)
/// </summary>
/// <param name="threadDef">thread deinition</param>
private void ExecDbCommand(ThreadDefiniton threadDef)
{
try
{
_statusList.AddStatusEvent(Int32.Parse(threadDef.ThreadNr), -1, Status.StatusType.dbThreadProcessingDataTable, threadDef.TempTableName, IsagEvents.IsagEventType.Status);
SqlCommand comm1 = threadDef.Conn.CreateCommand();
comm1.CommandText = "SELECT COUNT(*) FROM " + threadDef.TempTableName;
object result = comm1.ExecuteScalar();
_statusList.AddStatusEvent(Int32.Parse(threadDef.ThreadNr), (int)result, Status.StatusType.dbThreadCountTempTable, threadDef.TempTableName, IsagEvents.IsagEventType.Status);
int rowsAffected = 0;
string sql;
if (_templateSql.Length > 0)
{
if (threadDef.Conn.State != System.Data.ConnectionState.Open)
{
threadDef.Conn.Open();
}
SqlCommand comm = threadDef.Conn.CreateCommand();
//comm.CommandText = sql;
comm.CommandTimeout = _dbTimeout;
for (int i = 0; i < _templateSql.Length; i++)
{
sql = _templateSql[i].Replace("#SCD_" + Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, "[#SCD_" + threadDef.TempTableName.Substring(3))
.Replace("SCD_" + Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, "SCD_" + threadDef.TempTableName.Substring(3, threadDef.TempTableName.Length - 4))
.Replace(Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, threadDef.TempTableName)
.Replace(Constants.TEMP_TABLE_PLACEHOLDER, threadDef.TempTableName);
bool executeDbCommand = true;
int attempt = 1;
while (executeDbCommand)
{
try
{
comm.CommandText = sql;
rowsAffected = comm.ExecuteNonQuery();
executeDbCommand = false;
}
catch (Exception ex)
{
if (!ex.Message.Contains("Timeout") || (attempt > _reattempts && _reattempts != 0))
{
executeDbCommand = false;
SetError();
_errorMessages.Add(string.Format("DbCommand {0} failed. [{1}]: {2}", threadDef.ThreadNr,
DateTime.Now.ToString(), sql) + ex.ToString());
throw ex;
}
else
{
attempt++;
_messageList.AddMessage(string.Format("DbCommand Thread {0}: Timeout...trying again... [{1}]", threadDef.ThreadNr, DateTime.Now.ToString()),
IsagEvents.IsagEventType.Sql);
}
}
}
}
string message = string.Format("[Exec DbCommand Thread {0} : {1}]: {2} rows were affected by the Sql Command. ({3})",
threadDef.ThreadNr, _sqlCommandType.ToString(), rowsAffected.ToString(), DateTime.Now.ToString());
_messageList.AddMessage(message, IsagEvents.IsagEventType.Sql);
_statusList.AddStatusEvent(Int32.Parse(threadDef.ThreadNr), rowsAffected, Status.StatusType.dbJobFinished, threadDef.TempTableName, IsagEvents.IsagEventType.Status);
DropTemporaryTable(comm, threadDef);
threadDef.Conn.Close();
threadDef.Conn.Dispose();
}
}
catch (Exception ex)
{
if (!HasError)
{
SetError();
_errorMessages.Add(string.Format("DbCommand Thread {0} failed. [{1}] : {2}", threadDef.ThreadNr, DateTime.Now.ToString(), _templateSql) + ex.ToString());
}
}
}
