2023年6月21日发(作者：)
sqlserver之Forjsonpath应⽤在 server2016版本之后的新特性中，利⽤For Json path 可以将表格内容转成Json字符串,有⾃动转化和指定格式等转换。详细可以访问微软官⽅⽂档。在后台查询的语句需要拼接成字符串，显⽰到前端中。偶然看到sql server的新特性，参见了官⽅⽂档以及查询等，直接利⽤for json语句将表格转成固定格式的Json字符串。默认情况下，将整张表转换为数组，每⾏记录转为对象。应⽤背景在前端显⽰⽤户的订单则需根据⽤户的ID去查询订单情况。MyOrder表：orderdetail表：OrderState表：数据库查询语句1112select
D as'orderid',oney as 'ordersum',OrderStateName as 'orderstate',(select me as 'name', as 'img',unt as 'count',ice as 'price'from OrderDetails,meal where = and D=D for json path) as 'detail'from MyOrder,OrderState where tateID=tateID and ID=1 order by D Descfor json path
得到的Json2[ { "orderid": 31, "ordersum": 16, "orderstate": "未确认", "detail": [ { "name": "⽶饭", "img": "", "count": 1, "price": 2 }, { "name": "⾯⾷", "img": "", "count": 1, "price": 14 } ] }, ...... ]后台调⽤SQl 语句82936373839464748495using System;using c;using ent;using ;using ;using ;using nState;namespace { ///
} public String GetOrder(HttpContext context) { SqlParameter[] parameter = { // new SqlParameter("@memberid", 1), new SqlParameter("@memberid",32( n["userid"])) }; var jsonResult = new StringBuilder(); var reader = eReader(queryWithForJson, parameter); if (!s) { ("[]"); } else { while (()) { (ue(0).ToString()); } } return ng(); } public bool IsReusable { get { return false; } } }} string queryWithForJson = "select D as'orderid',oney as 'ordersum',OrderStateName as 'orderstate',(select ry注意Select语句的嵌套使⽤，可以在⼀个⾥在⽣成⼀个数据。因为For json path ⼀般是将表格的⼀⾏当作⼀个对象，整张表是⼀个对象数组。⽽想在整张表⽣成的数组⾥再⽣成数组需要利⽤上⾯的Select(select()as ‘’…)，其次还有⼀点要注意的是表之间的连接语句写再⾥⾯的select语句内。
2023年6月21日发(作者：)
sqlserver之Forjsonpath应⽤在 server2016版本之后的新特性中，利⽤For Json path 可以将表格内容转成Json字符串,有⾃动转化和指定格式等转换。详细可以访问微软官⽅⽂档。在后台查询的语句需要拼接成字符串，显⽰到前端中。偶然看到sql server的新特性，参见了官⽅⽂档以及查询等，直接利⽤for json语句将表格转成固定格式的Json字符串。默认情况下，将整张表转换为数组，每⾏记录转为对象。应⽤背景在前端显⽰⽤户的订单则需根据⽤户的ID去查询订单情况。MyOrder表：orderdetail表：OrderState表：数据库查询语句1112select
D as'orderid',oney as 'ordersum',OrderStateName as 'orderstate',(select me as 'name', as 'img',unt as 'count',ice as 'price'from OrderDetails,meal where = and D=D for json path) as 'detail'from MyOrder,OrderState where tateID=tateID and ID=1 order by D Descfor json path
得到的Json2[ { "orderid": 31, "ordersum": 16, "orderstate": "未确认", "detail": [ { "name": "⽶饭", "img": "", "count": 1, "price": 2 }, { "name": "⾯⾷", "img": "", "count": 1, "price": 14 } ] }, ...... ]后台调⽤SQl 语句82936373839464748495using System;using c;using ent;using ;using ;using ;using nState;namespace { ///
} public String GetOrder(HttpContext context) { SqlParameter[] parameter = { // new SqlParameter("@memberid", 1), new SqlParameter("@memberid",32( n["userid"])) }; var jsonResult = new StringBuilder(); var reader = eReader(queryWithForJson, parameter); if (!s) { ("[]"); } else { while (()) { (ue(0).ToString()); } } return ng(); } public bool IsReusable { get { return false; } } }} string queryWithForJson = "select D as'orderid',oney as 'ordersum',OrderStateName as 'orderstate',(select ry注意Select语句的嵌套使⽤，可以在⼀个⾥在⽣成⼀个数据。因为For json path ⼀般是将表格的⼀⾏当作⼀个对象，整张表是⼀个对象数组。⽽想在整张表⽣成的数组⾥再⽣成数组需要利⽤上⾯的Select(select()as ‘’…)，其次还有⼀点要注意的是表之间的连接语句写再⾥⾯的select语句内。
发布评论