public void CSV_DB_WithKey(int keyPosition)
{
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
};
columns.Insert(keyPosition, new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true)
{
IsIdentity = true
});
TableDefinition stagingTable = new TableDefinition($"test.Staging{keyPosition}", columns);
stagingTable.CreateTable();
CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv");
DBDestination <string[]> dest = new DBDestination <string[]>()
{
DestinationTableDefinition = stagingTable
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count($"test.Staging{keyPosition}", "Col1 Like '%ValueRow%' and Col2 <> 1"));
}
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)"));
}
public void ConvertIntoObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationRowTransformation");
CSVSource source = new CSVSource("res/RowTransformation/TwoColumns.csv");
//Act
RowTransformation <string[], MySimpleRow> trans = new RowTransformation <string[], MySimpleRow>(
csvdata =>
{
return(new MySimpleRow()
{
Col1 = int.Parse(csvdata[0]),
Col2 = csvdata[1]
});
});
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(Connection, "DestinationRowTransformation");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
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();
}
//MemberData(nameof(Connections), 200000, 1000)]
public void UsingNonGeneric(IConnectionManager connection, int numberOfRows, int batchSize)
{
//Arrange
TableDefinition destinationTable = CreateDestinationTable(connection, "CSVDestination");
CreateCSVFile("res/Csv/TestData.csv", numberOfRows, destinationTable);
var source = new CSVSource("res/Csv/TestData.csv");
var dest = new DBDestination(batchSize)
{
DestinationTableDefinition = destinationTable,
ConnectionManager = connection
};
source.LinkTo(dest);
//Act
BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) wswith {numberOfRows} rows of data",
() =>
{
source.Execute();
dest.Wait();
}
);
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CSVDestination"));
}
public void Excel_DB()
{
TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("Col1", "int", allowNulls: false),
new TableColumn("Col2", "nvarchar(100)", allowNulls: true),
new TableColumn("Col3", "decimal(10,2)", allowNulls: true)
});
stagingTable.CreateTable();
ExcelSource <ExcelData> source = new ExcelSource <ExcelData>("src/DataFlow/ExcelDataFile.xlsx")
{
Range = new ExcelRange(2, 4, 5, 9),
SheetName = "Sheet2"
};
DBDestination <ExcelData> dest = new DBDestination <ExcelData>()
{
DestinationTableDefinition = stagingTable
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(5, RowCountTask.Count("test.Staging"));
}
public void CompareFlowWithBulkInsert(IConnectionManager connection, int numberOfRows, int batchSize, double deviationGeneric, double deviationBulk)
{
//Arrange
CreateCSVFileIfNeeded(numberOfRows);
ReCreateDestinationTable(connection, "CSVDestinationNonGenericETLBox");
ReCreateDestinationTable(connection, "CSVDestinationBulkInsert");
ReCreateDestinationTable(connection, "CSVDestinationGenericETLBox");
var sourceNonGeneric = new CSVSource(GetCompleteFilePath(numberOfRows));
var destNonGeneric = new DBDestination(connection, "CSVDestinationNonGenericETLBox", batchSize);
var sourceGeneric = new CSVSource <CSVData>(GetCompleteFilePath(numberOfRows));
var destGeneric = new DBDestination <CSVData>(connection, "CSVDestinationGenericETLBox", batchSize);
//Act
var timeElapsedBulkInsert = GetBulkInsertTime(connection, numberOfRows);
var timeElapsedETLBoxNonGeneric = GetETLBoxTime(numberOfRows, sourceNonGeneric, destNonGeneric);
var timeElapsedETLBoxGeneric = GetETLBoxTime(numberOfRows, sourceGeneric, destGeneric);
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CSVDestinationNonGenericETLBox"));
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CSVDestinationGenericETLBox"));
Assert.True(Math.Abs(timeElapsedETLBoxGeneric.TotalMilliseconds - timeElapsedETLBoxNonGeneric.TotalMilliseconds) <
Math.Min(timeElapsedETLBoxGeneric.TotalMilliseconds, timeElapsedETLBoxNonGeneric.TotalMilliseconds) * deviationGeneric);
if (timeElapsedBulkInsert.TotalMilliseconds > 0)
{
Assert.True(timeElapsedBulkInsert < timeElapsedETLBoxNonGeneric);
Assert.True(timeElapsedBulkInsert.TotalMilliseconds * (deviationBulk + 1) > timeElapsedETLBoxNonGeneric.TotalMilliseconds);
}
}
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 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 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 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 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 CSV_DB_WithBatchChanges()
{
TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
});
stagingTable.CreateTable();
CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv");
DBDestination <string[]> dest = new DBDestination <string[]>(batchSize: 2)
{
DestinationTableDefinition = stagingTable,
BeforeBatchWrite =
rowArray => {
rowArray[0][0] = "NewValue";
return(rowArray);
}
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(1, RowCountTask.Count("test.Staging", "Col1 Like '%ValueRow%' and Col2 <> 1"));
Assert.AreEqual(2, RowCountTask.Count("test.Staging", "Col1 = 'NewValue'"));
}
public void WithBatchChanges(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBDestinationBatchChanges");
DBDestination dest = new DBDestination(connection, "DBDestinationBatchChanges", batchSize: 2)
{
BeforeBatchWrite = rowArray =>
{
rowArray[0][1] = "NewValue";
return(rowArray);
}
};
//Act
CSVSource source = new CSVSource("res/CSVSource/TwoColumns.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DBDestinationBatchChanges"));
Assert.Equal(2, RowCountTask.Count(connection, "DBDestinationBatchChanges", $"{d2c.QB}Col2{d2c.QE}='NewValue'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBDestinationBatchChanges", $"{d2c.QB}Col1{d2c.QE} = 2 AND {d2c.QB}Col2{d2c.QE}='Test2'"));
}
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 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 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 DBDestination()
{
//Arrange
string[] data = { "1", "2" };
MemorySource source = new MemorySource();
source.Data.Add(data);
DBDestination dest = new DBDestination("test");
source.LinkTo(dest);
//Act & Assert
Assert.Throws <ETLBoxException>(() =>
{
try
{
source.Execute();
dest.Wait();
}
catch (AggregateException e)
{
throw e.InnerException;
}
});
}
public void Start()
{
CSVSource sourceOrderData = new CSVSource("src/DataFlow/DemoData.csv");
sourceOrderData.Delimiter = ";";
RowTransformation <string[], Order> transIntoObject = new RowTransformation <string[], Order>(CSVIntoObject);
DBSource <Customer> sourceCustomerData = new DBSource <Customer>(CustomerTableDef);
LookupCustomerKey lookupCustKeyClass = new LookupCustomerKey();
Lookup <Order, Order, Customer> lookupCustomerKey = new Lookup <Order, Order, Customer>(
lookupCustKeyClass.FindKey, sourceCustomerData, lookupCustKeyClass.LookupData);
Multicast <Order> multiCast = new Multicast <Order>();
DBDestination <Order> destOrderTable = new DBDestination <Order>(OrderDataTableDef);
BlockTransformation <Order> blockOrders = new BlockTransformation <Order>(BlockTransformOrders);
DBDestination <Rating> destRating = new DBDestination <Rating>(CustomerRatingTableDef);
RowTransformation <Order, Rating> transOrderIntoCust = new RowTransformation <Order, Rating>(OrderIntoRating);
CustomDestination <Order> destSink = new CustomDestination <Order>(row => {; });
sourceOrderData.LinkTo(transIntoObject);
transIntoObject.LinkTo(lookupCustomerKey);
lookupCustomerKey.LinkTo(multiCast);
multiCast.LinkTo(destOrderTable);
multiCast.LinkTo(blockOrders);
blockOrders.LinkTo(transOrderIntoCust, ord => ord.Rating != null);
blockOrders.LinkTo(destSink, ord => ord.Rating == null);
transOrderIntoCust.LinkTo(destRating);
sourceOrderData.ExecuteAsync();
destOrderTable.Wait();
destRating.Wait();
}
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_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 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 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 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 CSV_RowTrans_DB()
{
TableDefinition destinationTableDefinition = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
});
destinationTableDefinition.CreateTable();
CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv");
RowTransformation <string[], MySimpleRow> trans = new RowTransformation <string[], MySimpleRow>(
csvdata => {
return(new MySimpleRow()
{
Value1 = csvdata[0],
Value2 = int.Parse(csvdata[1])
});
});
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>()
{
DestinationTableDefinition = destinationTableDefinition
};
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Staging"));
}
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 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 InitAction()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationRowTransformation");
CSVSource <MySimpleRow> source = new CSVSource <MySimpleRow>("res/RowTransformation/TwoColumnsIdMinus1.csv");
//Act
int IdOffset = 0;
RowTransformation <MySimpleRow, MySimpleRow> trans = new RowTransformation <MySimpleRow, MySimpleRow>(
"RowTransformation testing init Action",
row =>
{
row.Col1 += IdOffset;
return(row);
},
() => IdOffset += 1
);
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(Connection, "DestinationRowTransformation");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void SimpleFlow()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("Destination4CustomSource");
List <string> Data = new List <string>()
{
"Test1", "Test2", "Test3"
};
int _readIndex = 0;
Func <MySimpleRow> ReadData = () =>
{
var result = new MySimpleRow()
{
Col1 = _readIndex + 1,
Col2 = Data[_readIndex]
};
_readIndex++;
return(result);
};
Func <bool> EndOfData = () => _readIndex >= Data.Count;
//Act
CustomSource <MySimpleRow> source = new CustomSource <MySimpleRow>(ReadData, EndOfData);
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(Connection, "Destination4CustomSource");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
