Return multiple values from Stored Procedure in C#

Question

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.

        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

 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.

surveyId = manager.ExecuteNonQuery("pr_CreateSurvey");

I tried a lot but no luck. Please advise.

PrfctByDsgn · Accepted Answer · 2013-12-06 06:42:30Z

3

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

mySqlCommand.Parameters["@SurveyID"].Value

after you executed

mySqlCommand.ExecuteNonQuery();

answered Dec 6, 2013 at 6:42

PrfctByDsgn

1,0621 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nvoigt · Accepted Answer · 2013-12-06 06:39:48Z

1

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.

answered Dec 6, 2013 at 6:39

nvoigt

77.8k28 gold badges103 silver badges156 bronze badges

Comments

user3036342 · Accepted Answer · 2013-12-06 07:21:24Z

1

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

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

edited Dec 6, 2013 at 7:21

answered Dec 6, 2013 at 6:39

user3036342

1,0457 silver badges15 bronze badges

3 Comments

user3036342 Over a year ago

You shouldn't use ExecuteNonQuery. From MSDN: Executes a Transact-SQL statement against the connection and returns the number of rows affected.

user3036342 Over a year ago

See PrfctByDsgn's answer, it should work. With my suggestion you can't use ExecuteNonQuery

user2956437 Over a year ago

Thanks. I tried as mentioned in PrfctByDsgn's answer and it worked.

Collectives™ on Stack Overflow

Return multiple values from Stored Procedure in C#

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related