The returned rows affected by the Stored Procedure on each INSERT to display on the ASP.NET page

Question

I have a stored procedure containing as many as 10 different INSERTS, is it possible to return the COUNT lines affected on each INSERT page in ASP.NET C # so that I can display the stored procedure process for the ASP.NET client to view the page?

Answer 1

On the server side, send a message to the client using the RAISERROR function with a severity of 10 (a severity above 10 throws an exception that interrupts the procedure, i.e. passes the execution to the CATCH block, if any). In the following example, I did not add an error number, so the default error number of 50000 will be used by the RAISERROR function. Here is an 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, install 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 } 

Answer 2

After each tab, use @@ ROWCOUNT , then get the value on request.

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

Example:

 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 can you get @@rowcount with multiple requests? Here is 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

You need to use the following command at the beginning of the stored procedure:

SET NOCOUNT OFF

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

Here is my answer on how to do this in Delphi for the BACKUP MS SQL command. Sorry, I don't have enough knowledge in C #, but I think you can do it in C # with the SqlCommand class.