going from sqlconnection and sqlreader to using a tableadapter and ????

Below is my code right now Im using a direct sqlconnection for each request.? I would like to use Tableadapters and/or my BLL code like the rest of my sites pages.? I cant find how to do this progamatically and using the xmltextwriter. (Also i need to be able to name the xml output nodes like below)

Thanks for the help.

Neil

Private Sub GetAllEvents(ByVal SqlString)

? ? ? ? Response.Clear()

? ? ? ? Response.ContentType = "text/xml"

? ? ? ? Dim objX As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)

? ? ? ? objX.WriteStartDocument()

? ? ? ? objX.WriteStartElement("Events")

? ? ? ? Dim objConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\*********.mdf;Integrated Security=True;User Instance=True")

? ? ? ? objConnection.Open()

? ? ? ? Dim sql As String = SqlString

? ? ? ? Dim objCommand As New SqlCommand(sql, objConnection)

? ? ? ? Dim objReader As SqlDataReader = objCommand.ExecuteReader()

? ? ? ? While objReader.Read()

? ? ? ? ? ? objX.WriteStartElement("Event")

? ? ? ? ? ? objX.WriteAttributeString("EventId", objReader.GetInt32(0))

? ? ? ? ? ? objX.WriteAttributeString("EventName", objReader.GetString(1))

? ? ? ? ? ? objX.WriteAttributeString("EventDescription", objReader.GetString(2))

? ? ? ? ? ? objX.WriteAttributeString("EventDate", objReader.GetDateTime(3))

? ? ? ? ? ? objX.WriteAttributeString("CurrentDate", Date.Now.ToString)

? ? ? ? ? ? If Not objReader.IsDBNull(12) Then

? ? ? ? ? ? ? ? objX.WriteAttributeString("EventImage", objReader.GetString(12))

? ? ? ? ? ? End If

? ? ? ? ? ? objX.WriteEndElement()

? ? ? ? End While

? ? ? ? objReader.Close()

? ? ? ? objConnection.Close()

? ? ? ? objX.WriteEndElement()

? ? ? ? objX.WriteEndDocument()

? ? ? ? objX.Flush()

? ? ? ? objX.Close()

? ? ? ? Response.End()

? ? End Sub

Hi,

If you wish to write all the content of the query result to an Xml, I suggest you do the following step:

1. Use a SqlDataAdapter to fill a DataSet.
2. Call DataSet.WriteXml(REsponse.OutputStream) to output it.

Dim objConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\*********.mdf;Integrated Security=True;User Instance=True")
objConnection.Open()
Dim sql As String = SqlString
Dim DA As New SqlDataAdapter(sql, objConnection)
Dim ds As New DataSet()
DA.Fill(ds)
ds.WriteXml(Response.OutputStream)

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

That works great, However I need to have complete control over which fields are put in nodes and into attributes. We are passing the xml into flash and need to have control over each field. I have pre made table adapters and a BLL that handles all of the "BackEnd" Functions for administration. Trying to use those for the xml output as well(Dont want to dble code). Thanks for the help