for dotnet core 1.0, 1.1, 2.0 and .NET Framework 4.0 and higher
PM> Install-Package Sql2Json.Core
This library helps to convert sql query results (from a database) to JSON (including nested objects).
Some database engines lack the ability to query tables an return the results directly as JSON (e.g. Oracle). The mapping can be configured with an fluent interface or can be extracted out of an anonymous type defintion. The primary goal of this library is ease of use. With this library it is possible to skip the step to add an OR-Mapping Layer just to generate JSON. The Library supports all databases with an ado.net provider.
INSERT INTO customer (id, sname, fname, BDAY, ADR_STREET, ADR_CITY) VALUES(1, 'Meyers','Mike','1983-10-31 00:00:00.000', 'Street 1', 'Las Vegas');
INSERT INTO invoices (id, inv_date, amount, customer_id) VALUES(1, '2015-10-12 15:43:00.000', 500.2, 1);
INSERT INTO invoices (id, inv_date, amount, customer_id) VALUES(2, '2016-05-09 15:43:00.000', 155.2, 1);
INSERT INTO orders (id, ordernumber, articles, customer_id) VALUES (1,'O-1001','Book 1, Book 2',1);
INSERT INTO orders (id, ordernumber, articles, customer_id) VALUES (2,'O-1002','CD Burner, Usb stick 32Gb',1);var mapping = JsonMappingBuilder.RootQueryWithNesting(
@"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")
)
)
.Result;// Execute engine to create JSON
using (var engine = new MappingEngine(() => new SqliteConnection("Data Source=Test.db"), mapping))
using (var filestream = new FileStream("output.json", FileMode.CreateNew)) {
var context = new Dictionary<string, object>();
context.Add("min_amount", 100);
engine.ExecuteMapping(filestream, context);
}[{
"Id": 1,
"SName": "Meyers",
"FirstName": "Mike",
"LastUpdate": "2017-03-01",
"Birthday": "1983-10-31 00:00:00.000",
"Address": {
"Street": "Street 1",
"City": "Las Vegas"
},
"Invoices": [{
"InvoiceId": 1,
"Inv_Date": "2015-10-12 15:43:00.000",
"TotalAmount": 500.2
}, {
"InvoiceId": 2,
"Inv_Date": "2016-05-09 15:43:00.000",
"TotalAmount": 155.2
}
],
"Orders": [{
"OrderId": 1,
"ArticleSummary": "Book 1, Book 2"
}, {
"OrderId": 2,
"ArticleSummary": "CD Burner, Usb stick 32Gb"
}
]
}]As an alternative for the fluent mapping you can use the anonymous type mapping API. The following code creates the same JSON output.
var anonymousTypeMapping = Define.QueryWithNestedResults("cid",
@"select c.id as cid, c.sname, c.fname, c.BDAY, c.ADR_STREET, c.ADR_CITY, i.id as iid, i.inv_date, i.amount, o.articles, 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"
,
new
{
Id = Define.Column("cid"),
SurName = Define.Column("sname"),
FirstName = Define.Column("fname"),
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"),
ArticleSummary = Define.Column("articles"),
}
)
}
);
var mapping = anonymousTypeMapping.Compile();
using (var engine = new MappingEngine(() => new SqliteConnection("Data Source=Test.db"), mapping));
...It is possible to save the mapping defintion as external json for later use.
var mappingDef = Define.QueryWithNestedResults("id","select * from customer");
mappingDef.Compile().Save("mapping.json");Load a saved mapping from file and execute the mapping.
// Load mapping
var loadedCompiledMapping = MappingFileManager.Load(@"mapping.json");
// execute it
var engine = new MappingEngine(connection, loadedCompiledMapping);
...MIT License
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent