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