Run a SQL UPDATE command and return the number of affected records

I wanted a quick way to run a SQL UPDATE command and return the number of affected rows in .NET.  Here’s the code to do it:

int RecordCount = 0;
string SQL = "UPDATE MyTable SET MyField = @MyNewValue "
    + "WHERE MyKey = @MyKey "
    + "SET @RecordCount = @@ROWCOUNT";
SqlCommand MyCmd = new SqlCommand(SQL, MyConn);

// Use an out parameter to get the number of rows affected
SqlParameter RecordCountParam = new SqlParameter("@RecordCount", SqlDbType.Int);
RecordCountParam.Direction = ParameterDirection.Output;
MyCmd.Parameters.Add(RecordCountParam);

// Add the query parameters
MyCmd.Parameters.AddWithValue("@MyNewValue", MyNewValue);
MyCmd.Parameters.AddWithValue("@MyKey", MyKey);

// Run the query
MyCmd.ExecuteNonQuery();

// Check the number of records affected
if (Int32.TryParse(RecordCountParam.Value.ToString(), out RecordCount)
{
	// Do Stuff Here
}
Advertisements

2 thoughts on “Run a SQL UPDATE command and return the number of affected records

    1. Most of the time, but there’s a big caveat:

      “When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers.”

      http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.85).aspx

      That’s bit me a few times before! Should have mentioned it in the original post. @@ROWCOUNT is scope-safe and triggers run under different scope, ExecuteNonQuery() ignores such distinctions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s