C# 将参数作为 Xml 传递给存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/748087/
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
Passing Parameters as Xml to a Stored Procedure
提问by Rob Stevenson-Leggett
I've got a requirement to pass parameters as Xml to my stored procedures.
我需要将参数作为 Xml 传递给我的存储过程。
I have a WCF service in the middle tier that makes calls to my data layer which in turn forwards the request to the appropriate stored procedure.
我在中间层有一个 WCF 服务，它调用我的数据层，然后将请求转发到适当的存储过程。
The design is that the WCF service is responsible for building the Xml to pass to the Repository.
设计是WCF服务负责构建Xml传递给Repository。
I'm just wondering whether to keep control of what parameters are contained within the Xml in the middle tier or use a Dictionary built up by the client which I then convert to Xml in the middle tier?
我只是想知道是否要控制中间层的 Xml 中包含的参数，还是使用客户端构建的字典，然后我在中间层将其转换为 Xml？
At the moment I've gone for the latter - for example:
目前我已经选择了后者 - 例如：
public TestQueryResponseMessage TestQuery(TestQueryRequestMessage message)
{
var result = Repository.ExecuteQuery("TestQuery", ParamsToXml(message.Body.Params));
return new TestQueryResponseMessage
{
Body = new TestQueryResponse
{
TopicItems = result;
}
}
}
private string ParamsToXml(Dictionary<string, string> nvc)
{
//TODO: Refactor
StringBuilder sb = new StringBuilder();
sb.Append("<params>");
foreach (KeyValuePair<string, string> param in nvc)
{
sb.Append("<param>");
sb.Append("<" + param.Key + ">");
sb.Append(param.Value);
sb.Append("</" + param.Key + ">");
sb.Append("</param>");
}
sb.Append("</params>");
return sb.ToString();
}
However I might need to do it the first way. E.g.
但是，我可能需要采用第一种方式。例如
public TestQueryResponseMessage TestQuery(TestQueryRequestMessage message)
{
string xml = string.Format("<params><TestParameter>{0}</TestParameter></params>",message.Body.TestParameter)
var result = Repository.ExecuteQuery("TestQuery", xml);
return new TestQueryResponseMessage
{
Body = new TestQueryResponse
{
TopicItems = result;
}
}
}
What does the hivemind recommend?
hivemind 推荐什么？
采纳答案by Marc Gravell
If you must use xml; then rather than passing around a dictionary, I'd use a class that represents that data, and use XmlSerializerto fetch it as xml:
如果必须使用xml；然后，而不是传递字典，我会使用一个表示该数据的类，并使用XmlSerializer它作为 xml 获取它：
[Serializable, XmlRoot("args")]
public class SomeArgs {
[XmlElement("foo")] public string Foo { get; set; }
[XmlAttribute("bar")] public int Bar { get; set; }
}
...
SomeArgs args = new SomeArgs { Foo = "abc", Bar = 123 };
XmlSerializer ser = new XmlSerializer(typeof(SomeArgs));
StringWriter sw = new StringWriter();
ser.Serialize(sw, args);
string xml = sw.ToString();
This makes it much easier to manage which arguments apply to which queries, in an object-oriented way. It also means you don't have to do your own xml escaping...
这使得以面向对象的方式管理哪些参数适用于哪些查询变得更加容易。这也意味着您不必自己进行 xml 转义...
回答by Gulzar Nazim
I would put the xml construction code inside the domain object. That way, you can just call obj.GetXML() from Web Service or data layer.
我会将 xml 构造代码放在域对象中。这样，您只需从 Web 服务或数据层调用 obj.GetXML()。
回答by Bob The Janitor
You could just use an object Serialization class like this
您可以只使用这样的对象序列化类
public class Serialization
{
/// <summary>
/// Serializes the object.
/// </summary>
/// <param name="myObject">My object.</param>
/// <returns></returns>
public static XmlDocument SerializeObject(Object myObject)
{
XmlDocument XmlObject = new XmlDocument();
String XmlizedString = string.Empty;
try
{
MemoryStream memoryStream = new MemoryStream();
XmlSerializer xs = new XmlSerializer(myObject.GetType());
XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
xs.Serialize(xmlTextWriter, myObject);
memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
XmlizedString = UTF8ByteArrayToString(memoryStream.ToArray());
}
catch (Exception e)
{
System.Console.WriteLine(e);
}
XmlObject.LoadXml(XmlizedString);
return XmlObject;
}
/// <summary>
/// Deserializes the object.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="XmlizedString">The p xmlized string.</param>
/// <returns></returns>
public static T DeserializeObject<T>(String XmlizedString)
{
XmlSerializer xs = new XmlSerializer(typeof(T));
MemoryStream memoryStream = new MemoryStream(StringToUTF8ByteArray(XmlizedString));
//XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
Object myObject = xs.Deserialize(memoryStream);
return (T)myObject;
}
/// <summary>
/// To convert a Byte Array of Unicode values (UTF-8 encoded) to a complete String.
/// </summary>
/// <param name="characters">Unicode Byte Array to be converted to String</param>
/// <returns>String converted from Unicode Byte Array</returns>
private static String UTF8ByteArrayToString(Byte[] characters)
{
UTF8Encoding encoding = new UTF8Encoding();
String constructedString = encoding.GetString(characters);
return (constructedString);
}
/// <summary>
/// Converts the String to UTF8 Byte array and is used in De serialization
/// </summary>
/// <param name="pXmlString"></param>
/// <returns></returns>
private static Byte[] StringToUTF8ByteArray(String pXmlString)
{
UTF8Encoding encoding = new UTF8Encoding();
Byte[] byteArray = encoding.GetBytes(pXmlString);
return byteArray;
}
}
then you don't have to build the XML by hand, plus you can use this with any item to transform it using XSLT
那么您就不必手动构建 XML，而且您可以将它与任何项目一起使用以使用 XSLT 对其进行转换
回答by DBAndrew
Once you use Bob The Janitor's solution and you have your XML.
一旦您使用了 Bob The Janitor 的解决方案并且您拥有了 XML。
Create your stored procedure with a XML parameter. Then depending on how much XML you have and what your doing with it you can use Xquery or OpenXML to shred the XML document. Extract the data and perform the right action. This example is basic and pseudocode like but you should get the idea.
使用 XML 参数创建存储过程。然后，根据您拥有多少 XML 以及您使用它做什么，您可以使用 Xquery 或 OpenXML 来分解 XML 文档。提取数据并执行正确的操作。这个例子是基本的和伪代码一样，但你应该明白这个想法。
CREATE PROCEDURE [usp_Customer_INS_By_XML]
@Customer_XML XML
AS
BEGIN
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @Customer_XML
--OPEN XML example of inserting multiple customers into a Table.
INSERT INTO CUSTOMER
(
First_Name
Middle_Name
Last_Name
)
SELECT
First_Name
,Middle_Name
,Last_Name
FROM OPENXML (@xmldoc, '/ArrayOfCustomers[1]/Customer',2)
WITH(
First_Name VARCHAR(50)
,Middle_Name VARCHR(50)
,Last_Name VARCHAR(50)
)
EXEC sp_xml_removedocument @xmldoc
END
