sql server之在存储过程中利用OpenJson将Json字符串转化为表格

在Sql server2016的版本后，数据库增加了对Json格式的支持，详细信息可以参考微软官方文档链接

应用背景

在线订餐系统中，购物车的内容存储在浏览器缓存中,所以数据库关于订单的设计是订单表（订单ID,送货地址，用户ID,），订单明细表（订单ID,菜品ID,菜品数量，菜品价格,）。用户提交下单信息后，将订单信息和购物车缓存写入Json格式的Order字符串内，传到后台，在存储过程里利用OpenJson将JSon字符串转化为表格。以下基于sql server2017和.net4.0。

传到后台的Json字符串

	{
	    "CountPrice":57,
	    "orderInfo":[
	        {
	            "ID":1,
	            "MealName":"水煮肉片盖浇饭",
	            "Price":16,
	            "img":"/dishImages/4.jpg",
	            "MealDes":"美味的水煮肉片",
	            "MealTypeID":1,
	            "TypeID":1,
	            "TypeName":"盖浇饭",
	            "counter":2
	        },
	        {
	            "ID":2,
	            "MealName":"红烧肉盖浇饭",
	            "Price":20,
	            "img":"/dishImages/5.jpg",
	            "MealDes":"美味的红烧肉",
	            "MealTypeID":1,
	            "TypeID":1,
	            "TypeName":"盖浇饭",
	            "counter":1
	        },
	        {
	            "ID":4,
	            "MealName":"可乐",
	            "Price":5,
	            "img":"/dishImages/2.jpg",
	            "MealDes":"好喝可乐",
	            "MealTypeID":2,
	            "TypeID":2,
	            "TypeName":"饮料",
	            "counter":1
	        }
	    ],
	    "userInfo":{
	        "un":"zhulin",
	        "phone":"1827116754",
	        "address":"武汉"
	    }
	}

存储过程

这里参数2是是存储在Session里面的用户ID，参数二是Json字符串
注意要点，利用$来访问时，对象和数组访问方式不一样。

	create proc [dbo].[insert_order_orderdetails] @memberid int,@json NVARCHAR(MAX)
	as
	begin
	
	declare @total int,@phone varchar(50),@address varchar(50)
	
	--取出总价,取出手机号码,送货地址
	SELECT @total=total  FROM OPENJSON(@json)  
	with (total int '$.CountPrice')
	select @phone=phone,@address=DeliveryAddress from OpenJson(@json)
	with(phone varchar(50)'$.userInfo.phone',DeliveryAddress varchar(50) '$.userInfo.address')
	
	--插入订单表
	insert into MyOrder(MemberID,DeliveryAddress,TotalMoney,phone) values(@memberid,@address,@total,@phone)
	
	
	drop table temp--删除之前存在的临时表
	
	create table temp(OrderID int )
	----获得最大订单号并插入临时表temp
	insert into temp
	select Max(OrderID) from MyOrder
	
	--临时表和Json转化的表格进行笛卡尔积获得要插入到订单明表中的部分表
	insert into OrderDetails 
	select * from temp cross join OpenJson(@json,N'$.orderInfo')
	with 
	(MealID int '$.ID',--直接从$.orderInfo下面的目录开始访问
	MealPrice int '$.Price',
	MealCount int '$.counter'
	)as t 
	
	end

##后台
一般处理程序

	  public void ProcessRequest(HttpContext context)
	        {
	            context.Response.ContentType = "application/json";
	            //获取订单
	            String  orderStr = context.Request["order"];
	            SqlParameter[] parameter = {
	                new SqlParameter("@memberid", 1),
	                //Convert.ToInt32( context.Session["userid"])), 若在登陆状态
	                new SqlParameter("@json",orderStr)
	            };
	
	           SqlHelper.Proc("insert_order_orderdetails", parameter);
	
	
	        }