title: Execute SQL Server Stored Procedure with SqlHelper.ExecuteDataSet() and Get Return Value
body: <p>I have an <code>SQL Server Stored Procedure</code>. This procedure have an output parameter.
  On my <code>C#.NET</code> Appliaction, I execute this procedure via <code>SqlHelper.ExecuteDataSet()</code> and it returns a query result <code>DataSet</code>.
  How can I get the output parameter from the stored procedure procedure while using <code>SqlHelper.ExecuteDataSet()</code>.
  Some article said that I need to use <code>SqlHelper.ExecuteNonQuery()</code> but I need the <code>DataSet</code> too.</p>
  
  <p>This is my code:</p>
  
  <pre><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;
              }
          }
  </code></pre>
  
  <p>My <code>c</code> variable always return 0. Any Idea? Thanks in advance :)</p>
  
  <p>My Procedure is about like this:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].SPR_MyProcedure (@a Char(4), @bChar(1), @c Int Output)  
  SELECT * 
  FROM MyTable
  Set @c = 1
  </code></pre>