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 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(); }
public void DuplicateCheckInRowTrans() { //Arrange CsvSource <Poco> source = CreateDuplicateCsvSource("res/UseCases/DuplicateCheck.csv"); List <int> IDs = new List <int>(); //at the end of the flow, this list will contain all IDs of your source //Act RowTransformation <Poco, Poco> rowTrans = new RowTransformation <Poco, Poco>(input => { if (IDs.Contains(input.ID)) { input.IsDuplicate = true; } else { IDs.Add(input.ID); } return(input); }); Multicast <Poco> multicast = new Multicast <Poco>(); DbDestination <Poco> dest = CreateDestinationTable("dbo.DuplicateCheck"); VoidDestination <Poco> 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(); //Assert AssertDataWithoutDuplicates(); }