Passing a DataReader between methods and getting RETURN_VALUE

M

Martin Raychev

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;
///.....

}
 
W

William Ryan eMVP

Martin:

You need to have it closed before you can get the results but I'm not sure I
understand the problem. Passing datareaders is problematic and passing them
between layers can be Really really problematic. One of the many problems
is the need for an open connection. Once you close the connection, that's a
wrap... DataReaders only work with an open and available connection and you
can't reopen the connection and have the data waiting for you. You'll
notice that Microsoft's Data Access Application Block has an executeDataSet
method, but not one for datareaders. Because they are pegged to a
connection, passing them around is always going to be a challenged
methodology

Anyway, you probably want to wait until you are iterating through your rows
to get your Return value and at that point, maybe you can kill two birds
with one stone..taking care of your iteration and all in one place.

Another alternative would be to store the results of your reader (assuming
you are only using a field or two ) in a collection of objects that mirror
the fields in your query. However at that point your object would start
looking close enough to a datatable to question why you aren't just using a
datatable in the first place, which is probably a very practical methodology
to employ here

I'm thinking I may not have fully understood your problem though b/c if you
could iterate the loop and get the value at the same time( or get the return
value right after iterating the loop) I"m sure you would have done that.

So if I'm missing the point of the problem, please let me know. BTW, if
you'd like really thoughtful and provocative opinion on problems associated
with passing DataReaders around, drop Kathleen Dollard a line
http://www.gendotnet.com/ and ask her about it. I spoke with her for about
30 minutes on the subject at the MVP summit and it's defintiely something
she's studied in depth.

In the meantime though, let me know if I misunderstood and I'll try to get
your problem fixed now.

Cheers,

Bill

Martin Raychev said:
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;
///.....

}
Click to expand...
 
G

Guest

Hi

How about change the line
myReader = myCmd.ExecuteReader()
T
myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Bin Song, MCP
 
S

Scott M.

How about populating a DataTable with the data from the DataReader and then
passing the DataTable? The DataReader requires an open connection to work
and you certainly want to close your connections as soon as possible.

The passed DataTable is more flexible as a return type than the DataReader.


Martin Raychev said:
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;
///.....

}
Click to expand...
 
M

Martin Raychev

Hi Bin,

Well changing that, which actually I have done at some point, only makes the
connection to be closed when the datareader is closed. However I have to
keep the DataReader open so that in the calling method I can iterate through
the records. Probably a better thing to be done is to pass a DataTable
instead of DataReader although I am not sure whether I can iterate in the
same way through the records (While.Read()).

For now I am counting the records with a variable but I would really like to
make it with the RETURN_VALUE.

Thank you for answering!

Cheers,

Martin
 
M

Martin Raychev

Hi Bill,

Thank you very much for your comprehensive answer. Yes, you got my problem
right however after reading the replies to my post I thought I might not be
doing the right approach. I wanted to get the RETURN_VALUE the stored
procedure returns and wondered why that was not possible. As another
newsgroup user has said it doesn't make any sense of returning @@rowcount
because I still have to iterate thorough all the records of the DataReader.
Now I have done this with a variable summing the number of records.
You and Scott M. proposed to use a DataTable and pass it through methods
instead of passing DataReader, which requires open connection. I think I
will try this. My application now works but I want to do it the most
efficient way.

Thanks for pointing me to Kathleen Dollard and her website - a certainly
interesting area btw (of code generation) and I think I will drop her a
message.

Thanks again,
Cheers

Martin.

I have initially chosen DataReader for performance issues as I am dealing
with large amount of numerical data (hundreds of thousands of records).

William Ryan eMVP said:
Martin:

You need to have it closed before you can get the results but I'm not sure I
understand the problem. Passing datareaders is problematic and passing them
between layers can be Really really problematic. One of the many problems
is the need for an open connection. Once you close the connection, that's a
wrap... DataReaders only work with an open and available connection and you
can't reopen the connection and have the data waiting for you. You'll
notice that Microsoft's Data Access Application Block has an executeDataSet
method, but not one for datareaders. Because they are pegged to a
connection, passing them around is always going to be a challenged
methodology

Anyway, you probably want to wait until you are iterating through your rows
to get your Return value and at that point, maybe you can kill two birds
with one stone..taking care of your iteration and all in one place.

