/// <summary>
/// Fire even, status and error messages
/// </summary>
/// <param name="events">Isag events</param>
/// <param name="status">Status</param>
public void FireMessages(IsagEvents events, Status status)
{
_messageList.FireEvents(events);
_statusList.LogStatusEvents(status);
if (HasError)
{
foreach (string errorMessage in _errorMessages)
{
events.FireError(new string[] { "DbCommand", errorMessage });
}
if (_errorMessages.Count == 0)
{
events.FireError(new string[] { "DbCommand", "Fatal error: No Error Message available" });
}
}
}
/// <summary>
/// Fires event/statistic/error messages
/// </summary>
/// <param name="events">Isag events</param>
/// <param name="status">Status</param>
public void FireMessages(IsagEvents events, Status status)
{
_messageList.FireEvents(events);
_statusList.LogStatusEvents(status);
if (Status == StatusType.Error)
{
events.FireError(new string[] { "BulkCopy", "Thread " + _threadNr.ToString(), _errorMessage });
}
}
/// <summary>
/// Does the database command thread or a bulk copy thread has an error?
/// </summary>
/// <returns>Does the database command thread or a bulk copy thread has an error?</returns>
private bool HasError()
{
if (_dbCmdThread != null && _dbCmdThread.HasError)
{
_events.FireError(_dbCmdThread.GetErrorMessages());
_events.FireError(new string[] { "Error in DB Thread." });
throw new Exception("Error in DB Thread.");
}
foreach (ThreadBulkCopy thread in _bulkCopyThreads)
{
if (thread.Status == ThreadBulkCopy.StatusType.Error)
{
_events.FireError(new string[] { "Error in BulkCopy Thread." });
throw new Exception("Error in BulkCopy Thread.");
}
}
return(false);
}
/// <summary>
/// Executes the database command
/// (data is written from temporary table to destination table)
/// </summary>
/// <param name="tempTableName">temporary table</param>
public void ExecuteDbCommand(string tempTableName, int reattempts)
{
try
{
int rowsAffected = 0;
IsagEvents.IsagEventType eventType = IsagEvents.IsagEventType.MergeBegin;
string[] sqlTemplate = null;
_dbCommand.GetDbCommandDefinition(out eventType, out sqlTemplate);
if (sqlTemplate.Length > 0)
{
string sql;
SqlCommand comm = _conn.CreateCommand();
comm.CommandTimeout = _IsagCustomProperties.TimeOutDb;
if (_dbTransaction != null)
{
comm.Transaction = _dbTransaction;
}
for (int i = 0; i < sqlTemplate.Length; i++)
{
sql = sqlTemplate[i].Replace(Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, tempTableName)
.Replace(Constants.TEMP_TABLE_PLACEHOLDER, 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))
{
_events.FireError(new string[] { string.Format("DbCommand failed. [{0}]: {1}",
DateTime.Now.ToString(), sql) + ex.ToString() });
throw ex;
}
else
{
attempt++;
_events.Fire(IsagEvents.IsagEventType.Sql,
string.Format("DbCommand: Timeout...trying again... [{0}]", DateTime.Now.ToString()));
}
}
}
}
}
_events.Fire(eventType, string.Format("[Exec DbCommand: {0}]: {1} rows were affected by the Sql Command. ({2})",
eventType.ToString(), rowsAffected.ToString(), DateTime.Now.ToString()));
}
catch (Exception ex)
{
_events.FireError(new string[] { string.Format("DbCommand failed. [{0}]",
DateTime.Now.ToString()) + ex.ToString() });
throw;
}
}
/// <summary>
/// Gets sql command with placeholders for tables, columns, etc.
/// </summary>
/// <param name="eventType">event type</param>
/// <param name="sqlTemplate">sql command template</param>
public void GetDbCommandDefinition(out IsagEvents.IsagEventType eventType, out string[] sqlTemplate)
{
switch (_IsagCustomProperties.DbCommand)
{
case IsagCustomProperties.DbCommandType.Merge:
eventType = IsagEvents.IsagEventType.MergeBegin;
sqlTemplate = new string[] { GetExecuteStatementFromTemplate(SqlCreator.GetSqlMerge(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS)) };
break;
case IsagCustomProperties.DbCommandType.Merge2005:
eventType = IsagEvents.IsagEventType.Merge2005Begin;
sqlTemplate = new string[] { GetExecuteStatementFromTemplate(SqlCreator.GetSqlMerge2005(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS)) };
break;
case IsagCustomProperties.DbCommandType.UpdateTblInsertRow:
eventType = IsagEvents.IsagEventType.UpdateTblInsertRowBegin;
string spInsertName = "[#" + Constants.SP_INSERT_BY_CURSOR + "_" + Guid.NewGuid().ToString() + "]";
string sqlSpInsert = SqlCreator.GetSqlInsertSP(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS).Replace("<SPName>", spInsertName);
string sqlUpdate = SqlCreator.GetSqlUpdate(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS);
string sqlExecSp = "EXEC " + spInsertName;
string sqlDropSpInsert = "DROP PROCEDURE " + spInsertName;
sqlTemplate = new string[] { sqlSpInsert, sqlUpdate, sqlExecSp };
break;
case IsagCustomProperties.DbCommandType.UpdateRowInsertRow:
eventType = IsagEvents.IsagEventType.UpdateRowInsertRowBegin;
string spInsertName1 = "[#" + Constants.SP_INSERT_BY_CURSOR + "_" + _componentMetaData.Name + "_" + Guid.NewGuid().ToString() + "]";
string spUpdateName1 = "[#" + Constants.SP_UPDATE_BY_CURSOR + "_" + _componentMetaData.Name + "_" + Guid.NewGuid().ToString() + "]";
string sqlSpInsert1 = SqlCreator.GetSqlInsertSP(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS).Replace("<SPName>", spInsertName1);
string sqlSpUpdate1 = SqlCreator.GetSqlUpdateSP(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS).Replace("<SPName>", spUpdateName1);
string sqlExecInsertSp = "EXEC " + spInsertName1;
string sqlExecUpdateSp = "EXEC " + spUpdateName1;
string sqlDropSpInsert1 = "DROP PROCEDURE " + spInsertName1;
string sqlDropSpUpdate = "DROP PROCEDURE " + spUpdateName1;
sqlTemplate = new string[] { sqlSpInsert1, sqlSpUpdate1, sqlExecUpdateSp, sqlExecInsertSp };
break;
case IsagCustomProperties.DbCommandType.Insert:
eventType = IsagEvents.IsagEventType.InsertBegin;
sqlTemplate = new string[] { GetExecuteStatementFromTemplate(SqlCreator.GetSqlInsert(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS)) };
break;
case IsagCustomProperties.DbCommandType.BulkInsert:
eventType = IsagEvents.IsagEventType.BulkInsert;
sqlTemplate = null;
break;
case IsagCustomProperties.DbCommandType.BulkInsertRowLock:
eventType = IsagEvents.IsagEventType.BulkInsert;
sqlTemplate = null;
break;
default:
_events.FireError(new string[] { "Choosen DBCommand is invalid." });
throw new Exception("Choosen DBCommand is invalid.");
}
}