C#/SQL - 过程中的 SqlDbType.Xml 有什么问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/574928/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C#/SQL - What's wrong with SqlDbType.Xml in procedures?
提问by Jacob
I've asked few people why using xml as a parameter in stored procedure doesn't work and everyone said , that's just the way it is. I can't belive that.
我问过很少人为什么在存储过程中使用 xml 作为参数不起作用，每个人都说，就是这样。我不能相信。
command.Parameters.Add("@xmldoc", SqlDbType.Xml);
That's where compiler returns error and I can't use NVarChar beacouse it's limiteed to 4k sings. XML would be perfect as it can be 2gigs big.
这就是编译器返回错误的地方，我不能使用 NVarChar，因为它仅限于 4k sings。XML 将是完美的，因为它可以是 2gigs 大。
How come other SqlDbTypes work well and this one retruns error ?
为什么其他 SqlDbTypes 运行良好，而这个重新运行错误？
*
*
Error: Specified argument was out of the range of valid values. Parameter name: @xmldoc: Invalid SqlDbType enumeration value: 25.
错误：指定的参数超出了有效值的范围。参数名称：@xmldoc：无效的 SqlDbType 枚举值：25。
*
*
采纳答案by Gregory A Beamer
It does work. You will have to set up the Value as SqlXml and not a string, but it can be done. Imagine this table:
它确实有效。您必须将 Value 设置为 SqlXml 而不是字符串，但可以这样做。想象一下这张表：
CREATE TABLE XmlTest
(
[XmlTestId] [int] identity(1,1) primary key,
[XmlText] [xml] NOT NULL
)
And the sproc:
和 sproc：
CREATE PROCEDURE XmlTest_Insert
(
@XmlText xml
)
AS
INSERT INTO XmlTest (XmlText)
VALUES (@XmlText)
Now picture a console application that looks like this:
现在描绘一个如下所示的控制台应用程序：
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using System.Xml;
namespace TestConsole
{
class Program
{
static void Main(string[] args)
{
string xmlDoc = "<root><el1>Nothing</el1></root>";
string connString = "server=(local);database=IntroDB;UID=sa;PWD=pwd";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("XmlTest_Insert", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@XmlText", SqlDbType.Xml);
param.Value = new SqlXml(new XmlTextReader(xmlDoc
, XmlNodeType.Document, null));
cmd.Parameters.Add(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Dispose();
}
}
}
Bingo!
答对了！
This was done in Visual Studio 2008 (.NET 3.5), but I am fairly sure it should work in Visual Studio 2005 (2.0 Framework), as well.
这是在 Visual Studio 2008 (.NET 3.5) 中完成的，但我相当确定它也应该在 Visual Studio 2005 (2.0 Framework) 中工作。
回答by REA_ANDREW
Instead of using the Add Method, try using AddWithValue where you do not need to specify the type just the name and the value. Unless you are using a different direction to input?
不要使用 Add 方法，而是尝试使用 AddWithValue，其中您不需要仅指定名称和值的类型。除非你使用不同的方向来输入？
回答by Jaydeep Shil
//Create The StringWriter Object
var stringWriter = new System.IO.StringWriter();
//Create XmlSerializer Object for the serialization,
RequestUpdateRBCustomerExternal is the Class of which type having all the values
var serializer = new XmlSerializer(typeof(RequestUpdateRBCustomerExternal));
//request is of type RequestUpdateRBCustomerExternal
serializer.Serialize(stringWriter, request);
SqlXml xml = new SqlXml(new XmlTextReader(stringWriter.ToString(), XmlNodeType.Document, null));
cmd.CommandText ="insert into SAPDataTracking values('"+DateTime.Now+"','"+xml.Value+"')";
