使用 XML 序列化和 SQL 的 FOR XML PATH

• 下载源文件 - 19.3 KB

引言

使用数据库中的模型对象一直是复杂且繁琐的过程。模型结构越复杂，问题就越复杂，资源消耗也越大。SQL Server 2000 引入了 FOR XML 子句，但要获得想要的 XML 结构，需要使用 Explicit 模式的晦涩语法。同样，结构越复杂，查询就越糟糕。

SQL Server 2005 引入了 FOR XML Path 模式，允许您通过更直观的别名（看起来更像 XPath 表达式）来指定 XML 结构。通过一种相对简单的方式指定 XML 输出结构，通过 XML 序列化加载对象图可以极大地简化选择复杂数据的过程。

在本文中，我将通过一个简单的例子展示如何做到这一点。该演示包含两个用于将信息加载到模型中的类。一种是使用旧方法手动编写代码加载对象，另一种是使用 FOR XML Path 存储过程和 XML 序列化。由于该项目依赖于数据库，因此我没有包含预先构建的演示。要运行演示，您需要设置数据库，因此无法仅下载它就运行。

你需要的东西

要使用此方法，您的模型需要是 XML 可序列化的。有关如何执行此操作的复习，请参阅我关于使用 XmlSerializer 属性的文章。完成此操作后，您还需要一个 SQL 查询或存储过程，该过程使用 FOR XML 生成与您的模型 XML 结构相匹配的 XML。然后，您只需要编写代码来运行查询并反序列化 XML 输出（例如演示项目中包含的 SqlXmlData 类）。

演示项目

演示项目包含一个 SimpleCMS.sql SQL 文件，用于创建演示数据库。它会创建数据库、两个表、一个存储过程，并插入一些示例数据。演示应用程序旨在选择一个公司及其所有关联的联系人，以便在 TreeView 中显示。它仅用作概念验证，以展示此方法如何用于选择复杂数据。

数据库有两个表：Company 和 Contact。

模型与数据库结构类似，但更复杂一些。

老方法

演示项目包含一个名为 SqlRawData 的类，该类以繁琐的方式填充 Companies 模型对象。

SqlRawData 执行以下操作来获取公司及其关联联系人的信息

public CompanyList GetData(int id) {
    if (dbs == null || conn == null) {
        throw new ApplicationException("Data connection not yet initialized");
    }

    CompanyList obj = new CompanyList();
    obj.Companies = new List<Company>();
    using (IDbCommand cmd = conn.CreateCommand()) {
        cmd.CommandText = GetCompanySQL();
        cmd.Parameters.Add(new SqlParameter("@CompanyId", id));
        conn.Open();
        using (IDataReader idr = cmd.ExecuteReader()) {
            if (idr.Read()) {
                Company cmp = ReadCompany(idr);
                if (cmp != null) { obj.Companies.Add(cmp); }
            }
            idr.Close();
        }
    }

    foreach (Company cmp in obj.Companies) {
        cmp.Contacts = new List<Contact>();
        using (IDbCommand cmd = conn.CreateCommand()) {
            cmd.CommandText = GetContactSQL();
            cmd.Parameters.Add(new SqlParameter("@CompanyId", cmp.Id));
            using (IDataReader idr = cmd.ExecuteReader()) {
                while (idr.Read()) {
                    Contact cnt = ReadContact(idr);
                    if (cnt != null) { cmp.Contacts.Add(cnt); }
                }
            }
        }
    }
    conn.Close();
    return obj;
}

