public void ConvertObjects()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("BlockTransSource");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("BlockTransDest");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(Connection, "BlockTransSource");
DBDestination <MyOtherRow> dest = new DBDestination <MyOtherRow>(Connection, "BlockTransDest");
//Act
BlockTransformation <MySimpleRow, MyOtherRow> block = new BlockTransformation <MySimpleRow, MyOtherRow>(
inputData =>
{
return(inputData.Select(row => new MyOtherRow()
{
Col3 = row.Col1, Col4 = row.Col2
}).ToList());
});
source.LinkTo(block);
block.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void ColumnMapping(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "SpecialCharacterSource");
InsertTestData(connection, "SpecialCharacterSource");
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "SpecialCharacterDestination");
//Act
DBSource source = new DBSource()
{
ConnectionManager = connection,
SourceTableDefinition = s2c.TableDefinition
};
DBDestination dest = new DBDestination()
{
ConnectionManager = connection,
DestinationTableDefinition = d2c.TableDefinition
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(4, RowCountTask.Count(connection, "SpecialCharacterDestination"));
}
public void DB_RowTrans_DB()
{
TableDefinition sourceTableDefinition = CreateDBSourceTableForSimpleRow();
TableDefinition destinationTableDefinition = CreateDBDestinationTableForSimpleRow();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>()
{
SourceTableDefinition = sourceTableDefinition
};
RowTransformation <MySimpleRow, MySimpleRow> trans = new RowTransformation <MySimpleRow, MySimpleRow>(myRow => {
myRow.Value2 += 1;
return(myRow);
});
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>()
{
DestinationTableDefinition = destinationTableDefinition
};
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination"));
Assert.AreEqual(9, SqlTask.ExecuteScalar <int>("Check destination table", "select sum(Col2) from test.Destination"));
}
public void ModifyInputDataList()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("BlockTransSource");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("BlockTransDest");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(Connection, "BlockTransSource");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(Connection, "BlockTransDest");
//Act
BlockTransformation <MySimpleRow> block = new BlockTransformation <MySimpleRow>(
inputData => {
inputData.RemoveRange(1, 2);
inputData.Add(new MySimpleRow()
{
Col1 = 4, Col2 = "Test4"
});
return(inputData);
});
source.LinkTo(block);
block.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(2, RowCountTask.Count(Connection, "BlockTransDest"));
Assert.Equal(1, RowCountTask.Count(Connection, "BlockTransDest", "Col1 = 1 AND Col2='Test1'"));
Assert.Equal(1, RowCountTask.Count(Connection, "BlockTransDest", "Col1 = 4 AND Col2='Test4'"));
}
public void DB_RowTrans_DB_WithInitAction()
{
TableDefinition sourceTableDefinition = CreateDBSourceTableForSimpleRow();
TableDefinition destinationTableDefinition = CreateDBDestinationTableForSimpleRow();
RowTransformationTestClass testClass = new RowTransformationTestClass();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>()
{
SourceTableDefinition = sourceTableDefinition
};
RowTransformation <MySimpleRow, MySimpleRow> trans = new RowTransformation <MySimpleRow, MySimpleRow>(
"RowTransformation testing init Action",
testClass.TestTransformationFunc,
testClass.SetAddValue
);
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>()
{
DestinationTableDefinition = destinationTableDefinition
};
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination"));
Assert.AreEqual(9, SqlTask.ExecuteScalar <int>("Check destination table", "select sum(Col2) from test.Destination"));
}
public void SimpleMerge(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "DBMergeSource");
source2Columns.InsertTestData();
source2Columns.InsertTestDataSet2();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DBMergeDestination");
dest2Columns.InsertTestDataSet3();
DBSource <MyMergeRow> source = new DBSource <MyMergeRow>(connection, "DBMergeSource");
//Act
DBMerge <MyMergeRow> dest = new DBMerge <MyMergeRow>(connection, "DBMergeDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(connection, "DBMergeDestination", "Col1 BETWEEN 1 AND 7 AND Col2 LIKE 'Test%'"));
Assert.True(dest.DeltaTable.Count == 7);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "U").Count() == 2);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Key == 10).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "I").Count() == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "E" && row.Key == 1).Count() == 1);
}
public void WithDeltaDestination(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "DBMergeSource");
source2Columns.InsertTestData();
source2Columns.InsertTestDataSet2();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DBMergeDestination");
dest2Columns.InsertTestDataSet3();
TwoColumnsDeltaTableFixture delta2Columns = new TwoColumnsDeltaTableFixture(connection, "DBMergeDelta");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(connection, "DBMergeSource");
//Act
DBMerge <MySimpleRow> merge = new DBMerge <MySimpleRow>(connection, "DBMergeDestination");
DBDestination <MySimpleRow> delta = new DBDestination <MySimpleRow>(connection, "DBMergeDelta");
source.LinkTo(merge);
merge.LinkTo(delta);
source.Execute();
merge.Wait();
delta.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(connection, "DBMergeDestination", "Col1 BETWEEN 1 AND 7 AND Col2 LIKE 'Test%'"));
Assert.Equal(7, RowCountTask.Count(connection, "DBMergeDelta", "Col1 BETWEEN 1 AND 10 AND Col2 LIKE 'Test%'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDelta", "ChangeAction = 'D' AND Col1 = 10"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", "ChangeAction = 'U' AND Col1 IN (1,2,4)"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", "ChangeAction = 'I' AND Col1 IN (3,5,6)"));
}
public void SortSimpleDataDescending()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("SortSourceNonGeneric");
source2Columns.InsertTestData();
DBSource source = new DBSource(Connection, "SortSourceNonGeneric");
//Act
List <string[]> actual = new List <string[]>();
CustomDestination dest = new CustomDestination(
row => actual.Add(row)
);
Comparison <string[]> comp = new Comparison <string[]>(
(x, y) => int.Parse(y[0]) - int.Parse(x[0])
);
Sort block = new Sort(comp);
source.LinkTo(block);
block.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
List <int> expected = new List <int>()
{
3, 2, 1
};
Assert.Equal(expected, actual.Select(row => int.Parse(row[0])).ToList());
}
public void ModifyInputDataList()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("BlockTransSourceNonGeneric");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("BlockTransDestNonGeneric");
DBSource source = new DBSource(SqlConnection, "BlockTransSourceNonGeneric");
DBDestination dest = new DBDestination(SqlConnection, "BlockTransDestNonGeneric");
//Act
BlockTransformation block = new BlockTransformation(
inputData => {
inputData.RemoveRange(1, 2);
inputData.Add(new string[] { "4", "Test4" });
return(inputData);
});
source.LinkTo(block);
block.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(2, RowCountTask.Count(SqlConnection, "BlockTransDestNonGeneric"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "BlockTransDestNonGeneric", "Col1 = 1 AND Col2='Test1'"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "BlockTransDestNonGeneric", "Col1 = 4 AND Col2='Test4'"));
}
public void DataFlowLoggingInDifferentDB()
{
//Arrange
DataFlow.LoggingThresholdRows = 3;
SqlTask.ExecuteNonQuery(NoLogConnection, "Create source table", @"CREATE TABLE DFLogSource
(Col1 INT NOT NULL, Col2 NVARCHAR(50) NULL)");
SqlTask.ExecuteNonQuery(NoLogConnection, "Insert demo data", "INSERT INTO DFLogSource VALUES(1,'Test1')");
SqlTask.ExecuteNonQuery(NoLogConnection, "Insert demo data", "INSERT INTO DFLogSource VALUES(2,'Test2')");
SqlTask.ExecuteNonQuery(NoLogConnection, "Insert demo data", "INSERT INTO DFLogSource VALUES(3,'Test3')");
SqlTask.ExecuteNonQuery(LoggingConnection, "Create source table", @"CREATE TABLE DFLogDestination
(Col1 INT NOT NULL, Col2 NVARCHAR(50) NULL)");
DBSource source = new DBSource(NoLogConnection, "DFLogSource");
DBDestination dest = new DBDestination(LoggingConnection, "DFLogDestination");
//Act
ControlFlow.CurrentDbConnection = LoggingConnection;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(4, new RowCountTask("etl.Log", "TaskType = 'DF_DBSOURCE'")
{
DisableLogging = true,
ConnectionManager = LoggingConnection
}.Count().Rows);
Assert.Equal(4, new RowCountTask("etl.Log", "TaskType = 'DF_DBDEST'")
{
DisableLogging = true,
ConnectionManager = LoggingConnection
}.Count().Rows);
}
public void DB_DB()
{
TableDefinition sourceTableDefinition = new TableDefinition("test.Source", new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
});
sourceTableDefinition.CreateTable();
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test3',3)");
TableDefinition destinationTableDefinition = new TableDefinition("test.Destination", new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
});
destinationTableDefinition.CreateTable();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>()
{
SourceTableDefinition = sourceTableDefinition
};
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>()
{
DestinationTableDefinition = destinationTableDefinition
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination"));
}
public void ColumnMapping(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection,
"SourceColumnMapping", identityColumnIndex: 0);
source4Columns.InsertTestData();
//Act
DBSource <MyExtendedRow> source = new DBSource <MyExtendedRow>(connection, "SourceColumnMapping");
CustomDestination <MyExtendedRow> dest = new CustomDestination <MyExtendedRow>(
input =>
{
//Assert
Assert.InRange(input.Id, 1, 3);
Assert.StartsWith("Test", input.Text);
if (input.Id == 1)
{
Assert.Null(input.Value);
}
else
{
Assert.True(input.Value > 0);
}
Assert.InRange(input.Percentage, 1, 2);
});
source.LinkTo(dest);
source.Execute();
dest.Wait();
}
public void AccessIntoDB()
{
//Arrange
TableDefinition testTable = RecreateAccessTestTable();
SqlTask.ExecuteNonQuery(AccessOdbcConnection, "Insert test data",
"INSERT INTO TestTable (Field1, Field2) VALUES (1,'Test1');");
SqlTask.ExecuteNonQuery(AccessOdbcConnection, "Insert test data",
"INSERT INTO TestTable (Field1, Field2) VALUES (2,'Test2');");
SqlTask.ExecuteNonQuery(AccessOdbcConnection, "Insert test data",
"INSERT INTO TestTable (Field1, Field2) VALUES (3,'Test3');");
new SqlTask("Create Target Table", $@"CREATE TABLE dbo.AccessTargetTable (
Field1 DECIMAL not null, Field2 NVARCHAR(1000) not null)")
{
ConnectionManager = SqlConnection
}.ExecuteNonQuery();
//Act
DBSource <Data> source = new DBSource <Data>(AccessOdbcConnection)
{
SourceTableDefinition = testTable
};
DBDestination <Data> dest = new DBDestination <Data>(SqlConnection, "dbo.AccessTargetTable");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(SqlConnection, $"dbo.AccessTargetTable"));
}
public void DBSourceAndDestinationWithTableDefinition(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "Source");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "Destination");
//Act
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>()
{
SourceTableDefinition = source2Columns.TableDefinition,
ConnectionManager = connection
};
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>()
{
DestinationTableDefinition = dest2Columns.TableDefinition,
ConnectionManager = connection
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void MergeIntoAzure()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(AzureSqlConnection, "[dest].[AzureMergeDestination]");
d2c.InsertTestDataSet3();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(SqlConnection, "DBMergeSource");
//Act
DBMerge <MySimpleRow> dest = new DBMerge <MySimpleRow>(AzureSqlConnection, "[dest].[AzureMergeDestination]");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(6, RowCountTask.Count(AzureSqlConnection, "[dest].[AzureMergeDestination]", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 7 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.True(dest.DeltaTable.Count == 7);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "U").Count() == 2);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Col1 == 10).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "I").Count() == 3);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "E" && row.Col1 == 1).Count() == 1);
}
public void UsingDifferentObjectTypes()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("SourceMultipleLinks");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationMultipleLinks");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(SqlConnection, "SourceMultipleLinks");
DBDestination <MyOtherRow> dest = new DBDestination <MyOtherRow>(SqlConnection, "DestinationMultipleLinks");
RowTransformation <MySimpleRow, MyOtherRow> trans1 = new RowTransformation <MySimpleRow, MyOtherRow>
(row =>
{
return(new MyOtherRow()
{
ColA = row.Col1,
ColB = row.Col2
});
}
);
//Act
source.LinkTo <MyOtherRow>(trans1).LinkTo(dest);
//Assert
source.Execute();
dest.Wait();
dest2Columns.AssertTestData();
}
public void ACCESS2CSV_ViaOdbc()
{
ControlFlow.CurrentDbConnection = new AccessOdbcConnectionManager(new OdbcConnectionString(AccessConnectionStringParameter))
{
AlwaysUseSameConnection = false
};
var sqlConnMan = new SqlConnectionManager(new ConnectionString(ConnectionStringParameter));
TableDefinition testTable = RecreateTestTable();
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestTable (Field1, Field2) values (1,'Test1');");
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestTable (Field1, Field2) values (2,'Test2');");
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestTable (Field1, Field2) values (3,'Test3');");
new SqlTask("Create Target Table", $@"CREATE TABLE dbo.TargetTable (
Field1 decimal not null, Field2 nvarchar(1000) not null)")
{
ConnectionManager = sqlConnMan
}
.ExecuteNonQuery();
DBSource <Data> source = new DBSource <Data>(testTable);
DBDestination <Data> dest = new DBDestination <Data>("dbo.TargetTable", 1)
{
ConnectionManager = sqlConnMan
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, (new RowCountTask("dbo.TargetTable")
{
ConnectionManager = sqlConnMan
}).Count().Rows);
}
public void DB_Lookup_DB()
{
TableDefinition sourceTableDefinition = CreateDBSourceTableForInputRow();
TableDefinition destinationTableDefinition = CreateDBDestinationTableForOutputRow();
TableDefinition lookupTableDefinition = CreateDBLookupTable();
TransformationTestClass testClass = new TransformationTestClass();
DBSource <MyInputDataRow> source = new DBSource <MyInputDataRow>()
{
SourceTableDefinition = sourceTableDefinition
};
DBSource <MyLookupRow> lookupSource = new DBSource <MyLookupRow>()
{
SourceTableDefinition = lookupTableDefinition
};
Lookup <MyInputDataRow, MyOutputDataRow, MyLookupRow> lookup = new Lookup <MyInputDataRow, MyOutputDataRow, MyLookupRow>(
testClass.TestTransformationFunc, lookupSource, testClass.LookupData
);
DBDestination <MyOutputDataRow> dest = new DBDestination <MyOutputDataRow>()
{
DestinationTableDefinition = destinationTableDefinition
};
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test1' and Col2 = 'Lookup for 1'"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test2' and Col2 = 'Lookup for 2'"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test3' and Col2 = 'Lookup for 3'"));
}
public void DB_Multicast_DB()
{
TableDefinition sourceTableDefinition = CreateTableForMyDataRow("test.Source");
TableDefinition dest1TableDefinition = CreateTableForMyDataRow("test.Destination1");
TableDefinition dest2TableDefinition = CreateTableForMyDataRow("test.Destination2");
InsertDemoDataForMyRowTable("test.Source");
DBSource <MyDataRow> source = new DBSource <MyDataRow>();
source.SourceTableDefinition = sourceTableDefinition;
Multicast <MyDataRow> multicast = new Multicast <MyDataRow>();
DBDestination <MyDataRow> dest1 = new DBDestination <MyDataRow>();
dest1.DestinationTableDefinition = dest1TableDefinition;
DBDestination <MyDataRow> dest2 = new DBDestination <MyDataRow>();
dest2.DestinationTableDefinition = dest2TableDefinition;
source.LinkTo(multicast);
multicast.LinkTo(dest1);
multicast.LinkTo(dest2);
source.Execute();
dest1.Wait();
dest2.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Source", "Col2 in (1,2,3)"));
Assert.AreEqual(3, RowCountTask.Count("test.Destination1", "Col2 in (1,2,3)"));
Assert.AreEqual(3, RowCountTask.Count("test.Destination2", "Col2 in (1,2,3)"));
}
private void DataFlowForIdentityColumn(IConnectionManager connection)
{
DBSource <MyPartialRow> source = new DBSource <MyPartialRow>(connection, "Source4Cols");
DBDestination <MyPartialRow> dest = new DBDestination <MyPartialRow>(connection, "Destination4Cols");
source.LinkTo(dest);
source.Execute();
dest.Wait();
}
public void UpdateOnHashMatch()
{
//Arrange
CreateSourceTable("dbo.HashMatchSource");
CreateDestinationTable("dbo.HashMatchDestination");
//Act
DBSource source = new DBSource(ConnectionSource, "dbo.HashMatchSource");
RowTransformation trans = new RowTransformation(
row =>
{
Array.Resize(ref row, row.Length + 1);
row[row.Length - 1] = HashHelper.Encrypt_Char40(String.Join("", row));
return(row);
});
List <string[]> allEntriesInDestination = new List <string[]>();
Lookup lookup = new Lookup(
row =>
{
var matchingIdEntry = allEntriesInDestination.Where(destRow => destRow[0] == row[0]).FirstOrDefault();
if (matchingIdEntry == null)
{
row = null;
}
else
if (matchingIdEntry[matchingIdEntry.Length - 1] != row[row.Length - 1])
{
SqlTask.ExecuteNonQuery(ConnectionDestination, "update entry with different hashcode",
$@"UPDATE dbo.HashMatchDestination
SET name = '{ row[1] }',
age = '{ row[2] }',
hashcode = '{ row[3] }'
WHERE id = { row[0] }
");
}
return(row);
},
new DBSource(ConnectionDestination, "dbo.HashMatchDestination"),
allEntriesInDestination);
VoidDestination voidDest = new VoidDestination();
source.LinkTo(trans);
trans.LinkTo(lookup);
lookup.LinkTo(voidDest);
source.Execute();
voidDest.Wait();
//Assert
Assert.Equal(1, RowCountTask.Count(ConnectionDestination, $"dbo.HashMatchDestination", $"id = 1 AND name='Bugs' AND age = 12 AND hashcode = '{HashHelper.Encrypt_Char40("1Bugs12")}'"));
Assert.Equal(1, RowCountTask.Count(ConnectionDestination, $"dbo.HashMatchDestination", $"id = 2 AND name='Coyote' AND age = 8 AND hashcode = '{HashHelper.Encrypt_Char40("2Coyote8")}'"));
}
public void TestTransferAndLogging()
{
//CurrentDbConnection is always use if ConnectionManager is not specified otherwise!
ControlFlow.CurrentDbConnection = new SqlConnectionManager(ConnectionStringSource);
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Source
(Col1 nvarchar(100) null, Col2 int null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test3',3)");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(ConnectionStringLog);
CreateLogTablesTask.CreateLog();
new CreateTableTask("test.Destination", new List <ITableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
})
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Execute();
var sourceConnection = new SqlConnectionManager(new ConnectionString(ConnectionStringSource));
var destConnection = new SqlConnectionManager(new ConnectionString(ConnectionStringDest));
DBSource source = new DBSource(sourceConnection, "test.Source");
RowTransformation trans = new RowTransformation(row =>
{
LogTask.Info($"Test message: {row[0]}, {row[1]}"); //Log DB is used as this is the ControlFlow.CurrentDBConnection!
return(row);
});
DBDestination destination = new DBDestination(destConnection, "test.Destination");
source.LinkTo(trans);
trans.LinkTo(destination);
source.Execute();
destination.Wait();
Assert.AreEqual(1, new RowCountTask("test.Destination", "Col1 = 'Test1' AND Col2=1")
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Count().Rows);
Assert.AreEqual(1, new RowCountTask("test.Destination", "Col1 = 'Test2' AND Col2=2")
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Count().Rows);
Assert.AreEqual(1, new RowCountTask("test.Destination", "Col1 = 'Test3' AND Col2=3")
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Count().Rows);
}
public void UnknownTable()
{
//Arrange
DBSource source = new DBSource(SqlConnection, "UnknownTable");
MemoryDestination dest = new MemoryDestination();
//Act & Assert
Assert.Throws <ETLBoxException>(() =>
{
source.LinkTo(dest);
source.Execute();
dest.Wait();
});
}
public void DBMergeNoMergeIdColumn()
{
CreateSourceTable();
CreateDestinationTable();
DBSource <MySimpleRowNoMergeIdColumn> source = new DBSource <MySimpleRowNoMergeIdColumn>("test.Source");
DBMerge <MySimpleRowNoMergeIdColumn> dest = new DBMerge <MySimpleRowNoMergeIdColumn>("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
AssertDestinationTable();
}
public void DBSource()
{
//Arrange
DBSource source = new DBSource("test");
MemoryDestination dest = new MemoryDestination();
source.LinkTo(dest);
//Act & Assert
Assert.Throws <ETLBoxException>(() =>
{
source.Execute();
dest.Wait();
});
}
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'"));
}
public void SimpleLookupWithDynamicObject(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceLookupDynamicObject");
source2Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "DestinationLookupDynamicObject", -1);
DBSource <ExpandoObject> source = new DBSource <ExpandoObject>(connection, "SourceLookupDynamicObject");
DBDestination <ExpandoObject> dest = new DBDestination <ExpandoObject>(connection, "DestinationLookupDynamicObject");
//Act
List <ExpandoObject> lookupList = new List <ExpandoObject>();
CSVSource <ExpandoObject> lookupSource = new CSVSource <ExpandoObject>("res/Lookup/LookupSource.csv");
var lookup = new ETLBox.DataFlow.Lookup <ExpandoObject, ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
r.Col3 = lookupList
.Where(lkupRow => { dynamic lk = lkupRow as dynamic; return(int.Parse(lk.Key) == r.Col1); })
.Select(lkupRow => { dynamic lk = lkupRow as dynamic;
return(lk.Column3 == string.Empty ? null : Int64.Parse(lk.Column3)); })
.FirstOrDefault();
r.Col4 = lookupList
.Where(lkupRow => { dynamic lk = lkupRow as dynamic; return(int.Parse(lk.Key) == r.Col1); })
.Select(lkupRow => { dynamic lk = lkupRow as dynamic; return(double.Parse(lk.Column4)); })
.FirstOrDefault();
return(row);
},
lookupSource,
lookupList
);
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void TestDataflowWithSQLite()
{
//ControlFlow.CurrentDbConnection = new SQLiteConnectionManager(new SQLiteConnectionString(SQLiteConnectionStringParameter));
new SqlTask($"Test statement", $@"
CREATE TABLE source (
ID INTEGER PRIMARY KEY,
Col1 TEXT NOT NULL,
Col2 INTEGER NOT NULL
);
CREATE TABLE dest (
ID INTEGER PRIMARY KEY,
Col1 TEXT NOT NULL,
Col2 INTEGER NOT NULL
);
INSERT INTO source (Col1, Col2)
VALUES('Value1',1),
('Value2',2); ")
{
DisableLogging = true
}.ExecuteNonQuery();
var tableDefinition = new TableDefinition("source",
new List <TableColumn>()
{
new TableColumn("Col1", "TEXT"),
new TableColumn("Col2", "INTEGER")
});
DBSource source = new DBSource("source")
{
SourceTableDefinition = tableDefinition
};
DBDestination dest = new DBDestination("dest")
{
DestinationTableDefinition = tableDefinition
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(2, RowCountTask.Count("dest").Value);
}
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));
}
public void SimpleFlowWithBatchWrite()
{
//Arrange
TwoColumnsTableFixture s2C = new TwoColumnsTableFixture("CSVDestBatch");
s2C.InsertTestDataSet3();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(SqlConnection, "CSVDestBatch");
//Act
CSVDestination <MySimpleRow> dest = new CSVDestination <MySimpleRow>("./ObjectWithBatchWrite.csv", 2);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(File.ReadAllText("./ObjectWithBatchWrite.csv"),
File.ReadAllText("res/CSVDestination/TwoColumnsSet3.csv"));
}