vbnet Build up XML generically

Dim dr As SqlDataReader
Dim conn As SqlConnection = New SqlConnection(ConnectionString)
Dim cmd As SqlCommand = New SqlCommand()
Dim sb As New StringBuilder
Dim FieldCount As Int32 = 0
Dim FieldName As String = ""
Dim FieldValue As String = ""

sb.Append("<Header>")
sb.AppendLine()

conn.Open()

With cmd
	.CommandText = "Select * From TableName Where TableID > @ID"
	.Parameters.AddWithValue("@ID", 5)
	.CommandType = CommandType.Text
	.Connection = conn
	dr = .ExecuteReader()
End With

While dr.Read
	'get the number of fields in recordset
	FieldCount = (dr.FieldCount - 1) '0 based

	'loop through fields in this recordset, building up XML elements
	For i = 0 To FieldCount
		'set name/value of field
		FieldName = dr.GetName(i).ToString
		FieldValue = dr(i).ToString
		FieldValue = System.Net.WebUtility.HtmlEncode(FieldValue)

		'build up XML element as: <Name>Value</Name>
		sb.Append("<" & FieldName & ">" & FieldValue & "</" & FieldName & ">")
		sb.AppendLine()
	Next
End While

dr.Close()
cmd.Dispose()
conn.Close()
conn.Dispose()

sb.Append("</Header>")
Generically builds up XML from the contents of a table - the names of each column don't need to be known.

Updated: Saturday 9th October 2010, 21:44pm

There are 0 comments

Leave a comment of your own

Comments are currently closed.