Convert a .NET DataReader (Any Type) to XML

Category:

C#, VB.NET, ASP.NET

Type:

Snippets

Difficulty:

Intermediate

Author: sanjit tamhane

Version Compatibility: Visual Basic.NET

More information:

This is an extension to the code at http://www.freevbcode.com/ShowCode.asp?ID=5232, which converts a .NET SQLDataReader to XML. The enhancements are:

Output XML data can be bind to DataGrid via Dataset in UI Layer
Using IDataReader rather than SqlReader so can be used against any Data Provider.
CreateCommand can be used against any data provider without parameters

User the CreateDataReader function below to obtain an IDataReader object to pass to the ReaderToXML function.

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

Declarations:
' (None)

Code:

Public Function CreateDataReader(ByVal objConnection _ 
   As IDbConnection, ByVal typeCommand As Type, _
   ByVal strCommandText As String, ByRef strError As String) _
   As IDataReader

Dim objIDataReader As IDataReader

Dim objcmd As IDbCommand

Try

objcmd = Me.CreateCommand(typeCommand, strCommandText, _
          objConnection)

objIDataReader = CType(objcmd.ExecuteReader _
             (CommandBehavior.CloseConnection), IDataReader)

Return objIDataReader

Catch e As Exception

strError = e.Message

Return Nothing

End Try

End Function

Public Function ReaderToXML(ByVal objReader As IDataReader, _
                    ByRef strError As String, _
                    Optional ByVal ParentNodeName As String = "") As String
        'If ParentNodeName is not blank, it will be used as
        'Start End node of the XML

Dim sXML As String
        Dim intColumnCount As Integer
        Dim intCtr As Integer
        Dim sValue As String

ParentNodeName = Trim(ParentNodeName)
        Try
            intColumnCount = objReader.FieldCount
            If ParentNodeName <> "" Then sXML += "<" & ParentNodeName & ">"

Do While objReader.Read
                sXML += "<Table>"

'Loop through each row
                For intCtr = 0 To intColumnCount - 1
                    'Get the Value of each column
                    'Does not include nodes for null/blank values

If Not objReader.IsDBNull(intCtr) Then
                        sValue = objReader.Item(intCtr).ToString
                        If Trim(sValue) <> "" Then
                            sXML += "<" & objReader.GetName(intCtr) & ">" & XMLizeString(sValue) & ""
                        End If
                    End If
                Next
                sXML += "</table>"

Loop
            If ParentNodeName <> "" Then sXML += ""

Catch Ex As Exception
            sXML = ""
            strError = Ex.Message
        End Try
        Return sXML
    End Function

Private Function XMLizeString(ByVal sInput As String) As String
        Dim s As String

'SHORTENED VERSION TO REDUCE EXECUTION TIME
        'Return "