Working with Data Access Application Block and Xml

Topics: Data Access Application Block, General discussion
Mar 4, 2009 at 5:06 PM

I am having difficulties retrieving an Xml document from a SQL Server 2005 stored procedure and binding it to an XmlDataSource used to display the Xml document in a TreeView control.  What is the best practice for working with Xml and the Data Access Application Block in this context?  Thank you.

CREATE Procedure [dbo].[sp_GetXmlDoc]
    @CampaignId    int
AS
BEGIN
SELECT c.Id AS '@CampaignId'
      ,c.[Name] AS '@Name'
  FROM [Campaign] c
 WHERE c.[Id] = @CampaignId
   FOR XML PATH('Campaign'), ROOT('doc')
END

        <asp:XmlDataSource ID="XmlDataSource1" runat="server" OnLoad="XmlDataSource1_Load" XPath="Root/tree">
        </asp:XmlDataSource>
        <asp:TreeView ID="TreeView1" runat="server" DataSourceID="XmlDataSource1">
            <DataBindings>
                <asp:TreeNodeBinding DataMember="doc" TextField="Name" FormatString="{0}" />
  ...
            </DataBindings>
        </asp:TreeView>

    Protected Sub XmlDataSource1_Load(ByVal sender As Object, ByVal e As EventArgs)

        'Dim dbSql As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase(), SqlDatabase)

        If Not IsPostBack Then
     Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cmd As DbCommand = db.GetStoredProcCommand("sp_GetXmlDoc")
            db.AddInParameter(cmd, "CampaignId", DbType.Int32, cId)

            Dim ds As DataSet = db.ExecuteDataSet(cmd)
            XmlDataSource1.Data = ds.GetXml()
            XmlDataSource1.DataBind()
        End If

    End Sub

Mar 4, 2009 at 8:37 PM
I was able to get this working by using the ExecuteReader method rather than ExecuteDataSet.  I also had to cast the object to a field of type SqlDataReader.    

    Protected Sub XmlDataSource1_Load(ByVal sender As Object, ByVal e As EventArgs)

        If Not IsPostBack Then

            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cmd As DbCommand = db.GetStoredProcCommand("sp_GetMailingListTree")

            db.AddInParameter(cmd, "CampaignId", DbType.Int32, campaignId)
            db.AddInParameter(cmd, "MailingListId", DbType.Int32, mailingListId)

            Dim sdr As SqlDataReader = DirectCast(db.ExecuteReader(cmd), SqlDataReader)

            If sdr.Read() Then
                XmlDataSource1.Data = sdr.GetString(0)
                XmlDataSource1.DataBind()
            End If

        End If

    End Sub