Statics and connections

[Jason]

Hi

I have a web app that connects to a SQL 2000 server using SqlConnection
class.
my question is this, can i put this SqlConnection into a static variable
somewhere, so that i can initialise it once off and use it for all
connections thereafter?
Basically, would a static SqlConnection variable be problematic in a web
application?

Thanks
Jason

 

[Peter Strøiman]

Don't do that.

If you put that in a static variable, only one connection would exist. If
two people tried to access a web page at the same time, both requests would
use the same database connection, and that would be problematic. E.g. only
one data reader can be supported pr. connection.

So create your connections when you need them and dispose them immediately
when you're done with them. The .NET framework implements a connection pool
that can cache you connections. So even though you create a new connection
in each request, it may still be the same connection that gets reused.

If you're using C# you can use the "using" statement to ensure your
connection is disposed.
using( SqlConnection connection = new SqlConnection( "..." )
{
connection.Open();
...
}

When your code leaves the block, the connection is disposed. That is no
matter whether an exception is thrown, you explicitly return from the
function (calling return), or the code leaves the block normally.

If you're using VB, you can use the try-finally statemene (has exactly the
same effect as the C# example, just uses more lines - the finally block is
always executed)

dim connection as new SqlConnection("...")
try
connection.Open
...
finally
connection.Dispose
end try

 

[Jason]

excellent thanks.

very helpful, and very quick response!

Thanks again