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

On Oct 8, 2:27*am, "Mark B" <none...@none.com> wrote:
> 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

Thanks very much Alexey.