Problems using HasRows

[Robert Scheer]

Hi.

I am using VS.NET 2003. I have a function that runs a procedure and
returns a SqlDataReader:

Public Overloads Function RunProcedure(ByVal StoredProcName As String,
_
ByVal Parameters As IDataParameter()) As SqlDataReader

Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim returnReader As SqlDataReader

Try
cn = New SqlConnection
cn.ConnectionString = strConnectionString
cn.Open()

cmd = BuildQueryCommand(StoredProcName, Parameters)
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
returnReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return returnReader

End Function

When I try to use HasRows on the caller routine, I receive the error:
"Invalid attempt to HasRows when reader is closed".
If I remove the CommandBehaviour.CloseConnection option from the
DataReader this error goes away. Can't I use HasRows and
CommandBehaviour.CloseConnection?

Thanks,
Robert Scheer

 

[William Ryan eMVP]

CloseConnection simply closes the connection when the reader is closed.
That's very weird this all works if you take it out. In general, passing
readers is bad news and most of the time, real bad news. MS even left
readeres out of the Data Access Application blocks.

If you call HasRows in this block, it will work regardless of the
closeconnection. The fact you don't have a connection in the calling class
is probably more bad news. You may want to pass that in.

Just one other poitn. If you don't use CloseConnection, there's no way with
the way you created this to explicitly close the connection. That's a big
deal b/c it won't go back to the connection pool until a time unknown. Its
definitely something you don't want to do. So why not pass in the
connection from the caller, then you can close your connection and have all
the benefits. however, don't take that as my recommendation to pass readers
around.

HTH,

Bill
HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/