Re: Statics and connections

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Peter Strĝiman (blah_at_blahblahblah)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 16:20:17 +0100

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" <c_bananas@mighty.co.za> wrote in message
news:u0AO3W62EHA.3452@TK2MSFTNGP14.phx.gbl...
> 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
>
>

Relevant Pages

  • Re: the difference between SqlConnection.IDisposable.Disp­ose() and SqlConnection.Dispose()
    ... Framework code to see what it does. ... As long as you use Close on the Connection you're ... Sure, you can call Dispose if you want to, but it won't help the ... > isn't a property or something of SqlConnection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Ideal way to retrive dataset.
    ... In general it is a good idea to call dispose on any method that implements ... The best way to use the SqlConnection object in c# is ... closeand disposeconnection, and dispose SqlDataAdapter also. ... the connection will go back to the pool in this case. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: scalability of single SqlConnection instance
    ... Do you mean lock on the specific SqlConnection instance being used for the operation? ... Yes, you can consider a new "feature" that can permit multiple operations on a single connection, but it has so many side-effects, I don't recommend it. ... I think that technically my architecture is thread-safe (since only a single thread can make use of my single SqlConnection instance at once), but it's hardly a desirable way to do things. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Question about Dispose
    ... There is a common misconception going around regarding SqlConnection close ... Dispose does only two things, first it clears the SqlConnection connection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Question about Dispose
    ... There is a common misconception going around regarding SqlConnection close ... Dispose does only two things, first it clears the SqlConnection connection ...
    (microsoft.public.dotnet.framework.adonet)