public void AddStatusEvent(int nr, int count, Status.StatusType statusType, IsagEvents.IsagEventType eventType)
{
lock (_eventStatusList)
{
_eventStatusList.Add(new StatusEvent(nr, count, statusType, eventType));
}
}
/// <summary>
/// constructor
/// </summary>
/// <param name="nr">thread number</param>
/// <param name="count">number of data rows</param>
/// <param name="statusType">status type</param>
/// <param name="timestamp">timestamp</param>
/// <param name="eventType">event type</param>
public StatusEvent(int nr, int count, Status.StatusType statusType, DateTime timestamp, IsagEvents.IsagEventType eventType)
{
Nr = nr;
Count = count;
StatusType = statusType;
Timestamp = timestamp;
EventType = eventType;
}
/// <summary>
/// Creates Thread handler
/// </summary>
private void CreateThreadHandler()
{
IsagEvents.IsagEventType eventType = IsagEvents.IsagEventType.MergeBegin;
string[] sqlTemplate = null;
_dbCommand.GetDbCommandDefinition(out eventType, out sqlTemplate);
_threadHandler = new ThreadHandler(ComponentMetaData.InputCollection[Constants.INPUT_NAME], _IsagCustomProperties,
GetConnectionStringForThread(), Conn, _events, eventType, sqlTemplate, _status);
}
/// <summary>
/// Adds a message
/// </summary>
/// <param name="message">message text</param>
/// <param name="parameter">message parameter</param>
/// <param name="eventType">event type</param>
public void AddMessage(string message, string[] parameter, IsagEvents.IsagEventType eventType)
{
lock (_messages)
{
_messages.Add(new EventMessage()
{
Message = message, Parameter = parameter, EventType = eventType
});
}
}
/// <summary>
/// Adds a message
/// </summary>
/// <param name="message">message text</param>
/// <param name="eventType">event type</param>
public void AddMessage(string message, IsagEvents.IsagEventType eventType)
{
lock (_messages)
{
_messages.Add(new EventMessage()
{
Message = message, EventType = eventType
});
}
}
/// <summary>
/// constructor
/// </summary>
/// <param name="connection">Sql connection</param>
/// <param name="sqlCommandType">Sql command type</param>
/// <param name="dbTimeout">Database timout</param>
/// <param name="reattempts">Number of reattempts if database command failed</param>
/// <param name="templateSql">Sql command templates</param>
public ThreadDbCommand(SqlConnection connection, IsagEvents.IsagEventType sqlCommandType, int dbTimeout, int reattempts, string[] templateSql)
{
_connection = connection;
_sqlCommandType = sqlCommandType;
_dbTimeout = dbTimeout;
_reattempts = reattempts;
_templateSql = templateSql;
this.DoWork += new DoWorkEventHandler(ThreadDbCommand_DoWork);
this.RunWorkerAsync();
}
/// <summary>
/// Drops temporary table
/// </summary>
/// <param name="tempTableName">temporary table name</param>
/// <param name="eventType">Isag event type</param>
public void DropTempTable(string tempTableName, IsagEvents.IsagEventType eventType)
{
try
{
SqlExecutor.DropTable(tempTableName, _bulkConn, _IsagCustomProperties.TimeOutDb, _dbTransaction);
_events.Fire(eventType, string.Format("[Exec DbCommand: {0}]: Temporary Table has been dropped. ({1})",
eventType.ToString(), DateTime.Now.ToString()));
}
catch (Exception ex)
{
_events.Fire(eventType, string.Format("[Exec DbCommand: {0}]: Unable to drop temporary table. ({1})",
eventType.ToString(), DateTime.Now.ToString()));
throw ex;
}
}
/// <summary>
/// constructor
/// </summary>
/// <param name="input">SSIS input</param>
/// <param name="isagCustomProperties">Components custom properties</param>
/// <param name="cstr">Conectionststring</param>
/// <param name="conn">Sql connection</param>
/// <param name="events">Isag events</param>
/// <param name="dbCommandEventType">Database command event type</param>
/// <param name="dbCommandTemplate">Database command template</param>
/// <param name="status"></param>
public ThreadHandler(IDTSInput100 input, IsagCustomProperties isagCustomProperties,
string cstr, SqlConnection conn, IsagEvents events,
IsagEvents.IsagEventType dbCommandEventType, string[] dbCommandTemplate, Status status)
{
_conn = conn;
if (dbCommandTemplate != null)
{
_dbCmdThread = new ThreadDbCommand(conn, dbCommandEventType, isagCustomProperties.TimeOutDb, isagCustomProperties.Reattempts, dbCommandTemplate);
}
_maxAllowdThreads = isagCustomProperties.MaxThreadCount;
_input = input;
_isagCustomProperties = isagCustomProperties;
_timeoutDb = isagCustomProperties.TimeOutDb;
_reattempts = isagCustomProperties.Reattempts;
_cstr = cstr;
_events = events;
_status = status;
}
/// <summary>
/// Executes pre- oder post execute command
/// </summary>
/// <param name="cmdType"> Events.IsagEventType.PreSql oder Events.IsagEventType.PostSql </param>
private void ExecPreOrPostExecuteStatement(IsagEvents.IsagEventType cmdType)
{
string sql = "";
string cmdTypeName = "";
try
{
if (cmdType == IsagEvents.IsagEventType.PreSql && _IsagCustomProperties.HasPreSql)
{
sql = _dbCommand.GetExecuteStatementFromTemplate(_IsagCustomProperties.PreSql);
cmdTypeName = "PreSql";
}
else if (cmdType == IsagEvents.IsagEventType.PostSql && _IsagCustomProperties.HasPostSql)
{
sql = _dbCommand.GetExecuteStatementFromTemplate(_IsagCustomProperties.PostSql);
cmdTypeName = "PostSql";
}
else if (cmdType != IsagEvents.IsagEventType.PreSql && cmdType != IsagEvents.IsagEventType.PostSql)
{
throw new Exception("Unknown CommandType in ExecPrePostExecuteStatement: " + cmdType);
}
// execute pre- oder post execute command
if (sql.Length > 0)
{
SqlTransaction transaction = _IsagCustomProperties.UseMultiThreading ? null : _txAll.Transaction;
int rowsAffected = SqlExecutor.ExecSql(sql, Conn, _IsagCustomProperties.TimeOutDb, transaction);
_events.Fire(IsagEvents.IsagEventType.Sql,
"[ExecSql:" + cmdType.ToString() + "]: {0} rows were affected by the Sql Command.",
new string[] { rowsAffected.ToString(), ((int)cmdType).ToString() });
_events.Fire(cmdType, cmdTypeName + " Statement has been executed.");
}
}
catch (Exception ex)
{
_events.FireError(new string[] { cmdTypeName, ex.Message });
throw;
}
}
/// <summary>
/// Add status
/// </summary>
/// <param name="nr">thread number</param>
/// <param name="count">number of data rows</param>
/// <param name="statusType">status type</param>
/// <param name="timestamp">timestamp</param>
/// <param name="eventType">event type</param>
public void AddStatus(int nr, int count, StatusType statusType, DateTime timestamp, IsagEvents.IsagEventType eventType)
{
AddStatus(new StatusEvent(nr, count, statusType, timestamp, eventType));
}
/// <summary>
/// Add status
/// </summary>
/// <param name="nr">thread number</param>
/// <param name="count">number of data rows</param>
/// <param name="statusType">status type</param>
/// <param name="eventType">event type</param>
public void AddStatus(int nr, int count, StatusType statusType, IsagEvents.IsagEventType eventType)
{
AddStatus(nr, count, statusType, DateTime.Now, eventType);
}
/// <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;
}
}
public void AddStatusEvent(int nr, int count, Status.StatusType statusType, string param1, IsagEvents.IsagEventType eventType)
{
lock (_eventStatusList)
{
StatusEvent statusEvent = new StatusEvent(nr, count, statusType, eventType);
statusEvent.Param1 = param1;
_eventStatusList.Add(statusEvent);
}
}
/// <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.");
}
}
/// <summary>
/// Fire event
/// </summary>
/// <param name="eventType">Isag event type</param>
/// <param name="message">event message</param>
public static void Log(IsagEvents.IsagEventType eventType, string message)
{
Events.Fire(eventType, message + DateTime.Now.ToString() + ":" + DateTime.Now.Millisecond.ToString());
}
