Execute SQL Server Stored Procedure with SqlHelper.ExecuteDataSet() and Get Return Value

Question

I have an SQL Server Stored Procedure. This procedure have an output parameter. On my C#.NET Appliaction, I execute this procedure via SqlHelper.ExecuteDataSet() and it returns a query result DataSet. How can I get the output parameter from the stored procedure procedure while using SqlHelper.ExecuteDataSet(). Some article said that I need to use SqlHelper.ExecuteNonQuery() but I need the DataSet too.

This is my code:

        public DataSet GetDataPerTable(string spName, string a, string b, out int c)
        {
            try
            {
                c = 0;

                SqlParameter[] spParameter = new SqlParameter[3];

                spParameter[0] = new SqlParameter("@a", SqlDbType.Char, 4);
                spParameter[0].Direction = ParameterDirection.Input;
                spParameter[0].Value = a;

                spParameter[1] = new SqlParameter("@b", SqlDbType.Char, 1);
                spParameter[1].Direction = ParameterDirection.Input;
                spParameter[1].Value = b;

                spParameter[2] = new SqlParameter("@c", SqlDbType.Int);
                spParameter[2].Direction = ParameterDirection.ReturnValue;                
                c = Convert.ToInt32(spParameter[2].Value);

                return SqlHelper.ExecuteDataset(Configuration.MyConnectionString, CommandType.StoredProcedure, spName, spParameter);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

My c variable always return 0. Any Idea? Thanks in advance :)

My Procedure is about like this:

CREATE PROCEDURE [dbo].SPR_MyProcedure (@a Char(4), @bChar(1), @c Int Output)  
SELECT * 
FROM MyTable
Set @c = 1

podiluska · Accepted Answer · 2012-11-07T08:58:31.560

7

@c isn't the a return value, it's an output parameter

Change

spParameter[2].Direction = ParameterDirection.ReturnValue;

to

 spParameter[2].Direction = ParameterDirection.Output;

Also, you need to set your c variable after the call to ExecuteDataset eg:

DataSet dataset = SqlHelper.ExecuteDataset(Configuration.MyConnectionString, CommandType.StoredProcedure, spName, spParameter);
c=(int)spParameter[2];
return dataset;

edited Nov 07 '12 at 08:58

answered Nov 07 '12 at 08:48

podiluska

50,950
7
98
104

I've put my Stored Procedure script there :) – Willy Lazuardi Nov 07 '12 at 08:57
@WillyLazuardi Yes - it's definitely an output, not a returnvalue. – podiluska Nov 07 '12 at 09:00

score 4 · Answer 2 · edited May 23 '17 at 10:30

4

You have to use ParameterDirection.Output for parameter c. see Get output parameter value in ADO.NET

ParameterDirection.ReturnValue is a return value of the whole stored procedure, 0 by default or a value specified in RETURN statement.

edited May 23 '17 at 10:30

Community

1
1

answered Nov 07 '12 at 08:54

Frantisek Bachan

265
4
4

score 0 · Answer 3 · answered Nov 07 '12 at 09:10

SqlHelper.ExecuteDataSet doesnt return output parameter. see this link

http://www.mediachase.com/documentation/fileuploader/Api/Mediachase.FileUploader.SqlHelper.ExecuteDataset_overload_5.html

for output parameter in every example they r using ExecuteNonQuery see these aticles for output prameter

http://forums.asp.net/t/360456.aspx/1

http://www.codeproject.com/Articles/15666/Data-Access-Application-Block-NET-2-0-Get-Return-V

i hope it can help u

Execute SQL Server Stored Procedure with SqlHelper.ExecuteDataSet() and Get Return Value

3 Answers3