RE: Query producing XML appears to be cached

Hi,
By default the XMLDataSource control always caches its data, so try to set
the EnableCaching property to false.
Regards,
Mohamed Mosalem

"Simon Rigby" wrote:

Hi folks,

ASP.NET2, Sql Server 2005

I have an app that executed a sql server query that produces an XML
file used as the DateFile for an XMLDataSource bound to a TreeView. The
bizarre thing is that it seems to be cached even if I make a change to
the stored procedure. For example I changed a string literal in the
proc and it was completely ignored then next time I viewed the page.

The really wierd bit is that the response time is as if the query was
actually executing. At the moment the data in the database is not
refines and as a result the query is taking up to 5 seconds to run.
Regardless of the fact that the result appears to be cached the query
is still taking up to 5 seconds each time.

The only thing that seems to kick it into gear is:

Stop IIS
Kill the ASPNET worker process
Delete the temporary aspnet files for the site.
Start it all up again, and its fine.

I've tried it Firefox 1.5 and 2.0 (and disabling the cache using the
Web Developer Toolbar). Also tried IE 6 and 7 so dont think its browser
related.

This sounds like it should be a known issue but couldnt find anything
to do with it. Included are the proc, code behind, source view and
example of the XML returned from the proc.

CODE BEHIND:

protected void Page_Load(object sender, EventArgs e) {
dsXmlContent.Data = GetXmlDoc().OuterXml;
}

protected XmlDocument GetXmlDoc() {
SqlConnection cn = new SqlConnection(@"data
source=server;
database=database; user id=user; password=password");
SqlCommand cmd = new
SqlCommand("procContent_GetContentMap", cn);
cn.Open();
XmlReader xr = cmd.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.Load(xr);

xr.Close();
cn.Close();

return doc;

}

STORED PROCEDURE

Create PROCEDURE [dbo].[procContent_GetContentMap]

AS
BEGIN
SET NOCOUNT ON;

select
1 tag,
null parent,
null [Section!1!Section],
null [Sections!2!SectionID],
null [Sections!2!SectionName],
NULL [Pages!3!PageID],
NULL [Pages!3!PageName],
NULL [Contents!4!ContentID],
NULL [Contents!4!Html]

union all

select
2 tag,
1 parent,
null,
ID,
SectionName,
NULL,
NULL,
NULL,
NULL

from
Section

union all

select
3,
2,
null,
Section.ID,
null,
Page.ID,
Page.PageName,
NULL,
NULL

from
Section, Page

where
Section.ID = Page.SectionID

union all

select
4,
3,
null,
Section.ID,
null,
Page.ID,
null,
[Content].ID,
[Content].Html

from Section, Page, [Content]

where Section.ID = Page.SectionID
and Page.ID = [Content].PageID

order by [Section!1!Section], [Sections!2!SectionID],
[Pages!3!PageID], [Contents!4!ContentID]

for xml explicit

end

GENERATED XML DOCUMENT

<Section>
<Sections SectionID="1" SectionName="Test Section 1">
<Pages PageID="1" PageName="Section 1 Page 1">
<Contents ContentID="1" Html="Section 1 Page 1 content" />
</Pages>
<Pages PageID="2" PageName="Section 1 Page 2">
<Contents ContentID="2" Html="Section 1 Page 2 content" />
</Pages>
</Sections>
<Sections SectionID="2" SectionName="Test Section 2">
<Pages PageID="3" PageName="Section 2 Page 1">
<Contents ContentID="3" Html="Section 2 Page 1 content" />
</Pages>
<Pages PageID="4" PageName="Section 2 Page 2">
<Contents ContentID="4" Html="Section 2 Page 2 content" />
</Pages>
</Sections>
</Section>

SOURCE EXTRACT OF THE TREEVIEW CONTROL

<asp:TreeView ID="tvNav" runat="server" Style="position: relative"
PathSeparator="." DataSourceID="dsXmlContent" ExpandDepth="0"
ImageSet="XPFileExplorer" NodeIndent="15">
<DataBindings>
<asp:TreeNodeBinding
DataMember="Sections" ValueField="SectionID"
TextField="SectionName" />
<asp:TreeNodeBinding
DataMember="Pages" ValueField="PageID"
TextField="PageName" />
<asp:TreeNodeBinding
DataMember="Contents" ValueField="ContentID"
TextField="Html" />
</DataBindings>
</asp:TreeView>


.

Relevant Pages

  • Re: DTS Global Variables + SQL query
    ... they query I'm trying to run isn't as straight forward as I originally ... >>I'm creating a new Transform Data Task in SQL Server 2000. ... >>'Creating an XML object to read the XML ... > Script Task to set the values to global variables makes sense. ...
    (microsoft.public.sqlserver.dts)
  • Query producing XML appears to be cached
    ... I have an app that executed a sql server query that produces an XML ... actually executing. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: OLEDB consumer and for xml auto SELECT
    ... Query Analyzer but when I try to get it from the OLEDB consumer, ... >> I have a SELECT statement which retrieve data in xml format (FOR XML ... >> for SQL Server to the ODBC provider but the results are still the same! ...
    (microsoft.public.data.oledb)
  • Re: Large XML file opinions
    ... > have an app that will need to Query (read only operations is all I'll ever ... > I am wondering if I shouldn't just create a table in SQL server, ... > a routine that dumps the XML file's values into corresponding SQL server ...
    (microsoft.public.dotnet.xml)
  • Re: Download the JAVA , .NET and SQL Server interview with answers
    ... 2000 Interview questions of .NET, JAVA and SQL Server Interview ... XML Integration ...
    (comp.lang.java.programmer)