DBConcurrency and set nocount

  • Thread starter Thread starter Peter Zuber
  • Start date Start date
P

Peter Zuber

Dear NG

A prerequisite to handle dbconcurrency when using DataAdapters and Stored
Procedures is to SET NOCOUNT OFF in the an Update SP for example. As a
consequence, when the update statement fails due to a concurrency issue, the
record count returned will be zero. The DataAdapter will interpret this
condition as a concurrency conflict and throw a DBConcurrencyException.

My question now: As Microsoft recommends in general to SET NOCOUNT ON in
stored procedures we have an argument with our database guys. They don't
want to SET NOCOUNT OFF. How do you handle the issue. Do you SET NOCOUNT OFF
for update and delete statements or do you handle the concurrency issue in
the stored procedures on your own (raising an error...)
And what is the reason for the recommendation to SET NOCOUNT OFF in general
anyway? Performance?

Please let me know your experiences

Thank you in advance

Cheers,
Peter
 
Peter said:
A prerequisite to handle dbconcurrency when using DataAdapters and Stored
Procedures is to SET NOCOUNT OFF in the an Update SP for example. As a
consequence, when the update statement fails due to a concurrency issue, the
record count returned will be zero. The DataAdapter will interpret this
condition as a concurrency conflict and throw a DBConcurrencyException.

My question now: As Microsoft recommends in general to SET NOCOUNT ON in
stored procedures we have an argument with our database guys. They don't
want to SET NOCOUNT OFF. How do you handle the issue. Do you SET NOCOUNT OFF
for update and delete statements or do you handle the concurrency issue in
the stored procedures on your own (raising an error...)
And what is the reason for the recommendation to SET NOCOUNT OFF in general
anyway? Performance?
Click to expand...

SET NOCOUNT ON is done for performance as there isn't any message
reported back. I've never seen it make a lot of differences though.

However you have a conflict about functionality. You want concurrency
checks to be recognized by your ADO.NET layer. To get that accomplished
you need things on the DB server, i.e. SET NOCOUNT OFF. If you NOCOUNT
is set to ON, your concurrency code won't work, it's that simple.

If you need to write something to a file and you don't get permission
to do so, you can't write to the file. The counting mechanism is meant
to KNOW how many rows are affected.

So if your database guys refuse to switch NOCOUNT off, you can't
implement concurrency checks based on how many rows are affected. I
wouldn't know any other way to test how many rows are affected without
requerying and other hacks.

Frans

--
 
Back
Top