将XML作为字符串插入SQL列

I have a C# website that I need the ability to insert XML into a database column directly from a form field just as a string. I don't want to extract values from the XML, I simply want to insert the XML as-is as a string.

我有一个C#网站,我需要能够直接从表单字段将XML插入到数据库列中,就像字符串一样。我不想从XML中提取值,我只想将XML作为字符串插入。

This currently causes my code to choke.

这导致我的代码窒息。

Code:

objParameter.Add(new SqlParameter("@Description", txtDesc.Text.Trim()));

Text from form field:

表单字段中的文本:

<note>
<to>Me</to>
<from>You</from>
<heading>Reminder</heading>
<body>Don't forget to buy milk.</body>
</note>

4 个解决方案

#1

More than likely there is a xml data type in the database you are using(sql server, mysql, postgres, oracle). I realize you posted you did not want to extract data from the xml, but down the road it might be interesting to be able to extract data at the database layer. Just a thought.

很可能你正在使用的数据库中有一个xml数据类型(sql server,mysql,postgres,oracle)。我意识到你发布了你不想从xml中提取数据,但是在路上可能有趣的是能够在数据库层提取数据。只是一个想法。

#2

How i understand you need to insert xml data as string in database column. I write following sample:

我的理解你需要在数据库列中将xml数据作为字符串插入。我写下面的样本:

// xmlData from form
string xmlData = "<note> <to>Me</to> <from>You</from> <heading>Reminder</heading> <body>Don\'t forget to buy milk.</body> </note>";

// this replace is necessary because we need two apostrophe ('') to avoid sql syntax error.
// Also is necessary to filter xml data for SQL Injection
string filteredXmlData = xmlData.Replace("'", "''"); 

string CS= @"Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\TestProjects\Database.mdf;Integrated Security=True";
SqlConnection sqlCon= new SqlConnection(CS);
SqlCommand cmd = new SqlCommand("INSERT INTO MyTable (MyCol) values (@paramValue)", sqlCon);
cmd.Parameters.Add(new SqlParameter("@paramValue", filteredXmlData));

sqlCon.Open();
int rowsAffected = cmd.ExecuteNonQuery();
sqlCon.Close();

#3

Here is my guess as to what is going on. It could be that Asp.Net Request Validation is not allowing you to post the xml because it sees it as dangerous. You can disable validation but you shouldn't. You could do a couple of things, probably html encoding is your best option:

这是我猜测发生了什么。可能是Asp.Net请求验证不允许您发布xml,因为它认为它是危险的。您可以禁用验证,但不应该。你可以做几件事,可能html编码是你最好的选择:

string theXml = "";
if(txtDesc.Text != null)
{
    theXml = System.Net.WebUtility.HtmlEncode(txtDesc.Text.Trim());
    objParameter.Add(new SqlParameter("@Description", theXml));
}

#4

You first have to add the parameter to a command before setting the value like code below

首先必须在设置值之前将参数添加到命令中,如下面的代码所示

SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add("@Description", typeof(string));

#1

#2