希望从 aspx 表单将 JSON 对象发送到 Sql Server

Question

我有一个由输入和选择字段组成的网络表单，另外还有一组动态字段，数量范围从 3 到 n。我使用 JavaScript 将表单输入/选择转换为 JSON 对象。我想要做的是将该 JSON 对象发送到 SQL 存储过程。将 JSON 对象从 JavaScript 传递到 SQL Server（Microsoft SQL Server Web 版（64 位）10.50.6560.0）的最直接方法是什么？使用 APS.net 和 C#

//FOR SUBMITTING THE FORM

$("#submitRecipe").click(function (e) {

    e.preventDefault();

    //VARIABLES DEFINED

    var IngredientListQuanity;

    var MeasurementSizeName;

    var IngredientName;

    //DEFINE JSON OBJECT FOR FORM DATA

    var json = {

        "recipe": [{

            "RecipeContributor": $("#RecipeContributor").val(),

            "RecipeDifficulty": $("#RecipeDifficulty").val(),

            "RecipeRating": $("#RecipeRating").val(),

            "RecipeInstructions": $("#RecipeInstructions").val(),

            "RecipeName": $("#RecipeName").val(),

            "RecipePrepTime": $("#RecipePrepTime").val(),

            "RecipeCookTime": $("#RecipeCookTime").val(),

            "ImageURL": $("#ImageURL").val(),

            "RecipeProtein": $("#RecipeProtein").val(),

            "RecipeFats": $("#RecipeFats").val(),

            "RecipeCarbs": $("#RecipeCarbs").val(),

            "RecipeFiber": $("#RecipeFiber").val(),

            "RecipeDescription": $("#RecipeDescription").val()

        }],

        "recipeIngredients": []

    };

    try {

        //FINDS ALL INPUTS WITHIN EACH ROW OF INGREDIENTS

        $('#ingredientList').find('.rowHeader').each(function () {

            $(this).find('input,select').each(function () {

                if ($(this).hasClass("amount")) {

                    IngredientListQuanity = $(this).val();

                } else if ($(this).hasClass("size")) {

                    MeasurementSizeName = $(this).val();

                } else if ($(this).hasClass("ingredient")) {

                    IngredientName = $(this).val();

                } else { console.log($(this).attr('class')) }

            });

   

Answer 1

除非您正在使用，否则您SQL-Server 2016没有对JSONin的本机支持MSSQL。因此，最好的方法是将数据转换为XML，然后将其传递给Stored Procedure，这在应用程序必须将大量数据传递到数据库的情况下最常用。方法 1将数据作为 xml 传递为了传递数据，您需要对类定义进行一些更改：using System.Xml.Serialization;[XmlRoot(ElementName = "Rootobject")] //defining a root element for the xmlpublic class Rootobject{&nbsp; &nbsp; [XmlElement(ElementName = "recipe")] //defining the name for the serialization&nbsp; &nbsp; public Recipe[] recipe { get; set; }&nbsp; &nbsp; [XmlElement(ElementName = "recipeIngredients")]//defining the name for the serialization&nbsp; &nbsp; public Recipeingredient[] recipeIngredients { get; set; }}在上面的代码中，我做了一些更改，我添加了一个XMLRoot来指定 XML 的根元素，并且XmlElement基本上为根元素中的子元素定义一个名称。现在为了在 asp.net中将数据从javascript（客户端）发送到c#（服务器端），我们必须创建一个static由WebMethod赋予属性的方法（简单来说，它是一种可以从javascript 或 jquery ), 如下：[WebMethod]public static string postRootObject(Rootobject roots){&nbsp; &nbsp;try&nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;var objectXML = serializeListtoXML<Rootobject>(roots); //converting the given object into an xml string&nbsp; &nbsp; &nbsp; &nbsp;//passing the data to stored procedure as&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;var statusSP = sendXMLToSqlServer("readDataFromXML", objectXML);&nbsp; &nbsp; &nbsp; &nbsp;return "yaaay it works";&nbsp; &nbsp;}&nbsp; &nbsp;catch (Exception ex)&nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp;throw ex;&nbsp; &nbsp;}}然后在方法中，sendXMLToSqlServer(<procedurename>,<xmldata>)我将生成的 xml 字符串传递给过程：public static bool sendXMLToSqlServer(string procedure,string xmlData){&nbsp; &nbsp; var status = false;&nbsp; &nbsp; try&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; using (SqlConnection con = new SqlConnection(@"<your connection string goes here>"))&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; con.Open();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var com = new SqlCommand();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; com.CommandText = procedure;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; com.Connection = con;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; com.Parameters.Add(new SqlParameter("@data",xmlData));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; com.CommandType = System.Data.CommandType.StoredProcedure;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //i am using the dataAdapter approach to get the data from the procedure you can write your own code to read the output from the procedure&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var ds = new DataSet();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var da = new SqlDataAdapter(com);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; da.Fill(ds);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (ds.Tables[0].Rows.Count > 0) //check if there is any record from&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; status = true;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; catch (Exception ex)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; throw ex;&nbsp; &nbsp; }&nbsp; &nbsp; return status;}请注意，上面给出的创建 SQL 连接和执行代码的方法永远不应在表示层项目中，它们应始终位于单独的库中，并且应始终在表示层项目中引用（因此关注点分离），这也可以应用用于业务逻辑代码。正如您在上面的代码中看到的那样，我有一个名为 as 的存储过程，readDataFromXML其中 1 个参数命名为 as @data，数据类型为 as xml。现在访问xml在SQL这里边是程序将如何看起来像：CREATE PROCEDURE readdatafromxml @data XML&nbsp;AS&nbsp;BEGIN&nbsp;&nbsp; SELECT r.rc.value('(RecipeContributor/text())[1]', 'varchar(100)')&nbsp; AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;contributor,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeDifficulty/text())[1]', 'varchar(100)')&nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;difficulty,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeRating/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rating,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeInstructions/text())[1]', 'varchar(100)') AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;instructions,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeName/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NAME,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipePrepTime/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;preptime,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeCookTime/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;cooktime,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(ImageURL/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;imgurl,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeProtein/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp; AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;protien,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeFats/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fats,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeCarbs/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp; &nbsp; AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;carbs&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeFiber/text())[1]', 'varchar(100)')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS fiber,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.rc.value('(RecipeDescription/text())[1]', 'varchar(100)')&nbsp; AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;reciepdescription&nbsp;&nbsp; INTO&nbsp; &nbsp;#tmprecipe&nbsp;&nbsp; FROM&nbsp; &nbsp;@data.nodes('/Rootobject/recipe') AS r(rc)&nbsp;&nbsp; SELECT r.ri.value('(IngredientListQuanity/text())[1]', 'varchar(100)') AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;quantity,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.ri.value('(MeasurementSizeName/text())[1]', 'varchar(100)')&nbsp; &nbsp;AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sizename,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;r.ri.value('(IngredientName/text())[1]', 'varchar(100)')&nbsp; &nbsp; &nbsp; &nbsp; AS&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ingredientname&nbsp;&nbsp; INTO&nbsp; &nbsp;#tmprecipeingrident&nbsp;&nbsp; FROM&nbsp; &nbsp;@data.nodes('/Rootobject/recipeIngredients') AS r(ri)&nbsp;&nbsp; --i am using a simple select just to check if there is some data in the temporary table you can change the code to match your need.&nbsp; SELECT *&nbsp;&nbsp; FROM&nbsp; &nbsp;#tmprecipe;&nbsp;&nbsp; SELECT *&nbsp;&nbsp; FROM&nbsp; &nbsp;#tmprecipeingrident;&nbsp;&nbsp; --clearing the memory by dropping the temporary tables&nbsp; DROP TABLE #tmprecipeingrident;&nbsp;&nbsp; DROP TABLE #tmprecipe;&nbsp;END&nbsp;在上面，stored procedure我将数据从 传递xml到临时表并从xmlas访问它：FROM @data.nodes('/Rootobject/recipe') AS r(rc)&nbsp;这是将给定对象转换为XML字符串的代码：public static string serializeListtoXML<T>(T obj){&nbsp; &nbsp; System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();&nbsp; &nbsp; System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(obj.GetType());&nbsp; &nbsp; using (System.IO.MemoryStream ms = new System.IO.MemoryStream())&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; serializer.Serialize(ms, obj);&nbsp; &nbsp; &nbsp; &nbsp; ms.Position = 0;&nbsp; &nbsp; &nbsp; &nbsp; xmlDoc.Load(ms);&nbsp; &nbsp; &nbsp; &nbsp; return xmlDoc.InnerXml;&nbsp; &nbsp; }}在上面的代码中，我创建了一个XMLDocument将序列化对象传递到该xml文档，然后返回InnerXML该文档的对象。方法 2将数据作为JSON传递（仅在 SQL-Server 2016 及更高版本中使用）为了传递JSON给SQL-Server我们必须对上面的代码做一些改变：xml从Rootobject类中删除属性，然后将方法中的代码更改postRootObject(Rootobject roots)为将数据序列化为JSON字符串，如下所示：[WebMethod]public static string postRootObject(Rootobject roots){&nbsp; &nbsp; try&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; var objectJsonString = JsonConvert.SerializeObject(roots);&nbsp; &nbsp; &nbsp; &nbsp; //pass to stored procedure as&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; var statusSP = sendJsonToSqlServer("readDataFromJSON", objectJsonString);&nbsp; &nbsp; &nbsp; &nbsp; return "yaay it works";&nbsp; &nbsp; }&nbsp; &nbsp; catch (Exception ex)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; throw ex;&nbsp; &nbsp; }}在该方法中sendJsonToSqlServer把一切都因为相同的，因为它是sendXMLToSqlServer因为数据是我们派出会有string这情况下是相同的对象XML。现在唯一的改变，你需要在存储过程中，这将是为（使用JSON工作SQL Server 2016）：create procedure readDataFromJSON @data nvarchar(4000)asbegin&nbsp; select&nbsp;&nbsp; &nbsp;contributor,difficulty,rating,instructions,[name],preptime,cooktime,imgurl,protien,fats,carbs,fiber,reciepdescription&nbsp; &nbsp;into #tmprecipe&nbsp; &nbsp;from OPENJSON(@data,'$.Rootobject.recipe')&nbsp; &nbsp;WITH (&nbsp; &nbsp; &nbsp; RecipeContributor varchar(100) '$.RecipeContributor' as contributor,&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; RecipeDifficulty varchar(100) '$.RecipeDifficulty' as difficulty,&nbsp;&nbsp; &nbsp; &nbsp; RecipeRating varchar(100) '$.RecipeRating' as rating,&nbsp;&nbsp; &nbsp; &nbsp; RecipeInstructions varchar(100) '$.RecipeInstructions' as instructions,&nbsp;&nbsp; &nbsp; &nbsp; RecipeName varchar(100) '$.RecipeName' as name,&nbsp;&nbsp; &nbsp; &nbsp; RecipePrepTime varchar(100) '$.RecipePrepTime' as preptime,&nbsp;&nbsp; &nbsp; &nbsp; RecipeCookTime varchar(100) '$.RecipeCookTime' as cooktime,&nbsp;&nbsp; &nbsp; &nbsp; ImageURL varchar(100) '$.ImageURL' as imgurl,&nbsp;&nbsp; &nbsp; &nbsp; RecipeProtein varchar(100) '$.RecipeProtein' as protien,&nbsp;&nbsp; &nbsp; &nbsp; RecipeFats varchar(100) '$.RecipeFats' as fats,&nbsp;&nbsp; &nbsp; &nbsp; RecipeCarbs varchar(100) '$.RecipeCarbs' as carbs,&nbsp;&nbsp; &nbsp; &nbsp; RecipeFiber varchar(100) '$.RecipeFiber' as fibre,&nbsp;&nbsp; &nbsp; &nbsp; RecipeDescription varchar(100) '$.RecipeDescription' as reciepdescription,&nbsp;&nbsp; &nbsp;);&nbsp; &nbsp;select&nbsp;&nbsp; &nbsp; &nbsp;quantity,sizename,ingredientname&nbsp; &nbsp; &nbsp;into #tmprecipeingrident&nbsp; &nbsp; &nbsp;from OPENJSON(@data,'$.Rootobject.recipeIngredients')&nbsp; &nbsp; &nbsp;WITH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IngredientListQuanity varchar(100) '$.IngredientListQuanity' as quantity,&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MeasurementSizeName varchar(100) '$.MeasurementSizeName'&nbsp; as sizename,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IngredientName varchar(100) '$.IngredientName'&nbsp; as ingredientname&nbsp; &nbsp; &nbsp;);&nbsp; &nbsp; &nbsp;select * from #tmprecipe;&nbsp; &nbsp; &nbsp;select * from #tmprecipeingrident;&nbsp; &nbsp; &nbsp;drop table #tmprecipeingrident;&nbsp; &nbsp; &nbsp;drop table #tmprecipe;end注意：上面的代码没有经过测试，因为我没有 SQL-Server 2016。但是根据微软提供的文档，它应该可以进行一些调整（如果这不起作用）。