I have a Multi-tier ASP.NET application. In which am reading data from SQL Server 2005. Am fetching XML data directly from database using "FOR XML AUTO,ELEMENTS" suffix in my query. The problem is some SQL records contain data with invalid chars (like hex values) which throws error while reading it using the XMLReader obtained by executing SQLCommand.ExecuteXMLReader() method. I read that error can be avoided by making some changes to settings property of XMLReader, Am unable to change settings for this XMLReader, .NET says it read only. I've coded a method to do this in business layer of my app. Moreover many parts of my application are already using this method. Is there any way or work around to resolve this without affecting other parts of my app.
System.Web.HttpContext zContext = HttpContext.Current;
string zszRetString = "";
XmlReader zXmlReader = null;
XmlTextWriter zXmlWriter = null;
StreamReader zStreamReader = null;
MemoryStream zMemStream = null;
SqlConnection Conn;
SqlCommand Cmd;
Conn = new SqlConnection ( Database.m_DSN );
Cmd = new SqlCommand ( xszQueryString, Conn );
Cmd.CommandType = CommandType.Text;
try
{
Conn.Open ();
zMemStream = new MemoryStream ( 10 * 1024 );
zXmlReader = Cmd.ExecuteXmlReader ();
zXmlWriter = new XmlTextWriter ( zMemStream, System.Text.Encoding.ASCII );
if ( xbNeedHeader )
zXmlWriter.WriteStartDocument ( true );
if ( xszRootNodeName != "" )
zXmlWriter.WriteStartElement ( xszRootNodeName );
while ( zXmlReader.Read () )
{
switch ( zXmlReader.NodeType )
{
case XmlNodeType.Element:
zXmlWriter.WriteStartElement ( zXmlReader.Prefix, Utilities.EncodeForXML ( zXmlReader.LocalName ),
zXmlReader.NamespaceURI );
if ( zXmlReader.HasAttributes )
{
zXmlWriter.WriteAttributes ( zXmlReader, true );
zXmlReader.MoveToElement ();
}
if ( zXmlReader.IsEmptyElement )
zXmlWriter.WriteEndElement ();
break;
case XmlNodeType.EndElement:
zXmlWriter.WriteEndElement ();
break;
case XmlNodeType.Text:
zXmlWriter.WriteString ( Utilities.EncodeForXML ( zXmlReader.Value ) );
break;
case XmlNodeType.CDATA:
zXmlWriter.WriteCData ( zXmlReader.Value );
break;
}
}
if ( xszRootNodeName != "" )
zXmlWriter.WriteEndElement ();
if ( xbNeedHeader )
zXmlWriter.WriteEndDocument ();
zXmlWriter.Flush ();
zMemStream.Position = 0;
zStreamReader = new StreamReader ( zMemStream );
zszRetString = zStreamReader.ReadToEnd ();
}
catch ( Exception e )
{
//Exception handling code
}
finally
{
//Finalizer Code
}
return zszRetString;