private Company ReadCompany(IDataReader idr) {
    /*
    0 Cmp_id    1 Name        2 Address
    3 City        4 State        5 Zip
    */
    Company cmp = new Company();
    cmp.Id = Convert.ToInt32(idr[0]);
    cmp.Name = idr.GetString(1);
    cmp.Address = new Address();
    cmp.Address.Street = idr.GetString(2);
    cmp.Address.City = idr.GetString(3);
    cmp.Address.State = idr.GetString(4);
    cmp.Address.Zip = idr.GetString(5);
    return cmp;
}
private Contact ReadContact(IDataReader idr) {
    /*
    0 Cnt_id    1 [Name]        2 Email
    3 Phone        4 UserName        5 Password
    */
    Contact cnt = new Contact();
    cnt.Id = Convert.ToInt32(idr[0]);
    cnt.Name = idr.GetString(1);
    cnt.ContactInfo = new ContactInfo();
    cnt.ContactInfo.Email = idr.GetString(2);
    cnt.ContactInfo.Phone = idr.GetString(3);
    cnt.Login = new Login();
    cnt.Login.Uid = idr.GetString(4);
    cnt.Login.Pwd = idr.GetString(5);
    return cnt;
}

private string GetCompanySQL() {
    StringBuilder sql = new StringBuilder();

    return sql.ToString();
}
private string GetContactSQL() {
    StringBuilder sql = new StringBuilder();

    return sql.ToString();
}

由于我选择使用直接 SQL 而不是存储过程，这有点冗长，但仍然需要编写大量的代码来执行单独的查询并手动加载每个对象类型。（为了在此处显示示例，GetXXSQL() 方法已被截断。请参阅源下载以获取完整源。）

如您所见，我必须首先查询 Company 数据，然后我选择将 IDataReader 传递给另一个方法来构建对象。然后，我必须对 Contacts 执行相同的操作，并在创建 Company 时将它们添加到 Company 中。此方法还可以，但是数据结构越复杂，嵌套越深，代码就越复杂。这也需要越来越多的代码来适应更复杂的结构。此外，这需要多次数据库访问。在此示例中，您可以避免多次访问，但随着复杂度的增加，很容易出现无法使用单个查询完成的情况。

FOR XML Path

使用 SQL Server 2005 的 FOR XML Path，您可以用单个查询和更少的代码来实现相同的结果。演示项目中的 SqlXmlData 类具有多个用于序列化和反序列化 XML 的通用方法。它还包含以下方法，该方法接受 SQL 语句或存储过程名称、CommandType 和参数数组。此方法运行查询（假定它使用 FOR XML 并具有正确的结构），并将结果反序列化为泛型提供的类型。

public T GetData<T>(string sql, CommandType cmdtype, params SqlParameter[] parameters) {
    if (dbs == null || conn == null) {
        throw new ApplicationException("Data connection not yet initialized");
    }

    T obj = default(T);
    string xml = null;
    using (IDbCommand cmd = conn.CreateCommand()) {
        cmd.CommandText = sql;
        cmd.CommandType = cmdtype;
        foreach (SqlParameter idp in parameters) {
            cmd.Parameters.Add(idp);
        }
        conn.Open();
        using (IDataReader idr = cmd.ExecuteReader()) {
            if (idr.Read()) {
                xml = idr[0].ToString();
            }
            idr.Close();
        }
        conn.Close();
    }
    if (xml != null) {
        obj = Deserialize<T>(xml);
    }
    return obj;
}

使用此类，您必须编写的代码如下所示

private CompanyList GetCompany(int id) {
    CompanyList list = null;
    using (SqlXmlData data = new SqlXmlData(
        "Server=localhost;Database=SimpleCMS;Trusted_Connection=True;"
    )) {
        list = data.GetData<CompanyList>("spGetCompanyXML",
            CommandType.StoredProcedure,
            new SqlParameter("@CompanyId", id)
        );
    }
    return list;
}

存储过程

通过为查询中的字段提供类似 XPath 的映射，您可以指定输出 XML 的结构方式。您可以使用 "@a" 指定属性，"e" 指定元素，"e1/e2" 指定子元素，"text()" 指定文本，"data()" 指定数据值。可以通过也使用 FOR XML 的子查询来指定子结构。通过在 FOR XML 子句中包含 root('name')，您可以将结构封装在另一个节点中。spGetCompanyXML 存储过程如下所示

