ASP.NET SQL Server存储过程返回消息 [英] ASP.NET SQL Server stored procedure return Message

问题描述

我有一个不需要任何参数的存储过程，并且返回值0和消息:

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)

这是我的.NET方法，它调用存储过程:

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;
}

我希望将消息放入 item.message 中，我该怎么做?

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

推荐答案

在存储过程中，您可以查询 @@ ROWCOUNT ，它将为您提供受影响的记录.现在，您可以使用 SET 或 SELECT 语句(例如

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

或

SELECT MyRecordCount = @@RowCount

或者，如果您在一个过程中有多个操作需要跟踪，则可以创建多个变量并多次调用 SET 或 SELECT 或使用 TABLE 变量，例如.

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

将 @@ ROWCOUNT 的值插入表变量 @recordCount

下一步，要获取此信息，您需要调用从 @recordCount 表中选择的最后一行.

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

最后，您应该在代码中使用数据读取器，而不是使用 ExecuteNonQuery()方法.

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();
        }
    }

}

现在，该消息实际上是受影响的行的整数，而不是术语(98)受影响的行，但是如果您确实希望该确切消息可以按照您的意愿格式化字符串.

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

这篇关于ASP.NET SQL Server存储过程返回消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！