SQL Server CLR存储过程JSON参数 [英] SQL Server CLR Stored Procedure JSON Parameter

问题描述

我遇到了一种情况，我想将大量数据传递给存储过程以生成一些动态SQL。

I've come across a scenario where I want to pass a large amount of data to a Stored Procedure for generating some dynamic SQL.

我想要的数据通行证存储在我在ASP.NET MVC Web项目中使用的Json / C＃类对象中。

The data I want to pass is stored in this Json/C# class object that I use in my ASP.NET MVC web project.

[
 {
  "code":"ABC123",
  "Count": "12998",
  "Params":
  [
    {"name": "Recent", "value": "0-12m"},
    {"name": "Orders", "value": "1"}
  ]
 },
 {
  "code":"ABC124",
  "Count": "13998",
  "Params":
  [
   {"name": "Recent", "value": "0-12m"},
   {"name": "Orders", "value": "2"}
  ]
 },
 {
  "code":"ABC125",
  "Count": "7998",
  "Params":
  [
   {"name": "Recent", "value": "0-12m"},
   {"name": "Orders", "value": "3"}
  ]
 }
]
.....

然后使用此文本参数将其转换回JSON对象，我在动作过滤器

Then use this text parameter to convert back to a JSON object, I use this in an action filter to convert it to an object.

public class ObjectFilter : ActionFilterAttribute
{

    public string Param { get; set; }

    public Type RootType { get; set; }

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {

        if ((filterContext.HttpContext.Request.ContentType ?? string.Empty).Contains("application/json"))
        {

            object o =

            new DataContractJsonSerializer(RootType).ReadObject(filterContext.HttpContext.Request.InputStream);
            filterContext.HttpContext.Request.InputStream.Seek(0, SeekOrigin.Begin); // Rewind InputStream for other filters

            filterContext.ActionParameters[Param] = o;

        }

        else
        {

            var xmlRoot = XElement.Load(new StreamReader(filterContext.HttpContext.Request.InputStream,

            filterContext.HttpContext.Request.ContentEncoding));

            object o = new XmlSerializer(RootType).Deserialize(xmlRoot.CreateReader());

            filterContext.ActionParameters[Param] = o;

        }

    }

}

，然后在CLR存储过程中使用C＃等创建SQL语句，例如：

and then use C# etc in my CLR Stored Procedure to create a SQL statement such as:

UPDATE [Sample]
SET [Field] =
CASE
WHEN [Recent] = "0-12m" AND [Orders] = "1" THEN "ABC123"
WHEN [Recent] = "0-12m" AND [Orders] = "2" THEN "ABC124"
WHEN [Recent] = "0-12m" AND [Orders] = "3" THEN "ABC125"
...

这是否可能，并且有人曾经做过这样的事情吗？我已经看到了几篇有关使用XML参数的文章，但是没有一篇关于通过反序列化（？）json使用varchar参数的文章。

Is this possible and has anyone ever done anything like this. I've seen a few posts on using a XML parameter but none using a varchar parameter using de-serialized (?) json.

推荐答案

我试图使用我的代码来解析JSON字符串参数，但是所需的名称空间在SQL CLR项目中不可用，因此我切换到了已记录的Xml参数。

I've tried to use my code to parse a JSON string parameter but the required namespaces are not available in a SQL CLR project and I have therefore switched to the documented Xml parameter.

这篇关于SQL Server CLR存储过程JSON参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！