Convert a .NET DataReader to XML
Category:
C#, VB.NET, ASP.NETType:
SnippetsDifficulty:
BeginningAuthor: 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: