public void UsingRowTransformation()
{
Prepare();
var orderSource = new MemorySource <Order>();
orderSource.DataAsList.Add(new Order()
{
OrderNumber = 815, CustomerName = "John"
});
orderSource.DataAsList.Add(new Order()
{
OrderNumber = 4711, CustomerName = "Jim"
});
var rowTrans = new RowTransformation <Order>(
row => {
int?id = SqlTask.ExecuteScalar <int>(SqlConnection,
sql: $"SELECT Id FROM CustomerTable WHERE Name='{row.CustomerName}'");
row.CustomerId = id;
return(row);
});
/* Delete below here */
var dest = new MemoryDestination <Order>();
orderSource.LinkTo(rowTrans).LinkTo(dest);
orderSource.Execute();
}
public void CheckMemoryUsage(IConnectionManager connection, int numberOfRows, int batchSize, double deviation)
{
//Arrange
BigDataCsvSource.CreateCSVFileIfNeeded(numberOfRows);
ReCreateDestinationTable(connection, "CsvDestinationWithTransformation");
var sourceExpando = new CsvSource(BigDataCsvSource.GetCompleteFilePath(numberOfRows));
var trans = new RowTransformation <ExpandoObject, CSVData>(
row =>
{
dynamic r = row as ExpandoObject;
return(new CSVData()
{
Col1 = r.Col1,
Col2 = r.Col2,
Col3 = r.Col3,
Col4 = r.Col4
});
});
var destGeneric = new DbDestination <CSVData>(connection, "CsvDestinationWithTransformation", batchSize);
sourceExpando.LinkTo(trans);
trans.LinkTo(destGeneric);
//Act
long memAfter = 0;
long memBefore = 0;
bool startCheck = true;
int count = 1;
destGeneric.AfterBatchWrite = data =>
{
if (count++ % 50 == 0)
{
using Process proc = Process.GetCurrentProcess();
memAfter = proc.WorkingSet64;
if (startCheck)
{
memBefore = memAfter;
startCheck = false;
}
Assert.True(memAfter < (memBefore + (memBefore * deviation)));
}
};
var timeElapsedETLBox = BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) with {numberOfRows} rows of data using ETLBox",
() =>
{
sourceExpando.Execute();
destGeneric.Wait();
}
);
output.WriteLine("Elapsed " + timeElapsedETLBox.TotalSeconds + " seconds for ETLBox (Expando to object transformation).");
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationWithTransformation"));
//10.000.000 rows, batch size 10.000: ~8 min
//10.000.000 rows, batch size 1.000: ~10 min 10 sec
}
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"));
}
static void Main(string[] args)
{
Preparation();
SqlConnectionManager connMan = new SqlConnectionManager("Data Source=.;Initial Catalog=demo;Integrated Security=false;User=sa;password=reallyStrongPwd123");
CsvSource <string[]> source = new CsvSource <string[]>("demodata.csv");
RowTransformation <string[], Order> rowTrans = new RowTransformation <string[], Order>(
row => new Order()
{
Id = int.Parse(row[0]),
Item = row[1],
Quantity = int.Parse(row[2]) + int.Parse(row[3]),
Price = double.Parse(row[4]) * 100
});
DbDestination <Order> dest = new DbDestination <Order>(connMan, "OrderTable");
source.LinkTo(rowTrans);
rowTrans.LinkTo(dest);
source.Execute();
dest.Wait();
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
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 SimpleDataNoHeader()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationStringArray");
//Act
ExcelSource <string[]> source = new ExcelSource <string[]>("res/Excel/TwoColumnData.xlsx")
{
HasNoHeader = true
};
RowTransformation <string[], MyData> trans = new RowTransformation <string[], MyData>(row =>
{
MyData result = new MyData();
result.Col1 = int.Parse(row[0]);
result.Col2 = row[1];
return(result);
});
DbDestination <MyData> dest = new DbDestination <MyData>(Connection, "ExcelDestinationStringArray");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
static void Main(string[] args)
{
//Set up database
Preparation();
//Define the source
CsvSource <string[]> source = new CsvSource <string[]>("demodata.csv");
//Define the transformation
RowTransformation <string[], Order> rowTrans = new RowTransformation <string[], Order>(
row => new Order()
{
Item = row[1],
Quantity = int.Parse(row[2]) + int.Parse(row[3]),
Price = int.Parse(row[4]) / 100
});
//DbDestination needs a connection manager pointing to the right database
SqlConnectionManager connMan = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;Initial Catalog=demo;");
//Define the destination
DbDestination <Order> dest = new DbDestination <Order>(connMan, "OrderTable");
//Link & run flow
source.LinkTo(rowTrans);
rowTrans.LinkTo(dest);
source.Execute();
dest.Wait();
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
public void UsingFluentVoidPredicate()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("SourceMultipleLinks");
source2Columns.InsertTestData();
source2Columns.InsertTestDataSet2();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationMultipleLinks");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("SourceMultipleLinks", SqlConnection);
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>("DestinationMultipleLinks", SqlConnection);
RowTransformation <MySimpleRow> trans1 = new RowTransformation <MySimpleRow>(row => row);
//Act
var link1 = source.LinkTo(trans1, row => row.Col1 < 4, row => row.Col1 >= 4);
var link2 = link1.source.LinkTo(dest);
using (link1.link)
using (link2.link)
{
Task sourceT = source.ExecuteAsync();
Task destT = dest.Completion;
//Assert
sourceT.Wait();
destT.Wait();
dest2Columns.AssertTestData();
}
}
public void Linking3Transformations()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("SourceMultipleLinks");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationMultipleLinks");
DbSource <string[]> source = new DbSource <string[]>("SourceMultipleLinks", SqlConnection);
DbDestination <string[]> dest = new DbDestination <string[]>("DestinationMultipleLinks", SqlConnection);
RowTransformation <string[]> trans1 = new RowTransformation <string[]>(row => row);
RowTransformation <string[]> trans2 = new RowTransformation <string[]>(row => row);
RowTransformation <string[]> trans3 = new RowTransformation <string[]>(row => row);
//Act
var linkTo1 = source.LinkTo(trans1);
var linkTo2 = linkTo1.source.LinkTo(trans2);
var linkTo3 = linkTo2.source.LinkTo(trans3);
var linkTo4 = linkTo3.source.LinkTo(dest);
using (linkTo1.link)
using (linkTo2.link)
using (linkTo3.link)
using (linkTo4.link)
{
Task sourceT = source.ExecuteAsync();
Task destT = dest.Completion;
//Assert
sourceT.Wait();
destT.Wait();
dest2Columns.AssertTestData();
}
}
public void Test(IConnectionManager sourceConnection, IConnectionManager destConnection)
{
//Arrange
string QB = destConnection.QB;
string QE = destConnection.QE;
CreateSourceAndDestinationTables(sourceConnection, destConnection, QB, QE);
//Act
var nameSource = new DbSource <Name>(sourceConnection, "Name");
var personMerge = new DbMerge <People>(destConnection, "People");
var transform = new RowTransformation <Name, People>(d =>
{
return(new People()
{
FirstName = d.FIRST_NAME,
LastName = d.LAST_NAME,
Id = d.ID
});
});
nameSource.LinkTo(transform);
transform.LinkTo(personMerge);
nameSource.Execute();
personMerge.Wait();
//Assert
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 1 AND {QB}FirstName{QE} = 'Bugs' AND {QB}LastName{QE} IS NULL"));
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 2 AND {QB}FirstName{QE} IS NULL AND {QB}LastName{QE} = 'Pig'"));
Assert.Equal(1, RowCountTask.Count(destConnection, "People", $"{QB}Id{QE} = 3 AND {QB}FirstName{QE} = 'Franky' AND {QB}LastName{QE} IS NULL"));
}
static Network CreateNetwork(string targetFileName)
{
var source = new MemorySource <MyLinkingRow>();
for (int i = 0; i <= 5; i++)
{
source.DataAsList.Add(new MyLinkingRow()
{
Col1 = i, Col2 = $"Test{i}"
});
}
var row = new RowTransformation <MyLinkingRow>();
row.TransformationFunc = row => {
Console.WriteLine($"Sending row {row.Col1}|{row.Col2} into {targetFileName}");
Task.Delay(10).Wait();
return(row);
};
var dest = new XmlDestination <MyLinkingRow>(targetFileName, ResourceType.File);
source.LinkTo(row).LinkTo(dest);
return(new Network(source));
}
public void ConvertIntoObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationRowTransformationDynamic");
CsvSource <ExpandoObject> source = new CsvSource <ExpandoObject>("res/RowTransformation/TwoColumns.csv");
//Act
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
csvdata =>
{
dynamic c = csvdata as ExpandoObject;
c.Col1 = c.Header1;
c.Col2 = c.Header2;
return(c);
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(Connection, "DestinationRowTransformationDynamic");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
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 Start()
{
CSVSource sourceOrderData = new CSVSource("src/DataFlow/DemoData.csv");
sourceOrderData.Configuration.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 SimpleDataNoHeader()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationDynamic");
//Act
ExcelSource source = new ExcelSource("res/Excel/TwoColumnShiftedData.xlsx")
{
Range = new ExcelRange(3, 4),
HasNoHeader = true
};
RowTransformation trans = new RowTransformation(row =>
{
dynamic r = row as dynamic;
r.Col1 = r.Column1;
r.Col2 = r.Column2;
return(r);
});
DbDestination dest = new DbDestination(Connection, "ExcelDestinationDynamic");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void JsonPathInEpandoObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSourceNestedDynamic");
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
r.Col1 = r.Column1;
return(r);
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(SqlConnection, "JsonSourceNestedDynamic");
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedData.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "Value",
NewPropertyName = "Col2"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(trans).LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
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()
{
Col1 = csvdata[0],
Col2 = 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 ColumnMapping(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection, "Source");
source4Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "Destination", identityColumnIndex: 2);
DbSource <string[]> source = new DbSource <string[]>(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 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 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 SourceWithDifferentNames()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("XmlSource2ColsDynamic");
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
r.Col1 = r.Column1;
r.Col2 = r.Column2;
return(r);
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>("XmlSource2ColsDynamic", Connection);
//Act
XmlSource <ExpandoObject> source = new XmlSource <ExpandoObject>("res/XmlSource/TwoColumnsElementDifferentNames.xml", ResourceType.File)
{
ElementName = "MySimpleRow"
};
var link1 = source.LinkTo(trans);
var link2 = link1.source.LinkTo(dest);
using (link1.link)
using (link2.link)
{
source.Execute();
dest.Wait();
//Assert
dest2Columns.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 static void Run()
{
Console.WriteLine("Running data flow");
//Read data from csv file
CsvSource sourceOrderData = new CsvSource("DemoData.csv");
sourceOrderData.Configuration.Delimiter = ";";
//Transform into Order object
RowTransformation <ExpandoObject, Order> transIntoObject = new RowTransformation <ExpandoObject, Order>(
csvLine =>
{
dynamic order = csvLine as dynamic;
return(new Order()
{
//Header in Csv: OrderNumber;OrderItem;OrderAmount;CustomerName
Number = order.OrderNumber,
Item = order.OrderItem,
Amount = decimal.Parse(order.OrderAmount.ToString().Replace("€", ""), CultureInfo.GetCultureInfo("en-US")),
CustomerName = order.CustomerName
});
});
sourceOrderData.LinkTo(transIntoObject);
//Find corresponding customer id if customer exists in Customer table
DbSource <Customer> sourceCustomerData = new DbSource <Customer>("customer");
LookupTransformation <Order, Customer> lookupCustomerKey = new LookupTransformation <Order, Customer>(sourceCustomerData);
transIntoObject.LinkTo(lookupCustomerKey);
//Split data
Multicast <Order> multiCast = new Multicast <Order>();
lookupCustomerKey.LinkTo(multiCast);
//Store Order in Orders table
DbDestination <Order> destOrderTable = new DbDestination <Order>("orders");
multiCast.LinkTo(destOrderTable);
//Create rating for existing customers based total of order amount
Aggregation <Order, Rating> aggregation = new Aggregation <Order, Rating>();
multiCast.LinkTo(aggregation);
//Store the rating in the customer rating table
DbDestination <Rating> destRating = new DbDestination <Rating>("customer_rating");
aggregation.LinkTo(destRating);
//Execute the data flow synchronously
sourceOrderData.Execute();
destOrderTable.Wait();
destRating.Wait();
}
public void UpdateOnHashMatch()
{
//Arrange
CreateSourceTable("dbo.HashMatchSource");
CreateDestinationTable("dbo.HashMatchDestination");
//Act
DbSource <string[]> source = new DbSource <string[]>(ConnectionSource, "dbo.HashMatchSource");
RowTransformation <string[]> trans = new RowTransformation <string[]>(
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[]>();
LookupTransformation <string[], string[]> lookup = new LookupTransformation <string[], string[]> (
new DbSource <string[]>(ConnectionDestination, "dbo.HashMatchDestination"),
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);
},
allEntriesInDestination
);
VoidDestination <string[]> voidDest = new VoidDestination <string[]>();
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 WaitForLongRunningProcedure()
{
//Arrange
CreateProcedureTask.CreateOrAlter(SqlConnection, new ProcedureDefinition()
{
Name = "longrunning",
Definition = @"
WAITFOR DELAY '00:00:01';
SELECT @input + 10;",
Parameter = new List <ProcedureParameter>()
{
new ProcedureParameter()
{
Name = "input", DataType = "INT"
}
// new ProcedureParameter() { Name ="result", Out = true, DataType="INT"}
}
});
var source = new MemorySource <MySimpleRow>();
source.DataAsList.Add(new MySimpleRow()
{
Col1 = 1, Col2 = "Test1"
});
source.DataAsList.Add(new MySimpleRow()
{
Col1 = 2, Col2 = "Test2"
});
source.DataAsList.Add(new MySimpleRow()
{
Col1 = 3, Col2 = "Test3"
});
var dest = new MemoryDestination <MySimpleRow>();
var trans = new RowTransformation <MySimpleRow>(
row =>
{
row.Col1 = SqlTask.ExecuteScalar <int>(SqlConnection, "Read from procedure",
$"EXEC longrunning @input = {row.Col1}") ?? 0;
return(row);
}
);
//Act
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <MySimpleRow>(dest.Data,
row => Assert.Equal(11, row.Col1),
row => Assert.Equal(12, row.Col1),
row => Assert.Equal(13, row.Col1)
);
}
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 SplitCSVSourceIn2Tables()
{
//Arrange
TwoColumnsTableFixture dest1Table = new TwoColumnsTableFixture("SplitDataDestination1");
FourColumnsTableFixture dest2Table = new FourColumnsTableFixture("SplitDataDestination2");
var source = new CSVSource <CSVPoco>("res/Multicast/CSVSourceToSplit.csv")
{
Configuration = new CsvHelper.Configuration.Configuration()
{
Delimiter = ";"
}
};
var multicast = new Multicast <CSVPoco>();
var row1 = new RowTransformation <CSVPoco, Entity1>(input => {
return(new Entity1
{
Col1 = input.CSVCol1,
Col2 = input.CSVCol2
});
});
var row2 = new RowTransformation <CSVPoco, Entity2>(input => {
return(new Entity2
{
Col2 = input.CSVCol2,
Col3 = input.CSVCol3,
Col4 = input.CSVCol4
});
});
var destination1 = new DBDestination <Entity1>(Connection, "SplitDataDestination1");
var destination2 = new DBDestination <Entity2>(Connection, "SplitDataDestination2");
//Act
source.LinkTo(multicast);
multicast.LinkTo(row1);
multicast.LinkTo(row2);
row1.LinkTo(destination1);
row2.LinkTo(destination2);
source.Execute();
destination1.Wait();
destination2.Wait();
//Assert
dest1Table.AssertTestData();
dest2Table.AssertTestData();
}
/* Data flow
*
* JsonSource --> RowTransformation --> Lookup --> Multicast --> DbDestination ("orders" table)
* (Order data) | |
* CsvSource <---- --------> TextDestination ("order_data.log")
* ("customer.csv")
*/
static void Main(string[] args)
{
//Preparation
RecreateTargetTable();
//Step 1 - creating the components
var source = new JsonSource <OrderRow>("https://www.etlbox.net/demo/api/orders", ResourceType.Http);
var rowTransformation = new RowTransformation <OrderRow>();
rowTransformation.TransformationFunc = row => {
row.Quantity = int.Parse(row.Description.Split(":").ElementAt(1));
return(row);
};
var lookup = new LookupTransformation <OrderRow, ExpandoObject>();
lookup.Source = new CsvSource("files/customer.csv");
lookup.MatchColumns = new[] {
new MatchColumn()
{
LookupSourcePropertyName = "Id", InputPropertyName = "CustomerId"
}
};
lookup.RetrieveColumns = new[] {
new RetrieveColumn()
{
LookupSourcePropertyName = "Name", InputPropertyName = "CustomerName"
}
};
var multicast = new Multicast <OrderRow>();
var dbDest = new DbDestination <OrderRow>(sqlConnMan, "orders");
var textDest = new TextDestination <OrderRow>("files/order_data.log");
textDest.WriteLineFunc = row => {
return($"{row.OrderNumber}\t{row.CustomerName}\t{row.Quantity}");
};
//Step2 - linking components
source.LinkTo(rowTransformation);
rowTransformation.LinkTo(lookup);
lookup.LinkTo(multicast);
multicast.LinkTo(dbDest);
multicast.LinkTo(textDest, row => row.CustomerName == "Clark Kent", row => row.CustomerName != "Clark Kent");
//Step3 - executing the network
Network.Execute(source); //Shortcut for Network.ExecuteAsync(source).Wait();
}
public void WithNestedArraysInside()
{
//Arrange
MemoryDestination dest = new MemoryDestination();
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
return(r);
});
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedArray.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$.[*].ArrayCol1",
NewPropertyName = "ArrayCol1"
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$.[*].ArrayCol2",
NewPropertyName = "ArrayCol2"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(trans).LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <ExpandoObject>(dest.Data,
row => {
dynamic r = row as ExpandoObject; var lac1 = r.ArrayCol1 as List <object>; var lac2 = r.ArrayCol2;
Assert.True(r.Column1 == 1 && lac1.Count == 2 && lac2.Count == 2);
},
row => {
dynamic r = row as ExpandoObject; var lac1 = r.ArrayCol1 as List <object>; var lac2 = r.ArrayCol2;
Assert.True(r.Column1 == 2 && lac1.Count == 2 && lac2.Count == 2);
},
row => {
dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 3 && r.ArrayCol1 == "E" && r.ArrayCol2 == "TestE");
}
);
}
public void TestDuplicateCheckInRowTrans()
{
CreateLogTablesTask.CreateLog();
DataFlow.LoggingThresholdRows = 2;
CSVSource <Poco> source = new CSVSource <Poco>("src/DataFlowExamples/Duplicate.csv");
source.Configuration.Delimiter = ";";
source.Configuration.TrimOptions = CsvHelper.Configuration.TrimOptions.Trim;
source.Configuration.MissingFieldFound = null;
List <int> IDs = new List <int>(); //at the end of the flow, this list will contain all IDs of your source
RowTransformation <Poco, Poco> rowTrans = new RowTransformation <Poco, Poco>(input =>
{
if (IDs.Contains(input.ID))
{
input.IsDuplicate = true;
}
else
{
IDs.Add(input.ID);
}
return(input);
});
var multicast = new Multicast <Poco>();
var dest = new DBDestination <Poco>("dbo.Staging");
TableDefinition stagingTable = new TableDefinition("dbo.Staging", new List <TableColumn>()
{
new TableColumn("Key", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("ID", "INT", allowNulls: false),
new TableColumn("Value", "NVARCHAR(100)", allowNulls: false),
new TableColumn("Name", "NVARCHAR(100)", allowNulls: false)
});
stagingTable.CreateTable();
var trash = new VoidDestination <Poco>();
source.LinkTo(rowTrans);
rowTrans.LinkTo(multicast);
multicast.LinkTo(dest, input => input.IsDuplicate == false);
multicast.LinkTo(trash, input => input.IsDuplicate == true);
source.Execute();
dest.Wait();
trash.Wait();
}
