Dapper 输出参数没有返回值

Question

这是我的代码部分:

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;
}

这是我的SP，我用过 try和 catch根据输出参数值提交或回滚:

@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

但是，我无法检索输出参数值。执行时出现异常:

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

异常是这样说的:

Object reference not set to an instance of an object.

Answer 1

这只是 TDS 的一个功能，您将在 ADO.NET 中获得相同的功能；返回的参数值通常为 末 TDS流的；因此，在您完成使用数据之前，更新的值不可用。

基本上，您需要查询参数值后 您已完成 reader ，因为在那之前值(value)还没有回来。例如，以下匆忙添加的集成测试通过:

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");
    }
}