Need a return value from a stored procedure in C#

Question

With the following Stored Procedure in SQL Server 2005

IF OBJECT_ID('[dbo].[UserProfile]') IS NOT 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#?

Answer 1

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.

Answer 2

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

Answer 3

Or you could of used an Output parameter.

Answer 4

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.

Answer 5

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;