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
}
