Dapper output parameter is not returning values

Question

This is my code part:

CResponseVO objCResponseVO = new CResponseVO();

try
{
  var parameters = new DynamicParameters();
  parameters.Add("@UserId", currentUser.userId, DbType.Int32);
  parameters.Add("@Operation", operation, DbType.String);
  parameters.Add("@Output", dbType: DbType.Int32, direction: ParameterDirection.Output);

  using (var connection = SqlAccessHelper.SqlHelper.GetOpenConnection(SqlConnectionHelper.SqlConnectionString()))
  {
    var reader = connection.QueryMultiple("USP_DataExtract", parameters, (SqlTransaction)null, 1000000, CommandType.StoredProcedure);
    int result = parameters.Get<int>("@Output");
    if (operation != "insert")
    {
      ObservableCollection<DataExtraction.DataExtractionTracker> DataExtractionTracker = new ObservableCollection<DataExtraction.DataExtractionTracker>(reader.Read<DataExtraction.DataExtractionTracker>());
      objCResponseVO.addObject("ExtractionStatus", DataExtractionTracker);
    }

  objResponseVO.Result = result;
}

This is my SP, and I have used try and catch to commit or rollback based on ouput parameter value:

@UserID int=0,
@Operation varchar(50)= NULL,
@Output INT OUTPUT

AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION
      If(@Operation = 'select')
      BEGIN
        SELECT RequestId, UserId, RequestTime, Status,DownloadPath from DataExtractTracker where UserId= @UserID
      END

      If(@Operation = 'insert')
      BEGIN
        Insert into DataExtractTracker( UserId, RequestTime, Status) values (@UserID, GETDATE(), 'Waiting')
      END
      SET @Output = 0
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION
    SET @Output = 1 
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),  @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return 
    -- error information about the original error that 
    -- caused execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.);
  END CATCH
END

However, I am not able to retrieve the ouput parameter value. I am getting an exception when executing:

int result = parameters.Get<int>("@Output");

Exception says something like this:

Object reference not set to an instance of an object.

Answer 1

This is simply a feature of TDS, and you would get the same with ADO.NET; the returned parameter values are usually at the end of the TDS stream; consequently, the updated values are not available until after you have finished consuming the data.

Basically, you need to query the parameter values after you have finished with reader, because until then the value has not come back. For example, the following hastily added integration test passes:

public void TestOutputParameter()
{
    connection.Execute(@"
create proc #TestOutputParameterProc @Foo int, @Bar int out as
set @Bar = @Foo select 1 as [A] select 2 as [B]");
    try
    {
        var args = new DynamicParameters(new { Foo = 123 });
        args.Add("@Bar", dbType: DbType.Int32,
                               direction: ParameterDirection.Output);
        using (var grids = connection.QueryMultiple("#TestOutputParameterProc",
                   args, commandType: CommandType.StoredProcedure))
        {
            // this will fail here; we have not consumed the TDS data yet!
            // args.Get<int>("@Bar").IsEqualTo(123);

            // note we don't *have* to read the data here; disposing "grids"
            // would be enough to skip to the end of the TDS
            grids.Read<int>().Single().IsEqualTo(1); // A
            grids.Read<int>().Single().IsEqualTo(2); // B
        }
        // at this point we have consumed the TDS data, so the parameter
        // values have come back to the caller
        args.Get<int>("@Bar").IsEqualTo(123);
    }
    finally
    { // clean up the proc
        connection.Execute("drop proc #TestOutputParameterProc");
    }
}