ASP.NET SQL Server stored procedure return Message

Question

I have a stored procedure that does not require any parameters and the returns the value 0 and the messages:

(94 row(s) affected)

(1 row(s) affected)

My question is how do I get the messages:

(94 row(s) affected)

(1 row(s) affected)

This is my .NET Method that calls the stored procedure:

public List<MessageClass> ChequesToUpdate()
{
    message = new List<MessageClass>();

    MessageClass item = new MessageClass();

    try
    {
        using (connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand("MyStoredProcedure", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                command.ExecuteNonQuery();

                item.message = "message";
            }
        }
    }
    catch (Exception e)
    {
        item.message = e.Message;
    }
    finally
    {
        connection.Close();
    }

    message.Add(item);

    return message;
}

I am looking to put the message in item.message, how would I accomplish this?

This is generally not how stored procedures work: you should try to limit business logic to your C# code. The sproc should do whatever it needs to do and you decide what kind of message to generate based on the returned scalar value or table values. That being said, you could do something like `select 'it worked' as message` in your stored procedure, and query the `message` column in your code. — Arian Motamedi, Mar 01 '17 at 21:34
the messages you are talking about are generated by the execution program like sql management studio etc. why dont you use return value of command.executeNonquery() like var recordsAffected=command.executenonquery() and set message=$"{recordsAffected} records affected" — Gurpreet, Mar 05 '17 at 22:55
FYI: `SqlConnection.InfoMessage` works like a charm with `print` in T-SQL, but unfortunately not with these messages. — Julian, Mar 06 '17 at 21:52
You want to return the number of rows affected by the command and save it to an int variable but since the type of statement is `select` so it returns `-1`. Read this post to know why:http://stackoverflow.com/a/38060528/2946329 — Salah Akbari, Mar 07 '17 at 06:10

score 4 · Answer 1 · edited Mar 12 '17 at 16:34

ExecuteNonQuery returns the total number of affected row(s). So, if you want just total number of row(s) then simply you can get it using below statement:

var x = command.ExecuteNonQuery();

Otherwise, you have to use user define RAISERROR message in the stored procedure and catch it from C# connection.InfoMessage event. I've setup a test environment and tested it. I've created a table and inserted some data to check my SQL & C# code. Please check below SQL & C# code.

SQL:

Create Table psl_table
(
    [values] NVarChar(MAX)
)

Insert Into psl_table Values('a')
Insert Into psl_table Values('a')
Insert Into psl_table Values('a')
Insert Into psl_table Values('b')
Insert Into psl_table Values('b')
Insert Into psl_table Values('b')
Insert Into psl_table Values('b')
Insert Into psl_table Values('b')
Insert Into psl_table Values('b')
Insert Into psl_table Values('b')

Create Proc MyStoredProcedure
    As
Begin
    -- Declare a variable for Message
    Declare @Msg NVarChar(MAX)

    -- 1st SQL Statement
    Update psl_table Set [Values]='a' Where [Values]!='a'

    -- Generate the message and print that can get from C#
    Set @Msg = '(' + Convert(NVarChar,@@RowCount) + ' row(s) affected)'
    RAISERROR( @Msg, 0, 1 ) WITH NOWAIT

    -- 2nd SQL Statement
    Update psl_table Set [Values]='a'

    -- Generate the message and print that can get from C#
    Set @Msg = '(' + Convert(NVarChar,@@RowCount) + ' row(s) affected)'
    RAISERROR( @Msg, 0, 1 ) WITH NOWAIT
End

In this SQL, I've declared a variable @Msg to store message and built-in function RAISERROR to throw the message.

C# CODE:

public List<MessageClass> ChequesToUpdate()
{
    message = new List<MessageClass>();

    MessageClass item = new MessageClass();

    try
    {
        using (connection = new SqlConnection(connectionString))
        {
            connection.Open();

            connection.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
            {
                item.message = e.Message;
            };

            using (SqlCommand command = new SqlCommand("MyStoredProcedure", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                command.ExecuteNonQuery();
            }
        }
    }
    catch (Exception e)
    {
        item.message = e.Message;
    }
    finally
    {
        connection.Close();
    }

    message.Add(item);

    return message;
}

I've modified your code for desired output. I've used connection.InfoMessage event to catch message that throw from SQL.

For testing purpose I printed output in Console.

Output:

*ExecuteNonQuery return total number of affected row(s)* Not if the type of statement is `select` because for `select` the return value is `-1` look here http://stackoverflow.com/a/38060528/2946329 — Salah Akbari, Mar 08 '17 at 15:27
Yes, you are correct but when `SET NOCOUNT` is `ON`. Please check this link: [SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete](https://blogs.msdn.microsoft.com/spike/2009/01/27/sqlcommand-executenonquery-returns-1-when-doing-insert-update-delete/) — csharpbd, Mar 08 '17 at 16:54

score 3 · Accepted Answer · answered Mar 01 '17 at 21:39

In your stored procedure you can query the @@ROWCOUNT which will give you the records affected. Now you can then store this into a variable using a SET or SELECT statement such as

SET MyRecordCount = @@RowCount

or

SELECT MyRecordCount = @@RowCount

Alternatively if you have multiple operations in a single procedure you need to track you can either create multple variables and call the SET or SELECT multiple times or use a TABLE variable such as.

DECLARE @recordCount table (Records int not null)

--RUN PROCEDURE CODE

INSERT INTO @recordCount VALUES (@@ROWCOUNT)

--RUN MORE PROCEDURECT CODE

INSERT INTO @recordCount VALUES (@@ROWCOUNT)

--RETURN THE Row Count
SELECT Records FROM @recordCount

Where this will insert the value of @@ROWCOUNT into a table variable @recordCount

Next to get this information out you will need to call the last line select from the @recordCount table.

Finally in your code instead of using the ExecuteNonQuery() method you should use a data reader as.

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("MyStoredProcedure", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                item.message = reader.GetString(0);
            }
            reader.Close();
        }
    }

}

