DataAccessLayer.SqlHelper.Stored procedures. Output parameters

Question

I have a database with stored procedure inside. I'm trying to execute procedure using SqlHelper. However I'm getting weird error.

 public override User Insert(User entity)
  {
           var param = new SqlParameter() { Direction = ParameterDirection.Output, ParameterName = "@UserId", SqlDbType = SqlDbType.Int };
           int result = SqlHelper.ExecuteNonQuery(_connectionString, "sp_User_Insert",
                    param,
                    new SqlParameter("@Name", entity.Name),
                    new SqlParameter("@Password", entity.Password),
                    new SqlParameter("@Created", entity.Created),
                    new SqlParameter("@LastAccessed", entity.LastAccessed),
                    new SqlParameter("@LastLogin", entity.LastLogin),
                    new SqlParameter("@Email", entity.Email),
                    new SqlParameter("@CommunityId", entity.Community.Id));

            entity.Id = Convert.ToInt32(param.Value);

            return entity;           
    }

My dataAccesLayer falls here in SqlHelper.cs:

public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

            int retval = cmd.ExecuteNonQuery(); //Stops here with InvalidCastException.Failed to convert parameter value from a SqlParameter to a String.


            cmd.Parameters.Clear();
            return retval;
        }

My stored procedure looks like here:

ALTER PROCEDURE [dbo].[sp_User_Insert]
    @UserId int OUTPUT,
    @Name varchar(50),
    @Password varchar(50),
    @Created datetime,
    @LastAccessed datetime,
    @LastLogin datetime,
    @Email nchar(50),
    @CommunityId int    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN TRANSACTION;

    INSERT INTO [User] 
    VALUES( @Name,@Password,@Created,@LastAccessed,@LastLogin,@Email,@CommunityId);

    SET @UserId = SCOPE_IDENTITY();

    COMMIT TRANSACTION;
END

I thought I had done everything right, but I can't explain why I got this error. Please advise!!Thank you..

Answer 1

You should cast scope_identity to int and use ExecuteScalar instead of ExecuteNonQuery.

SQL

SELECT CAST(scope_identity() AS int)

c#

int retval = (Int32)cmd.ExecuteScalar();