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 ColumnMapping(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection,
"SourceColumnMapping", identityColumnIndex: 0);
source4Columns.InsertTestData();
//Act
DBSource <MyExtendedRow> source = new DBSource <MyExtendedRow>(connection, "SourceColumnMapping");
CustomDestination <MyExtendedRow> dest = new CustomDestination <MyExtendedRow>(
input =>
{
//Assert
Assert.InRange(input.Id, 1, 3);
Assert.StartsWith("Test", input.Text);
if (input.Id == 1)
{
Assert.Null(input.Value);
}
else
{
Assert.True(input.Value > 0);
}
Assert.InRange(input.Percentage, 1, 2);
});
source.LinkTo(dest);
source.Execute();
dest.Wait();
}
public void DataOnSheet2WithRange()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("ExcelDestination");
//Act
ExcelSource <ExcelDataSheet2> source = new ExcelSource <ExcelDataSheet2>("res/Excel/DataOnSheet2.xlsx")
{
Range = new ExcelRange(2, 4, 5, 9),
SheetName = "Sheet2"
};
DBDestination <ExcelDataSheet2> dest = new DBDestination <ExcelDataSheet2>(Connection, "ExcelDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(5, RowCountTask.Count(Connection, "ExcelDestination"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert1' AND Col3 = 5 AND Col4 = 1"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 IS NULL AND Col3 = 0 AND Col4 = 1.2"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 IS NULL AND Col3 = 7 AND Col4 = 1.234"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert4' AND Col3 = 8 AND Col4 = 1.2345"));
Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert5' AND Col3 = 9 AND Col4 = 2"));
}
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();
}
private void IdentityColumnAtTheEnd(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection, "Source4Cols", identityColumnIndex: 3);
source4Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "Destination4Cols", identityColumnIndex: 3);
//Act
DataFlowForIdentityColumn(connection);
//Assert
dest4Columns.AssertTestData();
}
public void IdentityAtTheEnd()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("CsvDestination4Columns", identityColumnIndex: 3);
DbDestination <string[]> dest = new DbDestination <string[]>(Connection, "CsvDestination4Columns");
//Act
CsvSource <string[]> source = new CsvSource <string[]>("res/CsvSource/ThreeColumnsNoId.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void JsonWithDecimalType()
{
//Arrange
FourColumnsTableFixture d4c = new FourColumnsTableFixture("JsonSource4ColsDynamic");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(Connection, "JsonSource4ColsDynamic");
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/FourColumns.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d4c.AssertTestData();
}
public void IdentityInTheMiddle()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("CSVDestination4Columns", identityColumnIndex: 2);
DBDestination dest = new DBDestination(Connection, "CSVDestination4Columns");
//Act
CSVSource source = new CSVSource("res/CSVSource/ThreeColumnsNoId.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
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 SourceMoreColumnsThanDestination(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection, "SourceDynamic4Cols");
source4Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DestinationDynamic2Cols");
//Act
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>(connection, "SourceDynamic4Cols");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(connection, "DestinationDynamic2Cols");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void UsingClassMapAndNoHeader()
{
//Arrange
FourColumnsTableFixture d4c = new FourColumnsTableFixture("CsvDestination4ColumnsClassMap");
DbDestination <MyExtendedRow> dest = new DbDestination <MyExtendedRow>("CsvDestination4ColumnsClassMap", Connection);
//Act
CsvSource <MyExtendedRow> source = new CsvSource <MyExtendedRow>("res/CsvSource/FourColumnsInvalidHeader.csv");
source.Configuration.RegisterClassMap <ExtendedClassMap>();
source.Configuration.HasHeaderRecord = false;
source.Configuration.ShouldSkipRecord = ShouldSkipRecordDelegate;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d4c.AssertTestData();
}
public void DifferentColumnsInView(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture s4c = new FourColumnsTableFixture(connection, "dbsource_extended");
s4c.InsertTestData();
CreateViewTask.CreateOrAlter(connection, "DbSourceViewExtended", $"SELECT {s4c.QB}Col2{s4c.QE}, {s4c.QB}Col4{s4c.QE} FROM dbsource_extended");
FourColumnsTableFixture d4c = new FourColumnsTableFixture(connection, "DbDestinationExtended", 1);
//Act
DbSource <MyExtendedRow> source = new DbSource <MyExtendedRow>("DbSourceViewExtended", connection);
DbDestination <MyExtendedRow> dest = new DbDestination <MyExtendedRow>("DbDestinationExtended", connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d4c.AssertTestData();
}
public void SimpleLookupFromDB(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "Source");
source2Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "Destination");
FourColumnsTableFixture lookup4Columns = new FourColumnsTableFixture(connection, "Lookup");
lookup4Columns.InsertTestData();
DBSource <MyInputDataRow> source = new DBSource <MyInputDataRow>(connection, "Source");
DBSource <MyLookupRow> lookupSource = new DBSource <MyLookupRow>(connection, "Lookup");
//Act
List <MyLookupRow> LookupTableData = new List <MyLookupRow>();
Lookup <MyInputDataRow, MyOutputDataRow, MyLookupRow> lookup = new Lookup <MyInputDataRow, MyOutputDataRow, MyLookupRow>(
row =>
{
MyOutputDataRow output = new MyOutputDataRow()
{
Col1 = row.Col1,
Col2 = row.Col2,
Col3 = LookupTableData.Where(ld => ld.Key == row.Col1).Select(ld => ld.LookupValue1).FirstOrDefault(),
Col4 = LookupTableData.Where(ld => ld.Key == row.Col1).Select(ld => ld.LookupValue2).FirstOrDefault(),
};
return(output);
}
, lookupSource
, LookupTableData
);
DBDestination <MyOutputDataRow> dest = new DBDestination <MyOutputDataRow>(connection, "Destination");
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void JsonPathListIntoDynamic()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("JsonSourceNestedDynamic4Cols");
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(SqlConnection, "JsonSourceNestedDynamic4Cols");
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedData4Cols.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Col2",
JsonPath = "Value",
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Object",
JsonPath = "Number[0]",
NewPropertyName = "Col4"
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Array",
JsonPath = "[1].Value",
NewPropertyName = "Col3"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
Assert.Equal(1, RowCountTask.Count(SqlConnection, "JsonSourceNestedDynamic4Cols", $"Col2 = 'Test2' AND Col3 = 4711 AND Col4='1.23'"));
}
public void SimpleLookupWithoutObject(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "Source");
source2Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "Destination", -1);
FourColumnsTableFixture lookup4Columns = new FourColumnsTableFixture(connection, "Lookup");
lookup4Columns.InsertTestData();
DBSource source = new DBSource(connection, "Source");
DBDestination dest = new DBDestination(connection, "Destination");
//Act
List <string[]> lookupList = new List <string[]>();
DBSource lookupSource = new DBSource(connection, "Lookup");
Lookup lookup = new Lookup(
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);
},
lookupSource,
lookupList
);
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void WithIdentityShift(IConnectionManager connection, int identityIndex)
{
//SQLite does not support Batch Insert on Non Nullable Identity Columns
if (connection.GetType() != typeof(SQLiteConnectionManager))
{
//Arrange
FourColumnsTableFixture destTable = new FourColumnsTableFixture(connection, "BulkInsert4Columns", identityIndex);
TableData data = new TableData(destTable.TableDefinition, 2);
object[] values = { "Test1", null, 1.2 };
data.Rows.Add(values);
object[] values2 = { "Test2", 4711, 1.23 };
data.Rows.Add(values2);
object[] values3 = { "Test3", 185, 1.234 };
data.Rows.Add(values3);
//Act
SqlTask.BulkInsert(connection, "Bulk insert demo data", data, "BulkInsert4Columns");
//Assert
destTable.AssertTestData();
}
}
public void Exceding20Columns()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("ExcelDestination");
//Act
ExcelSource <Excel21Cols> source = new ExcelSource <Excel21Cols>("res/Excel/MoreThan20Cols.xlsx")
{
Range = new ExcelRange(1, 2),
};
MemoryDestination <Excel21Cols> dest = new MemoryDestination <Excel21Cols>();
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.Collection <Excel21Cols>(dest.Data,
r => Assert.True(r.Col1 == 1 && r.Col2 == "Test1" && r.N == "N" && r.V == "V"),
r => Assert.True(r.Col1 == 2 && r.Col2 == "Test2" && r.N == "N" && r.V == "V"),
r => Assert.True(r.Col1 == 3 && r.Col2 == "Test3" && r.N == "N" && r.V == "V")
);
}
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>("SourceLookupSameType", connection);
DbSource <MyLookupRow> lookupSource = new DbSource <MyLookupRow>("LookupSameType", connection);
var lookup = new ETLBox.DataFlow.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>("DestinationLookupSameType", connection);
source.LinkTo(lookup);
lookup.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