Another alternative would be to store the results of your reader (assuming
you are only using a field or two ) in a collection of objects that mirror
the fields in your query. However at that point your object would start
looking close enough to a datatable to question why you aren't just using a
datatable in the first place, which is probably a very practical methodology
to employ here

I'm thinking I may not have fully understood your problem though b/c if you
could iterate the loop and get the value at the same time( or get the return
value right after iterating the loop) I"m sure you would have done that.

So if I'm missing the point of the problem, please let me know. BTW, if
you'd like really thoughtful and provocative opinion on problems associated
with passing DataReaders around, drop Kathleen Dollard a line
http://www.gendotnet.com/ and ask her about it. I spoke with her for about
30 minutes on the subject at the MVP summit and it's defintiely something
she's studied in depth.

In the meantime though, let me know if I misunderstood and I'll try to get
your problem fixed now.

Cheers,

Bill

Martin Raychev said:
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;
///.....

}
Click to expand...
Click to expand...
 
W

William Ryan eMVP

Hi Martin:

I just wanted to touch base and make sure you got everything worked out. If
not, let me know.

Bill
Martin Raychev said:
Hi Bill,

Thank you very much for your comprehensive answer. Yes, you got my problem
right however after reading the replies to my post I thought I might not be
doing the right approach. I wanted to get the RETURN_VALUE the stored
procedure returns and wondered why that was not possible. As another
newsgroup user has said it doesn't make any sense of returning @@rowcount
because I still have to iterate thorough all the records of the DataReader.
Now I have done this with a variable summing the number of records.
You and Scott M. proposed to use a DataTable and pass it through methods
instead of passing DataReader, which requires open connection. I think I
will try this. My application now works but I want to do it the most
efficient way.

Thanks for pointing me to Kathleen Dollard and her website - a certainly
interesting area btw (of code generation) and I think I will drop her a
message.

Thanks again,
Cheers

Martin.

I have initially chosen DataReader for performance issues as I am dealing
with large amount of numerical data (hundreds of thousands of records).

William Ryan eMVP said:
Martin:

You need to have it closed before you can get the results but I'm not
Click to expand...
sure
I
understand the problem. Passing datareaders is problematic and passing them
between layers can be Really really problematic. One of the many problems
is the need for an open connection. Once you close the connection,
Click to expand...
that's
a
wrap... DataReaders only work with an open and available connection and you
can't reopen the connection and have the data waiting for you. You'll
notice that Microsoft's Data Access Application Block has an executeDataSet
method, but not one for datareaders. Because they are pegged to a
connection, passing them around is always going to be a challenged
methodology

Anyway, you probably want to wait until you are iterating through your rows
to get your Return value and at that point, maybe you can kill two birds
with one stone..taking care of your iteration and all in one place.

Another alternative would be to store the results of your reader (assuming
you are only using a field or two ) in a collection of objects that mirror
the fields in your query. However at that point your object would start
looking close enough to a datatable to question why you aren't just
Click to expand...
using
a
datatable in the first place, which is probably a very practical methodology
to employ here

I'm thinking I may not have fully understood your problem though b/c if you
could iterate the loop and get the value at the same time( or get the return
value right after iterating the loop) I"m sure you would have done that.

So if I'm missing the point of the problem, please let me know. BTW, if
you'd like really thoughtful and provocative opinion on problems associated
with passing DataReaders around, drop Kathleen Dollard a line
http://www.gendotnet.com/ and ask her about it. I spoke with her for about
30 minutes on the subject at the MVP summit and it's defintiely something
she's studied in depth.

In the meantime though, let me know if I misunderstood and I'll try to get
your problem fixed now.

Cheers,

Bill
Click to expand...
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;
///.....

}
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Datareader to Datatable gets stuck... 3
RETURN VALUES AND OUTPUT PARAMETERS USING THE DATA ACCESS APPLICATION BLOCK 1
I can't stand this compiler.... I spent HOURS (!) trying to make 10
Not getting SqlException using DataReader 4
Stored procedure not returning a value in @RETURN_VALUE 3
Get Return value from SQL Stored Procedure. 2
Output Parameters null when using SqlCommand.ExecuteReader()...? 6
Dynamically Populating a SqlParameter object 5

Top