返回c#中存储过程中的多个值

I need to return 2 values from stored procedure in my application. Below is the code snippet in my application. I need to get the values of SureveyID & InputID below after the respective insert statements.

我需要在应用程序中返回存储过程中的两个值。下面是我的应用程序中的代码片段。在分别插入语句之后，我需要获取suyid & InputID的值。

        int surveyId = 0;
        int inputId = 0;    
        SqlDataManager manager = new SqlDataManager();
        manager.AddParameter("@Name", surveyInstance.SurveyName);
        manager.AddParameter("@Type", surveyInstance.SurveyType);
        manager.AddParameter("@UserId", surveyInstance.UserId);
        manager.AddParameter("@InputType", surveyInstance.InputType);
        manager.AddParameter("@DisplayName", surveyInstance.DisplayName);
        manager.AddOutputParameter("@SurveyID",System.Data.DbType.Int32,surveyId);
        manager.AddOutputParameter("@InputID", System.Data.DbType.Int32,inputId);
        manager.ExecuteNonQuery("pr_CreateSurvey");

AddParameter & AddOutputParameter is custom method as below

AddParameter & AddOutputParameter是自定义方法，如下所示

 public void AddParameter(string parameterName, DbType parameterType, object        parameterValue)
    {
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = parameterName;
        parameter.DbType = parameterType;
        parameter.Value = parameterValue;
        parameters.Add(parameter);
    }

public void AddOutputParameter(string parameterName, DbType parameterType, object parameterValue)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = parameterName;
            parameter.DbType = parameterType;
            parameter.Value = parameterValue;
            parameter.Direction = ParameterDirection.Output;
            parameters.Add(parameter);
        }

Below is code snippet from stored procedure

下面是存储过程的代码片段

ALTER PROCEDURE [dbo].[pr_CreateSurvey] 
    -- Add the parameters for the stored procedure here
    @Name varchar(50),@Type varchar(50),@UserId varchar(50),@InputType varchar(50),@DisplayName varchar(50),
    @SurveyID int OUTPUT,@InputID int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    Insert into surveys(name,user_id,display_name,type) values(@Name,@UserId,@DisplayName,@Type)
    SET @SurveyID = SCOPE_IDENTITY()
    Insert into input_types(name) values (@InputType)
    SET @InputID = SCOPE_IDENTITY()
END

The insert statements are working fine but I am not getting back any value in my application. Its 0. I tried returning 1 value(SurveyID) by using below statement but still not getting correct value. Its returning -1 everytime.

insert语句运行良好，但我在应用程序中没有返回任何值。0。我尝试使用下面的语句返回一个值(SurveyID)，但仍然没有得到正确的值。它的每次返回1。

surveyId = manager.ExecuteNonQuery("pr_CreateSurvey");

I tried a lot but no luck. Please advise.

我试了很多，但没有运气。请建议。

3 个解决方案

#1

find the values of the output parameters in the Parameters collection of your SqlCommand ... like

在SqlCommand的参数集合中找到输出参数的值……就像

mySqlCommand.Parameters["@SurveyID"].Value

after you executed

在你执行

mySqlCommand.ExecuteNonQuery();

#2

Keep a variable of your ouput parameters around and check the .Value afterwards. The ouput gets written to the parameter, but due to boxing, it does not get written to your int. Basically, your int was copied into the parameter and your original variables do not change.

保留ouput参数的变量，然后检查。value。ouput被写入参数，但是由于装箱，它不会被写入您的int。基本上，您的int被复制到参数中，您的原始变量不会改变。

#3

ExecuteNonQuery returns the number of rows affected, not anything else.

ExecuteNonQuery返回受影响的行数，而不是其他。

I've never used output parameters in my stored procedures for this. What I usually do is select out the values I want to return.

我从未在存储过程中使用过输出参数。我通常做的是选择我想要返回的值。

So after you do this:

在你这么做之后

SET @SurveyID = SCOPE_IDENTITY()
SET @InputID = SCOPE_IDENTITY()

I will do this

我将这样做

select @SurveyID as SurveyID,@InputID as InputID

Of course I'll declare those variables within the stored procedure and not as OUTPUT

当然，我将在存储过程中声明这些变量，而不是作为输出

This should give you what you want. If it's the correct way? Not sure. But it sure as hell works and is easy ;)

这会给你想要的。如果正确的话?不确定。但它确实是地狱，而且很容易;

#1