How do I output stored procedure to XML?

M

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,836
Messages
2,569,750
Members
45,545
Latest member
rapter____0

Latest Threads

Top