Thursday, October 22, 2009

How do I get the return value and dataset when calling a stored procedure in a typed DataSet object?

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

0 comments:

My Achievements

Member of

Blog Archive

Followers