Retrieving Rows Affected Counts

D

Doug

I would like to pass the following commands to SQL 2005 using ADO.NET
SQLCommand in VS2005:

SELECT * FROM Table1 WHERE Column1 = 'SomeValue';
SELECT * FROM Table2;
UPDATE Table1 SET Column1 = 'something' WHERE Column2 = 'SomeOtherValue';
UPDATE Table2 SET Column1 = 1234 WHERE Column2 = 'YetAnotherValue'
etc...

I get back the result sets OK but what I am having trouble finding is how
many rows were affected by each individual UPDATE statement.

I know I can add @@ROWCOUNT to the script above but the commands are entered
in an interface similiar to Mgmt Studio and would like to display something
like the Messages tab in Mgmt Studio. The commands are passed to a remote
SQL Server instance, executed, and the results are returned via a web service.

It seems like the SqlDataReader.RecordsAffected property will give me a
count of rows affected, but it is a sum of all UPDATE (or DELETE) statements
that executed for the whole script. So I would have to divide out the
separate commands and execute them separately to use that method. I am
wondering if there is another way...

TIA!
 
W

William Vaughn \(MVP\)

I would probably put these commands into a stored procedure and pass back
the counts (or other data) in OUTPUT parameters.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
D

Doug

Thanks for the response but the commands are passed adhoc style so they will
be different every time (again, like you might enter into Mgmt Studio). Any
results that have a data set associated with it (like SELECT commands) I
obviously don't have a problem with. But for UPDATE and DELETE statements, I
would like to respond to the user how many rows were affected by each of the
commands.

I've found other threads talking about using SET NOCOUNT ON to *prevent*
this information from being returned, but I can't seem to find anything
specific on how to get the information if you really want it. In BOL, SET
NOCOUNT ON refers to preventing DONE_IN_PROC messages but I can't figure out
how to access these DONE_IN_PROC messages with SET NOCOUNT OFF.
 
S

sloan

William is correct.

If you want the granularity you are speaking of (instead of a "Grand Total")
you will need to send back the data as an output parameter, one for ~each
UPDATE you're interested in.
 
D

Doug

I guess I am hoping someone can shed some light on how to access the
DONE_IN_PROC messages referred to in BOL and other ADO posts (if that is even
possible).

I understand breaking out the commands and sending an OUTPUT parameter back
for each one but I would like to allow the user to type in multiple
consecutive statements on the front end without having to try and parse
through the command script on the backend to insert OUTPUT parameters
everywhere. Somehow, Mgmt Studio is doing this.
 
D

Doug

Thanks Sloan! That looks like what I was looking for. I'll give it a try
and post my results.
 
D

Doug

Well, I guess I'm not like all the others. :)

InfoMessage event seems to just catch error messages (which is actually good
to know how to get the error details but not specifically what I am looking
for here). However, it does give me some hope that there is a way to get
what I want.

Please let me know if you have any other ideas or thoughts! Thanks!
 
W

William Vaughn \(MVP\)

I don't like the answer here despite I don't agree with your approach. I
would never let users enter SQL commands and just execute them ad hoc. I'm
investigating why SSMS can show the correct number of resultsets, rowsets
and correct (unsummarized) rows affected counts.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
S

sloan

I still think an output parameter for each update you're interested in is
the most clearcut solution.
 
D

Doug

I found a solution! To get back the row count for each T-Sql statement
(SELECT, UPDATE, DELETE), I am using the SqlCommand.StatementCompleted event.
Works exactly as expected.

I don't know why I didn't see this before but your suggestion for the event
on SqlConnection caused me to take a second look at both objects. I also
plan to use the SqlConnection.InfoMessage event as things like 'print' come
across on that event along with detail error messages.

Anyways, thanks sloan for the push in the right direction. Much appreciated!
 
D

Doug

The "users" are database support personnal not application "users". Anyways,
I found that SqlCommand.StatementCompleted event gives me the records
affected for each T-Sql statement in the script, which is what I was after.

Thanks for taking a look at this William!
 
C

Cor Ligthert[MVP]

Doug,

I would use AdoNet or Linq to SQL.

Then is only send over the line what has to be updated and is that not done
at SQL server side

And then you know even before it is done how many rows are affected

Cor
 
S

sloan

Well, I actually learned something today.
I didn't know about that event.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand_events.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.statementcompleted.aspx

Here is the EventArgs (for future googlers out there)
http://msdn.microsoft.com/en-us/library/system.data.statementcompletedeventargs_members.aspx

And now I see why. I was messing around with InfoMessage in the 1.1
framework, and this is new to 2.0 (even though I've been doing 2.0/3.0 work
for over 2 years now).

Thanks for posting your find.
 
S

Stephan Unrau

When doing either INSERT or UPDATE with a command, you can retrieve the rows affected by calling ExecuteNonQuery() and checking the return value

Example:

conn = New MySqlConnection(Me.strConnString)
cmd = conn.CreateCommand()
cmd.CommandText = strSQL
conn.Open()
iRowsUpdated = cmd.ExecuteNonQuery()



William Vaughn \(MVP\) wrote:

Re: Retrieving Rows Affected Counts
19-Feb-09

I would probably put these commands into a stored procedure and pass back
the counts (or other data) in OUTPUT parameters

--
_________________________________________________________________________
William R. Vaugh
President and Founder Beta V Corporatio
Author, Mentor, Dad, Grandp
Microsoft MV
(425) 556-9205 (Pacific time
Hitchhiker???s Guide to Visual Studio and SQL Server (7th Edition
http://betav.com http://betav.com/blog/billv
___________________________________________________________________________________________



EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

XML to RTF parsing 2
Getting RowCounts for Multiple Statements 1
retrieving individual row counts from a stored procedure 5
Complex Queries 1
Update table with SQL 5
Identity fields being null 4
T-SQL Return row affected count in CLR proc? 9
Access TextBox.Text inside a Dynamically Created Table (Code behind = vb) 1

Top