Best practice for returning disconnected XML RRS feed

  • Question

  • I have an application which consists of a presentation layer, business layer in VB.Net and SQL server. One of the tasks I am performing frequently is to build up an XML document which is used by the presentation layer. This XML is often comprised of various fragments from the database and from values determined in the business layer. I use an XmlWriter to format this document and return it to the presentation layer as an XmlReader.


    I have a data component which encapsulates the code for getting a connection string and formatting a call to SQL. It takes a command object as a parameter and using the ExecuteXmlReader method returns an XmlReader object. This is handy because I can then use the XmlWriter.WriteNode method to append the XML.


    I started encountering connection pool timeouts on a test server and after doing a bit of reading, I discovered that the connection is not always returned to the pool when an XmlReader object created by ExecuteXmlReader is closed. It doesn't have the CommandBehaviour.CloseConnection setting that is available with ExecuteReader.


    Closing the connection immediately after ExecuteXmlReader obviously wont work because it cuts off the XmlReader's access to the underlying data. But once the XmlReader has been passed back to the presentation layer, the command object is no longer in scope, so it's not possible to call the Connection.Close method after the XmlReader has been consumed.


    I thought that creating an XmlWriter on a memory stream, writing the XmlReader to it with WriteNode, and then calling XmlReader.Create on the underlying stream would give me a disconnected XmlReader. This seems to survive beyond the Connection.Close but when I try to use WriteNode to append it to my XmlWriter in the business layer, I get a "more than one root element" error. I believe this is because the FOR XML query is returning multiple rows. I don't get this error when using the XmlReader object returned by ExecuteXMLReader directly. So I have abandonned that approach because it does not work for me.


    Does anyone have any pointers on the best way to do this? Should I be using XmlDocuments instead? The problem with that is that XmlReaders are supported so well by other XML handling objects and the XmlDocument less so.

    Thursday, May 10, 2007 6:39 AM

All replies