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 Preparation()
{
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB"));
CreateSchemaTask.Create("demo");
OrderDataTableDef.CreateTable();
CustomerTableDef.CreateTable();
CustomerRatingTableDef.CreateTable();
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Sandra Kettler')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Nick Thiemann')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Zoe Rehbein')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Margit Gries')");
}
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 BigIntIdentity()
{
//Arrange
SqlTask.ExecuteNonQuery(SqlConnection, "Create table", @"
CREATE TABLE BigIntIdentity (
Id BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1)
)"
);
//Act
var result = TableDefinition.GetDefinitionFromTableName("BigIntIdentity", SqlConnection);
//Assert
Assert.True(true);
}
internal TableDefinition CreateTableForInput1(string tableName)
{
TableDefinition def = new TableDefinition(tableName, new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: true),
new TableColumn("Col2", "int", allowNulls: true)
});
def.CreateTable();
SqlTask.ExecuteNonQuery("Insert demo data", $"insert into {tableName} values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", $"insert into {tableName} values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", $"insert into {tableName} values('Test3',3)");
return(def);
}
void ReCreateOtherTable(IConnectionManager connection, string tablename)
{
DropTableTask.DropIfExists(connection, tablename);
CreateTableTask.Create(connection, tablename,
new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Other", "VARCHAR(100)", allowNulls: true, isPrimaryKey: false),
});
ObjectNameDescriptor TN = new ObjectNameDescriptor(tablename, connection);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(10,'TestX')");
}
public void TestCleanUpSchema()
{
string schemaName = "s" + TestHelper.RandomString(9);
SqlTask.ExecuteNonQuery("Create schema", $"create schema {schemaName}");
SqlTask.ExecuteNonQuery("Create schema", $"create table {schemaName}.Table1 ( Nothing int null )");
SqlTask.ExecuteNonQuery("Create schema", $"create view {schemaName}.View1 as select * from {schemaName}.Table1");
SqlTask.ExecuteNonQuery("Create schema", $"create procedure {schemaName}.Proc1 as select * from {schemaName}.Table1");
var objCountSql = new SqlTask("Count object", $@"select count(*) from sys.objects obj
inner join sys.schemas sch on sch.schema_id = obj.schema_id
where sch.name = '{schemaName}'");
Assert.AreEqual(3, objCountSql.ExecuteScalar <int>());
CleanUpSchemaTask.CleanUp(schemaName);
Assert.AreEqual(0, objCountSql.ExecuteScalar <int>());
}
public void CustomSourceWithWebService()
{
SqlTask.ExecuteNonQuery("Create test table",
@"CREATE TABLE dbo.ws_dest
( Id INT NOT NULL, UserId INT NOT NULL, Title NVARCHAR(100) NOT NULL, Completed BIT NOT NULL )"
);
WebserviceReader wsreader = new WebserviceReader();
CustomSource <Todo> source = new CustomSource <Todo>(wsreader.ReadTodo, wsreader.EndOfData);
DBDestination <Todo> dest = new DBDestination <Todo>("dbo.ws_dest");
source.LinkTo(dest);
source.Execute();
dest.Wait();
}
public void IfTableExists(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Drop table if exists"
, $@"DROP TABLE IF EXISTS ExistTableTest");
//Act
var existsBefore = IfExistsTask.IsExisting(connection, "ExistTableTest");
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, $@"CREATE TABLE ExistTableTest ( Col1 INT NULL )");
var existsAfter = IfExistsTask.IsExisting(connection, "ExistTableTest");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
public void IfIndexExists(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Create index test table"
, $@"CREATE TABLE indextable (col1 INT NULL)");
//Act
var existsBefore = IfIndexExistsTask.IsExisting(connection, "index_test", "indextable");
SqlTask.ExecuteNonQuery(connection, "Create test index"
, $@"CREATE INDEX index_test ON indextable (col1)");
var existsAfter = IfIndexExistsTask.IsExisting(connection, "index_test", "indextable");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
private void CreateSourceAndDestination(out DBSource source, out DBDestination dest)
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE Source
(Col1 nvarchar(100) null, Col2 int null)");
for (int i = 1; i <= 10; i++)
{
SqlTask.ExecuteNonQuery("Insert demo data", $"insert into Source values('Test{i}',{i})");
}
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE Destination
(ID int not null identity (1,1) primary key,
Col1 nvarchar(30) null, Col2 nvarchar(30) null)");
source = new DBSource("Source");
dest = new DBDestination("Destination", 3);
}
private static TableDefinition RecreateTestTable()
{
try {
SqlTask.ExecuteNonQuery("Try to drop table", @"DROP TABLE TestTable;");
} catch { }
TableDefinition testTable = new TableDefinition("TestTable", new List <TableColumn>()
{
new TableColumn("Field1", "NUMBER", allowNulls: true),
new TableColumn("Field2", "CHAR", allowNulls: true)
});
new CreateTableTask(testTable)
{
ThrowErrorIfTableExists = true
}.Execute();
return(testTable);
}
public void TestComment()
{
//Arrange
SqlTask.ExecuteNonQuery(MySqlConnection, "Create table", @"
CREATE TABLE testcomment (
`comment` INT NULL COMMENT 'test'
)"
);
//Act
var result = TableDefinition.FromTableName(MySqlConnection, "testcomment");
//Assert
Assert.Collection(result.Columns,
tc => Assert.True(tc.DataType == "int" && tc.Comment == "test")
);
}
public void IfDatabaseExists(IConnectionManager connection)
{
//Arrange
string dbName = ("ETLBox_" + HashHelper.RandomString(10)).ToLower();
var existsBefore = IfDatabaseExistsTask.IsExisting(connection, dbName);
//Act
SqlTask.ExecuteNonQuery(connection, "Create DB", $"CREATE DATABASE {dbName}");
var existsAfter = IfDatabaseExistsTask.IsExisting(connection, dbName);
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
//Cleanup
DropDatabaseTask.Drop(connection, dbName);
}
public void IfViewExists(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Drop view if exists"
, $@"DROP VIEW IF EXISTS existview_test");
//Act
var existsBefore = IfTableOrViewExistsTask.IsExisting(connection, "existview_test");
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, $@"CREATE VIEW existview_test AS SELECT 1 AS test");
var existsAfter = IfTableOrViewExistsTask.IsExisting(connection, "existview_test");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
private static void ExecuteDataFlow_ExtendedRows()
{
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source (Col2, Col4) values('Test1', '2.5')");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source (Col2, Col4) values('Test2', '12.5')");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source (Col2, Col4) values('Test3', '123.5')");
DBSource <MyExtendedRow> source = new DBSource <MyExtendedRow>("test.Source");
DBDestination <MyExtendedRow> dest = new DBDestination <MyExtendedRow>("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col2 = 'Test1' AND Col4='2.5'"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col2 = 'Test2' AND Col4='12.5'"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col2 = 'Test3' AND Col4='123.5'"));
}
private void CreateDestinationTable(string tableName)
{
DropTableTask.DropIfExists(ConnectionDestination, tableName);
TableDefinition sourceTable = new TableDefinition(tableName, new List <TableColumn>()
{
new TableColumn("id", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("name", "NVARCHAR(100)", allowNulls: false),
new TableColumn("age", "INT", allowNulls: false),
new TableColumn("hashcode", "CHAR(40)", allowNulls: false),
});
sourceTable.CreateTable(ConnectionDestination);
SqlTask.ExecuteNonQuery(ConnectionDestination, "Insert demo data"
, $"INSERT INTO {tableName} (name, age, hashcode) VALUES('Bugs',12, '{HashHelper.Encrypt_Char40("1Bugs12")}')");
SqlTask.ExecuteNonQuery(ConnectionDestination, "Insert demo data"
, $"INSERT INTO {tableName} (name, age, hashcode) VALUES('Coyote',10, '{HashHelper.Encrypt_Char40("2Coyote10")}')");
}
public void ReadDifferentTypes(IConnectionManager connection)
{
//Arrange
CreateTableTask.Create(connection, "different_type_table",
new List <TableColumn>()
{
new TableColumn("int_col", "INT", allowNulls: true),
new TableColumn("long_col", "BIGINT", allowNulls: true),
new TableColumn("decimal_col", "FLOAT", allowNulls: true),
new TableColumn("double_col", "FLOAT", allowNulls: true),
new TableColumn("datetime_col", "DATETIME", allowNulls: true),
new TableColumn("date_col", "DATE", allowNulls: true),
new TableColumn("string_col", "VARCHAR(200)", allowNulls: true),
new TableColumn("char_col", "CHAR(1)", allowNulls: true),
new TableColumn("decimal_string_col", "DECIMAL(12,10)", allowNulls: true),
new TableColumn("null_col", "CHAR(1)", allowNulls: true),
new TableColumn("enum_col", "INT", allowNulls: true),
});
SqlTask.ExecuteNonQuery(connection, "Insert test data",
@"INSERT INTO different_type_table
(int_col, long_col, decimal_col, double_col, datetime_col, date_col
, string_col, char_col, decimal_string_col, null_col, enum_col)
VALUES (1, -1, 2.3, 5.4, '2010-01-01 10:00:00.000', '2020-01-01', 'Test', 'T', '13.4566', NULL, 2 )");
//Act
DbSource <MyDataTypeRow> source = new DbSource <MyDataTypeRow>(connection, "different_type_table");
MemoryDestination <MyDataTypeRow> dest = new MemoryDestination <MyDataTypeRow>();
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(1, dest.Data.First().IntCol);
Assert.Equal(-1, dest.Data.First().LongCol);
Assert.Equal(2.3M, dest.Data.First().DecimalCol);
Assert.True(dest.Data.First().DoubleCol >= 5.4 && dest.Data.First().DoubleCol < 5.5);
Assert.Equal("2010-01-01 10:00:00.000", dest.Data.First().DateTimeCol.ToString("yyyy-MM-dd hh:mm:ss.fff"));
Assert.Equal("2020-01-01", dest.Data.First().DateCol.ToString("yyyy-MM-dd"));
Assert.Equal("Test", dest.Data.First().StringCol);
Assert.Equal('T', dest.Data.First().CharCol);
Assert.StartsWith("13.4566", dest.Data.First().DecimalStringCol);
Assert.Null(dest.Data.First().NullCol);
Assert.Equal(EnumType.Value2, dest.Data.First().EnumCol);
}
private void RunSubSequence()
{
Sequence.Execute("Test sub sequence 1.1", () =>
{
SqlTask.ExecuteNonQuery($"Sql #2", "Select 1 as test");
SqlTask.ExecuteNonQuery($"Sql #3", "Select 1 as test");
LogTask.Warn("Warn message #1");
});
Sequence.Execute("Test sub sequence 1.2", () =>
{
SqlTask.ExecuteNonQuery($"Sql #4", "Select 1 as test");
});
Sequence.Execute("Test sub sequence 1.3",
() =>
{
Sequence.Execute("Test sub sequence 2.1", () =>
{
Sequence.Execute("Test sub sequence 3.1", () =>
{
SqlTask.ExecuteNonQuery($"Sql #5", "Select 1 as test");
SqlTask.ExecuteNonQuery($"Sql #6", "Select 1 as test");
LogTask.Warn("Warn message #2");
});
CustomTask.Execute($"Custom #1", () => {; });
SqlTask.ExecuteNonQuery($"Sql #7", "Select 1 as test");
});
Sequence.Execute("Test sub sequence 2.2", () =>
{
CustomTask.Execute($"Custom #2", () => {; });
SqlTask.ExecuteNonQuery($"Sql #7", "Select 1 as test");
});
Sequence.Execute("Test sub sequence 2.3", () =>
{
SqlTask.ExecuteNonQuery($"Sql #8", "Select 1 as test");
CustomTask.Execute($"Custom #2", () => {; });
Sequence.Execute("Test sub sequence 3.3", () =>
{
SqlTask.ExecuteNonQuery($"Sql #9", "Select 1 as test");
SqlTask.ExecuteNonQuery($"Sql #10", "Select 1 as test");
LogTask.Error("Error message");
});
});
});
CustomTask.Execute($"Custom #3", () => {; });
}
public void CSV_RowTrans_DB_NonGeneric()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Staging
(Col1 int null, Col2 nvarchar(100) null)");
CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv");
RowTransformation trans = new RowTransformation(
csvdata => {
return(new string[] { csvdata[1], csvdata[0] });
});
DBDestination dest = new DBDestination("test.Staging");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Staging"));
}
public void FastExecutingSqlsInParallel()
{
//Arrange
List <int> array = new List <int>()
{
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
};
//Act
Parallel.ForEach(array, new ParallelOptions {
MaxDegreeOfParallelism = 8
},
curNr => SqlTask.ExecuteNonQuery(Connection, $"Test statement {curNr}",
$"INSERT INTO FastParallel VALUES({curNr})")
);
//Assert
Assert.Equal(10, RowCountTask.Count(Connection, "FastParallel"));
}
private void CreateSourceTable(string tableName)
{
DropTableTask.DropIfExists(ConnectionSource, tableName);
TableDefinition sourceTable = new TableDefinition(tableName, new List <TableColumn>()
{
new TableColumn("id", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("name", "NVARCHAR(100)", allowNulls: false),
new TableColumn("age", "INT", allowNulls: false),
});
sourceTable.CreateTable(ConnectionSource);
SqlTask.ExecuteNonQuery(ConnectionSource, "Insert demo data"
, $"INSERT INTO {tableName} (name, age) VALUES('Bugs',12)");
SqlTask.ExecuteNonQuery(ConnectionSource, "Insert demo data"
, $"INSERT INTO {tableName} (name, age) VALUES('Coyote',8)");
SqlTask.ExecuteNonQuery(ConnectionSource, "Insert demo data"
, $"INSERT INTO {tableName} (name, age) VALUES('Pete',19)");
}
public void Prepare()
{
RecreateDatabase("demo", SqlConnection.ConnectionString as SqlConnectionString);
DropTableTask.DropIfExists(SqlConnection, "CustomerTable");
var td = new TableDefinition("CustomerTable"
, new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false),
new TableColumn("Name", "NVARCHAR(100)", allowNulls: true)
});
td.CreateTable(SqlConnection);
SqlTask.ExecuteNonQuery(SqlConnection,
"INSERT INTO CustomerTable VALUES (1,'John')");
SqlTask.ExecuteNonQuery(SqlConnection,
"INSERT INTO CustomerTable VALUES (2,'Jim')");
}
public void AutoIncrement()
{
//Arrange
SqlTask.ExecuteNonQuery(MySqlConnection, "Create table", @"
CREATE TABLE identity (
`Id` INT AUTO_INCREMENT NOT NULL PRIMARY KEY
)"
);
//Act
var result = TableDefinition.GetDefinitionFromTableName(MySqlConnection, "identity");
//Assert
Assert.Collection(result.Columns,
tc => Assert.True(tc.DataType == "int" && tc.NETDataType == typeof(Int32) &&
tc.IsIdentity && tc.IsPrimaryKey)
);
}
public void ExecuteNonQueryWithParameter(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture tc = new TwoColumnsTableFixture(connection, "ParameterTest");
//Act
var parameter = new List <QueryParameter>
{
new QueryParameter("value1", "INT", 1),
new QueryParameter("value2", "NVARCHAR(100)", "Test1")
};
SqlTask.ExecuteNonQuery(connection, "Test insert with parameter",
$"INSERT INTO {tc.QB}ParameterTest{tc.QE} VALUES (@value1, @value2)", parameter);
//Assert
Assert.Equal(1, RowCountTask.Count(connection, "ParameterTest", $@"{tc.QB}Col1{tc.QE} = 1 AND {tc.QB}Col2{tc.QE}='Test1'"));
}
public void OnlyNullValue(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Create destination table", @"CREATE TABLE source_onlynulls
(col1 VARCHAR(100) NULL, col2 VARCHAR(100) NULL)");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO source_onlynulls VALUES(NULL, NULL)");
//Act
DBSource source = new DBSource(connection, "source_onlynulls");
MemoryDestination dest = new MemoryDestination();
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <string[]>(dest.Data,
row => Assert.True(row[0] == null && row[1] == null));
}
private void CreateSourceAndDestinationTables(IConnectionManager connection, string sourcetable, string desttable)
{
var cols = new List <TableColumn>()
{
new TableColumn("Col1", "INT", allowNulls: false),
new TableColumn("Col2", "VARCHAR(100)", allowNulls: true)
};
_sourcedef = new TableDefinition(sourcetable, cols);
_destdef = new TableDefinition(desttable, cols);
CreateTableTask.Create(connection, _sourcedef);
CreateTableTask.Create(connection, _destdef);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {sourcetable} VALUES(1,'Test1')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {sourcetable} VALUES(2,'Test2')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {sourcetable} VALUES(3,'Test3')");
}
public void DBSourceWithColumnMapping()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Source
(Col1 nvarchar(100) null, Col2 nvarchar(100) null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1','Test2')");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1','Test2')");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1','Test2')");
DBSource <ColumnMapRow> source = new DBSource <ColumnMapRow>("test.Source");
CustomDestination <ColumnMapRow> dest = new CustomDestination <ColumnMapRow>(
input => {
Assert.AreEqual("Test1", input.Col1);
Assert.AreEqual("Test2", input.B);
});
source.LinkTo(dest);
source.Execute();
dest.Wait();
}
public void ControlFlowLoggingInDifferentDB()
{
//Arrange
//Act
SqlTask.ExecuteNonQuery(NoLogConnection, "Create source table", @"CREATE TABLE CFLogSource
(Col1 INT NOT NULL, Col2 NVARCHAR(50) NULL)");
ControlFlow.DefaultDbConnection = NoLogConnection;
SqlTask.ExecuteNonQuery("Insert demo data", "INSERT INTO CFLogSource VALUES(1,'Test1')");
//Assert
Assert.Equal(4, new RowCountTask("etlbox_log", "task_type = 'SqlTask' ")
{
DisableLogging = true,
ConnectionManager = LoggingConnection
}.Count().Rows);
}
public void TestReadLogTask(IConnectionManager connection)
{
//Arrange
CreateLogTableTask.Create(connection, "test_readlog");
ControlFlow.AddLoggingDatabaseToConfig(connection, NLog.LogLevel.Info, "test_readlog");
SqlTask.ExecuteNonQuery(connection, "Test Task", "Select 1 as test");
//Act
List <LogEntry> entries = ReadLogTableTask.Read(connection);
//Assert
Assert.Collection <LogEntry>(entries,
l => Assert.True(l.Message == "Test Task" && l.TaskAction == "START" && l.TaskType == "SqlTask"),
l => Assert.True(l.Message == "Test Task" && l.TaskAction == "END" && l.TaskType == "SqlTask")
);
//Cleanup
DropTableTask.Drop(connection, ControlFlow.LogTable);
}