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

After every Inserts, use @@ROWCOUNT, then get the value by query.

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Sample:

USE AdventureWorks2012;
GO
UPDATE HumanResources.Employee 
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
GO

Edit: How you can get @@rowcount with multiple query? Here's an example:

DECLARE @t int
DECLARE @t2 int
SELECT * from table1
SELECT @t=@@ROWCOUNT
SELECT * from table2
SELECT @t2=@@ROWCOUNT
SELECT @t,@t2'

Answer 3

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
}