Getting back query results as text

[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.

 

[Cor Ligthert[MVP]]

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

 

[BobRoyAce]

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

table.

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.

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.

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.

 

[Andrew Morton]

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.

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

 

[BobRoyAce]

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

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.

 

[Harry]

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.

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

 

[BobRoyAce]

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?

 

[Harry]

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?

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

 

[BobRoyAce]

Dim Cols As DataColumnCollection = ds.Tables(0).Columns 'ds is a dataset

So, can I not directly go after SqlDataReader for this information?

 

[Armin Zingler]

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

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


Armin

 

[BobRoyAce]

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

Armin

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

 

[Cor Ligthert[MVP]]

Bob,

With a datatable the column.columnname returns that as well.

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.columnname.aspx

Cor