Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Passing a DataReader between methods and getting RETURN_VALUE

Reply
Thread Tools

Passing a DataReader between methods and getting RETURN_VALUE

 
 
Martin Raychev
Guest
Posts: n/a
 
      04-22-2004
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;
///.....

}



 
Reply With Quote
 
 
 
 
Kevin Spencer
Guest
Posts: n/a
 
      04-22-2004
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" <(E-Mail Removed)> wrote in message
news:#RLAe#(E-Mail Removed)...
> 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;
> ///.....
>
> }
>
>
>



 
Reply With Quote
 
 
 
 
bruce barker
Guest
Posts: n/a
 
      04-22-2004
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" <(E-Mail Removed)> wrote in message
news:#RLAe#(E-Mail Removed)...
> 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;
> ///.....
>
> }
>
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there some methods like 'datareader' in MySQLdb for handling massdata Jeremy.Chen Python 2 01-06-2009 01:00 PM
Is there a way to find the class methods of a class, just like'methods' finds the instance methods? Kenneth McDonald Ruby 5 09-26-2008 03:09 PM
Does RETURN_VALUE always result in an empty stack? dwhall Python 0 05-09-2007 01:20 PM
Help with SP - what exactly is RETURN_VALUE? D. Shane Fowlkes ASP .Net 9 03-14-2006 08:41 AM
difference between class methods and instance methods John M. Gabriele Python 18 02-18-2005 05:17 PM



Advertisments