Passing a DataReader between methods and getting RETURN_VALUE

Discussion in 'ASP .Net' started by Martin Raychev, Apr 22, 2004.

  1. Hi all,



    I have the following problem:



    I have a private method that returns a SqlDataReader. For this to work I
    have not to close the DB connection in the above method. I do this only to
    have the possibility to iterate through the entire rows set in a while loop,
    located in the calling method.



    I have included a few lines of code to get the number of rows fetched from
    the DB. I do this with SqlParameter("RETURN_VALUE", SqlDbType.Int) [ I am
    using a stored procedure that return @@rowcount].



    I have found out that I am getting the appropriate value for the stored
    procedure parameter ("RETURN_VALUE") ONLY when I have explicitly close the
    DB connection. Unfortunately when the control is returned to the calling
    method the usual error message



    Invalid attempt to Read when reader is closed



    is being received as the connection is closed and there's no such DataReader
    already.



    Does anyone know a workaround for this?



    Thanks,

    Martin



    ------------------------

    private SqlDataReader GetReader(string parameter, string date)

    {

    DateTime MyDateTime;

    .....//. date parsing


    SqlConnection myConn = new SqlConnection(ConnectionString());
    SqlCommand myCmd = new SqlCommand();

    SqlDataReader myReader=null;



    myCmd.CommandType = CommandType.StoredProcedure;
    myCmd.Connection = myConn;
    myCmd.CommandText = "GetData";
    myCmd.CommandTimeout = 250;

    SqlParameter Param1 = new SqlParameter();
    Param1 = myCmd.Parameters.Add("@parameter", SqlDbType.VarChar, 12);
    Param1.Direction = ParameterDirection.Input;
    Param1.Value = parameter;



    SqlParameter Param2 = new SqlParameter();
    Param2 = myCmd.Parameters.Add("@date", SqlDbType.VarChar, 20);
    Param2.Direction = ParameterDirection.Input;
    Param2.Value = MyDateTime.Date.ToShortDateString();


    SqlParameter outValue = new SqlParameter();
    outValue = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
    outValue.Direction = ParameterDirection.ReturnValue;

    myConn.Open();
    myReader = myCmd.ExecuteReader();



    // this works only is myConn.Close() is executed but then

    // we cannot return a READER to the calling method???

    intRowsReturned =
    Convert.ToInt32(myCmd.Parameters["RETURN_VALUE"].Value);
    return myReader;
    }



    // the calling method

    private void btnSQLGet_Click(object sender, System.EventArgs e)

    {

    /// ... .

    // get the data in a reader

    SqlDataReader myReader = GetReader(cboParameter.Text, txtDate.Text);
    if (myReader==null)
    return;
    ///.....

    }
    Martin Raychev, Apr 22, 2004
    #1
    1. Advertising

  2. You can't read output parameters in a DataReader until the DataReader is
    closed. Close the DataReader, and leave the Connection opened to read the
    value.

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    Big things are made up
    of lots of little things.

    "Martin Raychev" <> wrote in message
    news:#RLAe#...
    > Hi all,
    >
    >
    >
    > I have the following problem:
    >
    >
    >
    > I have a private method that returns a SqlDataReader. For this to work I
    > have not to close the DB connection in the above method. I do this only to
    > have the possibility to iterate through the entire rows set in a while

    loop,
    > located in the calling method.
    >
    >
    >
    > I have included a few lines of code to get the number of rows fetched from
    > the DB. I do this with SqlParameter("RETURN_VALUE", SqlDbType.Int) [ I am
    > using a stored procedure that return @@rowcount].
    >
    >
    >
    > I have found out that I am getting the appropriate value for the stored
    > procedure parameter ("RETURN_VALUE") ONLY when I have explicitly close the
    > DB connection. Unfortunately when the control is returned to the calling
    > method the usual error message
    >
    >
    >
    > Invalid attempt to Read when reader is closed
    >
    >
    >
    > is being received as the connection is closed and there's no such

    DataReader
    > already.
    >
    >
    >
    > Does anyone know a workaround for this?
    >
    >
    >
    > Thanks,
    >
    > Martin
    >
    >
    >
    > ------------------------
    >
    > private SqlDataReader GetReader(string parameter, string date)
    >
    > {
    >
    > DateTime MyDateTime;
    >
    > .....//. date parsing
    >
    >
    > SqlConnection myConn = new SqlConnection(ConnectionString());
    > SqlCommand myCmd = new SqlCommand();
    >
    > SqlDataReader myReader=null;
    >
    >
    >
    > myCmd.CommandType = CommandType.StoredProcedure;
    > myCmd.Connection = myConn;
    > myCmd.CommandText = "GetData";
    > myCmd.CommandTimeout = 250;
    >
    > SqlParameter Param1 = new SqlParameter();
    > Param1 = myCmd.Parameters.Add("@parameter", SqlDbType.VarChar, 12);
    > Param1.Direction = ParameterDirection.Input;
    > Param1.Value = parameter;
    >
    >
    >
    > SqlParameter Param2 = new SqlParameter();
    > Param2 = myCmd.Parameters.Add("@date", SqlDbType.VarChar, 20);
    > Param2.Direction = ParameterDirection.Input;
    > Param2.Value = MyDateTime.Date.ToShortDateString();
    >
    >
    > SqlParameter outValue = new SqlParameter();
    > outValue = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
    > outValue.Direction = ParameterDirection.ReturnValue;
    >
    > myConn.Open();
    > myReader = myCmd.ExecuteReader();
    >
    >
    >
    > // this works only is myConn.Close() is executed but then
    >
    > // we cannot return a READER to the calling method???
    >
    > intRowsReturned =
    > Convert.ToInt32(myCmd.Parameters["RETURN_VALUE"].Value);
    > return myReader;
    > }
    >
    >
    >
    > // the calling method
    >
    > private void btnSQLGet_Click(object sender, System.EventArgs e)
    >
    > {
    >
    > /// ... .
    >
    > // get the data in a reader
    >
    > SqlDataReader myReader = GetReader(cboParameter.Text, txtDate.Text);
    > if (myReader==null)
    > return;
    > ///.....
    >
    > }
    >
    >
    >
    Kevin Spencer, Apr 22, 2004
    #2
    1. Advertising

  3. Martin Raychev

    bruce barker Guest

    1) the return value and parameters values of a stored proc are returned
    after all result sets in the proc are returned.
    2) a datareader is a forward only cursor, so to get to the return value, you
    need to read thru all rows, and result sets
    3) a close will read thru all rows and result sets for you, but is not
    necessary, the following code will also work:

    // process all result sets to get to parameters and reutn value

    do
    {
    while (dr.Read())
    ;
    } while (dr.NextResult())

    // now you can access output parameters and the return value

    returning a row count makes little sesnse, as you could add up the row
    count yourself, as you have to read them all to get to the count return
    value.

    -- bruce (sqlwork.com)


    "Martin Raychev" <> wrote in message
    news:#RLAe#...
    > Hi all,
    >
    >
    >
    > I have the following problem:
    >
    >
    >
    > I have a private method that returns a SqlDataReader. For this to work I
    > have not to close the DB connection in the above method. I do this only to
    > have the possibility to iterate through the entire rows set in a while

    loop,
    > located in the calling method.
    >
    >
    >
    > I have included a few lines of code to get the number of rows fetched from
    > the DB. I do this with SqlParameter("RETURN_VALUE", SqlDbType.Int) [ I am
    > using a stored procedure that return @@rowcount].
    >
    >
    >
    > I have found out that I am getting the appropriate value for the stored
    > procedure parameter ("RETURN_VALUE") ONLY when I have explicitly close the
    > DB connection. Unfortunately when the control is returned to the calling
    > method the usual error message
    >
    >
    >
    > Invalid attempt to Read when reader is closed
    >
    >
    >
    > is being received as the connection is closed and there's no such

    DataReader
    > already.
    >
    >
    >
    > Does anyone know a workaround for this?
    >
    >
    >
    > Thanks,
    >
    > Martin
    >
    >
    >
    > ------------------------
    >
    > private SqlDataReader GetReader(string parameter, string date)
    >
    > {
    >
    > DateTime MyDateTime;
    >
    > .....//. date parsing
    >
    >
    > SqlConnection myConn = new SqlConnection(ConnectionString());
    > SqlCommand myCmd = new SqlCommand();
    >
    > SqlDataReader myReader=null;
    >
    >
    >
    > myCmd.CommandType = CommandType.StoredProcedure;
    > myCmd.Connection = myConn;
    > myCmd.CommandText = "GetData";
    > myCmd.CommandTimeout = 250;
    >
    > SqlParameter Param1 = new SqlParameter();
    > Param1 = myCmd.Parameters.Add("@parameter", SqlDbType.VarChar, 12);
    > Param1.Direction = ParameterDirection.Input;
    > Param1.Value = parameter;
    >
    >
    >
    > SqlParameter Param2 = new SqlParameter();
    > Param2 = myCmd.Parameters.Add("@date", SqlDbType.VarChar, 20);
    > Param2.Direction = ParameterDirection.Input;
    > Param2.Value = MyDateTime.Date.ToShortDateString();
    >
    >
    > SqlParameter outValue = new SqlParameter();
    > outValue = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
    > outValue.Direction = ParameterDirection.ReturnValue;
    >
    > myConn.Open();
    > myReader = myCmd.ExecuteReader();
    >
    >
    >
    > // this works only is myConn.Close() is executed but then
    >
    > // we cannot return a READER to the calling method???
    >
    > intRowsReturned =
    > Convert.ToInt32(myCmd.Parameters["RETURN_VALUE"].Value);
    > return myReader;
    > }
    >
    >
    >
    > // the calling method
    >
    > private void btnSQLGet_Click(object sender, System.EventArgs e)
    >
    > {
    >
    > /// ... .
    >
    > // get the data in a reader
    >
    > SqlDataReader myReader = GetReader(cboParameter.Text, txtDate.Text);
    > if (myReader==null)
    > return;
    > ///.....
    >
    > }
    >
    >
    >
    bruce barker, Apr 22, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. D. Shane Fowlkes

    Help with SP - what exactly is RETURN_VALUE?

    D. Shane Fowlkes, Mar 13, 2006, in forum: ASP .Net
    Replies:
    9
    Views:
    8,371
    Prashant
    Mar 14, 2006
  2. John M. Gabriele
    Replies:
    18
    Views:
    1,129
    Steven Bethard
    Feb 18, 2005
  3. dwhall
    Replies:
    0
    Views:
    357
    dwhall
    May 9, 2007
  4. Jeremy.Chen
    Replies:
    2
    Views:
    339
    Jeremy.Chen
    Jan 6, 2009
  5. Kenneth McDonald
    Replies:
    5
    Views:
    288
    Kenneth McDonald
    Sep 26, 2008
Loading...

Share This Page