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