Return rows affected from a Stored Procedure on each INSERT to display in ASP.NET page

Question

I have a stored procedure that contains like 10 different INSERTS, is it possible to return the COUNT of the rows affected on each INSERT to ASP.NET c# page so i can display Stored Procedure process for the client viewing that ASP.NET page?

Answer 1

You need to use following command in the start of your stored procedure:

SET NOCOUNT OFF

In this case SQL server will send text messages ("X rows affected" ) to client in real time after each INSERT/UPDATE. So all you need is to read these messages in your software.

Here is my answer how to do it in Delphi for BACKUP MS SQL command. Sorry I've not enough knowledge in C# but I guess you can do it in C# with SqlCommand class.

Answer 2

On the server side send the message to the client using RAISERROR function with severity 10 (severity higher than 10 causes exception that breaks procedure execution, i.e. transfers execution to the CATCH block, if there is one). In the following example I haven't added error number, so the default error number of 50000 will be used by RAISERROR function. Here is the example:

DECLARE @count INT = 0
DECLARE @infoMessage VARCHAR(1000) = ''

-- INSERT

SET @count = @@ROWCOUNT
SET @infoMessage = 'Number of rows affected ' + CAST(@count AS VARCHAR(10))
RAISERROR(@infoMessage, 10, 0) WITH NOWAIT

-- another INSERT

SET @count = @@ROWCOUNT
SET @infoMessage = 'Number of rows affected ' + CAST(@count AS VARCHAR(10))
RAISERROR(@infoMessage, 10, 0) WITH NOWAIT

On the client side, set the appropriate event handlers, here is an example:

using (SqlConnection conn = new SqlConnection(...))
{
    conn.FireInfoMessageEventOnUserErrors = true;
    conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
    using (SqlCommand comm = new SqlCommand("dbo.sp1", conn) 
           { CommandType = CommandType.StoredProcedure })
    {
        conn.Open(); 
        comm.ExecuteNonQuery();
    }
}

static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    // Process received message
}