C# EnterpriseLibrary trouble obtaining an Output Parameter

Question

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.

Answer 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);