closing a sqldatareader connection.

[Guest]

greeting,
attach below are my codes for a sqldatareader method, it returns a
sqldatareader. if you notice, i have my close connection in the finally
method. anyway, since its returning a sqldatareader, i cannt read it and i
get an error stating that it cannot be read while the connection is close, if
i comment the connection.close method, it works fine. but how about the
connection? how to i close it? thanks in advance for your reply.

Public Function DistiByLogin(ByVal strLoginId As String) As SqlDataReader

Dim objGetConn As New DATTConnection.DATTDataAccess
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim objErr As New ErrorHandler
Dim objReader As SqlDataReader

Try

objConn = objGetConn.GetSQLConnection(objErr)
objConn.Open()

objCmd.Connection = objConn
objCmd.CommandType = CommandType.StoredProcedure
objCmd.CommandText = "pr_DISTI_CMF_NO"
objCmd.Parameters.Add("@LoginId", SqlDbType.NVarChar, 80).Value
= strLoginId

objReader = objCmd.ExecuteReader

Return objReader

Catch ex As Exception

Finally
objConn.Close()
objConn.Dispose()
objCmd.Dispose()
objGetConn = Nothing
objCmd = Nothing
End Try

 

[W.G. Ryan eMVP]

You can't the way you are doing it. You may want to pass in the connection
and/or command with a connection and pass that connection in. Remember that
ADO.NET has 'connected' object and 'disconnected' objects. So you can use a
DataSet for instance with or without ever having a database and a connection
to it. As far as IDBCommand Objects (SqlCommand, OleDbCommand etc) and
IDataReader objects (OleDbDataReader, SqlDataReader) you must have a
connnection and they can only do something while the connection is both open
and available. Architecturally, dataReaders aren't something you want to
pass between layers so you don't really isolate much by specifically
avoiding passing in the connection although this is a bit of an
oversimplified statement. Check out the way the Data Access Application
Block treats the issue
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
for a good feel for how you can do it - but basically just pass in your
connection. Also, you may find Angel's post
http://weblogs.asp.net/angelsb/archive/2004/10/08/240123.aspx
interesting although it's not directly addressed to the problem you're
having. Keeping that connection.Close in a finally block is a wise choice-
one of those Always do kind of things.

HTH,

Bill

 

[Guest]

hi asha
u can do one thing

when u execute command do it like this
SqlDataReader myReader =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);

and remove this line
objConn.Close() from the finally block;

the good thing about this is that, after u close the reader the connection
is automatically closed

regards
ansil

 

[Hermit Dave]

as Ryan mentioned, by design DataReaders aren't supposed to be passed
around. You could use mechanism suggested by Ansil but if you need to pass
data around you should consider using DataSets which are disconnected
objects.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Guest]

thanks for the reply. if speed was my concern, would it be wise to query a
sqldatareader 1st then only pass it into a dataset manually by looping at it?

 

[Hermit Dave]

well datareaders are used for which processing. like say you need to pass a
custom collection or an arraylist. in those scenarios you would need to
iterate and populate the collection before passing it. DataSet on the other
hand is a sort of collection object. and you wouldnt benifit from using
readers to populate the dataset.
unless ofcourse you want to check the value of current row before deciding
whether you wish to add it to the returned results or not.

i would suggest directly using DataAdapter.Fill to populate the dataset and
then returning it and ofcourse closing the connection.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[W.G. Ryan eMVP]

Asha:

DataAdapters use DataReaders under the scenes for their .Fill method - so
looping through it and filling it manually would provide at best a marginal
performance benefit with a lot more code.

 

[Miha Markic [MVP C#]]

Asha:

DataAdapters use DataReaders under the scenes for their .Fill method - so
looping through it and filling it manually would provide at best a
marginal
performance benefit with a lot more code.

Au contraire
It would be slower since there are few tweaks within Fill method.

 

[W.G. Ryan eMVP]

Actually Miha, I do agree with you. I've tried it a few times and seen that
..Fill was slightly faster in each instance. The ultimate point I was trying
to make was simply that even if it did work 'faster', any benefit realised
would be more than offset by the extra coding and maintenance time, not to
mention higher probability of an error/exception being raised b/c, well,
chances are that Fill has been tested better than most stuff we'd write.

When I tried the comparison, I turned off Constraints and used index based
references instead of nominal based ones - however I wasn't sure I actually
coded it the 'best' way I could have but I still think you're right.