ADO.NET 3.5 Cookbook, 2nd Edition

9.11. Filling a DataSet Using an XML Template Query

Problem

You need to fill a DataSet using an XML template query.

Solution

Use the SQL XML Managed classes to use an XML template query to fill a DataSet.

The solution uses one XML file named GetContactQuery.xml, shown in Example 9-18. The solution assumes the file is in the same directory as the solution file FillDataSetXmlTemplateQuery.sln.

Example 9-18. File: GetContactQuery.xml

<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
    <sql:header>
        <sql:param name="ContactID" />
    </sql:header>
    <sql:query>
        SELECT
            ContactID, FirstName, LastName
        FROM
            Person.Contact
        WHERE
            ContactID = @ContactID
        FOR XML AUTO
    </sql:query>
</ROOT>

The solution creates a SqlXmlCommand template query and its single SqlXmlParameter object. The parameter is set to the user-specified value. A SqlXmlDataAdapter object is created and executed to fill a new DataSet based on the template query. The result set in the DataSet is output to the console.

The project needs a reference to the assembly Microsoft.Data.SqlXml. If this assembly is not installed on your computer, you can download it from Microsoft Download Center—the URL is http://www.microsoft.com/downloads/details.aspx?FamilyID=51d4a154-8e23-47d2-a033-764259cfb53b&DisplayLang=en as of the writing of this book.

The C# code in Program.cs in the project FillDataSetXmlTemplateQuery is shown in Example 9-19.

Example 9-19. File: Program.cs for FillDataSetXmlTemplateQuery solution

using ...