Shiran's Blog

There is no direct way to retrieve the returning value from a stored procedure in typed DataSet object. To accomplish this what you have to do is just add a new method in the partial class of the generated table adapter. Ill show how to do this step by step.

Consider the following stored procedure, which returns a dataset plus a return value. (not a complicated SP). In this case I just return 0 from the SP.

CREATE PROCEDURE [dbo].[GetUser]
@EmailID VARCHAR (100)
AS
BEGIN
SELECT
[UserID],
[FirstName],
[LastName],
[Phone]
FROM
[dbo].[User]
WHERE
[EmailID] = @EmailID

RETURN 0
END

Now add the method following method in the partial class of the generated table adapter. In my case it's UserTableAdapter.

 partial class UserTableAdapter
 {
     public object GetReturnValue(int commandIndex)
     {
            return this.CommandCollection[commandIndex].Parameters[0].Value;
     }
 }

Ok, now you can retrieve the return value plus the dataset,

private DSTableAdapters.UserTableAdapter _userAdapter = null;

protected DSTableAdapters.UserTableAdapter Adapter{
     get{
         if (_userAdapter == null)
           _userAdapter = new DSTableAdapters.UserTableAdapter();
               return _userAdapter;
           }
        }

This is the method that retrieve dataset and the get return value by calling the method (GetReturnValue(int commandIndex) ) we added in to generated partial class

public int GetUser(string emailid)
{
    int success ;
    try
    {     

        DS.UserDataTable userDataTable = Adapter.GetUser(emailid);
        if (userDataTable.Rows.Count > 0)
        {
           //assingning to variables
            FirstName = userDataTable.Rows[0]["FirstName"].ToString();
            LastName = userDataTable.Rows[0]["LastName"].ToString();
            success = int.Parse(Adapter.GetReturnValue(0).ToString());
        }   
    }
    catch (Exception ex)
    {
        //write you exception handling
    }

    return success; 

}