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