Now the message is actually an integer of the rows affected not the term (98) row affected but if you really wanted that exact message could just format the string as you wish.

item.message = string.Format("({0}) rows affected", reader.GetInt32(0))

Solomon Rutzky · Answer 3 · 2017-03-08T15:16:10.960

You can't capture those specific messages (i.e. (94 row(s) affected)) since they are not being sent by SQL Server; they are being sent by the client program, either SQLCMD or SSMS. However, yes, you can capture the row counts, and without needing to add RAISERROR or PRINT statements to your Stored Procedure(s). Besides, you (or many others reading this question) might not even have the ability to update the Stored Procedure(s) to inject the extra PRINT / RAISERROR / SELECT statements that output the row counts.

But first, two warnings about some of the other suggestions made here:

You might not be able to use the return value of ExecuteNonQuery as it doesn't return values for non-DML statements, including SELECT statements. It only returns values from INSERT, UPDATE, and DELETE statements, as stated here SqlCommand.ExecuteNonQuery:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. ... For all other types of statements, the return value is -1.

The question does not state what types of queries are being executed, so it is possible that you are only using these DML statements. Still, everyone should be aware of what is (and is not) reflected by this return value.
You might not want to capture the print messages via an InfoMessage handler as that would require parsing to ensure that the message being captured isn't some other message, such as a warning or other info.

That being said, you want to set up a SqlCommand.StatementCompleted event handler as it passes along the rows affected for each statement (RecordCount is an int).

internal static void StatementCompletedHandler(object Sender,
StatementCompletedEventArgs EventInfo)
{
    // do something with EventInfo.RecordCount

    return;
}

And then just attach it to the SqlCommand object:

_Command.StatementCompleted += StatementCompletedHandler;

If working in ASP.NET restricts your ability to stored the returned value in a static variable (because it is shared and might not be thread safe), then you can define the handler inline as an anonymous delegate, in which case it would be able to access an instance variable declared within your ChequesToUpdate method.

score 0 · Answer 4 · answered Mar 07 '17 at 02:34

0

Every end of the statement in stored procedure, Insert @@rowcount in a table. Then fetch it back to your Method using dataset.

answered Mar 07 '17 at 02:34

Spythere

55
4

isn't this the exact same as I noted in an answer over a week ago? – Nico Mar 07 '17 at 22:56

score 0 · Answer 5 · edited May 23 '17 at 12:25

0

You will not be able to get the rows affected messages in C# by default. Instead you need to modify your procedure to PRINT/RAISERROR the messages, so you will be able to access them in C# using SqlConnection.InfoMessage as mentioned by @Julian.

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

edited May 23 '17 at 12:25

Community

1
1

answered Mar 07 '17 at 10:14

Venu

455
2
7

score 0 · Answer 6 · answered Mar 08 '17 at 05:35

I would return @@rowcount from procedure and in .net code i would read that value.

Add return (@@rowcount) in your procedure as below

create procedure testprocedure
as
begin
    update test1 set id =100
    return(@@rowcount)
end

And your .net code looks below with parameter direction

    SqlConnection conn;
        using(conn = new SqlConnection(@"Data Source=;Initial Catalog=;User ID = sa;Password="))
        {
            conn.Open();

            using (SqlCommand command = new SqlCommand("testprocedure", conn))
            {
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter retValue = command.Parameters.Add("return", SqlDbType.Int);
                retValue.Direction = ParameterDirection.ReturnValue;
                command.ExecuteNonQuery();
                Console.WriteLine("no of records affected " + retValue.Value);
                Console.ReadLine();
            }
        }

ASP.NET SQL Server stored procedure return Message

6 Answers6

Linked