Detect Connection Leaks? 1

[vbkris]

hi guys,

this is what I have currently in my data access layer:

'Open Connection stmt
.......

'The actual execution
Dim ObjDtReader As SqlDataReader = objComnd.ExecuteReader(CommandBehavior.SingleRow)
If (ObjDtReader.HasRows) Then
ObjDtReader.Read()
End If
return ObjDtReader

if you notice, i NEVER close the connection in that statement.

I have the following doubts:
1. Does the connection close automatically for CommandBehavior.SingleRow command behaviour? I was unabe to close the connection in the above method because the reader object has to be returned back to the calling code.
I close the reader object in the calling code. will that close the connection???

2. If not what is the best way to detect leaks? I tried using the sp_who, but all it said in query analyser was "AWAITING COMMAND". thats it.

any help is appreciated...




Known is handfull, Unknown is worldfull

 

[JohnYingling]

This from MSDN
CommandBehavior Enumeration
CloseConnection When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[vbkris]

hi jonh,

my problem is with the CommandBehavior.SingleRow behavoir. lets say you write a function that will return back a singe row from the DB, for this you write a function like this:

function GetSingleRow() as DataReader

'Open Connection stmt
.......

'The actual execution
Dim ObjDtReader As SqlDataReader = objComnd.ExecuteReader(CommandBehavior.SingleRow)
If (ObjDtReader.HasRows) Then
ObjDtReader.Read()
End If
return ObjDtReader

end function


I cannot close the connection as I have to return back the reader. therefore I wanted to know if the single row behavior will automatically close the connection when the reader closes...

Known is handfull, Unknown is worldfull

 

[ca8msm]

Does the connection close automatically for CommandBehavior.SingleRow command behaviour?

No, it doesn't.

I close the reader object in the calling code. will that close the connection???

No, it won't with your current code but you could modify it to do so by adding CommandBehavior.CloseConnection to the execute reader method e.g.

MyCommand.ExecuteReader(CommandBehavior.SingleRow & CommandBehavior.CloseConnection)

You should then close the Reader from the sub that calls the function which will then trigger the connection to be closed.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

wow, never knew that could be done. from where excatly did you source that out???

Known is handfull, Unknown is worldfull

 

[ca8msm]

I just assumed it could be as the help file for the CommandBehavior states:

The CommandBehavior values are used by the ExecuteReader method of IDbCommand and any classes derived from it.

[b]A bitwise combination of these values may be used.[/b]

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

and any ideas to track connection leaks???

Known is handfull, Unknown is worldfull

 

[ca8msm]

You could have a look at SQL Profiler and see the current activity


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

you can check connection leaks using the profiler??? one more new info. how excactly do you do that???

Known is handfull, Unknown is worldfull

 

[jbenson001]

You can use sp_who to see if a conntion is active.

 

[ca8msm]

you can check connection leaks using the profiler??? one more new info. how excactly do you do that???

All I said was that it could be used to see the current activity. You could the Profiler in conjuction with sp_who to see what command is being run and whether the connection exists. Once the profiler reports that the command has finished running, you know that any database commands should be cleaned up.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

hmm, will try that...

Known is handfull, Unknown is worldfull

 

[chrissie1]

But don't forget that .net does it's own connectionpooling. It's not because you say it to close that it will do that right away. I know the connection object has a property to make sure that it does close I thought it was something like closeallpools.

Or you could add something to the connectionstring.

I know E&F mentioned this somewhere lately. I see if I can find the thread.

Christiaan Baes
Belgium

"My new site" - Me

 

[chrissie1]

here it is

thread796-1256940

Christiaan Baes
Belgium

"My new site" - Me