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>
