public void SourceWithDifferentNames()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSource2ColsDynamic");
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
r.Col1 = r.Column1;
r.Col2 = r.Column2;
return(r);
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>("JsonSource2ColsDynamic", Connection);
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/TwoColumnsDifferentNames.json", ResourceType.File);
var linkTo1 = source.LinkTo(trans);
var linkTo2 = linkTo1.source.LinkTo(dest);
using (linkTo1.link)
using (linkTo2.link)
{
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
}
public static async Task <HttpResponseMessage> Run(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("Received a json file to transform.");
string filename = Guid.NewGuid().ToString() + ".csv";
log.LogInformation("Temp file used to hold data: " + filename);
var jsonSource = new JsonSource()
{
CreateStreamReader = _ => new StreamReader(req.Body)
};
var csvFileDest = new CsvDestination(filename);
jsonSource.LinkTo(csvFileDest);
await Network.ExecuteAsync(jsonSource);
log.LogInformation("Successfully stored data in file - now returning the content as response.");
return(new HttpResponseMessage()
{
StatusCode = HttpStatusCode.OK,
Content = new StreamContent(new FileStream(filename, FileMode.Open))
});
}
public void JsonPathInEpandoObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSourceNestedDynamic");
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
r.Col1 = r.Column1;
return(r);
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(SqlConnection, "JsonSourceNestedDynamic");
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedData.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "Value",
NewPropertyName = "Col2"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(trans).LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void JsonFromWebService()
{
JsonSource source = new JsonSource("res/JsonSource/EmptyObject.json", ResourceType.File);
MemoryDestination dest = new MemoryDestination();
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.True(dest.Data.Count == 0);
}
public void ReadEmptyArray()
{
JsonSource source = new JsonSource("res/JsonSource/EmptyArray.json", ResourceType.File);
MemoryDestination dest = new MemoryDestination();
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.True(dest.Data.Count == 0);
}
/* 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 WithNestedArraysInside()
{
//Arrange
MemoryDestination dest = new MemoryDestination();
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
return(r);
});
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedArray.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$.[*].ArrayCol1",
NewPropertyName = "ArrayCol1"
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$.[*].ArrayCol2",
NewPropertyName = "ArrayCol2"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(trans).LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <ExpandoObject>(dest.Data,
row => {
dynamic r = row as ExpandoObject; var lac1 = r.ArrayCol1 as List <object>; var lac2 = r.ArrayCol2;
Assert.True(r.Column1 == 1 && lac1.Count == 2 && lac2.Count == 2);
},
row => {
dynamic r = row as ExpandoObject; var lac1 = r.ArrayCol1 as List <object>; var lac2 = r.ArrayCol2;
Assert.True(r.Column1 == 2 && lac1.Count == 2 && lac2.Count == 2);
},
row => {
dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 3 && r.ArrayCol1 == "E" && r.ArrayCol2 == "TestE");
}
);
}
public void JsonFromWebService()
{
//Arrange
MemoryDestination <Todo> dest = new MemoryDestination <Todo>(200);
//Act
JsonSource <Todo> source = new JsonSource <Todo>("https://jsonplaceholder.typicode.com/todos");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.All(dest.Data, item => Assert.True(item.Key > 0));
}
public void WithoutErrorLinking()
{
//Arrange
MemoryDestination <MySimpleRow> dest = new MemoryDestination <MySimpleRow>();
//Act
JsonSource <MySimpleRow> source = new JsonSource <MySimpleRow>("res/JsonSource/TwoColumnsErrorLinking.json", ResourceType.File);
//Assert
Assert.Throws <Newtonsoft.Json.JsonReaderException>(() =>
{
source.LinkTo(dest);
source.Execute();
dest.Wait();
});
}
public void ArrayInObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSourceArrayInObject");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>("JsonSourceArrayInObject", SqlConnection);
//Act
JsonSource <MySimpleRow> source = new JsonSource <MySimpleRow>("res/JsonSource/ArrayInObject.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.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 SimpleFlowWithStringArray()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSource2ColsNonGen");
DbDestination <string[]> dest = new DbDestination <string[]>(Connection, "JsonSource2ColsNonGen");
//Act
JsonSource <string[]> source = new JsonSource <string[]>("res/JsonSource/TwoColumnsStringArray.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void JsonPathInJsonPropertyAttribute()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSourceNested");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(SqlConnection, "JsonSourceNested");
//Act
JsonSource <MySimpleRow> source = new JsonSource <MySimpleRow>("res/JsonSource/NestedData.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void JsonFromFile()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSource2Cols");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(Connection, "JsonSource2Cols");
//Act
JsonSource <MySimpleRow> source = new JsonSource <MySimpleRow>("res/JsonSource/TwoColumns.json", ResourceType.File);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
private static SyncData ReadLastSyncKey()
{
try {
var syncsource = new JsonSource <SyncData>("LastSyncId.json");
syncsource.DisableLogging = true;
var memdest = new MemoryDestination <SyncData>();
memdest.DisableLogging = true;
syncsource.LinkTo(memdest);
Network.Execute(syncsource);
return(memdest.Data.First());
} catch {
}
return(new SyncData()
{
SyncId = -1
});
}
static void Main(string[] args)
{
StartWebServer();
JsonSource <Order> source = new JsonSource <Order>("https://www.etlbox.net/demo/api/orders", ResourceType.Http);
source.HttpClient = CreateDefaultHttpClient();
ColumnRename <Order> rename = new ColumnRename <Order>();
rename.RenameColumns = new[]
{
new RenameColumn()
{
CurrentName = "Id", NewName = "OrderId"
},
new RenameColumn()
{
CurrentName = "CustomerId", NewName = "CId"
},
new RenameColumn()
{
CurrentName = "Description", RemoveColumn = true
}
};
JsonDestination destination = new JsonDestination();
destination.ResourceType = ResourceType.Http;
destination.HttpClient = CreateDefaultHttpClient();
destination.HttpRequestMessage.Method = HttpMethod.Post;
destination.HasNextUri = (streamMetaData, row) => true;
destination.GetNextUri = (streamMetaData, row) =>
{
streamMetaData.HttpRequestMessage.Headers.Authorization = new AuthenticationHeaderValue("Bearer", "Some token");
return($"http://localhost:61456/post/{streamMetaData.ProgressCount}");
};
source.LinkTo(rename);
rename.LinkTo(destination);
Network.Execute(source);
WriteServerLog();
}
public void JsonFromWebService()
{
// Arrange
HttpClient httpClient = MoqJsonResponse(File.ReadAllText("res/JsonSource/Todos.json"));
//Arrange
MemoryDestination <Todo> dest = new MemoryDestination <Todo>();
//Act
JsonSource <Todo> source = new JsonSource <Todo>("http://test.com/");
source.HttpClient = httpClient;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.All(dest.Data, item => Assert.True(item.Key > 0));
Assert.Equal(5, dest.Data.Count);
}
public void PaginatedRequest()
{
//Arrange
MemoryDestination <Todo> dest = new MemoryDestination <Todo>();
int page = 1;
//Act
JsonSource <Todo> source = new JsonSource <Todo>();
source.GetNextUri = c => $"res/JsonSource/Todos_Page" + page++ + ".json";
source.HasNextUri = c => page <= 3;
source.ResourceType = ResourceType.File;
//source.HttpClient = httpClient;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.All(dest.Data, item => Assert.True(item.Key > 0));
Assert.Equal(5, dest.Data.Count);
}
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 JsonAPIRequestWithMetaData()
{
//Arrange
MemoryDestination dest = new MemoryDestination();
bool firstRequest = true;
//Act
JsonSource source = new JsonSource();
source.GetNextUri = meta =>
{
return(firstRequest ? $"res/JsonSource/JsonAPI.json" : $"res/JsonSource/JsonAPINext.json");
};
source.HasNextUri = meta =>
{
Assert.Equal(3, meta.ProgressCount);
if (firstRequest)
{
Assert.Equal(firstRequestUnparsedToBe, meta.UnparsedData, ignoreCase: true, ignoreLineEndingDifferences: true, ignoreWhiteSpaceDifferences: true);
}
else
{
Assert.Equal(secondRequestUnparsedToBe, meta.UnparsedData, ignoreCase: true, ignoreLineEndingDifferences: true, ignoreWhiteSpaceDifferences: true);
}
bool result = firstRequest;
firstRequest = false;
return(result);
};
source.ResourceType = ResourceType.File;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, dest.Data.Count);
}
public void WithMultipleTokens()
{
//Arrange
MemoryDestination dest = new MemoryDestination();
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedData.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$.Value",
NewPropertyName = "Value"
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$['Id']",
NewPropertyName = "Id"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <ExpandoObject>(dest.Data,
row => { dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 1 && r.Id == "A" && r.Value == "Test1"); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 2 && r.Id == "B" && r.Value == "Test2"); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 3 && r.Id == "C" && r.Value == "Test3"); }
);
}
public void WithObjectErrorLinking()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSourceErrorLinking");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(SqlConnection, "JsonSourceErrorLinking");
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
//Act
JsonSource <MySimpleRow> source = new JsonSource <MySimpleRow>("res/JsonSource/TwoColumnsErrorLinking.json",
ResourceType.File);
source.LinkTo(dest);
source.LinkErrorTo(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))
);
}