Serious problems keeping an hold on database support in ASP.NET 2

[Edwin Knoppert]

ExecuteNonQuery requires an open and available Connection. The connection's
current state is closed.

Often, i mean a few times a day, the database is no longer reachable.
The appication pool must be reset to activate it again.
I believe after 30minutes it comes alive as well.

What is going on?
I can't find so now and then issues like this.

 

[xhead]

How do you open the connection in the first place? It sounds like you
might be opening the connection once in your web app and leaving it
open...For better performance, you should be opening the Connection as
late as possible and closing it as soon as possible. A connection is an
expensive resource at both the client (web server) and database server
end, and built in connection pooling will help the creation and
disposal of these connections to be very fast.

I would write the code something like this:

' assuming the Command object has already been initialized,
' and its Connection property set
Try
cmd.Connection.Open
cmd.ExecuteNonQuery
Catch ex as Exception
' deal with the exception, or wrap it and throw it for someone else
to deal with
Finally
' Always try to close the connection, even if an exception has
occurred
Try
cmd.Connection.Close
Catch ex as Exception
' ignore any errors when attempting to close the connection
' it could already be closed
End Try
End Try

Mike

 

[Karl Seguin]

Xhead:
Some [minor] comments on your sample code.

1 - I see no reason to rethrow the exception, either deal with it (which
most people can't) or don't catch it at all. There's no advantage to
repackaging it.
2 - Your try inside the finally is dangerous. You are swallowing any
exceptions.. What if conenction.Close() caused an OutOfMemoryException or
ThreadAbortException? You should _never_ swallow exception unless you are
working in a global exception handler. Just check if it's closed and call
close (or better, check if it's nothing and called dispose). If the call to
dispose throws an exception, I'm ready to bet most applications would do
well to crash at this point.

Try
connection.Open
command.ExecuteNonQuery
Finally
if not connection is nothing then
connection.Dispose
end if
if not command is nothing then
command.Dispose
end if
end try

Karl

 

[Edwin Knoppert]

Thanks fellas, will try this tomorrow.
To me the garbage collection should fix this all imo.
Maybe my mistake.

Afaik the destroy of an object, should invoke any close as well isn't?
So the garbage collection terminating the object at some point should in
theory close the connections as well.

Maybe wishful thinking

Thanks,

Xhead:
Some [minor] comments on your sample code.

1 - I see no reason to rethrow the exception, either deal with it (which
most people can't) or don't catch it at all. There's no advantage to
repackaging it.
2 - Your try inside the finally is dangerous. You are swallowing any
exceptions.. What if conenction.Close() caused an OutOfMemoryException or
ThreadAbortException? You should _never_ swallow exception unless you are
working in a global exception handler. Just check if it's closed and call
close (or better, check if it's nothing and called dispose). If the call
to dispose throws an exception, I'm ready to bet most applications would
do well to crash at this point.

Try
connection.Open
command.ExecuteNonQuery
Finally
if not connection is nothing then
connection.Dispose
end if
if not command is nothing then
command.Dispose
end if
end try

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/

How do you open the connection in the first place? It sounds like you
might be opening the connection once in your web app and leaving it
open...For better performance, you should be opening the Connection as
late as possible and closing it as soon as possible. A connection is an
expensive resource at both the client (web server) and database server
end, and built in connection pooling will help the creation and
disposal of these connections to be very fast.

I would write the code something like this:

' assuming the Command object has already been initialized,
' and its Connection property set
Try
cmd.Connection.Open
cmd.ExecuteNonQuery
Catch ex as Exception
' deal with the exception, or wrap it and throw it for someone else
to deal with
Finally
' Always try to close the connection, even if an exception has
occurred
Try
cmd.Connection.Close
Catch ex as Exception
' ignore any errors when attempting to close the connection
' it could already be closed
End Try
End Try

Mike

 

[Edwin Knoppert]

I have a related question.
I wrote me a wrapper which returns a reader, can i still safely close the
connection object?
The function returns the reader object as return value.
A declared(dim) reader receives this return refer and i use it further.

Thanks fellas, will try this tomorrow.
To me the garbage collection should fix this all imo.
Maybe my mistake.

Afaik the destroy of an object, should invoke any close as well isn't?
So the garbage collection terminating the object at some point should in
theory close the connections as well.

Maybe wishful thinking

Thanks,

Xhead:
Some [minor] comments on your sample code.

1 - I see no reason to rethrow the exception, either deal with it (which
most people can't) or don't catch it at all. There's no advantage to
repackaging it.
2 - Your try inside the finally is dangerous. You are swallowing any
exceptions.. What if conenction.Close() caused an OutOfMemoryException
or ThreadAbortException? You should _never_ swallow exception unless you
are working in a global exception handler. Just check if it's closed and
call close (or better, check if it's nothing and called dispose). If the
call to dispose throws an exception, I'm ready to bet most applications
would do well to crash at this point.

Try
connection.Open
command.ExecuteNonQuery
Finally
if not connection is nothing then
connection.Dispose
end if
if not command is nothing then
command.Dispose
end if
end try

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/

How do you open the connection in the first place? It sounds like you
might be opening the connection once in your web app and leaving it
open...For better performance, you should be opening the Connection as
late as possible and closing it as soon as possible. A connection is an
expensive resource at both the client (web server) and database server
end, and built in connection pooling will help the creation and
disposal of these connections to be very fast.

I would write the code something like this:

' assuming the Command object has already been initialized,
' and its Connection property set
Try
cmd.Connection.Open
cmd.ExecuteNonQuery
Catch ex as Exception
' deal with the exception, or wrap it and throw it for someone else
to deal with
Finally
' Always try to close the connection, even if an exception has
occurred
Try
cmd.Connection.Close
Catch ex as Exception
' ignore any errors when attempting to close the connection
' it could already be closed
End Try
End Try

Mike

Edwin Knoppert wrote:
ExecuteNonQuery requires an open and available Connection. The
connection's
current state is closed.

Often, i mean a few times a day, the database is no longer reachable.
The appication pool must be reset to activate it again.
I believe after 30minutes it comes alive as well.

What is going on?
I can't find so now and then issues like this.

 

[Edwin Knoppert]

I believe this topic answers my questrion:

http://forums.asp.net/777619/ShowPost.aspx

Note that this part. forum has extensive faq's.

http://forums.asp.net/13/ShowForum.aspx

I have a related question.
I wrote me a wrapper which returns a reader, can i still safely close the
connection object?
The function returns the reader object as return value.
A declared(dim) reader receives this return refer and i use it further.

Thanks fellas, will try this tomorrow.
To me the garbage collection should fix this all imo.
Maybe my mistake.

Afaik the destroy of an object, should invoke any close as well isn't?
So the garbage collection terminating the object at some point should in
theory close the connections as well.

Maybe wishful thinking

Thanks,

Xhead:
Some [minor] comments on your sample code.

1 - I see no reason to rethrow the exception, either deal with it (which
most people can't) or don't catch it at all. There's no advantage to
repackaging it.
2 - Your try inside the finally is dangerous. You are swallowing any
exceptions.. What if conenction.Close() caused an OutOfMemoryException
or ThreadAbortException? You should _never_ swallow exception unless
you are working in a global exception handler. Just check if it's
closed and call close (or better, check if it's nothing and called
dispose). If the call to dispose throws an exception, I'm ready to bet
most applications would do well to crash at this point.

Try
connection.Open
command.ExecuteNonQuery
Finally
if not connection is nothing then
connection.Dispose
end if
if not command is nothing then
command.Dispose
end if
end try

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/


How do you open the connection in the first place? It sounds like you
might be opening the connection once in your web app and leaving it
open...For better performance, you should be opening the Connection as
late as possible and closing it as soon as possible. A connection is an
expensive resource at both the client (web server) and database server
end, and built in connection pooling will help the creation and
disposal of these connections to be very fast.

I would write the code something like this:

' assuming the Command object has already been initialized,
' and its Connection property set
Try
cmd.Connection.Open
cmd.ExecuteNonQuery
Catch ex as Exception
' deal with the exception, or wrap it and throw it for someone else
to deal with
Finally
' Always try to close the connection, even if an exception has
occurred
Try
cmd.Connection.Close
Catch ex as Exception
' ignore any errors when attempting to close the connection
' it could already be closed
End Try
End Try

Mike

Edwin Knoppert wrote:
ExecuteNonQuery requires an open and available Connection. The
connection's
current state is closed.

Often, i mean a few times a day, the database is no longer reachable.
The appication pool must be reset to activate it again.
I believe after 30minutes it comes alive as well.

What is going on?
I can't find so now and then issues like this.