SQL FOR XML Query results to Text via ASP.NET

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

As a workaround to a problem I'm having with the results
pane in Query Analyzer properly displaying a text field in
a Latin1_General_Bin collation database, I've begun trying
to use ASP.NET to return the results of the query in Text
for use as a one time XML file build. The query is a FOR
XML Explicit.

So far, I've tried sending the results of the query via a
stored procedure to a datagrid. However this cuts off
much of the results due the column and row limitation in
the grid. What I would simply like to do is some kind of
response.write of the results of the query as text
directly in the browser or in a very large text box rather
than the datagrid. Then I can copy/paste to a text file
and work with the results there.

The code follows below:

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim cmd As System.Data.SqlClient.SqlCommand
cmd = New System.Data.SqlClient.SqlCommand
("RobXML", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
DataGrid1.DataSource = myDataReader
DataGrid1.DataBind()
myDataReader.Close()
Me.SqlConnection1.Close()
End Sub

Private Sub DataGrid1_SelectedIndexChanged(ByVal
sender As System.Object, ByVal e As System.EventArgs)

End Sub
End Class
 
I tried the code below from the help files to read through
the datareader and print the results, however, I get
a "timeout" error. Is there a way to use Response.Write
instead? The query results are large. Thank you in
advance.

If myDataReader.HasRows Then
Do While myDataReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab
& "{1}", myDataReader.GetInt32(0), myDataReader.GetString
(1))
Loop
Else
Console.WriteLine("No rows returned.")
End If
 
I doubt this is the best way to do it, but what I came up
with was to hide the XML in an HTML Comment then edit the
file deleting the HTML stuff and keep the XML results. If
anyone has a better solution, I would be interested.
Thank you.

Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim cmd As System.Data.SqlClient.SqlCommand
cmd = New System.Data.SqlClient.SqlCommand
("STSspXMLHistory", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
Me.SqlConnection1.Open()
Dim myDataReader As
System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
(CommandBehavior.CloseConnection)
Response.Write("<!--")
If myDataReader.HasRows Then
Do While myDataReader.Read()
Response.Write(myDataReader.GetValue
(0).ToString)
Loop
Else
Response.Write("No rows returned.")
End If
Response.Write("-->")

'DataGrid1.DataSource = myDataReader
'DataGrid1.DataBind()

myDataReader.Close()
Me.SqlConnection1.Close()
End Sub
 
Back
Top