Need a return value from a stored procedure in C#

With the following Stored Procedure in SQL Server 2005

IF OBJECT_ID('[dbo].[UserProfile]') IS NO开发者_如何学运维T NULL

DROP PROCEDURE [dbo].[UserProfile]  

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[UserProfile] 
(      
      @UserId VARCHAR(20)
)

AS

IF @UserId = 'userId'
BEGIN
SELECT @UserId = 'Yes'
END

ELSE
SELECT @UserId = 'No'

  SELECT @UserId AS RESULT 

  RETURN RESULT

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

C# code, needs to obtain the result of the stored procedure

public String UserProfile(string userId)
{
   String result;
   System.Data.Common.DbCommand cmd = this.mConn.CreateCommand();

   try
   {

       cmd.CommandTimeout = this.mCmdTimeout;
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.CommandText = "UserProfile";

       DbParameter p = cmd.CreateParameter();

       p.ParameterName = "@UserId";
       p.DbType = DbType.String;
       p.Value = userId;

       cmd.Parameters.Add(p);

       cmd.Connection.Open();
       cmd.ExecuteNonQuery();
       cmd.Connection.Close();

       //Need to assign the value of the stored procedure's return to result
       result = Convert.ToString(cmd.Parameters["@UserId"].Value);
   }
   catch (Exception ex)
   {
       throw;
   }
   finally
   {
       cmd.Connection.Close();
   }

   return result;
}

How do I return the result of my stored procedure in C#?

I was also looking for returning value from Stored procedure and capture it in C#.

I was returning various return codes based on the logic in my code, and executescalar will not help as I wanted to return out of stored proc in between wherever required with the appropriate code and not use select. So by adding an extra parameter with Direction as ReturnValue, you can capture the return value. No need of OUT parameters to capture return value from stored proc.

       preturn.ParameterName = "@Return_Value";  
       preturn.DbType = DbType.Int;        
       preturn.Direction = ParameterDirection.ReturnValue;

So here is what I came up with. Editing the original code in this post to keep it simple.

public String UserProfile(string userId)  
{  
   String result;  
   System.Data.Common.DbCommand cmd = this.mConn.CreateCommand();  

   try  
   {  

       cmd.CommandTimeout = this.mCmdTimeout;  
       cmd.CommandType = CommandType.StoredProcedure;  
       cmd.CommandText = "UserProfile";  

       DbParameter p = cmd.CreateParameter();  

       p.ParameterName = "@UserId";  
       p.DbType = DbType.String;  
       p.Value = userId;  

       cmd.Parameters.Add(p);  

       DbParameter preturn = cmd.CreateParameter();        
       preturn.ParameterName = "@Return_Value";  
       preturn.DbType = DbType.Int;        
       preturn.Direction = ParameterDirection.ReturnValue;

       cmd.Parameters.Add(preturn);  

       cmd.Connection.Open();  
       cmd.ExecuteNonQuery();  
       cmd.Connection.Close();  

       //Need to assign the value of the stored procedure's return to result  
       result = Convert.ToString(cmd.Parameters["@Return_Value"].Value);  
   }  
   catch (Exception ex)  
   {  
       throw;  
   }  
   finally  
   {  
       cmd.Connection.Close();  
   }  

   return result;  
}  

In this case, you can use the ExecuteScalar() method of Command to return the value. ExecuteScalar will look at the first column of the first record returned from the database.

Try adding this to the parameter's properties before executing the command:

cmd.Parameters["@UserId"].Direction = System.Data.ParameterDirection.InputOutput;

**Edit: **
As Ryan Brunner stated in comments, you must mark your parameter as OUT in the stored procedure for this to work.

You can use an Output parameter as well.

In storedprocedure:

CREATE PROCEDURE [dbo].[UserProfile] 
(      
      @UserId VARCHAR(20) Output
)

In code:

p.ParameterName = "@UserId";
p.DbType = DbType.String;
p.Direction = System.Data.ParameterDirection.Output;
p.Value = userId;

You could have also used an Output parameter.

Using...?

Linq2Entities? Drag the sproc into the designer and it will be a function in c# returning the correct value. Linq2Sql? Drag the sproc into the designer and it will be a function in c# returning the correct value. ADO? - just assign the result of ExecuteScalar to a variable