Running The OUTPUT Clause From C#

May 1st, 2007 Adrian Banks

SQL Server 2005 introduced a new feature called the output clause. This enables INSERT, UPDATE and DELETE queries to be run, with the original information which has been changed being returned. This is particularly useful if you want to run a query and know what has been changed by it by returning the identites of the modified rows.

The full documentation for the output clause can be found in SQL Server 2005 Books Online.

In trying to use this feature, I could get it to work in a query window, but when trying it using C# and ADO, it was not obvious how to execute the query and return the results because the ExecuteNonQuery() method of SqlCommand only returns the count of the number of rows that have been updated. After a bit of unsuccessful searching, I came across a post by Keyvan Nayyeri with something that gave me an idea:

OUTPUT clause works like a SELECT statement but its usage differs in INSERT, UPDATE and DELETE commands


Switching my code around to run the update query using the ExecuteReader() method of SqlCommand as would be used for a SELECT query proved to be fruitful, enabling the returned result set to be read.

Entry Filed Under: MSDN, C#, SQL Server, .Net


Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

2 Comments Add your own

  • 1. PJ  |  May 8th, 2007 at 14:28

    The problem I find with using ExecuteReader() to capture the OUTPUT clause is that it doesn’t “see” the exceptions raised by RAISERROR in the stored procedure (even with a severity higher than 10)… have you encountered this? For example: if you put a RAISERROR in a stored procedure with a severity of 16, then call the stored procedure using ExecuteReader, you won’t see a SqlException on the ADO.NET side… if you change your code to ExecuteNonQuery() you will.

    Any thoughts??

  • 2. Adrian Banks  |  May 8th, 2007 at 22:49

    I’ve tried this and cannot get the behaviour you have described. Using ExecuteReader() from ADO.Net and calling a stored procedure that raises errors of differing severities produces the following behaviour:

    Severities 1 to 10: No exception caught.
    Severities 11 to 16: SQLException caught.

    This tallies with the information on the MSDN Database Engine Error Severities article.

Leave a Comment

Required

Required, hidden

Please enter the following word: magic word

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Subscribe to the comments via RSS Feed


Calendar

March 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
2728293031  

Most Recent Posts

Related Posts

RSS Feeds Feeds

Visitors

Visitor Map