将参数作为Xml传递给存储过程

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以传递到存储库。

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推荐什么？

4 个解决方案

#1

If you must use xml; then rather than passing around a dictionary, I'd use a class that represents that data, and use XmlSerializer to 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转义......

#2

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

#3

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对其进行转换

#4

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（）即可。