使用 SQL Server 2005 中的 XML 数据类型在 SQL 参数中传递数组

• 下载演示项目 - 7.0 KB

引言

在 T-SQL 中，将值数组作为 SQL 参数传递一直很麻烦。在 SQL Server 2005 中，XML 数据类型可以帮助模拟数组。

背景

T-SQL 没有数组的概念；它只有简单类型（例如，INTEGER，VARCHAR）。Web 或应用程序服务器的一个典型场景是使用多个参数调用存储过程。例如，您可能进行信息处理，并确定应该使用给定状态标记客户 ID 数组。实现该场景的直观方法是将客户 ID 列表传递给存储过程

private void PushStatus(int[] customerIDs, CustomerStatus status)
{
    CallSproc(customerIDs, status);
}

不幸的是，您将无法编写接受 INTEGER 数组作为输入参数的存储过程。实现该场景的一种可能方法是多次调用存储过程，每个 ID 调用一次

private void PushStatus(int[] customerIDs, CustomerStatus status)
{
    foreach(int id in customerIDs)
    {
        CallSproc(id, status);
    }
}

这是一种流行的方法，但它的可扩展性不好。如果您要传递的 ID 数组很大（例如，超过 10 个元素），则存储过程调用的延迟将开始降低整体操作的性能。为了使性能保持可接受，我们需要在一个调用中将整个数组传递到数据库服务器，或者至少比数组大小少得多地进行调用。在 SQL Server 2005 之前，一种流行的解决方案是将数组连接成一个字符串，并将该字符串传递给存储过程。该解决方案存在几个问题

• 在 T-SQL 中拆分字符串是可能的，但这是一个繁琐且缓慢的过程。这是因为 T-SQL 不是一种优化用于操作字符串的语言。
• 与所有序列化过程一样，您必须选择一个分隔符（例如管道），并禁止从输入中使用或转义此字符。 这在两端都引入了复杂性。
• 输入是一个字符串（varchar 或 text），因此，从语义上讲，这种技术对于阅读存储过程的人来说是令人困惑的。

使用 XML

SQL Server 2005 的一个重要亮点是引入了 XML 数据类型。 XML 非常适合传输各种结构信息。 使用 XML，我们可以实现上一节中的场景。 我们只需将数组（例如 {42, 73, 2007}）转换为 XML 文档

<list>
    <item>42</item>
    <item>73</item>
    <item>2007</item>
</list>

这种方法有很多优点

• 使用 T-SQL 的 x-query 功能，可以很容易地从 T-SQL 中的 XML 文档中提取信息。
• 序列化过程中存在特殊字符（例如 <、>），但 XML 的转义机制是众所周知的，并且由 T-SQL 处理。
• 存储过程的语义更加清晰，因为 XML 始终表示信息包。

Using the Code

本文中提出的迷你库由 C# 中的一个静态辅助类和一个 T-SQL 函数组成。 静态类有一个方法

public static SqlXml GetXml(IEnumerable list)
{
    //We don't use 'using' or dispose or close the stream, 

    //since it leaves in the return variable

    MemoryStream stream = new MemoryStream();

    using (XmlWriter writer = XmlWriter.Create(stream))
    {
        writer.WriteStartElement("list");

        foreach (object obj in list)
        {
            writer.WriteElementString("item", obj.ToString());
        }

        writer.WriteEndElement();
        stream.Position = 0;

        return new SqlXml(stream);
    }
}

它接受对象列表（任何类型的对象；它仅依赖于 ToString 实现）并返回一个 XML 变量，该变量已准备好提供给 SqlParameter。 在 SQL Server 上，该函数是一个内联表函数

CREATE FUNCTION [lm].[SplitList]
(
    @list AS XML
)
RETURNS TABLE
AS
RETURN
(
    SELECT tempTable.item.value('.', 'VARCHAR(MAX)') AS Item
    FROM @list.nodes('list/item') tempTable(item)
);

它接受一个 XML 参数并返回一个包含一列的表，其中每一行都是 XML 节点的内容。

结论

本文中显示的技术展示了将 XML 作为 SQL Server 2005 中的一等公民的另一个有趣的好处。将对象数组传递给存储过程可以提高批处理操作的性能。这可以提高应用程序的性能和可伸缩性。