Convert a .NET DataReader to XML

Category:
C#, VB.NET, ASP.NET
Type:
Snippets
Difficulty:
Beginning

Author: Intelligent Solutions Inc.

Version Compatibility: Visual Basic.NET

More information:
If you want to convert results from a data source to XML, you can do so easily using the DataSet object and its GetXML method. However, I have found this method slow in many cases. As an alternative, you can loop through a data reader and convert it row by row, field by field, as in this code. Though it involves a lot more code and seems clunky, it seems faster, at least for the resultsets I have had to convert.

Please read the comments, it refers to a few decisions I made; you can easily do things differently if desired. In particular, I use a method that tries to use the CDATA qualifier only if necessary; the problem is that, because I do not know offhand all the characters which require the CDATA escape, there are some cases where it is used unnecessarily. If you want, you can change it to improve its accuracy, to always use CDATA, or to never use it.

Sample Usage

   Dim sConnString As String
        Dim objCmd As SqlCommand
        Dim objReader As SqlDataReader
        Dim objConn As SqlConnection

        sConnString = "Persist Security Info=False;User ID=sa;Password=password;Initial Catalog=mydatabase;Data Source=mySQLServer"
        objConn = New SqlConnection(sConnString)
        objConn.Open()

        objCmd = New SqlCommand("SELECT* FROM MyTable", objConn)
        objCmd.CommandType = CommandType.Text
        objReader = objCmd.ExecuteReader
        Debug.Write(ReaderToXML(objReader, "Data"))
        objReader.Close()
        objConn.Close()

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:

Code: