Getting back query results as text

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

BobRoyAce

If I run a query in SQL Management studio, I can set options such that
I can see the results returned by the query as text at the bottom of
the form. Sample results could be as follows:
column headers, followed by blank line, followed by "(0 row(s)
affected)" for SELECT query that returns nothing
column headers, followed by rows of data for records that match
SELECT criteria
"(0 row(s) affected)" for UPDATE query that affects nothing
etc.

What I want to be able to do is have a Function that will run a query,
based on passed in SQL, and then return a String containing the
results, as described above. I know how to execute a query, just
wondering if there's an easy way to get the results as a string.

The DB that I am running against is SQL Server 2005.
 
Bob,

I can be wrong, but in my idea don't you see the result as string but as a
table.

To set a table to a string you need to create that.

Using a simple datatable would do this with normal looping through the rows
and columns.
I assume that you are able to do that yourself as this is very simple.

Cor
 
I can be wrong, but in my idea don't you see the result as string but as a
table.
Click to expand...

Actually, in SQL Server Management Studio (SSMS), you can see the
result set as a table OR as text. I'm sure that SSMS is generating
that text representation of the results somehow...just want to know an
easy way to do it myself...generically.
To set a table to a string you need to create that.
Click to expand...

Unless there's some method, or routine, that I can call to get it.
Using a simple datatable would do this with normal looping through the rows
and columns.
I assume that you are able to do that yourself as this is very simple.
Click to expand...

Was just wondering if there is some already created method of some
object, or some routine already written, that would facilitate
this...rather than doing more work than necessary, and/or reinventing
the wheel.
 
BobRoyAce said:
Actually, in SQL Server Management Studio (SSMS), you can see the
result set as a table OR as text. I'm sure that SSMS is generating
that text representation of the results somehow...just want to know an
easy way to do it myself...generically.


Unless there's some method, or routine, that I can call to get it.


Was just wondering if there is some already created method of some
object, or some routine already written, that would facilitate
this...rather than doing more work than necessary, and/or reinventing
the wheel.
Click to expand...

It really is as simple as reading all the results and appending them to a
StringBuilder

While sqlreader.Read()
myStringBuilder.Append(sqlreader.GetString(0))
myStringBuilder.Append(someSeparator)
End While

Andrew
 
It really is as simple as reading all the results and appending them to a
StringBuilder

While sqlreader.Read()
    myStringBuilder.Append(sqlreader.GetString(0))
    myStringBuilder.Append(someSeparator)
End While
Click to expand...

Yea, but the problem is that I want to create a generic function that
can do this independent of how many columns may be passed back. Doing
it your way would require me to first determine how many columns there
are, then loop through, appending each to the next until get to next
row, and so on. I was just wondering if there was a simple method call
that I could instead. For example, sqlreader.GetResultsAsText.
 
BobRoyAce said:
If I run a query in SQL Management studio, I can set options such that
I can see the results returned by the query as text at the bottom of
the form. Sample results could be as follows:
column headers, followed by blank line, followed by "(0 row(s)
affected)" for SELECT query that returns nothing
column headers, followed by rows of data for records that match
SELECT criteria
"(0 row(s) affected)" for UPDATE query that affects nothing
etc.

What I want to be able to do is have a Function that will run a query,
based on passed in SQL, and then return a String containing the
results, as described above. I know how to execute a query, just
wondering if there's an easy way to get the results as a string.

The DB that I am running against is SQL Server 2005.
Click to expand...

Have a look at the ADO.Net ExecuteNonQurey as a starting point:

For UPDATE, INSERT, and DELETE statements, the return value is the number of
rows affected by the command. When a trigger exists on a table being
inserted or updated, the return value includes the number of rows affected
by both the insert or update operation and the number of rows affected by
the trigger or triggers. For all other types of statements, the return value
is -1. If a rollback occurs, the return value is also -1.

Just make sure that in your stored procedure to: SET NOCOUNT OFF

For simple datasets, the Rows.Count has some significance
 
Perhaps I'm not making myself clear. Suppose that I have a
SqlDataReader that has been populated with the results of a query.
Before I list the results that came back (i.e. rows of data), I want
to list the names of the fields/columns that are included. How can I
grab and list those?
 
BobRoyAce said:
Perhaps I'm not making myself clear. Suppose that I have a
SqlDataReader that has been populated with the results of a query.
Before I list the results that came back (i.e. rows of data), I want
to list the names of the fields/columns that are included. How can I
grab and list those?
Click to expand...

Dim Cols As DataColumnCollection = ds.Tables(0).Columns 'ds is a dataset
Dim sb As New System.Text.StringBuilder
Dim i As Integer = 0

'------------------
'write a header row
'------------------
For Each col As DataColumn In Cols

If (iter > 0) Then

sb.Append("," & col.ColumnName)

Else

sb.Append(col.ColumnName)

End If

iter += 1

Next
 
Harry said:
Dim Cols As DataColumnCollection = ds.Tables(0).Columns 'ds is a
dataset Dim sb As New System.Text.StringBuilder
Dim i As Integer = 0

'------------------
'write a header row
'------------------
For Each col As DataColumn In Cols

If (iter > 0) Then

sb.Append("," & col.ColumnName)

Else

sb.Append(col.ColumnName)

End If

iter += 1

Next
Click to expand...

That's from a datatable, not from a datareader. With the datareader, the
GetName method returns the name of the specified column.


Armin
 
That's from a datatable, not from a datareader. With the datareader, the
GetName method returns the name of the specified column.

Armin
Click to expand...

Thanks, Armin...GetName is what I needed to get the fieldnames.
 
Back
Top