SQL Script Example - Return Row Count with Stored Procedure and C#

This is a simple stored procedure that does an update and returns a row count.

CREATE PROCEDURE [dbo].[ChangeItemValue]

   @rowid int,
   @newvalue varchar(255),
   @rowcount int OUT
AS
BEGIN
   UPDATE [myTable] SET [myCol]=@newvalue WHERE [id]=@rowid;
   SET @rowcount=@@ROWCOUNT;
   RETURN @@ROWCOUNT;
END

This is a sample of some C# code that calls the stored procedure.  You will need to add some "using" statements for System.Data and System.Data.SqlClient.  The code below is abbreviated (does not contain everything on how to connect, declarations, try...catch blocks, etc.

   //Create your connection object.....(code abbreviated)
   conn.open ....

   SqlCommand cmd = new SqlCommand("ChangeItemValue", conn);
   cmd .CommandType = CommandType.StoredProcedure;
   cmd .Parameters.Add(new SqlParameter("@rowid", 3));
   cmd .Parameters.Add(new SqlParameter("@newvalue", "my new value!"));

   SqlParameter rowsAffected = new SqlParameter("@rowcount", SqlDbType.Int);

   rowsAffected.Direction = ParameterDirection.Output;
   cmd .Parameters.Add(rowsAffected);

   cmd .ExecuteNonQuery();

   Int32 result = (int)rowsAffected.Value;