c# - 可以使用 DataReader 返回输出参数

Question

使用 ExecuteReader我能够返回一个 DataReader，但是 out 参数返回 0。

使用 ExecuteNonQuery我能够检索 out 参数(具有正确的值)，但 ExecuteNonQuery 不返回 DataReader。

这里是给出上下文的过程:

SQL 查询:

CREATE PROCEDURE [dbo].[SelectDays]
    @dateStart datetime,
    @dateEnd datetime,
    @recordCount bigint out
AS
BEGIN
    select @recordCount = count(*)
    from dbo.[Days]
    where [Date]>=@dateStart and [Date]<=@dateEnd;

    select [ID],[Name]
    from dbo.[Days]
    where [Date]>=@dateStart and [Date]<=@dateEnd;
END

有没有办法返回 DataReader 以及 out 参数，或者我应该为每个创建两个单独的过程？

C#代码:

     Int32 returnValue = 0;

     Parameters parameters = new Parameters();
     parameters.Add(new SqlParameter("@dateStart", dateStart != null ? (object)dateStart : DBNull.Value));
     parameters.Add(new SqlParameter("@dateEnd", dateEnd != null ? (object)dateEnd : DBNull.Value));
         SqlParameter out_recordCount = new SqlParameter("@recordCount", SqlDbType.BigInt);
         out_recordCount.Direction = ParameterDirection.InputOutput;
         out_recordCount.Value = recordCount;
         parameters.Add(out_recordCount);

     SqlParameter return_Value = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
     return_Value.Direction = ParameterDirection.ReturnValue;
     parameters.Add(return_Value);

     dataReader = this.command.ExecuteReader("dbo.SelectDays", CommandType.StoredProcedure, parameters.ToArray());

     if(out_recordCount.Value != DBNull.Value)
     recordCount = Convert.ToInt64(out_recordCount.Value);

     returnValue = Convert.ToInt32(return_Value.Value);

     return returnValue;

Answer 1

输出参数的值在返回任何结果集后来自 SQLServer 的流中(我相信返回值也是如此)。这意味着在您从 DataReader 读取所有行(或者我相信关闭它)之前，您不会看到该值。因此，告诉您结果集中行数的输出参数用处不大。

但是，下面的代码片段演示了您应该使用的操作顺序:

using(SqlConnection connection = new SqlConnection("[your connection string here]"))
{
  connection.Open();

  using (SqlCommand command = connection.CreateCommand())
  {
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "dbo.SelectDays";

    command.Parameters.AddWithValue("@dateStart", dateStart != null ? (object)dateStart : DBNull.Value);
    command.Parameters.AddWithValue("@dateEnd", dateEnd != null ? (object)dateEnd : DBNull.Value);

    SqlParameter out_recordCount = new SqlParameter("@recordCount", SqlDbType.BigInt);
    out_recordCount.Direction = ParameterDirection.InputOutput;
    out_recordCount.Value = recordCount;

    command.Parameters.Add(out_recordCount);

    SqlParameter return_Value = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
    return_Value.Direction = ParameterDirection.ReturnValue;
    command.Parameters.Add(return_Value);

    using(SqlDataReader reader = command.ExecuteReader())
    {
      while(reader.Read()) { /* do whatever with result set data here */ }
    }

    /* Output and return values are not available until here */

    if (out_recordCount.Value != DBNull.Value)
      recordCount = Convert.ToInt64(out_recordCount.Value);

    returnValue = Convert.ToInt32(return_Value.Value);

    return returnValue;
  }
}