select
    Company.Cmp_id "@id",
    Company.[Name] "@name",
    Company.Address "Address/Street",
    Company.City "Address/City",
    Company.State "Address/State",
    Company.Zip "Address/Zip",
    (
        select
            Contact.Cnt_id "@id",
            Contact.[Name] "@name",
            Contact.Email "ContactInfo/@email",
            Contact.Phone "ContactInfo/@phone",
            Contact.UserName "Login/@uid",
            Contact.Password "Login/@pwd"
         from
            Contact
         where
            Contact.Cmp_id = @CompanyId
         order by
            Contact.[Name]
         for xml path ('Contact'), root('Contacts'), type
    )
 from
    Company
 where
    Company.Cmp_id = @CompanyId
 for xml path ('Company'), root('Companies'), type

此存储过程将产生如下输出

<Companies>
  <Company id="1" name="Company A">
    <Address>
      <Street>101 A St.</Street>
      <City>Hays</City>
      <State>Kansas</State>
      <Zip>67601</Zip>
    </Address>
    <Contacts>
      <Contact id="1" name="Bob Black">
        <ContactInfo email="bb@companyA.com" phone="(123) 456-7890" />
        <Login uid="bb" pwd="bb7" />
      </Contact>
      <Contact id="2" name="Bob Brown">
        <ContactInfo email="bbn@companyA.com" phone="(123) 456-7891" />
        <Login uid="bn" pwd="bn1" />
      </Contact>
      <Contact id="3" name="Bob White">
        <ContactInfo email="bw@companyA.com" phone="(123) 456-7892" />
        <Login uid="bw" pwd="bw2" />
      </Contact>
    </Contacts>
  </Company>
</Companies>

由于 Contacts 子查询的 for xml path ('Contact'), root('Contacts'), type 子句包含 root('Contacts')，因此 Contact 节点位于 Company 下的 Contacts 节点内。

现在我们有了一个存储过程，该过程生成包含 Company 信息以及所有关联联系人的 XML，所有这些都在一个查询中完成，我们只需要将该 XML 输出反序列化到 CompanyList 对象。而且，使用演示项目中包含的 SqlXmlData 类，您需要编写的代码非常少。除此之外，您只需要确保用于反序列化的模型具有必要的 XMLSerializer 属性来镜像生成的 XML。

回顾

XML 可序列化模型

如上所述，我有一篇关于使用 XmlSerializer 属性主题的文章。如果您有示例 XML 或 XSD 模式可供参考，可以使用 XSD.exe 或 Skeleton Crew 等工具生成 XML 可序列化代码模型。而且，如果您还没有模型，您可以从 XML 结构开始，然后从中生成代码。如果您已经有一个不可 XML 序列化的模型，上面的文章可以帮助您为模型添加必要的属性，以生成您想要的 XML。

XML 查询

FOR XML 子句的 Path 模式的文档似乎很零散。我能找到的大部分关于该主题的介绍都很基础。但经过一些实验，希望您能使其达到您需要的效果。无论如何，以下是我找到的一些参考资料

• 将 FOR XML EXPLICIT 迁移到新的 PATH 模式（第二部分）
• Microsoft SQL Server 2005 中 FOR XML 的新功能

XMLSerializer

当然，您可以重用演示项目的 SqlXmlData 类。您可以根据需要对其进行修改。或者，您可以从头开始创建自己的。我选择专门使用 System.Data.SqlClient 实现，因为据我所知，SQL Server 2005 是唯一支持 FOR XML 子句的引擎，但我也可能错了。

结论

当然，这对于插入或更新信息没有帮助。但是，选择结构复杂的复杂数据通常比插入和更新更繁琐、更耗时且更容易出错。我相信在某些情况下，即使是这种方法也会很复杂，甚至可能不可行。

您如何处理取决于您的情况。如果您已经有一个 XML 可序列化的模型，您将希望创建与该 XML 结构匹配的查询。如果您的模型尚不可 XML 序列化，那么您将有一个干净的起点，并可以根据您的模型和数据决定如何将两者在 XML 中匹配。

在对 SQL Server 2000 的 Explicit 模式的 FOR XML 的复杂性感到失望之后，我非常兴奋地看到了 Path 模式。我希望这能帮助一些人看到 SQL Server 2005 通过这种方法提供的巨大可能性。 Path 模式与 XML 序列化相结合，是选择复杂数据的强大工具组合。