how to get Stored Procedure output parameter in c#

Question

I am trying to get the value of output parameter of a stored procedure in c# function. when i execute the SP i am getting correct result in sql server but i can't get in c#. Can you please tell me how can i get the output value in c#. Below is the function i am trying to get the output value in C# DAC.

public DataSet GetOrderItemPictureByOrderId(int orderID, int PageIndex)
    {
        DataSet dtOrder = new DataSet();

        /// Connect to database.
        Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);
        using (DbCommand cmd = db.GetStoredProcCommand("uspGetOrderItemPicturePageWise"))
        {
            /// Set parameter values.
            db.AddInParameter(cmd, "@OrderID", DbType.Int32, orderID);
            db.AddInParameter(cmd, "@PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(cmd, "@PageSize", DbType.Int32, 6);
            db.AddOutParameter(cmd, "@RecordCount", DbType.Int32, 4);

            try
            {
                dtOrder = db.ExecuteDataSet(cmd);
                string outputValue = cmd.Parameters["@RecordCount"].Value.ToString(); // here i want to get the output value and want to return the value to main code 
            }
            catch (Exception ex)
            {
                LogErrors.WriteError(ex);
            }
        }
        return dtOrder;
    }
}

Here i am calling the function :-

 DataSet _ds = _orderPicDAC.GetOrderItemPictureByOrderId(OrderID, PageIndex);

My Store Procedure :--

 CREATE PROCEDURE [dbo].[uspGetOrderItemPicturePageWise]    
      @OrderID int,    
      @PageIndex INT = 1    
      ,@PageSize INT = 10    
      ,@RecordCount INT OUTPUT    
AS    
BEGIN    
      SET NOCOUNT ON;    
      SELECT ROW_NUMBER() OVER    
      (    
            ORDER BY [PictureId] ASC    
      )AS RowNumber,    
      Orderid,    
      GenericFieldID,    
      ItemImage    
     INTO #Results    
      FROM [OrderPictures]    
      WHERE OrderID = @OrderID  
      SELECT @RecordCount = COUNT(*)    
      FROM #Results    

      SELECT * FROM #Results    
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1    

      DROP TABLE #Results    
END

Thanks for your help in advance.

You can use this to see how to return more than value and this for reading data from DataSet. — elirandav
– elirandav, Commented May 17, 2018 at 6:08
Why are you returning dtOrder if you really want the result from the RecordCount parameter? And, for that matter, why would you convert an int representing an amount into a string? Just change the return type to Int32 and return (Int32)cmd.Parameters["@RecordCount"].Value — Nyerguds
– Nyerguds, Commented May 17, 2018 at 7:44
Also, you're not closing your database. You may want to put db in a using block too. — Nyerguds
– Nyerguds, Commented May 17, 2018 at 7:46

Gagan Deep · Accepted Answer · 2018-05-17 09:33:21Z

2

If you want to get value of output parameter outside the method without changing the return type of the method, you may use out parameters in C#.

Change your method definition like below:

public DataSet GetOrderItemPictureByOrderId(int orderID, int PageIndex, out string outputValue)
{
    //code here
    outputValue = db.GetParameterValue(cmd, "@RecordCount");
    //code here

}

and then call this method

 string outvalue;
 DataSet _ds = _orderPicDAC.GetOrderItemPictureByOrderId(OrderID, PageIndex,out outvalue;);

You will have the value in outvalue variable.

edited May 17, 2018 at 9:33

answered May 17, 2018 at 5:56

Gagan Deep

1,51911 silver badges13 bronze badges

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

5 Comments

A.Goutam Over a year ago

is something i need to change in GetOrderItemPictureByOrderId(int orderID, int PageIndex) because i want to get this on my page also

Gagan Deep Over a year ago

i dont think i understood your question but if you want out value to be accessible in other sections of the page, you may declare a static page level string variable and assign the variable out value and then use it in the other methods of the page. You don't need to change anything in your method.

A.Goutam Over a year ago

i want to access string outputValue at my page.

Nyerguds Over a year ago

@GaganDeep The original code is already fetching it; the question was about returning it. Though I don't understand why the code doesn't simply return it. I don't think that DataSet dtOrder will contain anything useful in a stored proc operation with an out param anyway.

Gagan Deep Over a year ago

If that's the case then me must use an out parameter in the method signature and assign the value to it in the body.

Prany · Accepted Answer · 2018-05-17 05:53:53Z

0

You can try with below

using (SqlConnection con = new SqlConnection(dc.Con))
{
using (SqlCommand cmd = new SqlCommand("SP_ADD", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@OrderID", DbType.Int32, orderID);
    con.Open();
    cmd.ExecuteNonQuery();
}            
}

answered May 17, 2018 at 5:53

Prany

2,1332 gold badges17 silver badges35 bronze badges

2 Comments

Jaquez Over a year ago

You don't retrieve the value of the output parameter. You also don't need the outer set of curly braces.

Jamesckel Over a year ago

As far as I know, there is no variant of AddWithValue that takes 3 parameters.

Collectives™ on Stack Overflow

how to get Stored Procedure output parameter in c#

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related