可以使用DataReader返回out参数 [英] Possible to return an out parameter with a DataReader

问题描述

使用ExecuteReader 我可以返回 DataReader ，但是 out 参数返回0.

Using ExecuteReader I am able to return a DataReader, but the out parameter is returning 0.

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

Using ExecuteNonQuery I am able to retrieve the out parameter (with the correct value) but the ExecuteNonQuery does not return a DataReader.

以下是提供上下文的过程:

Here is the procedure to give context:

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 参数，还是应该为每个程序创建两个单独的过程?

Is there a way I could return a DataReader as well as the out parameter, or should I create two separate procedures for each?

     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;

推荐答案

在返回任何结果集之后，输出参数的值将位于SQLServer的流中(我相信返回值也是如此).这意味着，直到您从DataReader中读取了所有行(或者我相信将其关闭)之后，您才可以看到该值.因此，告诉您结果集中的行数的输出参数几乎没有用.

The value for your output parameter is in the stream from SQLServer AFTER any results sets returned (I believe this is also true of the return value). That means you won't see the value until after you read all the rows from the DataReader (or close it I believe). So an output parameter that tells you the number of rows in the result set is of little use.

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

However, the code fragment below demonstrates the sequence of operations you should be using:

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

这篇关于可以使用DataReader返回out参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！