如何用sql server的xml query输出作为WPF的数据源？

Question

      由于TreeView的分层结构，很适合用XML作为数据源来填充其节点，网上很多实例都是直接以XML文件或者以内嵌Xdata的形式作为TreeView的数据源。

       现在的问题是数据保存在Sql Server中，通过For XML Explict关键字可以得到用于填充TreeView的数据，要通过什么方式将Sql Server的查询结果集（XML格式）作为TreeView的数据源呢？个人觉得还是得用ado.net获取结果集，然后怎么跟TreeView的数据源耦合就没概念了，最好有简单代码，谢谢。

Answer 1

你好，

对于你的问题，可能需要学习如何在WPF应用中使用XML绑定, XmlDataProvider 是一个办法：

#XmlDataProvider Class
http://msdn.microsoft.com/en-us/library/system.windows.data.xmldataprovider(v=vs.110).aspx

在我的Demo中，使用SQL查询一个表单的内容：

Select EmployeeID, LastName, FirstName, Title, City From Employees For XML Auto

使用DataSet.GetXML() 获取XML内容，写入文件：

string connectionString = "Initial Catalog=northwind;Data Source=localhost;Integrated Security=SSPI;";

SqlConnection sdwDBConnection = new SqlConnection(connectionString);
sdwDBConnection.Open();

// To generate XML File using C# from SQL Server Data

using (SqlDataAdapter da = new SqlDataAdapter())
{
    SqlCommand command = new SqlCommand("Select EmployeeID, LastName, FirstName, Title, City From Employees For XML Auto", sdwDBConnection);
    da.SelectCommand = command;

    string xml = "";
    using (DataSet ds = new DataSet())
    {
        da.Fill(ds);

        if (ds != null && ds.Tables.Count > 0)
               xml = ds.GetXml();
        xml = xml.Replace("\r\n", "").Replace("&lt;", "<").Replace("&gt;", ">");
        xml = Regex.Replace(xml, @"<NewDataSet>[\s]+<Table>[\s]+<XML_[\w\d-]+>", "<NewDataSet>");
        xml = Regex.Replace(xml, @"</XML_[\w\d-]+>[\s]+</Table>[\s]?</NewDataSet>", "</NewDataSet>");
        xml = "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" + xml;
        // Compose a string that consists of three lines.

        // Write the string to a file.
        System.IO.StreamWriter file = new System.IO.StreamWriter(System.IO.Directory.GetCurrentDirectory() + @"\..\..\XMLData.xml");
        file.Write(xml);
        file.Close();
    }
}

// Close the connection
sdwDBConnection.Close();

以上代码要放置在 InitializeComponent(); 之前, XML内容：

<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy" Title="Sales Representative" City="Seattle" />
<Employees EmployeeID="2" LastName="Fuller" FirstName="Andrew" Title="Vice President, Sales" City="Tacoma" />
<Employees EmployeeID="3" LastName="Leverling" FirstName="Janet" Title="Sales Representative" City="Kirkland" />

...

XAML:

<Window.DataContext>
        <XmlDataProvider x:Name="source1" Source="XMLData.xml" XPath="NewDataSet/Employees" />
</Window.DataContext>

<Grid>
        <TreeView x:Name="tv1" ItemsSource="{Binding}">
            <TreeView.ItemTemplate>
                <HierarchicalDataTemplate >
                    <HierarchicalDataTemplate.ItemsSource>
                        <Binding XPath="child::*" />
                    </HierarchicalDataTemplate.ItemsSource>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="{Binding XPath=@EmployeeID}" />
                        <TextBlock Text="{Binding XPath=@LastName}" Margin="10,0,0,0" />
                        <TextBlock Text="{Binding XPath=@FirstName}" Margin="10,0,0,0" />
                        <TextBlock Text="{Binding XPath=@Title}" Margin="10,0,0,0" />
                        <TextBlock Text="{Binding XPath=@City}" Margin="10,0,0,0" />
                    </StackPanel>
                </HierarchicalDataTemplate>
            </TreeView.ItemTemplate>
        </TreeView>
</Grid>

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

     谢谢回复,有点麻烦,我的原意是如果直接有个类似Command.ExcuteXMLreader能够直接返回XMLDataProvider对象就方便了.目前看来没有.

Answer 3

你好，

据我所知，是没有现成的方法方便地实现你要的功能

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.