Possible to return an out parameter with a DataReader

Question

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

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:

SQL Query:

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

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

C# code:

     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;

Can you show your C# code? Are you setting the CommandType to CommandType.StoredProcedure? How are you passing the parameters to the command? — juharr
– juharr, Commented Aug 17, 2015 at 14:14
What exactly is command? I'm not familiar with an overload of SqlCommand.ExecuteReader that takes the command text, type, and parameters. — juharr
– juharr, Commented Aug 17, 2015 at 14:23

KyleMit · Accepted Answer · 2017-11-17 15:17:48Z

18

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

edited Nov 17, 2017 at 15:17

KyleMit♦

31.4k74 gold badges517 silver badges712 bronze badges

answered Aug 17, 2015 at 14:26

user957902

3,07017 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pierre Nortje Over a year ago

Appreciate your time, but I need to return the reader. The above solution would work, but the Read() needs to be done outside of the method. I decided to separate the two methods.

Stevie Over a year ago

This was pretty helpful. Needed to do an extra reader.NextResult() for the reader to become closed and the parameter value to be populated

user2866442 · Accepted Answer · 2018-04-16 07:51:24Z

7

To get the values from OUTPUT parameters with SqlDataReader you can only after the reader will be closed.

So you need to add this code before you try to get the values

if(!dataReader.IsClosed)
    dataReader.Close(); 
 if(out_recordCount.Value != DBNull.Value)
     recordCount = Convert.ToInt64(out_recordCount.Value);
 returnValue = Convert.ToInt32(return_Value.Value);

answered Apr 16, 2018 at 7:51

user2866442

8598 silver badges5 bronze badges

Collectives™ on Stack Overflow

Possible to return an out parameter with a DataReader

SQL Query:

C# code:

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

SQL Query:

C# code:

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related