public void ModifyInputDataList()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("BlockTransSourceDynamic");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("BlockTransDestDynamic");
DBSource <ExpandoObject> source = new DBSource <ExpandoObject>(SqlConnection, "BlockTransSourceDynamic");
DBDestination <ExpandoObject> dest = new DBDestination <ExpandoObject>(SqlConnection, "BlockTransDestDynamic");
//Act
BlockTransformation <ExpandoObject> block = new BlockTransformation <ExpandoObject>(
inputData => {
inputData.RemoveRange(1, 2);
dynamic nr = new ExpandoObject();
nr.Col1 = 4;
nr.Col2 = "Test4";
inputData.Add(nr);
return(inputData);
});
source.LinkTo(block);
block.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(2, RowCountTask.Count(SqlConnection, "BlockTransDestDynamic"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "BlockTransDestDynamic", "Col1 = 1 AND Col2='Test1'"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "BlockTransDestDynamic", "Col1 = 4 AND Col2='Test4'"));
}
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.Col2 += 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 UsingTableDefinitions(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceTableDef");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DestinationTableDef");
//Act
DBSource source = new DBSource()
{
SourceTableDefinition = source2Columns.TableDefinition,
ConnectionManager = connection
};
DBDestination dest = new DBDestination()
{
DestinationTableDefinition = dest2Columns.TableDefinition,
ConnectionManager = connection
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void DB_MulticastWPredicates_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, row => row.Value2 <= 2);
multicast.LinkTo(dest2, row => row.Value2 > 2);
source.Execute();
dest1.Wait();
dest2.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Source", "Col2 in (1,2,3)"));
Assert.AreEqual(2, RowCountTask.Count("test.Destination1", "Col2 in (1,2)"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination2"));
}
public void ColumnMapping(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SpecialCharacterSource");
InsertTestData(connection, "SpecialCharacterSource");
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "SpecialCharacterDestination");
//Act
DBSource source = new DBSource()
{
ConnectionManager = connection,
SourceTableDefinition = source2Columns.TableDefinition
};
DBDestination dest = new DBDestination()
{
ConnectionManager = connection,
DestinationTableDefinition = dest2Columns.TableDefinition
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(4, RowCountTask.Count(connection, "SpecialCharacterDestination"));
}
public void SimpleMerge(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.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", $"{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.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 SqlWithNamedColumns(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "SourceSql");
s2c.InsertTestData();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DestinationSql");
//Act
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>()
{
Sql = $@"SELECT CASE WHEN {s2c.QB}Col1{s2c.QE} IS NOT NULL THEN {s2c.QB}Col1{s2c.QE} ELSE {s2c.QB}Col1{s2c.QE} END AS {s2c.QB}Col1{s2c.QE},
{s2c.QB}Col2{s2c.QE}
FROM {s2c.QB}SourceSql{s2c.QE}",
ConnectionManager = connection
};
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(connection, "DestinationSql");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d2c.AssertTestData();
}
public void DBSource_RowTrans_DBDest_Issue3()
{
SqlTask.ExecuteNonQuery("Create test table",
@"CREATE TABLE dbo.test
(Col1 int null, Col2 int null, Col3 int null)"
);
DBSource <EntitiesInfo> source = new DBSource <EntitiesInfo>()
{
Sql = "SELECT * FROM (VALUES (1,2,3), (4,5,6), (7,8,9)) AS MyTable(Col1,Col2,Col3)"
};
RowTransformation <EntitiesInfo, EntitiesInfo> rowT = new RowTransformation <EntitiesInfo, EntitiesInfo>(
input => new EntitiesInfo {
Col1 = input.Col2 + input.Col3,
Col2 = 0,
Col3 = input.Col1
}
);
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString(ConnectionStringParameter));
DBDestination <EntitiesInfo> dest = new DBDestination <EntitiesInfo>("test");
source.LinkTo(rowT);
rowT.LinkTo(dest);
source.Execute();
dest.Wait();
}
public void WithSqlNotMatchingColumns(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceNotMatchingCols");
source2Columns.InsertTestData();
SqlTask.ExecuteNonQuery(connection, "Create destination table", @"CREATE TABLE DestinationNotMatchingCols
(Col3 nvarchar(100) null, Col4 nvarchar(100) null, Col1 nvarchar(100) null)");
//Act
DBSource source = new DBSource()
{
Sql = "SELECT Col1, Col2 FROM SourceNotMatchingCols",
ConnectionManager = connection
};
DBDestination dest = new DBDestination(connection, "DestinationNotMatchingCols");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DestinationNotMatchingCols"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationNotMatchingCols", "Col3 = '1' AND Col4='Test1'"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationNotMatchingCols", "Col3 = '2' AND Col4='Test2'"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationNotMatchingCols", "Col3 = '3' AND Col4='Test3'"));
}
public void EnforcingTruncate(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.InsertTestDataSet3();
DBSource <MyMergeRow> source = new DBSource <MyMergeRow>(connection, "DBMergeSource");
//Act
DBMerge <MyMergeRow> dest = new DBMerge <MyMergeRow>(connection, "DBMergeDestination");
dest.UseTruncateMethod = true;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDestination"));
Assert.True(dest.DeltaTable.Count == 5);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "E" && row.Key == 1).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "U" && row.Key == 2).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "I" && row.Key == 3).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Key == 4).Count() == 1);
Assert.True(dest.DeltaTable.Where(row => row.ChangeAction == "D" && row.Key == 10).Count() == 1);
}
public void RedirectErrorWithObject(IConnectionManager connection)
{
if (connection.GetType() == typeof(SQLiteConnectionManager))
{
Task.Delay(100).Wait(); //Database was locked and needs to recover after exception
}
//Arrange
CreateSourceTable(connection, "DBSourceErrorLinking");
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DBDestinationErrorLinking");
//Act
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(connection, "DBSourceErrorLinking");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(connection, "DBDestinationErrorLinking");
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
source.LinkTo(dest);
source.LinkErrorTo(errorDest);
source.Execute();
dest.Wait();
errorDest.Wait();
//Assert
dest2Columns.AssertTestData();
Assert.Collection <ETLBoxError>(errorDest.Data,
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText)),
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText)),
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText))
);
}
public void DB_Lookup_DB_nongeneric()
{
TableDefinition sourceTableDefinition = CreateDBSourceTableForInputRow();
TableDefinition destinationTableDefinition = CreateDBDestinationTableForOutputRow();
TableDefinition lookupTableDefinition = CreateDBLookupTable();
List <string[]> lookupList = new List <string[]>();
DBSource source = new DBSource("test.Source");
DBSource lookupSource = new DBSource("test.Lookup");
Lookup lookup = new Lookup(
row => {
row[1] = lookupList.Where(ld => ld[0] == row[1]).Select(ld => ld[1]).FirstOrDefault();
return(row);
},
lookupSource,
lookupList
);
DBDestination dest = new DBDestination("test.Destination");
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 InsertIntoTable()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("CustomDestinationDynamicSource");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("CustomDestinationDynamicDestination");
//Act
DBSource <ExpandoObject> source = new DBSource <ExpandoObject>(Connection, "CustomDestinationDynamicSource");
CustomDestination <ExpandoObject> dest = new CustomDestination <ExpandoObject>(
row => {
dynamic r = row as ExpandoObject;
SqlTask.ExecuteNonQuery(Connection, "Insert row",
$"INSERT INTO dbo.CustomDestinationDynamicDestination VALUES({r.Col1},'{r.Col2}')");
}
);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void DestinationWithIdentityColumn(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceDynamicIDCol");
source2Columns.InsertTestData();
CreateTableTask.Create(connection, "DestinationDynamicIdCol",
new List <TableColumn>()
{
new TableColumn("Id", "BIGINT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col2", "VARCHAR(100)", allowNulls: true),
new TableColumn("Col1", "INT", allowNulls: true),
new TableColumn("ColX", "INT", allowNulls: true),
});
//Act
DBSource <ExpandoObject> source = new DBSource <ExpandoObject>(connection, "SourceDynamicIDCol");
DBDestination <ExpandoObject> dest = new DBDestination <ExpandoObject>(connection, "DestinationDynamicIdCol");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
string QB = ConnectionManagerSpecifics.GetBeginQuotation(connection);
string QE = ConnectionManagerSpecifics.GetEndQuotation(connection);
Assert.Equal(3, RowCountTask.Count(connection, "DestinationDynamicIdCol"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicIdCol", $"{QB}Col1{QE} = 1 AND {QB}Col2{QE}='Test1' AND {QB}Id{QE} > 0 AND {QB}ColX{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicIdCol", $"{QB}Col1{QE} = 2 AND {QB}Col2{QE}='Test2' AND {QB}Id{QE} > 0 AND {QB}ColX{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(connection, "DestinationDynamicIdCol", $"{QB}Col1{QE} = 3 AND {QB}Col2{QE}='Test3' AND {QB}Id{QE} > 0 AND {QB}ColX{QE} IS NULL"));
}
public void ColumnMapping(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection, "Source");
source4Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "Destination", identityColumnIndex: 2);
DBSource source = new DBSource(connection, "Source");
RowTransformation <string[], MyExtendedRow> trans = new RowTransformation <string[], MyExtendedRow>(
row => new MyExtendedRow()
{
Id = int.Parse(row[0]),
Text = row[1],
Value = row[2] != null ? (long?)long.Parse(row[2]) : null,
Percentage = decimal.Parse(row[3])
});
//Act
DBDestination <MyExtendedRow> dest = new DBDestination <MyExtendedRow>(connection, "Destination");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
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 WithDeltaDestination(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.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", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 7 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.Equal(7, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 10 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'D' AND {d2c.QB}Col1{d2c.QE} = 10"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'U' AND {d2c.QB}Col1{d2c.QE} IN (1,2,4)"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'I' AND {d2c.QB}Col1{d2c.QE} IN (3,5,6)"));
}
public void SimpleFlowWithDefaultConnection()
{
//Arrange
CreateTableTask.Create("TestSourceTable",
new List <TableColumn>()
{
new TableColumn("Col1", "VARCHAR(100)")
});
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestSourceTable VALUES ('T');");
CreateTableTask.Create("TestDestinationTable",
new List <TableColumn>()
{
new TableColumn("Col1", "VARCHAR(100)")
});
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>("TestSourceTable");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>("TestDestinationTable");
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.True(RowCountTask.Count("TestDestinationTable").Value == 1);
}
public void WithSqlNotMatchingColumns(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "SourceNotMatchingCols");
s2c.InsertTestData();
SqlTask.ExecuteNonQuery(connection, "Create destination table",
$@"CREATE TABLE destination_notmatchingcols
( col3 VARCHAR(100) NULL
, col4 VARCHAR(100) NULL
, {s2c.QB}Col1{s2c.QE} VARCHAR(100) NULL)");
//Act
DBSource source = new DBSource()
{
Sql = $"SELECT {s2c.QB}Col1{s2c.QE}, {s2c.QB}Col2{s2c.QE} FROM {s2c.QB}SourceNotMatchingCols{s2c.QE}",
ConnectionManager = connection
};
DBDestination dest = new DBDestination(connection, "destination_notmatchingcols");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "destination_notmatchingcols"));
Assert.Equal(1, RowCountTask.Count(connection, "destination_notmatchingcols", $"col3 = '1' AND col4='Test1'"));
Assert.Equal(1, RowCountTask.Count(connection, "destination_notmatchingcols", $"col3 = '2' AND col4='Test2'"));
Assert.Equal(1, RowCountTask.Count(connection, "destination_notmatchingcols", $"col3 = '3' AND col4='Test3'"));
}
public void SplitInto2Tables()
{
//Arrange
TwoColumnsTableFixture sourceTable = new TwoColumnsTableFixture("Source");
sourceTable.InsertTestData();
TwoColumnsTableFixture dest1Table = new TwoColumnsTableFixture("Destination1");
TwoColumnsTableFixture dest2Table = new TwoColumnsTableFixture("Destination2");
DBSource source = new DBSource(Connection, "Source");
DBDestination dest1 = new DBDestination(Connection, "Destination1");
DBDestination dest2 = new DBDestination(Connection, "Destination2");
//Act
Multicast multicast = new Multicast();
source.LinkTo(multicast);
multicast.LinkTo(dest1);
multicast.LinkTo(dest2);
source.Execute();
dest1.Wait();
dest2.Wait();
//Assert
dest1Table.AssertTestData();
dest2Table.AssertTestData();
}
public void DB_Multicast_DB_WithStringArray()
{
TableDefinition sourceTableDefinition = CreateTableForMyDataRow("test.Source");
TableDefinition dest1TableDefinition = CreateTableForMyDataRow("test.Destination1");
TableDefinition dest2TableDefinition = CreateTableForMyDataRow("test.Destination2");
InsertDemoDataForMyRowTable("test.Source");
DBSource source = new DBSource();
source.SourceTableDefinition = sourceTableDefinition;
Multicast multicast = new Multicast();
DBDestination dest1 = new DBDestination();
dest1.DestinationTableDefinition = dest1TableDefinition;
DBDestination dest2 = new DBDestination();
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)"));
}
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 PredicateFilteringWithInteger()
{
//Arrange
TwoColumnsTableFixture sourceTable = new TwoColumnsTableFixture("Source");
sourceTable.InsertTestData();
TwoColumnsTableFixture dest1Table = new TwoColumnsTableFixture("Destination1");
TwoColumnsTableFixture dest2Table = new TwoColumnsTableFixture("Destination2");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(Connection, "Source");
DBDestination <MySimpleRow> dest1 = new DBDestination <MySimpleRow>(Connection, "Destination1");
DBDestination <MySimpleRow> dest2 = new DBDestination <MySimpleRow>(Connection, "Destination2");
//Act
Multicast <MySimpleRow> multicast = new Multicast <MySimpleRow>();
source.LinkTo(multicast);
multicast.LinkTo(dest1, row => row.Col1 <= 2);
multicast.LinkTo(dest2, row => row.Col1 > 2);
source.Execute();
dest1.Wait();
dest2.Wait();
//Assert
Assert.Equal(1, RowCountTask.Count(Connection, "Destination1", "Col1 = 1 AND Col2='Test1'"));
Assert.Equal(1, RowCountTask.Count(Connection, "Destination1", "Col1 = 2 AND Col2='Test2'"));
Assert.Equal(1, RowCountTask.Count(Connection, "Destination2", "Col1 = 3 AND Col2='Test3'"));
}
public void Sql_DB()
{
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>()
{
Sql = $@"select * from (values ('Test1',1), ('Test2',2), ('Test3',3)) AS MyTable(Col1,Col2)"
};
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>()
{
DestinationTableDefinition = destinationTableDefinition
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test1' AND Col2=1"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test2' AND Col2=2"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test3' AND Col2=3"));
}
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 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 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 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 SortSimpleDataDescending()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("SortSource");
source2Columns.InsertTestData();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(Connection, "SortSource");
//Act
List <MySimpleRow> actual = new List <MySimpleRow>();
CustomDestination <MySimpleRow> dest = new CustomDestination <MySimpleRow>(
row => actual.Add(row)
);
Comparison <MySimpleRow> comp = new Comparison <MySimpleRow>(
(x, y) => y.Col1 - x.Col1
);
Sort <MySimpleRow> block = new Sort <MySimpleRow>(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 => row.Col1).ToList());
}
public void BatchSize()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("MemoryDestinationBatchSizeSource");
source2Columns.InsertTestData();
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(SqlConnection, "MemoryDestinationBatchSizeSource");
MemoryDestination <MySimpleRow> dest = new MemoryDestination <MySimpleRow>()
{
BatchSize = 2
};
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection(dest.Data,
d => Assert.True(d.Col1 == 1 && d.Col2 == "Test1"),
d => Assert.True(d.Col1 == 2 && d.Col2 == "Test2"),
d => Assert.True(d.Col1 == 3 && d.Col2 == "Test3")
);
}
