Ado.net iterating through each column in row of SqlDataReader

Question

I am writing a simple XML web service and want to return raw XML. I am doing this by creating a string literal that represents the XML. My data source is a SqlDataReader object that contains the results of a stored proc call. The way I am doing it right now is by using a while loop and reading values from the item property like so :

'string builder to create the literal
Dim sb As New StringBuilder()
sb.Append("<?xml version='1.0' encoding='ISO-8859-1'?>")
sb.Append("<members>")

'dr is an instance of SqlDataReader
While (dr.Read())
    sb.Append("<member>")
    sb.Append(String.Concat("<PersonnelID>", dr.Item("PersonnelID"), "</PersonnelID>"))
    sb.Append(String.Concat("<FirstName>", dr.Item("FirstName"), "</FirstName>"))
    sb.Append("</member>")
End While
sb.Append("</members>")

The issue is there are over a dozen fields that are returned from the stored proc and writing each of them out like that seems inefficient. Is there a way to iterate through each row getting the column name and the value, so I could do something like this:

sb.Append(String.Concat("<",ColName,">", dr.Item(ColName), "</",ColName,">"))

Could anyone suggest how I could do this? Thanks!

Tim Schmelter · Accepted Answer · 2012-03-19 15:39:41Z

5

This should work (added the using-statement just for the sake of completeness):

Using dr = cmd.ExecuteReader()
    Dim sb As New System.Text.StringBuilder()
    sb.Append("<?xml version='1.0' encoding='ISO-8859-1'?>")
    sb.Append("<members>")
    While (dr.Read())
        sb.Append("<member>")
        For i = 0 To dr.FieldCount - 1
            sb.Append(String.Format("<{0}>{1}</{0}>", dr.GetName(i), dr(i)))
        Next
        sb.Append("</member>")
    End While
    sb.Append("</members>")
End Using

edited Mar 19, 2012 at 15:39

answered Mar 19, 2012 at 15:24

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ado.net iterating through each column in row of SqlDataReader

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related