SQLDataSource binding to treeview control when using "FOR XML" on select

Discussion in 'ASP .Net Web Controls' started by Stan Spotts, Jun 12, 2006.

  1. Stan Spotts

    Stan Spotts Guest

    One of our associates wants to pull data in from SQL Server 2000 using a
    "FOR XML" statement on his select that represents hierarchical data. He
    thought it should be easy to bind a TreeView control to this using a
    SqlDataSource, but said that it didn't work. Now he's wondering if he needs
    some combination of a SqlDataSource and an XmlDataSource.

    He's not newsgroup savvy, so I offered to post for him. I don't have his
    source code, but if needed I'll ask him for it.

    Is there a limitation that won't let him do this, or a trick that will?

    Thanks,
    --Stan
     
    Stan Spotts, Jun 12, 2006
    #1
    1. Advertising

  2. Hello Stan,

    Thank you for posting in the MSDN newsgroup.

    From your description, I understand one of your associates will use the
    ASP.NET 2.0 TreeView control to populate some data from database, and the
    data is of xml format which is retrieved from SQL Server database through
    FOR XML clause. Currently you're wondering how to utlize the ASP.NET 2.0's
    datasource control binding to associate the data from database to the
    TreeView, correct?

    Based on my understanding, since the TreeView control's XmlDocument
    databinding is rely on the XmlDataSource, so we still have to use
    XmlDataSource rather than SqlDataSource(SqlDatasource is focus on supplying
    relational database data rather than XML based data). However,
    XmlDataSource by default require us to supply a static xml data file, and
    in your scenario, the xml data is retrieved from SQL Database through FOR
    XML query, I think we need some code to programmatically get the xmlstream
    from database and assign to to the XmlDataSource control. For example:

    ======code behind==============
    Page_Load(object sender, EventArgs e)
    {
    XmlDataSource1.Data = GetXmlDoc().OuterXml;
    }


    protected XmlDocument GetXmlDoc()
    {
    SqlConnection conn = new SqlConnection(

    WebConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].
    ConnectionString
    );

    conn.Open();

    string sql = "select CultureID, Name from Production.Culture FOR
    XML AUTO, ROOT('root')";

    SqlCommand comm = new SqlCommand(sql, conn);


    XmlReader reader = comm.ExecuteXmlReader();


    XmlDocument doc = new XmlDocument();
    doc.Load(reader);

    reader.Close();
    conn.Close();


    return doc;
    }
    ====================

    so it is the "Data" property of the XmlDatasource control we use to
    dynamically populate the XML data. And in the aspx page, we still bind the
    TreeView control with the XmlDataSource(XmlDataSource1):

    =======================
    <form id="form1" runat="server">
    <div>
    <asp:XmlDataSource ID="XmlDataSource1" runat="server" >
    </asp:XmlDataSource>

    </div>
    <asp:TreeView ID="TreeView1" runat="server"
    DataSourceID="XmlDataSource1" >
    <DataBindings >
    <asp:TreeNodeBinding DataMember="Production.Culture"
    TextField="Name" ValueField="CultureID" />
    </DataBindings>
    </asp:TreeView>

    </form>
    ===================

    Just some of my consideration. Also, you can also programmatically use
    System.Xml namespace's classes to query XmlNode List and directly bind to
    the TreeView(without any DataSource control).

    Please feel free to post here if you have any other ideas or concerns.

    Regards,

    Steven Cheng
    Microsoft MSDN Online Support Lead


    ==================================================

    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.

    ==================================================


    This posting is provided "AS IS" with no warranties, and confers no rights.



    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
     
    Steven Cheng[MSFT], Jun 13, 2006
    #2
    1. Advertising

  3. Stan Spotts

    Stan Spotts Guest

    Perfect response, Steven - that helped him out.

    Thanks,
    --Stan

    "Steven Cheng[MSFT]" <> wrote in message
    news:eek:...
    > Hello Stan,
    >
    > Thank you for posting in the MSDN newsgroup.
    >
    > From your description, I understand one of your associates will use the
    > ASP.NET 2.0 TreeView control to populate some data from database, and the
    > data is of xml format which is retrieved from SQL Server database through
    > FOR XML clause. Currently you're wondering how to utlize the ASP.NET 2.0's
    > datasource control binding to associate the data from database to the
    > TreeView, correct?
    >
    > Based on my understanding, since the TreeView control's XmlDocument
    > databinding is rely on the XmlDataSource, so we still have to use
    > XmlDataSource rather than SqlDataSource(SqlDatasource is focus on
    > supplying
    > relational database data rather than XML based data). However,
    > XmlDataSource by default require us to supply a static xml data file, and
    > in your scenario, the xml data is retrieved from SQL Database through FOR
    > XML query, I think we need some code to programmatically get the xmlstream
    > from database and assign to to the XmlDataSource control. For example:
    >
    > ======code behind==============
    > Page_Load(object sender, EventArgs e)
    > {
    > XmlDataSource1.Data = GetXmlDoc().OuterXml;
    > }
    >
    >
    > protected XmlDocument GetXmlDoc()
    > {
    > SqlConnection conn = new SqlConnection(
    >
    > WebConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].
    > ConnectionString
    > );
    >
    > conn.Open();
    >
    > string sql = "select CultureID, Name from Production.Culture FOR
    > XML AUTO, ROOT('root')";
    >
    > SqlCommand comm = new SqlCommand(sql, conn);
    >
    >
    > XmlReader reader = comm.ExecuteXmlReader();
    >
    >
    > XmlDocument doc = new XmlDocument();
    > doc.Load(reader);
    >
    > reader.Close();
    > conn.Close();
    >
    >
    > return doc;
    > }
    > ====================
    >
    > so it is the "Data" property of the XmlDatasource control we use to
    > dynamically populate the XML data. And in the aspx page, we still bind the
    > TreeView control with the XmlDataSource(XmlDataSource1):
    >
    > =======================
    > <form id="form1" runat="server">
    > <div>
    > <asp:XmlDataSource ID="XmlDataSource1" runat="server" >
    > </asp:XmlDataSource>
    >
    > </div>
    > <asp:TreeView ID="TreeView1" runat="server"
    > DataSourceID="XmlDataSource1" >
    > <DataBindings >
    > <asp:TreeNodeBinding DataMember="Production.Culture"
    > TextField="Name" ValueField="CultureID" />
    > </DataBindings>
    > </asp:TreeView>
    >
    > </form>
    > ===================
    >
    > Just some of my consideration. Also, you can also programmatically use
    > System.Xml namespace's classes to query XmlNode List and directly bind to
    > the TreeView(without any DataSource control).
    >
    > Please feel free to post here if you have any other ideas or concerns.
    >
    > Regards,
    >
    > Steven Cheng
    > Microsoft MSDN Online Support Lead
    >
    >
    > ==================================================
    >
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    >
    > ==================================================
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    >
    >
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
     
    Stan Spotts, Jun 13, 2006
    #3
  4. You're welcome Stan,

    Glad to be of assistance!

    Regards,

    Steven Cheng
    Microsoft MSDN Online Support Lead


    ==================================================

    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.

    ==================================================


    This posting is provided "AS IS" with no warranties, and confers no rights.



    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)
     
    Steven Cheng[MSFT], Jun 14, 2006
    #4
  5. Stan Spotts

    Bob Lawhorn Guest

    RE: SQLDataSource binding to treeview control when using "FOR XML"

    Steven Cheng, when I attempt to use the code you provided I receive a "This
    document already has a 'DocumentElement' node." error message on the
    doc.Load(reader); instruction. It appears that this is related to the fact
    that I am using the 'For XML Explicit' option in my stored procedure. Can
    you provide any suggestions in how to modify this code to allow it to process
    correctly?
    - Bob


    "Steven Cheng[MSFT]" wrote:

    > Hello Stan,
    >
    > Thank you for posting in the MSDN newsgroup.
    >
    > From your description, I understand one of your associates will use the
    > ASP.NET 2.0 TreeView control to populate some data from database, and the
    > data is of xml format which is retrieved from SQL Server database through
    > FOR XML clause. Currently you're wondering how to utlize the ASP.NET 2.0's
    > datasource control binding to associate the data from database to the
    > TreeView, correct?
    >
    > Based on my understanding, since the TreeView control's XmlDocument
    > databinding is rely on the XmlDataSource, so we still have to use
    > XmlDataSource rather than SqlDataSource(SqlDatasource is focus on supplying
    > relational database data rather than XML based data). However,
    > XmlDataSource by default require us to supply a static xml data file, and
    > in your scenario, the xml data is retrieved from SQL Database through FOR
    > XML query, I think we need some code to programmatically get the xmlstream
    > from database and assign to to the XmlDataSource control. For example:
    >
    > ======code behind==============
    > Page_Load(object sender, EventArgs e)
    > {
    > XmlDataSource1.Data = GetXmlDoc().OuterXml;
    > }
    >
    >
    > protected XmlDocument GetXmlDoc()
    > {
    > SqlConnection conn = new SqlConnection(
    >
    > WebConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].
    > ConnectionString
    > );
    >
    > conn.Open();
    >
    > string sql = "select CultureID, Name from Production.Culture FOR
    > XML AUTO, ROOT('root')";
    >
    > SqlCommand comm = new SqlCommand(sql, conn);
    >
    >
    > XmlReader reader = comm.ExecuteXmlReader();
    >
    >
    > XmlDocument doc = new XmlDocument();
    > doc.Load(reader);
    >
    > reader.Close();
    > conn.Close();
    >
    >
    > return doc;
    > }
    > ====================
    >
    > so it is the "Data" property of the XmlDatasource control we use to
    > dynamically populate the XML data. And in the aspx page, we still bind the
    > TreeView control with the XmlDataSource(XmlDataSource1):
    >
    > =======================
    > <form id="form1" runat="server">
    > <div>
    > <asp:XmlDataSource ID="XmlDataSource1" runat="server" >
    > </asp:XmlDataSource>
    >
    > </div>
    > <asp:TreeView ID="TreeView1" runat="server"
    > DataSourceID="XmlDataSource1" >
    > <DataBindings >
    > <asp:TreeNodeBinding DataMember="Production.Culture"
    > TextField="Name" ValueField="CultureID" />
    > </DataBindings>
    > </asp:TreeView>
    >
    > </form>
    > ===================
    >
    > Just some of my consideration. Also, you can also programmatically use
    > System.Xml namespace's classes to query XmlNode List and directly bind to
    > the TreeView(without any DataSource control).
    >
    > Please feel free to post here if you have any other ideas or concerns.
    >
    > Regards,
    >
    > Steven Cheng
    > Microsoft MSDN Online Support Lead
    >
    >
    > ==================================================
    >
    > When responding to posts, please "Reply to Group" via your newsreader so
    > that others may learn and benefit from your issue.
    >
    > ==================================================
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
    >
     
    Bob Lawhorn, Jun 26, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. pologc

    Binding XML to an IE Treeview

    pologc, Jan 29, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    865
    pologc
    Jan 29, 2004
  2. Dan Sikorsky
    Replies:
    1
    Views:
    626
    =?Utf-8?B?Y2xpY2tvbg==?=
    Mar 29, 2006
  3. Sobin Thomas

    Binding Gridview with Sqldatasource control

    Sobin Thomas, Aug 26, 2008, in forum: ASP .Net
    Replies:
    0
    Views:
    505
    Sobin Thomas
    Aug 26, 2008
  4. Sobin Thomas
    Replies:
    1
    Views:
    443
    Munna
    Aug 27, 2008
  5. palmiere
    Replies:
    1
    Views:
    473
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page