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