Capture SQL print output + result set rows?

  • Thread starter Thread starter Mario T. Lanza
  • Start date Start date
M

Mario T. Lanza

Greetings,

I am developing a SQL Server Query Analyzer replacement app to use in
the field to avoid having to purchase licensed copies for a vast
number of machines.

One of the things that Query Analyzer does is capture the output of
PRINT statements in the Messages area. I have been able to mimic this
by subscribing to the InfoMessages event of the SqlConnection object;
however, this only works when I call the SqlCommand.ExecuteNonQuery
method.

Sometimes the stored procs that I call from within my Query Analyzer
app return rows that I want to capture in a DataGrid. I was able to
do this using the SqlDataAdapter.Fill method to the table bound to the
grid.

The problem here is merging the two functions. I want to BOTH capture
result set rows AND the PRINT output text by making only one call to
the stored proc. I could call the proc twice, once using the
SqlDataAdapter.Fill method and once using the
SqlCommand.ExecuteNonQuery method, but this seems foolish.

1. Any ideas on solving this issue?

2. How about capturing the rows from multiple SELECT statements
executed within a stored proc?

3. Finally, it would be nice to be able to cancel a SqlCommand that is
actively running. I assume this is done with threads? Anybody have a
sample snippet of code?

Thanks!
Mario T. Lanza
Clarity Information Architecture, Inc.
 
Hi Mario:

It's pretty easy, comments inline:

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Mario T. Lanza said:
Greetings,

I am developing a SQL Server Query Analyzer replacement app to use in
the field to avoid having to purchase licensed copies for a vast
number of machines.

One of the things that Query Analyzer does is capture the output of
PRINT statements in the Messages area. I have been able to mimic this
by subscribing to the InfoMessages event of the SqlConnection object;
however, this only works when I call the SqlCommand.ExecuteNonQuery
method.

Sometimes the stored procs that I call from within my Query Analyzer
app return rows that I want to capture in a DataGrid. I was able to
do this using the SqlDataAdapter.Fill method to the table bound to the
grid.

The problem here is merging the two functions. I want to BOTH capture
result set rows AND the PRINT output text by making only one call to
the stored proc. I could call the proc twice, once using the
SqlDataAdapter.Fill method and once using the
SqlCommand.ExecuteNonQuery method, but this seems foolish.

1. Any ideas on solving this issue?
Click to expand...
You can trap the Connection's InfoMessage event.
http://www.knowdotnet.com/articles/connections.html Even though this is
supposed to trap errors with a severity level < 10 and print messages aren't
errors inthe traditional sense, they'll come back with infomessage.
2. How about capturing the rows from multiple SELECT statements
executed within a stored proc?
Click to expand...
If your DB supports batch queries, which it does if you are using SqlServer,
then you the subsequent queries will be in additinoal tables. If you do a
DataSet.Tables.Count you should see that. (So, if you have a batch query,
just call Adapter.Fill(dataset) and then check your table count.
3. Finally, it would be nice to be able to cancel a SqlCommand that is
actively running. I assume this is done with threads? Anybody have a
sample snippet of code?
Click to expand...

Although you may want to use a Thread as well to help make a more responsive
ui (data access apps lend themeselves nicely to multithreading), you can
call SqlCommand's .Cancel method. You'll note that the documentation says
it 'attempts to cancel a the execution..."
http://msdn.microsoft.com/library/d...rfsystemdatasqlclientsqlcommandclasstopic.asp
I recommend reading the Remarks section b/c this isn't a guaranteed process
and it often behaves in ways you may not want - but so does cancelling a
command in QA.

If you have any additional questions, please don't hesitate to let me know.
HTH,

Bill
 
Bill,

Your suggestions worked fine, thanks. Now I call all my SQL COMMANDS
using the SqlDataAdapter.Fill(DataSet) method. Plus I am subscribed
to the SqlConnection.InfoMessage event. Trouble is I want to capture
the "rows affected" values that usually appear in the SQL Server Query
Analyzer. I can get this value when I execute my sql statements via a
SqlCommand.ExecuteNonQuery method, but not with the DataAdapter.

Any ideas?
Mario
 
Fill and Update return integers and will show the records affected although
with fill it may not be what you want b/c it's the nubmer that were changed
if I remember correctly. After fill, you can interrogate
DataTableName.Rows.Count to get the record count. Glad the infomessage
worked for you.

You may also want to read my article(s) on datatable .compute which is
another good way to grab aggregate information like count, sum, avg etc,
especially if you need to filter it on anything.
http://www.knowdotnet.com/articles/expressions.html

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
I apologize for keeping this going when I should have gotten it by
now. Here's the jist the best way I can explain it.

======================================
CREATE PROCEDURE DoTest AS

print 'customers...'
select top 10 * from Customer

print 'customer orders...'
select top 10 * from [CustomerOrder]

print 'updating customer...'
update Customer
set GivenName = 'John'
where Surname = 'Smith' and GivenName = 'Jon'
======================================

If I call this procedure in SQL Query Analyzer I get...

======================================
customers...

(10 row(s) affected)

checks...

(10 row(s) affected)

updating customer...

(1 row(s) affected)
======================================
In addition the grids panel populates two grids with 10 rows each.


I want to mimic these messages exactly. Granted, it's not super
important; my app will work just fine without precise messages;
however, it's more of a thorn in my brain asking, "Why does something
so seemingly easy seem so hard to accomplish?"

Anyway, you've been very helpful. If there's no easy way, thanks for
your help thus far!
Mario
 
Back
Top