C# EnterpriseLibrary trouble obtaining an Output Parameter

I’m writing a Stored Procedure that captures certain notes a user does in a Web Application. The procedures stores the notes, and I need the output parameter to notify the user if his/her notes have been captured correctly or not. However, I’m having issues with my output parameter.

Here is my stored procedure:

CREATE PROCEDURE captureNotes (
@IdFile bigint,
@IdSection int,
@Notes varchar(500),
@IsOk bit,
@User varchar(100),
@Bit bit OUTPUT)
AS
BEGIN
SET NOCOUNT ON
  BEGIN TRY
  --Checking if we are going to overwrite previous notes--
    DECLARE @Count int;
    SET @Count = (SELECT
      COUNT(IdFile)
    FROM WebValidation
    WHERE IdFile = @IdFile
    AND IdSection = @IdSection
    AND User = @User);
    IF @Count > 0
    BEGIN
      UPDATE WebValidation
      SET Notes = @Notes,
          IsOk = @IsOk, Date = GETDATE()
      WHERE IdFile = @IdFile
      AND IdSection = @IdSection
      AND User = @User
      SET @Bit=1;
    END
    ELSE
    BEGIN
      INSERT INTO WebValidation
        VALUES (@IdFile, @IdSection, @Notes, @IsOk, @User, GETDATE());
        SET @Bit=1;
    END
    SET @Bit = 1;
    SET NOCOUNT OFF
  END TRY
  BEGIN CATCH
    SET @Bit = 0;
    SET NOCOUNT OFF;
  END CATCH
END

My C# code

public int captureNotes(Int64 idFile, int idSection,
        string notes, bool isOk, string user)
    {
        try
        {
            db = DatabaseFactory.CreateDatabase("CnxPrincipal");
            cmd = db.GetStoredProcCommand("[captureNotes]");
            cmd.CommandTimeout = DBExecutionTimeout;

            db.AddInParameter(cmd, "@IdFile", DbType.Int64, idFile);
            db.AddInParameter(cmd, "@IdSection", DbType.Int16, idSection);
            db.AddInParameter(cmd, "@Notes", DbType.String, notes);
            db.AddInParameter(cmd, "@IsOk", DbType.Boolean, isOk);
            db.AddInParameter(cmd, "@User", DbType.String, user);
            int result = 0;
            db.AddOutParameter(cmd, "@Bit", DbType.Boolean, result);
            db.ExecuteNonQuery(cmd);
            return result;
        } catch (Exception ex) { throw ex; }
        finally { cmd.Dispose(); db = null; }
    }

The code will always return 0, although the notes are added succesfully. What am I doing wrong?
Thanks for your time.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

AddOutParameter doesn’t take a value as its last parameter, but it need a size (for a boolean I suppose its just one byte). Moreover output parameters contain a valid value only after you finished with the command. So given the fact that AddOutParameter is void, you need a way to get back that parameter and look at its value after the execution of the query.

I cannot test it but is seems logical to follow this path.

db.AddOutParameter(cmd, "@Bit", DbType.Boolean, 1);
db.ExecuteNonQuery(cmd);

// Get the parameter back after the completition of the command.
DbParameter par = cmd.Parameters.GetParameter("@Bit");

// The value property is of type object and can be null, but given the SP
// above it should never be null, so we can have
return Convert.ToInt32(par.Value);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0