Saturday, February 25, 2012

Need urgent help to sort this out!

I've made this example and it loads a picture into a database. (MsSql )
Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"
Is it supposed to be like this or is it supposed to be reemoved after .net is finished??
Code snip
_______________________________________________________

Dim connAsNew SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")

Dim cmdAsNew SqlCommand("Select * from tab_bild", cnn)

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

conn.Close()

Response.Write("Picture info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

EndTry

EndSub
________________________________________________________________

Please can someone explain this for me or sort this out for me.
All help is welcome even if its only points me too a direction.

Regards
Tombola

You should move yourconn.Close() into a Finally block.
|||Thanks for the reply Morton!
I suspect that you mean something like this.
----------------

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

Response.Write("Bild info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

Finally

conn.Close()

EndTry


----------------
However it still leaves a sleeping process, but it will eventually time out, and be killed, I suppose.
I dont think this would bee such a good solution tough, what if the server is short off memory and there is a lot of sleeping processes that just waits to be timed out. The system would eventually freeze I think. Both IIS and MsSql harvests memory if I remember right.
Anyway the question would still be, Should the .net SqlClient leave a sleeping process on the server when the SqlClient is finished with all its doings?
Regards
Tombola|||I believe what you are seeing is a phenomenon of connection pooling. This is a good thing.
You should likely add a cmd.Dispose() after the conn.Close(). AndI would also add a conn.Dispose() after the conn.Close() for goodmeasure. It's a good idea to Dispose any object which implementthe IDisposable interface once you are done with it.

No comments:

Post a Comment