How do I output stored procedure to XML?

[Mark B]

What amendments to the ASP.NET 3.5. VB code below output the MSSQL stored
procedure dataset result to XML (as a response stream)?

(I also guessing to use Response.ContentType = "text/xml"):


Imports System.Data.SqlClient
Imports System.Data

Partial Class pages_default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

'Output stored procedure result as XML
'--------------------------------------------

Dim sqlConnection1 As New
SqlConnection(sfGeneral.fGetConnectionString())
Dim cmd As New SqlCommand

Dim dr As SqlDataReader = Nothing
cmd.Connection = sqlConnection1
cmd.CommandText = "uspXMLSource"

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@EnterLanguageCode",
Session("strLanguageSetting"))

Try
sqlConnection1.Open()
dr = cmd.ExecuteReader
If dr.HasRows Then
dr.Read()
End If
Catch ex As SqlException
Throw ex
Finally
sqlConnection1.Close()
sqlConnection1.Dispose()
End Try
End Sub
End Class

 

[Guest]

What amendments to the ASP.NET 3.5. VB code below output the MSSQL stored
procedure dataset result to XML (as a response stream)?

(I also guessing to use Response.ContentType = "text/xml"):

Imports System.Data.SqlClient
Imports System.Data

Partial Class pages_default
    Inherits System.Web.UI.Page

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

        'Output stored procedure result as XML
        '--------------------------------------------

        Dim sqlConnection1 As New
SqlConnection(sfGeneral.fGetConnectionString())
        Dim cmd As New SqlCommand

        Dim dr As SqlDataReader = Nothing
        cmd.Connection = sqlConnection1
        cmd.CommandText = "uspXMLSource"

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@EnterLanguageCode",
Session("strLanguageSetting"))

        Try
            sqlConnection1.Open()
            dr = cmd.ExecuteReader
            If dr.HasRows Then
               dr.Read()
            End If
        Catch ex As SqlException
            Throw ex
        Finally
            sqlConnection1.Close()
            sqlConnection1.Dispose()
        End Try
    End Sub
End Class

Use DataSet, it can retrieve XML from relational data.

Example:

Dim ds As New dataset
Dim connStr As String = "database=NorthWind;Data
Source=localhost;User id=sa;password=sa"
Using conn As New SqlConnection(connStr)
Dim command As New SqlCommand("select * from customers",
conn)
conn.Open()
ds.DataSetName = "Customers"
ds.Load(command.ExecuteReader(),
LoadOption.OverwriteChanges, "customers")
Response.ContentType = "text/xml"
ds.WriteXml(Response.OutputStream)
End Using

 

[Mark B]

Thanks very much Alexey.