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(); }