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?

Answer 1

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:

Answer 2

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))