what are the ways to ensure a sql connection is close?

[Guest]

greetings, besides using objconn.State() to find out if a sql connection is
close or open, is there any other way to accomplish this task? thanks in
advance.
anywhere is sql server or the machine to tell this?

 

[Karl]

You can call Dispose() on the SQlConnection.....this will ensure that the
connection is closed, and won't cause any harm if it already is (it itself
checks to see the state of the connection).

All you need to make sure is that you aren't calling dispose() on a null
reference (of course).

Karl

 

[Tom Dacon]

What's wrong with the State property? There aren't many things in life that
are much much easier than this. What could be simpler than:

if ( objconn.State == ConnectionState.Closed )

???

Of course, if you want to know if it's NOT closed then I'm sure you know
that it would be:

if ( objConn.State != ConnectionState.Closed )


In considerable puzzlement,
Tom Dacon
Dacon Software Consulting

 

[Guest]

thanks for the reply dacon... the .state works fine... but what i would like
to know is that, are they any other ways to tell if a connection is close?
e.g. from the sql server we could use sp_who *which i know your pretty
fimiliar with, how about other ways. can i tell it from the machine? thanks
dacon for the reply.

 

[Guest]

thanks for the reply dacon... the .state works fine... but what i would like
to know is that, are they any other ways to tell if a connection is close?
e.g. from the sql server we could use sp_who *which i know your pretty
fimiliar with, how about other ways. can i tell it from the machine? thanks
dacon for the reply.

 

[Girish bharadwaj]

Why?

--
Girish Bharadwaj
http://msmvps.com/gbvb

thanks for the reply dacon... the .state works fine... but what i would
like
to know is that, are they any other ways to tell if a connection is close?
e.g. from the sql server we could use sp_who *which i know your pretty
fimiliar with, how about other ways. can i tell it from the machine?
thanks
dacon for the reply.

<snip/>

 

[Matze]

Why?

why why?

There are many good reasons do look for open connections. use select *
from sysprocesses (master db) in Queryanalyzer and filter results for
yur dbid. If you dont know dbid use select * from sysdatabases where
name = 'mydb'.
I wish there was a way to break into the debugger when a new
connection is opened...that would make hunting for unclosed
connections much more easy.

There is also a way to use EM but this methods works beter for me (no
caching in UI)

regards
Mathias Fritsch

 

[Girish Bharadwaj]

Ok. That was a bit cryptic. The question was answered in other places and
the OP asked for other ways to do this. Well, If the OPs question was not
answered by the answers here, I wanted to understand why this was needed so
that we might be able to come up with a better solution.

 

[Bliss]

Common practice for me is to put the Connection.Close() in the finally
block of my service layer. The service layer opens the connection and
passes it to the DAO layer for use. After the DAO layer retunrs to the
service layer the finally block will close it. However with data
readers that need to be passed back to the view or the code behind
this will not work. SO make sure to open all data readers with the
correct constructor =-)

B