Archive for May, 2007

Running The OUTPUT Clause From C#

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.

2 comments Permalink May 1st, 2007 Adrian Banks
Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post


Calendar

May 2007
M T W T F S S
« Apr   Jul »
 123456
78910111213
14151617181920
21222324252627
28293031  

Posts by Month

Posts by Category

RSS Feeds Feeds

Visitors

Visitor Map