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