public void UsingLookupWithAttributes()
{
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 lookupSource = new DbSource <CustomerWithAttr>(SqlConnection, "CustomerTable");
var lookup = new LookupTransformation <Order, CustomerWithAttr>();
lookup.Source = lookupSource;
var dest = new MemoryDestination <Order>();
orderSource.LinkTo(lookup).LinkTo(dest);
Network.Execute(orderSource);
foreach (var row in dest.Data)
{
Console.WriteLine($"Order:{row.OrderNumber} Name:{row.CustomerName} Id:{row.CustomerId}");
}
//Output
//Order:815 Name:John Id:1
//Order:4711 Name:Jim Id:2
}
public void NoLookupSource()
{
//Arrange
MemorySource <MyDataRow> source = new MemorySource <MyDataRow>();
source.DataAsList.Add(new MyDataRow()
{
Col1 = 1, Col2 = "Test1"
});
//Act
var lookup = new LookupTransformation <MyDataRow, MyLookupRow>();
MemoryDestination <MyDataRow> dest = new MemoryDestination <MyDataRow>();
//Assert
Assert.Throws <ETLBoxException>(() =>
{
try
{
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
}
catch (AggregateException e) { throw e.InnerException; }
});
//Assert
}
private static void RunExceptionFlowWithType <T>()
{
//Arrange
MemorySource <InputDataRow> source = new MemorySource <InputDataRow>();
source.DataAsList.Add(new InputDataRow()
{
LookupId = 1
});
MemorySource <T> lookupSource = new MemorySource <T>();
var lookup = new LookupTransformation <InputDataRow, T>(lookupSource);
MemoryDestination <InputDataRow> dest = new MemoryDestination <InputDataRow>();
source.LinkTo(lookup);
lookup.LinkTo(dest);
//Act && Assert
Assert.Throws <ETLBoxException>(() =>
{
try
{
source.Execute();
dest.Wait();
}
catch (AggregateException e)
{
throw e.InnerException;
}
});
}
public DbMerge(string tableName)
{
TableName = tableName;
DestinationTableAsSource = new DbSource <TInput>();
DestinationTable = new DbDestination <TInput>();
Lookup = new LookupTransformation <TInput, TInput>();
OutputSource = new CustomSource <TInput>();
}
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 MultipleMatchAndRetrieveColumns()
{
//Arrange
MemorySource <InputDataMultiple> source = new MemorySource <InputDataMultiple>();
source.DataAsList.Add(new InputDataMultiple()
{
LookupId1 = 1, LookupId2 = "T1"
});
source.DataAsList.Add(new InputDataMultiple()
{
LookupId1 = 2, LookupId2 = "TX"
});
source.DataAsList.Add(new InputDataMultiple()
{
LookupId1 = 4, LookupId2 = "T2"
});
source.DataAsList.Add(new InputDataMultiple()
{
LookupId1 = 3, LookupId2 = "T3"
});
MemorySource <LookupDataMultiple> lookupSource = new MemorySource <LookupDataMultiple>();
lookupSource.DataAsList.Add(new LookupDataMultiple()
{
Id1 = 1, Id2 = "T1", Value1 = "Test1", Value2 = 100
});
lookupSource.DataAsList.Add(new LookupDataMultiple()
{
Id1 = 2, Value1 = "Test2", Value2 = 200
});
lookupSource.DataAsList.Add(new LookupDataMultiple()
{
Id1 = 3, Id2 = "T3", Value2 = 300
});
var lookup = new LookupTransformation <InputDataMultiple, LookupDataMultiple>();
lookup.Source = lookupSource;
MemoryDestination <InputDataMultiple> dest = new MemoryDestination <InputDataMultiple>();
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <InputDataMultiple>(dest.Data,
r => Assert.True(r.LookupId1 == 1 && r.LookupValue1 == "Test1" && r.LookupValue2 == 100),
r => Assert.True(r.LookupId1 == 2 && r.LookupValue1 == null),
r => Assert.True(r.LookupId1 == 4 && r.LookupValue1 == null),
r => Assert.True(r.LookupId1 == 3 && r.LookupValue1 == null && r.LookupValue2 == 300)
);
}
public void OneMatchOneRetrieveColumn()
{
//Arrange
MemorySource <InputDataRow> source = new MemorySource <InputDataRow>();
source.DataAsList.Add(new InputDataRow()
{
LookupId = 1
});
source.DataAsList.Add(new InputDataRow()
{
LookupId = 2
});
source.DataAsList.Add(new InputDataRow()
{
LookupId = 4
});
source.DataAsList.Add(new InputDataRow()
{
LookupId = 3
});
MemorySource <LookupData> lookupSource = new MemorySource <LookupData>();
lookupSource.DataAsList.Add(new LookupData()
{
Id = 1, Value = "Test1"
});
lookupSource.DataAsList.Add(new LookupData()
{
Id = 2, Value = "Test2"
});
lookupSource.DataAsList.Add(new LookupData()
{
Id = 3, Value = "Test3"
});
var lookup = new LookupTransformation <InputDataRow, LookupData>();
lookup.Source = lookupSource;
MemoryDestination <InputDataRow> dest = new MemoryDestination <InputDataRow>();
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <InputDataRow>(dest.Data,
r => Assert.True(r.LookupId == 1 && r.LookupValue == "Test1"),
r => Assert.True(r.LookupId == 2 && r.LookupValue == "Test2"),
r => Assert.True(r.LookupId == 4 && r.LookupValue == null),
r => Assert.True(r.LookupId == 3 && r.LookupValue == "Test3")
);
}
/* 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 AttributesWithDynamic()
{
Prepare();
var orderSource = new MemorySource();
dynamic sourceRow1 = new ExpandoObject();
sourceRow1.OrderNumber = 815;
sourceRow1.CustomerName = "John";
orderSource.DataAsList.Add(sourceRow1);
dynamic sourceRow2 = new ExpandoObject();
sourceRow2.OrderNumber = 4711;
sourceRow2.CustomerName = "Jim";
orderSource.DataAsList.Add(sourceRow2);
var lookupSource = new DbSource(SqlConnection, "CustomerTable");
var lookup = new LookupTransformation();
lookup.MatchColumns = new[] {
new MatchColumn()
{
LookupSourcePropertyName = "Name"
, InputPropertyName = "CustomerName"
}
};
lookup.RetrieveColumns = new[] {
new RetrieveColumn()
{
LookupSourcePropertyName = "Id",
InputPropertyName = "CustomerId"
}
};
lookup.Source = lookupSource;
var dest = new MemoryDestination();
orderSource.LinkTo(lookup).LinkTo(dest);
Network.Execute(orderSource);
foreach (dynamic row in dest.Data)
{
Console.WriteLine($"Order:{row.OrderNumber} Name:{row.CustomerName} Id:{row.CustomerId}");
}
//Output
//Order:815 Name:John Id:1
//Order:4711 Name:Jim Id:2
}
public void SimpleLookupWithDynamicObject(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceLookupDynamicObject");
source2Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "DestinationLookupDynamicObject", -1);
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>(connection, "SourceLookupDynamicObject");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(connection, "DestinationLookupDynamicObject");
//Act
List <ExpandoObject> lookupList = new List <ExpandoObject>();
CsvSource <ExpandoObject> lookupSource = new CsvSource <ExpandoObject>("res/Lookup/LookupSource.csv");
var lookup = new LookupTransformation <ExpandoObject, ExpandoObject>(
lookupSource,
row =>
{
dynamic r = row as ExpandoObject;
r.Col3 = lookupList
.Where(lkupRow => { dynamic lk = lkupRow as dynamic; return(int.Parse(lk.Key) == r.Col1); })
.Select(lkupRow =>
{
dynamic lk = lkupRow as dynamic;
return(lk.Column3 == string.Empty ? null : Int64.Parse(lk.Column3));
})
.FirstOrDefault();
r.Col4 = lookupList
.Where(lkupRow => { dynamic lk = lkupRow as dynamic; return(int.Parse(lk.Key) == r.Col1); })
.Select(lkupRow => { dynamic lk = lkupRow as dynamic; return(double.Parse(lk.Column4)); })
.FirstOrDefault();
return(row);
},
lookupList
);
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void NoErrorLinking()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(SqlConnection, "LookupErrorLinkingDest");
CreateSourceTable(SqlConnection, "LookupErrorLinkingSource");
DbSource <MyLookupRow> lookupSource = new DbSource <MyLookupRow>(SqlConnection, "LookupErrorLinkingSource");
MemorySource <MyInputDataRow> source = new MemorySource <MyInputDataRow>();
source.DataAsList = new List <MyInputDataRow>()
{
new MyInputDataRow()
{
Col1 = 1
},
new MyInputDataRow()
{
Col1 = 2
},
new MyInputDataRow()
{
Col1 = 3
}
};
//Act & Assert
Assert.ThrowsAny <Exception>(() =>
{
List <MyLookupRow> LookupTableData = new List <MyLookupRow>();
LookupTransformation <MyInputDataRow, MyLookupRow> lookup = new LookupTransformation <MyInputDataRow, MyLookupRow>(
lookupSource,
row =>
{
row.Col2 = LookupTableData.Where(ld => ld.Key == row.Col1).Select(ld => ld.LookupValue).FirstOrDefault();
return(row);
}
, LookupTableData
);
DbDestination <MyInputDataRow> dest = new DbDestination <MyInputDataRow>(SqlConnection, "LookupErrorLinkingDest");
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
});
}
public void UsingLookupWithRetrievalByKeyFunc()
{
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 lookupSource = new DbSource <Customer>(SqlConnection, "CustomerTable");
var lookup = new LookupTransformation <Order, Customer>();
lookup.Source = lookupSource;
lookup.GetInputRecordKeyFunc = inputrow => inputrow.CustomerName;
lookup.GetSourceRecordKeyFunc = sourcerow => sourcerow.Name;
lookup.RetrievalByKeyFunc = (inputrow, dict) => {
if (dict.ContainsKey(inputrow.CustomerName))
{
inputrow.CustomerId = dict[inputrow.CustomerName].Id;
}
return(inputrow);
};
var dest = new MemoryDestination <Order>();
orderSource.LinkTo(lookup).LinkTo(dest);
Network.Execute(orderSource);
foreach (var row in dest.Data)
{
Console.WriteLine($"Order:{row.OrderNumber} Name:{row.CustomerName} Id:{row.CustomerId}");
}
//Output
//Order:815 Name:John Id:1
//Order:4711 Name:Jim Id:2
}
public void UsingLookup()
{
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 lookupSource = new DbSource <Customer>(SqlConnection, "CustomerTable");
var lookup = new LookupTransformation <Order, Customer>();
lookup.Source = lookupSource;
lookup.RetrievalFunc =
(row, cache) => {
row.CustomerId = cache.Where(cust => cust.Name == row.CustomerName)
.Select(cust => cust.Id)
.FirstOrDefault();
return(row);
};
var dest = new MemoryDestination <Order>();
orderSource.LinkTo(lookup).LinkTo(dest);
Network.Execute(orderSource);
foreach (var row in dest.Data)
{
Console.WriteLine($"Order:{row.OrderNumber} Name:{row.CustomerName} Id:{row.CustomerId}");
}
//Output
//Order:815 Name:John Id:1
//Order:4711 Name:Jim Id:2
}
public void PartialDbCacheWithSql()
{
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 lookupSource = new DbSource <CustomerWithAttr>(SqlConnection, "CustomerTable");
var lookup = new LookupTransformation <Order, CustomerWithAttr>();
lookup.Source = lookupSource;
lookup.CacheMode = CacheMode.Partial;
lookup.PartialCacheSettings.LoadBatchSize = 1;
lookup.PartialCacheSettings.LoadCacheSql = batch =>
$@"SELECT Id, Name
FROM CustomerTable
WHERE Name in ({string.Join(",", batch.Select(r => $"'{r.CustomerName}'"))})";
var dest = new MemoryDestination <Order>();
orderSource.LinkTo(lookup).LinkTo(dest);
Network.Execute(orderSource);
foreach (var row in dest.Data)
{
Console.WriteLine($"Order:{row.OrderNumber} Name:{row.CustomerName} Id:{row.CustomerId}");
}
//Output
//Order:815 Name:John Id:1
//Order:4711 Name:Jim Id:2
}
public void PartialDbCacheWithAttributes()
{
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 lookupSource = new DbSource <CustomerWithAttr>(SqlConnection, "CustomerTable");
var lookup = new LookupTransformation <Order, CustomerWithAttr>();
lookup.Source = lookupSource;
lookup.CacheMode = CacheMode.Partial;
lookup.PartialCacheSettings.LoadBatchSize = 1;
var dest = new MemoryDestination <Order>();
orderSource.LinkTo(lookup).LinkTo(dest);
Network.Execute(orderSource);
foreach (var row in dest.Data)
{
Console.WriteLine($"Order:{row.OrderNumber} Name:{row.CustomerName} Id:{row.CustomerId}");
}
//Output
//Order:815 Name:John Id:1
//Order:4711 Name:Jim Id:2
/* Delete below here */
}
public void UsingGetInputRecordKeyFunc()
{
var source = new CsvSource <InputRow>("InputData.csv");
source.Configuration.MissingFieldFound = null;
var lookupSource = new MemorySource <LookupRow>();
lookupSource.DataAsList = new List <LookupRow>()
{
new LookupRow()
{
LookupId = "idstringa", LookupValue = "A"
},
new LookupRow()
{
LookupId = "idstringb", LookupValue = "B"
},
new LookupRow()
{
LookupId = "idstringc", LookupValue = "C"
}
};
var lookup = new LookupTransformation <InputRow, LookupRow>();
lookup.Source = lookupSource;
lookup.GetInputRecordKeyFunc = row => row.Id.ToLower();
lookup.GetSourceRecordKeyFunc = row => row.LookupId;
var dest = new CsvDestination <InputRow>("output1.csv");
source.LinkTo(lookup).LinkTo(dest);
Network.Execute(source);
PrintFile("InputData.csv");
PrintFile("output1.csv");
}
public void SimpleLookupWithoutObject(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceNonGenericLookup");
source2Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "DestinationNonGenericLookup", -1);
FourColumnsTableFixture lookup4Columns = new FourColumnsTableFixture(connection, "LookupNonGeneric");
lookup4Columns.InsertTestData();
DbSource <string[]> source = new DbSource <string[]>("SourceNonGenericLookup", connection);
DbDestination <string[]> dest = new DbDestination <string[]>("DestinationNonGenericLookup", connection);
//Act
List <string[]> lookupList = new List <string[]>();
DbSource <string[]> lookupSource = new DbSource <string[]>("LookupNonGeneric", connection);
LookupTransformation <string[], string[]> lookup = new LookupTransformation <string[], string[]> (
lookupSource,
row =>
{
Array.Resize(ref row, 4);
row[2] = lookupList.Where(lkupRow => lkupRow[0] == row[0]).Select(lkupRow => lkupRow[2]).FirstOrDefault();
row[3] = lookupList.Where(lkupRow => lkupRow[0] == row[0]).Select(lkupRow => lkupRow[3]).FirstOrDefault();
return(row);
},
lookupList
);
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void OverwritingComparisonInObject()
{
var source = new CsvSource <MyInputRow>("InputData.csv");
source.Configuration.MissingFieldFound = null;
var lookupSource = new MemorySource <MyLookupRow>();
lookupSource.DataAsList = new List <MyLookupRow>()
{
new MyLookupRow()
{
LookupId = new ComparableObject("idstringa"), LookupValue = "A"
},
new MyLookupRow()
{
LookupId = new ComparableObject("idstringb"), LookupValue = "B"
},
new MyLookupRow()
{
LookupId = new ComparableObject("idstringc"), LookupValue = "C"
}
};
var lookup = new LookupTransformation <MyInputRow, MyLookupRow>();
lookup.Source = lookupSource;
var dest = new CsvDestination <MyInputRow>("output2.csv");
source.LinkTo(lookup).LinkTo(dest);
Network.Execute(source);
PrintFile("InputData.csv");
PrintFile("output1.csv");
}
public void InputTypeSameAsOutput(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection, "SourceLookupSameType");
source4Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "DestinationLookupSameType");
FourColumnsTableFixture lookup4Columns = new FourColumnsTableFixture(connection, "LookupSameType");
lookup4Columns.InsertTestData();
DbSource <MyDataRow> source = new DbSource <MyDataRow>(connection, "SourceLookupSameType");
DbSource <MyLookupRow> lookupSource = new DbSource <MyLookupRow>(connection, "LookupSameType");
var lookup = new LookupTransformation <MyDataRow, MyLookupRow>();
lookup.TransformationFunc =
row =>
{
row.Col1 = row.Col1;
row.Col2 = row.Col2;
row.Col3 = lookup.LookupData.Where(ld => ld.Key == row.Col1).Select(ld => ld.LookupValue1).FirstOrDefault();
row.Col4 = lookup.LookupData.Where(ld => ld.Key == row.Col1).Select(ld => ld.LookupValue2).FirstOrDefault();
return(row);
};
lookup.Source = lookupSource;
DbDestination <MyDataRow> dest = new DbDestination <MyDataRow>(connection, "DestinationLookupSameType");
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void WithObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(SqlConnection, "LookupErrorLinkingDest");
CreateSourceTable(SqlConnection, "LookupErrorLinkingSource");
DbSource <MyLookupRow> lookupSource = new DbSource <MyLookupRow>(SqlConnection, "LookupErrorLinkingSource");
MemorySource <MyInputDataRow> source = new MemorySource <MyInputDataRow>();
source.DataAsList = new List <MyInputDataRow>()
{
new MyInputDataRow()
{
Col1 = 1
},
new MyInputDataRow()
{
Col1 = 2
},
new MyInputDataRow()
{
Col1 = 3
},
new MyInputDataRow()
{
Col1 = 4
}
};
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
//Act
List <MyLookupRow> LookupTableData = new List <MyLookupRow>();
LookupTransformation <MyInputDataRow, MyLookupRow> lookup = new LookupTransformation <MyInputDataRow, MyLookupRow>(
lookupSource,
row =>
{
row.Col2 = LookupTableData.Where(ld => ld.Key == row.Col1).Select(ld => ld.LookupValue).FirstOrDefault();
if (row.Col1 == 4)
{
throw new Exception("Error record");
}
return(row);
}
, LookupTableData
);
DbDestination <MyInputDataRow> dest = new DbDestination <MyInputDataRow>(SqlConnection, "LookupErrorLinkingDest");
source.LinkTo(lookup);
lookup.LinkTo(dest);
lookup.LinkLookupSourceErrorTo(errorDest);
lookup.LinkLookupTransformationErrorTo(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))
);
}
