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

异常信息大致如下：

对象引用未设置为对象的实例。

- Abhinandana Shetty

Answer 1

这只是TDS的一个特性，在ADO.NET中也会出现相同的情况；返回的参数值通常位于TDS流的末尾，因此在完成数据消费之前更新的值将不可用。

基本上，你需要在使用reader后查询参数值。直到这时，值才回来。例如，下面这个匆忙添加的集成测试通过：

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

- Marc Gravell