我有一個(gè)由輸入和選擇字段組成的網(wǎng)絡(luò)表單，另外還有一組動(dòng)態(tài)字段，數(shù)量范圍從 3 到 n。我使用 JavaScript 將表單輸入/選擇轉(zhuǎn)換為 JSON 對(duì)象。我想要做的是將該 JSON 對(duì)象發(fā)送到 SQL 存儲(chǔ)過(guò)程。將 JSON 對(duì)象從 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')) } });
1 回答
鳳凰求蠱
TA貢獻(xiàn)1825條經(jīng)驗(yàn) 獲得超4個(gè)贊
除非您正在使用，否則您SQL-Server 2016沒有對(duì)JSONin的本機(jī)支持MSSQL。
因此，最好的方法是將數(shù)據(jù)轉(zhuǎn)換為XML，然后將其傳遞給Stored Procedure，這在應(yīng)用程序必須將大量數(shù)據(jù)傳遞到數(shù)據(jù)庫(kù)的情況下最常用。
方法 1將數(shù)據(jù)作為 xml 傳遞
為了傳遞數(shù)據(jù)，您需要對(duì)類定義進(jìn)行一些更改：
using System.Xml.Serialization;
[XmlRoot(ElementName = "Rootobject")] //defining a root element for the xml
public class Rootobject
{
[XmlElement(ElementName = "recipe")] //defining the name for the serialization
public Recipe[] recipe { get; set; }
[XmlElement(ElementName = "recipeIngredients")]//defining the name for the serialization
public Recipeingredient[] recipeIngredients { get; set; }
}
在上面的代碼中，我做了一些更改，我添加了一個(gè)XMLRoot來(lái)指定 XML 的根元素，并且XmlElement基本上為根元素中的子元素定義一個(gè)名稱。
現(xiàn)在為了在 asp.net中將數(shù)據(jù)從javascript（客戶端）發(fā)送到c#（服務(wù)器端），我們必須創(chuàng)建一個(gè)static由WebMethod賦予屬性的方法（簡(jiǎn)單來(lái)說(shuō)，它是一種可以從javascript 或 jquery ), 如下：
[WebMethod]
public static string postRootObject(Rootobject roots)
{
try
{
var objectXML = serializeListtoXML<Rootobject>(roots); //converting the given object into an xml string
//passing the data to stored procedure as
var statusSP = sendXMLToSqlServer("readDataFromXML", objectXML);
return "yaaay it works";
}
catch (Exception ex)
{
throw ex;
}
}
然后在方法中，sendXMLToSqlServer(<procedurename>,<xmldata>)我將生成的 xml 字符串傳遞給過(guò)程：
public static bool sendXMLToSqlServer(string procedure,string xmlData)
{
var status = false;
try
{
using (SqlConnection con = new SqlConnection(@"<your connection string goes here>"))
{
con.Open();
var com = new SqlCommand();
com.CommandText = procedure;
com.Connection = con;
com.Parameters.Add(new SqlParameter("@data",xmlData));
com.CommandType = System.Data.CommandType.StoredProcedure;
//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
var ds = new DataSet();
var da = new SqlDataAdapter(com);
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0) //check if there is any record from
status = true;
}
}
catch (Exception ex)
{
throw ex;
}
return status;
}
請(qǐng)注意，上面給出的創(chuàng)建 SQL 連接和執(zhí)行代碼的方法永遠(yuǎn)不應(yīng)在表示層項(xiàng)目中，它們應(yīng)始終位于單獨(dú)的庫(kù)中，并且應(yīng)始終在表示層項(xiàng)目中引用（因此關(guān)注點(diǎn)分離），這也可以應(yīng)用用于業(yè)務(wù)邏輯代碼。
正如您在上面的代碼中看到的那樣，我有一個(gè)名為 as 的存儲(chǔ)過(guò)程，readDataFromXML其中 1 個(gè)參數(shù)命名為 as @data，數(shù)據(jù)類型為 as xml。
現(xiàn)在訪問xml在SQL這里邊是程序?qū)⑷绾慰雌饋?lái)像：
CREATE PROCEDURE readdatafromxml @data XML
AS
BEGIN
SELECT r.rc.value('(RecipeContributor/text())[1]', 'varchar(100)') AS
contributor,
r.rc.value('(RecipeDifficulty/text())[1]', 'varchar(100)') AS
difficulty,
r.rc.value('(RecipeRating/text())[1]', 'varchar(100)') AS
rating,
r.rc.value('(RecipeInstructions/text())[1]', 'varchar(100)') AS
instructions,
r.rc.value('(RecipeName/text())[1]', 'varchar(100)') AS
NAME,
r.rc.value('(RecipePrepTime/text())[1]', 'varchar(100)') AS
preptime,
r.rc.value('(RecipeCookTime/text())[1]', 'varchar(100)') AS
cooktime,
r.rc.value('(ImageURL/text())[1]', 'varchar(100)') AS
imgurl,
r.rc.value('(RecipeProtein/text())[1]', 'varchar(100)') AS
protien,
r.rc.value('(RecipeFats/text())[1]', 'varchar(100)') AS
fats,
r.rc.value('(RecipeCarbs/text())[1]', 'varchar(100)') AS
carbs
,
r.rc.value('(RecipeFiber/text())[1]', 'varchar(100)')
AS fiber,
r.rc.value('(RecipeDescription/text())[1]', 'varchar(100)') AS
reciepdescription
INTO #tmprecipe
FROM @data.nodes('/Rootobject/recipe') AS r(rc)
SELECT r.ri.value('(IngredientListQuanity/text())[1]', 'varchar(100)') AS
quantity,
r.ri.value('(MeasurementSizeName/text())[1]', 'varchar(100)') AS
sizename,
r.ri.value('(IngredientName/text())[1]', 'varchar(100)') AS
ingredientname
INTO #tmprecipeingrident
FROM @data.nodes('/Rootobject/recipeIngredients') AS r(ri)
--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.
SELECT *
FROM #tmprecipe;
SELECT *
FROM #tmprecipeingrident;
--clearing the memory by dropping the temporary tables
DROP TABLE #tmprecipeingrident;
DROP TABLE #tmprecipe;
END
在上面，stored procedure我將數(shù)據(jù)從 傳遞xml到臨時(shí)表并從xmlas訪問它：
FROM @data.nodes('/Rootobject/recipe') AS r(rc)
這是將給定對(duì)象轉(zhuǎn)換為XML字符串的代碼：
public static string serializeListtoXML<T>(T obj)
{
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(obj.GetType());
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
serializer.Serialize(ms, obj);
ms.Position = 0;
xmlDoc.Load(ms);
return xmlDoc.InnerXml;
}
}
在上面的代碼中，我創(chuàng)建了一個(gè)XMLDocument將序列化對(duì)象傳遞到該xml文檔，然后返回InnerXML該文檔的對(duì)象。
方法 2將數(shù)據(jù)作為JSON傳遞（僅在 SQL-Server 2016 及更高版本中使用）
為了傳遞JSON給SQL-Server我們必須對(duì)上面的代碼做一些改變：
xml從Rootobject類中刪除屬性，然后將方法中的代碼更改postRootObject(Rootobject roots)為將數(shù)據(jù)序列化為JSON字符串，如下所示：
[WebMethod]
public static string postRootObject(Rootobject roots)
{
try
{
var objectJsonString = JsonConvert.SerializeObject(roots);
//pass to stored procedure as
var statusSP = sendJsonToSqlServer("readDataFromJSON", objectJsonString);
return "yaay it works";
}
catch (Exception ex)
{
throw ex;
}
}
在該方法中sendJsonToSqlServer把一切都因?yàn)橄嗤?，因?yàn)樗莝endXMLToSqlServer因?yàn)閿?shù)據(jù)是我們派出會(huì)有string這情況下是相同的對(duì)象XML。現(xiàn)在唯一的改變，你需要在存儲(chǔ)過(guò)程中，這將是為（使用JSON工作SQL Server 2016）：
create procedure readDataFromJSON @data nvarchar(4000)
as
begin
select
contributor,difficulty,rating,instructions,[name],preptime,cooktime,imgurl,protien,fats,carbs,fiber,reciepdescription
into #tmprecipe
from OPENJSON(@data,'$.Rootobject.recipe')
WITH (
RecipeContributor varchar(100) '$.RecipeContributor' as contributor,
RecipeDifficulty varchar(100) '$.RecipeDifficulty' as difficulty,
RecipeRating varchar(100) '$.RecipeRating' as rating,
RecipeInstructions varchar(100) '$.RecipeInstructions' as instructions,
RecipeName varchar(100) '$.RecipeName' as name,
RecipePrepTime varchar(100) '$.RecipePrepTime' as preptime,
RecipeCookTime varchar(100) '$.RecipeCookTime' as cooktime,
ImageURL varchar(100) '$.ImageURL' as imgurl,
RecipeProtein varchar(100) '$.RecipeProtein' as protien,
RecipeFats varchar(100) '$.RecipeFats' as fats,
RecipeCarbs varchar(100) '$.RecipeCarbs' as carbs,
RecipeFiber varchar(100) '$.RecipeFiber' as fibre,
RecipeDescription varchar(100) '$.RecipeDescription' as reciepdescription,
);
select
quantity,sizename,ingredientname
into #tmprecipeingrident
from OPENJSON(@data,'$.Rootobject.recipeIngredients')
WITH (
IngredientListQuanity varchar(100) '$.IngredientListQuanity' as quantity,
MeasurementSizeName varchar(100) '$.MeasurementSizeName' as sizename,
IngredientName varchar(100) '$.IngredientName' as ingredientname
);
select * from #tmprecipe;
select * from #tmprecipeingrident;
drop table #tmprecipeingrident;
drop table #tmprecipe;
end
注意：上面的代碼沒有經(jīng)過(guò)測(cè)試，因?yàn)槲覜]有 SQL-Server 2016。但是根據(jù)微軟提供的文檔，它應(yīng)該可以進(jìn)行一些調(diào)整（如果這不起作用）。
- 1 回答
- 0 關(guān)注
- 193 瀏覽
添加回答
舉報(bào)
0/150
提交
取消