private static void RunMappingEngineNestedResultsBenchmark()
{
var mapping = JsonMappingBuilder.Root()
.Property("Version", "2.0")
.QueryWithNesting("Results",
@"select c.id as cid, c.sname,'2017-03-01' as LastUpdate, c.fname, c.BDAY, c.ADR_STREET, c.ADR_CITY, i.id as iid, i.inv_date, i.amount, o.article, o.id as oid, oi.item_name,oi.price, oi.id as oiid
from customer c
left join invoices i on c.id = i.customer_id
left join orders o on c.id = o.customer_id
left join order_items oi on o.id = oi.order_id
", //where amount>${context:min_amount:Int32}
// Id Column
"cid"
, cfg1 => cfg1
.Column("Id", "cid")
.Column("SName")
.Column("LastUpdate")
.Column("Birthday", "BDAY")
.Object("Address", cfg2 => cfg2
.Column("Street", "ADR_STREET")
.Column("City", "ADR_CITY")
)
.NestedResults("Invoices", "iid", cfg2 => cfg2
.Column("InvoiceId", "iid")
.Column("Inv_Date")
.Column("TotalAmount", "amount")
)
.NestedResults("Orders", "oid", cfg2 => cfg2
.Column("OrderId", "oid")
.Column("ArticleName", "article")
.NestedResults("Items", "oiid", cfg3 => cfg3
.Column("id", "oiid")
.Column("Item_Name")
.Column("Price")
)
)
)
//.Property("Last-Update", "2010-10-10")
.Result;
var sw = new Stopwatch();
sw.Start();
using (var engine = new MappingEngine(() => new NpgsqlConnection("Server=localhost;User Id=postgres;" +
"Password=admin;Database=test;"), mapping))
{
using (var ms = new FileStream("out-sql2json-nested.json", FileMode.Create))
{
var context = new Dictionary <string, object>();
// context.Add("min_amount", 10);
engine.ExecuteMapping(ms, context, false);
}
}
sw.Stop();
Console.WriteLine("RunMappingEngineNestedResultsBenchmark time elapsed: " + sw.ElapsedMilliseconds);
}
public void NestedSelectMappingTest()
{
QueryDefinition nestedSelectMapping = Define.QueryResult("select id as cid, sname,'2017-03-01' as LastUpdate, fname, BDAY, ADR_STREET, ADR_CITY from customer",
new
{
Id = Define.Column("cid"),
SName = Define.Column(),
Age = 22,
LastUpdate = Define.Column(),
Birthday = Define.Column("BDAY"),
Address = new
{
Street = Define.Column("ADR_STREET"),
City = Define.Column("ADR_CITY"),
},
Invoices = Define.QueryResult("select id as iid, inv_date, amount from invoices where customer_id=${column:cid} and amount > ${context:min_amount}",
new
{
InvoiceId = Define.Column("iid"),
Inv_Date = Define.Column(),
TotalAmount = Define.Column("amount"),
}
),
Orders = Define.QueryResult("select id as oid, article from orders where customer_id=${column:cid}",
new
{
OrderId = Define.Column("oid"),
ArticleName = Define.Column("article"),
}
)
});
var compiledMapping = nestedSelectMapping.Compile();
// Execute Mapping
DoInConnection((connection) =>
{
using (var engine = new MappingEngine(connection, compiledMapping))
{
using (var ms = new MemoryStream())
{
var context = new Dictionary <string, object>();
context.Add("min_amount", 100);
engine.ExecuteMapping(ms, context, false);
var json = System.Text.Encoding.UTF8.GetString(ms.ToArray());
Assert.Equal(nestedSelectGeneratedJson, json);
}
}
}
);
}
public void TestMappingBuilderObjectAsRoot()
{
var mapping = JsonMappingBuilder.Root()
.Property("Version", "2.0")
.QueryWithNesting("Results",
@"select c.id as cid, sname,'2017-03-01' as LastUpdate, fname, BDAY, ADR_STREET, ADR_CITY, i.id as iid, i.inv_date, i.amount, o.article, o.id as oid
from customer c
left join orders o on c.id = o.customer_id
left join invoices i on c.id = i.customer_id
where i.amount > ${context:min_amount}",
// Id Column
"cid"
, cfg1 => cfg1
.Column("Id", "cid")
.Column("SName")
.Column("LastUpdate")
.Column("Birthday", "BDAY")
.Object("Address", cfg2 => cfg2
.Column("Street", "ADR_STREET")
.Column("City", "ADR_CITY")
)
.NestedResults("Invoices", "iid", cfg2 => cfg2
.Column("InvoiceId", "iid")
.Column("Inv_Date")
.Column("TotalAmount", "amount")
)
.NestedResults("Orders", "oid", cfg2 => cfg2
.Column("OrderId", "oid")
.Column("ArticleName", "article")
)
)
//.Property("Last-Update", "2010-10-10")
.Result;
using (var engine = new MappingEngine(() => new SqliteConnection("Data Source=Test.db"), mapping))
{
using (var ms = new MemoryStream())
{
var context = new Dictionary <string, object>();
context.Add("min_amount", 100);
engine.ExecuteMapping(ms, context, false);
var json = System.Text.Encoding.UTF8.GetString(ms.ToArray());
Assert.Equal(nestedResultsGeneratedJson, json);
}
}
}
public void TestNestedResultsMappingAnonymousType()
{
TemplateDefinition nestedResultsMapping = Define.Template(
new
{
Version = "2.0",
Results =
Define.QueryWithNestedResults("cid",
@"select c.id as cid, sname,'2017-03-01' as LastUpdate, fname, BDAY, ADR_STREET, ADR_CITY, i.id as iid, i.inv_date, i.amount, o.article, o.id as oid
from customer c
left join orders o on c.id = o.customer_id
left join invoices i on c.id = i.customer_id
where i.amount > ${context:min_amount}"
,
new
{
Id = Define.Column("cid"),
SName = Define.Column(),
LastUpdate = Define.Column(),
Birthday = Define.Column("BDAY"),
Address = new
{
Street = Define.Column("ADR_STREET"),
City = Define.Column("ADR_CITY"),
},
Invoices = Define.NestedResults("iid",
new
{
InvoiceId = Define.Column("iid"),
Inv_Date = Define.Column(),
TotalAmount = Define.Column("amount"),
}
),
Orders = Define.NestedResults("oid",
new
{
OrderId = Define.Column("oid"),
ArticleName = Define.Column("article"),
}
)
}
)
});
// Compile Mapping
var compiledMapping = nestedResultsMapping.Compile();
// Execute Mapping
using (var engine = new MappingEngine(() => new SqliteConnection("Data Source=Test.db"), compiledMapping))
using (var ms = new MemoryStream())
{
var context = new Dictionary <string, object>();
context.Add("min_amount", 100);
engine.ExecuteMapping(ms, context, false);
var json = System.Text.Encoding.UTF8.GetString(ms.ToArray());
Assert.Equal(nestedResultsGeneratedJson, json);
}
}