public void Start()
{
//Recreate database
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Delete("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
//Logging (only works with existing configuration nlog config in App.config)
CreateLogTablesTask.CreateLog();
StartLoadProcessTask.Start("Process 1");
ControlFlow.STAGE = "Staging";
SqlTask.ExecuteNonQuery("some sql", "Select 1 as test");
TransferCompletedForLoadProcessTask.Complete();
ControlFlow.STAGE = "DataVault";
Sequence.Execute("some custom code", () => { });
LogTask.Warn("Some warning!");
EndLoadProcessTask.End("Everything successful");
string jsonLP = GetLoadProcessAsJSONTask.GetJSON();
string jsonLog = GetLogAsJSONTask.GetJSON(1);
}
public void IsLoadProcessKeySetForLogTask()
{
//Arrange
ControlFlow.DefaultDbConnection = SqlConnection;
CreateLoadProcessTableTask.Create("test_lp_logtask");
CreateLogTableTask.Create("test_log_logtask");
ControlFlow.AddLoggingDatabaseToConfig(SqlConnection, NLog.LogLevel.Info, "test_log_logtask");
//Act
StartLoadProcessTask.Start("Test process 15");
long?processId1 = ControlFlow.CurrentLoadProcess.Id;
LogTask.Error("Test1");
LogTask.Warn("Test2");
LogTask.Info("Test3");
//Assert
Assert.Equal(3, new RowCountTask("test_log_logtask",
$"message like 'Test%' AND load_process_id = {processId1}")
{
DisableLogging = true
}.Count().Rows);
//Cleanup
DropTableTask.Drop(ControlFlow.LogTable);
DropTableTask.Drop(ControlFlow.LoadProcessTable);
}
public void IsLoadProcessKeySetIfRestarted()
{
//Arrange
ControlFlow.DefaultDbConnection = SqlConnection;
CreateLoadProcessTableTask.Create("test_lp_restart");
CreateLogTableTask.Create("test_log_restart");
ControlFlow.AddLoggingDatabaseToConfig(SqlConnection, NLog.LogLevel.Info, "test_log_restart");
//Act
StartLoadProcessTask.Start("Test process 13");
long?processId1 = ControlFlow.CurrentLoadProcess.Id;
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
Assert.Equal(2, new RowCountTask("test_log_restart", $"message='Test Task' AND load_process_id = {processId1}")
{
DisableLogging = true
}.Count().Rows);
StartLoadProcessTask.Start("Test process 14");
long?processId2 = ControlFlow.CurrentLoadProcess.Id;
//Assert
Assert.NotEqual(processId1, processId2);
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
Assert.Equal(2, new RowCountTask("test_log_restart", $"message='Test Task' AND load_process_id = {processId2}")
{
DisableLogging = true
}.Count().Rows);
//Cleanup
DropTableTask.Drop(ControlFlow.LogTable);
DropTableTask.Drop(ControlFlow.LoadProcessTable);
}
public void ReadLastAbortedProcess()
{
//Arrange
ControlFlow.DefaultDbConnection = SqlConnection;
CreateLoadProcessTableTask.Create("test_lpkey_lastabort");
StartLoadProcessTask.Start("Test process 10");
Task.Delay(10).Wait(); //Sql Server datetime is not that exact
EndLoadProcessTask.End();
Task.Delay(10).Wait();
StartLoadProcessTask.Start("Test process 11");
Task.Delay(10).Wait(); //Sql Server datetime is not that exact
AbortLoadProcessTask.Abort();
StartLoadProcessTask.Start("Test process 12");
Task.Delay(10).Wait(); //Sql Server datetime is not that exact
EndLoadProcessTask.End();
//Act
var lp = ReadLoadProcessTableTask.ReadWithOption(ReadOptions.ReadLastAborted);
//Assert
Assert.True(lp.IsFinished);
Assert.True(lp.WasAborted);
Assert.False(lp.WasSuccessful);
Assert.Equal("Test process 11", lp.ProcessName);
//Cleanup
DropTableTask.Drop(SqlConnection, "test_lpkey_lastabort");
}
public void EndLoadProcess(IConnectionManager connection)
{
//Arrange
CreateLoadProcessTableTask.Create(connection, "test_lp_end");
StartLoadProcessTask.Start(connection, "Test process 2");
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == true);
//Act
EndLoadProcessTask.End(connection, "End process 2");
//Assert
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == false);
Assert.True(ControlFlow.CurrentLoadProcess.WasSuccessful == true);
Assert.True(ControlFlow.CurrentLoadProcess.IsFinished == true);
Assert.True(ControlFlow.CurrentLoadProcess.EndDate >= DateTime.Now.AddSeconds(-1));
Assert.Equal(1, RowCountTask.Count(connection, "test_lp_end",
"is_running=0 and was_successful=1 and was_aborted=0"));
Assert.Equal(1, RowCountTask.Count(connection, "test_lp_end",
"start_message IS NULL AND end_message = 'End process 2' AND abort_message IS NULL"));
//Cleanup
DropTableTask.Drop(connection, "test_lp_end");
}
public void TestStartLoadProcessTaskWithMessage()
{
StartLoadProcessTask.Start("Test process 1", "Message 1", "SourceA");
Assert.AreEqual(1, new SqlTask("Check if load process messages are correct", $"select count(*) from etl.LoadProcess where StartMessage = 'Message 1' and Source='SourceA' and EndMessage is null and AbortMessage is null")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public async Task Handle(string request)
{
//string sC = "data source=QHB-CRMDB001.centralservices.local;initial catalog=eInsightCRM_OceanProperties_QA;uid=eInsightCRM_eContact_OceanProperties;pwd=Tv3CxdZwA%9;MultipleActiveResultSets=True";
//string dC = "data source=localhost;initial catalog=eInsightCRM_AMResorts_QA;uid=sa;pwd=123456;MultipleActiveResultSets=True";
//string dT = "dbo.D_Customer";
//string sql = "SELECT TOP 20 CustomerID, FirstName, LastName, Email, PropertyCode, InsertDate, SourceID, AddressStatus, DedupeCheck, AllowEMail, Report_Flag, UNIFOCUS_SCORE FROM dbo.D_Customer with(Nolock);";
//string sql1 = "SELECT TOP 20 CustomerID, FirstName, LastName, Email, PropertyCode, InsertDate, SourceID, AddressStatus, DedupeCheck, AllowEMail, Report_Flag, UNIFOCUS_SCORE FROM dbo.D_Customer_03092017 with(Nolock);";
//List<Dictionary<string,string>> SQL_GetDataFromPMS_SpecialRequestsList = SQLHelper.GetDbValues(sC, "SQL_GetDataFromPMS_SpecialRequests", sql, null);
//new DataFlowTask<D_Customer>().runTask(sC, dC, dT, sql, true, true, new List<string>() { "FirstName", "LastName" }, new List<string>() { "CustomerID", "FirstName", "LastName", "Email", "PropertyCode", "InsertDate", "SourceID", "AddressStatus", "DedupeCheck", "AllowEMail", "Report_Flag", "UNIFOCUS_SCORE" });
// new DataFlowTask<D_Customer>().runTask1<D_Customer, D_Customer, D_Customer>(sC, dC, dT, sql, sql1, true, true, new List<string>() { "FirstName"}, new List<string>() { "CustomerID", "FirstName", "LastName", "Email", "PropertyCode", "InsertDate", "SourceID", "AddressStatus", "DedupeCheck", "AllowEMail", "Report_Flag", "UNIFOCUS_SCORE" });
try
{
string eContactLogConnectionString = System.Configuration.ConfigurationManager.AppSettings["econtact-log-db-sql"];
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString(eContactLogConnectionString));
CreateLogTablesTask.CreateLog();
StartLoadProcessTask.Start("Process 1", "Start Message 1", "ETL");
var settings = JsonConvert.DeserializeObject <Dictionary <string, object> >(request);
ControlFlow.STAGE = "0";
eContactDBManager.GetCompanySetting(settings);
ControlFlow.SetLoggingDatabase(new SqlConnectionManager(eContactLogConnectionString));
//Customer
CustomerTask CT = new CustomerTask();
CT.Start();
}
catch (Exception ex)
{
ControlFlow.STAGE = "0";
new LogTask()
{
Message = $"Exception({ex.Message}) Occurred. Task Stopped!", ActionType = "Stop"
}.Info();
NLog.LogManager.GetCurrentClassLogger().Fatal(ex);
}
//Console.WriteLine("Starting ControlFlow example");
//ControlFlowTasks cft = new ControlFlowTasks();
//cft.Start();
//Console.WriteLine("ControlFlow finished...");
//Console.WriteLine("Start Logging example");
//Logging log = new Logging();
//log.Start();
//Console.WriteLine("Logging finished...");
//Console.WriteLine("Starting DataFlow example");
//DataFlowTasks dft = new DataFlowTasks();
//dft.Preparation();
//dft.Start();
//Console.WriteLine("Dafaflow finished...");
//Console.WriteLine("Press any key to continue...");
//Console.ReadLine();
}
public void TestLoadProcessKeyInLog()
{
StartLoadProcessTask.Start("Test process 5");
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
Assert.AreEqual(2, new SqlTask("Check if load process key is set", $"select count(*) from etl.Log where Message='Test Task' and LoadProcessKey = {ControlFlow.CurrentLoadProcess.LoadProcessKey}")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void StartLoadProcessWithMessage()
{
//Arrange
//Act
StartLoadProcessTask.Start("Test process 1", "Message 1", "SourceA");
//Assert
Assert.Equal(1, new RowCountTask("etl.LoadProcess",
"StartMessage = 'Message 1' and Source='SourceA' and EndMessage is null and AbortMessage is null")
{
DisableLogging = true
}.Count().Rows);
}
public void TestLoadProcessKeyForLogTask()
{
StartLoadProcessTask.Start("Test process 8");
int?processKey1 = ControlFlow.CurrentLoadProcess.LoadProcessKey;
LogTask.Error("Test1");
LogTask.Warn("Test2");
LogTask.Info("Test3");
Assert.AreEqual(3, new SqlTask("Check if load process key is set", $"select count(*) from etl.Log where Message like 'Test%' and LoadProcessKey = {processKey1}")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void IsControlFlowStageLogged()
{
//Arrange
StartLoadProcessTask.Start("Test process 1", "Message 1", "SourceA");
//Act
ControlFlow.STAGE = "SETUP";
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
//Assert
Assert.Equal(2, new RowCountTask("etl.Log",
$"Message='Test Task' and Stage = 'SETUP'")
{
DisableLogging = true
}.Count().Rows);
}
public void IsLoadProcessKeyInLog()
{
//Arrange
StartLoadProcessTask.Start("Test process 5");
//Act
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
//Assert
Assert.Equal(2, new RowCountTask("etl.Log",
$"Message='Test Task' and LoadProcessKey = {ControlFlow.CurrentLoadProcess.LoadProcessKey}")
{
DisableLogging = true
}.Count().Rows);
}
public void StartLoadProcessWithMessage(IConnectionManager connection)
{
//Arrange
CreateLoadProcessTableTask.Create(connection, "test_lp_withmessage");
//Act
StartLoadProcessTask.Start(connection, "Test process 1", "Message 1", "SourceA");
//Assert
Assert.Equal(1, RowCountTask.Count(connection, "test_lp_withmessage",
"start_message = 'Message 1' AND source='SourceA' AND end_message IS NULL AND abort_message IS NULL"));
//Cleanup
DropTableTask.Drop(connection, "test_lp_withmessage");
}
public void IsLoadProcessKeySetForLogTask()
{
//Arrange
StartLoadProcessTask.Start("Test process 15");
int?processKey1 = ControlFlow.CurrentLoadProcess.LoadProcessKey;
//Act
LogTask.Error("Test1");
LogTask.Warn("Test2");
LogTask.Info("Test3");
//Assert
Assert.Equal(3, new RowCountTask("etl.Log",
$"Message like 'Test%' AND LoadProcessKey = {processKey1}")
{
DisableLogging = true
}.Count().Rows);
}
public void TestIsTransferCompletedForLoadProcessTask()
{
StartLoadProcessTask.Start("Test process 4");
Assert.IsTrue(ControlFlow.CurrentLoadProcess.IsRunning == true);
DateTime beforeTask = DateTime.Now;
Task.Delay(10).Wait(); //Sql Server datetime is not that exact
TransferCompletedForLoadProcessTask.Complete(ControlFlow.CurrentLoadProcess.LoadProcessKey);
Assert.AreEqual(2, new SqlTask("Check if transfer completed was in log", "select count(*) from etl.Log where TaskType='TRANSFERCOMPLETE'")
{
DisableLogging = true
}.ExecuteScalar <int>());
DateTime afterTask = DateTime.Now;
Assert.IsTrue(ControlFlow.CurrentLoadProcess.IsRunning == true);
Assert.IsTrue(ControlFlow.CurrentLoadProcess.TransferCompletedDate <= afterTask && ControlFlow.CurrentLoadProcess.TransferCompletedDate >= beforeTask);
}
public void StartLoadProcess(IConnectionManager connection)
{
//Arrange
CreateLoadProcessTableTask.Create(connection, "test_load_process");
//Act
StartLoadProcessTask.Start(connection, "Test process 1");
//Assert
Assert.True(ControlFlow.CurrentLoadProcess != null);
Assert.Equal("Test process 1", ControlFlow.CurrentLoadProcess.ProcessName);
Assert.True(ControlFlow.CurrentLoadProcess.StartDate >= DateTime.Now.AddSeconds(-1));
Assert.Equal(1, RowCountTask.Count(connection, ControlFlow.LoadProcessTable,
"start_message IS NULL and end_message IS NULL and abort_message IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, ControlFlow.LoadProcessTable,
"is_running = 1 AND was_successful=0 AND was_aborted=0"));
//Cleanup
DropTableTask.Drop(connection, "test_load_process");
}
public void LoggingInSourceAndDestination()
{
CreateLogTablesTask.CreateLog();
StartLoadProcessTask.Start("Test");
DataFlow.LoggingThresholdRows = 3;
DBSource source;
DBDestination dest;
CreateSourceAndDestination(out source, out dest);
source.LinkTo(dest);
source.Execute();
dest.Wait();
EndLoadProcessTask.End();
Assert.AreEqual(4, RowCountTask.Count("etl.Log", "TaskType = 'DF_DBSOURCE' AND TaskAction = 'LOG' AND LoadProcessKey IS NOT NULL"));
Assert.AreEqual(4, RowCountTask.Count("etl.Log", "TaskType = 'DF_DBDEST' AND TaskAction = 'LOG' AND LoadProcessKey IS NOT NULL"));
Assert.AreEqual(1, RowCountTask.Count("etl.LoadProcess"));
}
public void AbortLoadProcess()
{
//Arrange
CreateLoadProcessTableTask.Create(SqlConnection, "test_lp_abort");
StartLoadProcessTask.Start(SqlConnection, "Test process 3");
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == true);
//Act
AbortLoadProcessTask.Abort(SqlConnection, ControlFlow.CurrentLoadProcess.Id);
//Assert
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == false);
Assert.True(ControlFlow.CurrentLoadProcess.WasAborted == true);
Assert.True(ControlFlow.CurrentLoadProcess.AbortMessage == null);
Assert.Equal(1, RowCountTask.Count(SqlConnection, "test_lp_abort"
, "is_running=0 and was_successful=0 and was_aborted=1"));
//Cleanup
DropTableTask.Drop(SqlConnection, "test_lp_abort");
}
public void IsTransferCompletedForLoadProcess()
{
//Arrange
StartLoadProcessTask.Start("Test process 4");
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == true);
DateTime beforeTask = DateTime.Now;
Task.Delay(10).Wait(); //Sql Server datetime is not that exact
//Act
TransferCompletedForLoadProcessTask.Complete(ControlFlow.CurrentLoadProcess.LoadProcessKey);
//Assert
Assert.Equal(2, new RowCountTask("etl.Log", "TaskType='TransferCompletedForLoadProcessTask'")
{
DisableLogging = true
}.Count().Rows);
DateTime afterTask = DateTime.Now;
Assert.True(ControlFlow.CurrentLoadProcess.IsRunning == true);
Assert.True(ControlFlow.CurrentLoadProcess.TransferCompletedDate <= afterTask && ControlFlow.CurrentLoadProcess.TransferCompletedDate >= beforeTask);
}
public void IsLoadProcessKeyInLog(IConnectionManager connection)
{
//Arrange
CreateLoadProcessTableTask.Create(connection, "test_lpkey_inlog");
CreateLogTableTask.Create(connection, "test_lpkey_log");
ControlFlow.AddLoggingDatabaseToConfig(connection, NLog.LogLevel.Info, "test_lpkey_log");
StartLoadProcessTask.Start(connection, "Test process 5");
//Act
SqlTask.ExecuteNonQuery(connection, "Test Task", "Select 1 as test");
//Assert
Assert.Equal(2, new RowCountTask("test_lpkey_log",
$"message='Test Task' and load_process_id = {ControlFlow.CurrentLoadProcess.Id}")
{
DisableLogging = true,
ConnectionManager = connection
}.Count().Rows);;
//Cleanup
DropTableTask.Drop(connection, ControlFlow.LogTable);
DropTableTask.Drop(connection, ControlFlow.LoadProcessTable);
}
public void IsLoadProcessKeySetIfRestarted()
{
//Arrange
StartLoadProcessTask.Start("Test process 13");
int?processKey1 = ControlFlow.CurrentLoadProcess.LoadProcessKey;
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
Assert.Equal(2, new RowCountTask("etl.Log", $"Message='Test Task' AND LoadProcessKey = {processKey1}")
{
DisableLogging = true
}.Count().Rows);
//Act
StartLoadProcessTask.Start("Test process 14");
int?processKey2 = ControlFlow.CurrentLoadProcess.LoadProcessKey;
//Assert
Assert.NotEqual(processKey1, processKey2);
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
Assert.Equal(2, new RowCountTask("etl.Log", $"Message='Test Task' AND LoadProcessKey = {processKey2}")
{
DisableLogging = true
}.Count().Rows);
}
private void RunProcess1()
{
StartLoadProcessTask.Start("Process 1", "Start");
SqlTask.ExecuteNonQuery($"Just some sql", "Select 1 as test");
EndLoadProcessTask.End("End");
}