Re: Connection Pooling, Dispose/Close/Using

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

From: bruce barker (nospam_brubar_at_safeco.com)
Date: 10/15/04 

Date: Thu, 14 Oct 2004 17:42:54 -0700

1) unlike C++, the setup for a try/catch is cheap, and there is no test per
line of (look at the il). a throw is expensive though.

2) for sql connections the following is best

  SqlConnection myConnection =
        new
SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
  try
  {
    SqlCommand myCommand = new SqlCommand();
    myCommand.Connection = myConnection;
    myCommand.CommandText = "sp_DELETE_SOMESTUFF";
    myCommand.CommandType = CommandType.StoredProcedure;
    myConnection.Open();
    myCommand.ExecuteNonQuery();
  }
  finally
  {
    myConnection.Close();
  }

you want to close a connection as soon as possible, as this will return the
unmanged connection back to the pool. also Close() handles releasing all
unmanged resources, so no need to call Dispose, you can leave this for the
GC. your leak must be a bug, because Displose should call Close.

-- bruce (sqlwork.com)

"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> wrote
in message news:0117408E-7628-4289-AD06-5912DA25549A@microsoft.com...
> My preference is finally, as it gives me the opportunity to add a catch in
> error conditions. Realize, however, that the try actually runs a bit
slower,
> as it evaluates each statement run to ensure there are no exceptions,
> regardless of whether you catch or not. I find using best for cases where
you
> are fairly certain you will not end in exception. I am sure there are some
> who will disagree.
>
> To your example: Although you are allowing auto dispose, with using, you
are
> not closing your connection explicitly. This means you could, potentially,
> have tied resources until the GC comes along. The object is marked as
> disposed, but that does not mean it is cleaned up yet. If you want to go
back
> to your first method, explicitly close() the conn, and you should see an
> improvement. According to what I have read of internals, the underlying
> object is released when the object is closed. You are relying on the
implicit
> Dispose() to Close() your object in your first example. That is not good.
>
>
> ---
>
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ***************************
> Think Outside the Box!
> ***************************
>
>
> "Pierson C" wrote:
>
> > I am developing on a website that is utilizing SQL Server 2000. Shortly
> > after deploying the site, we began having timeout issues due to the max
> > connections.
> >
> > 1st instinct was to diligently tidy up our connections. We did so by
> > encapsulating with the using statement. Ex:
> >
> > using (SqlConnection myConnection = new
> >
> > SqlConnectionConfigurationSettings.AppSettings["ConnectionString"]))
> > {
> > SqlCommand myCommand = new SqlCommand();
> > myCommand.Connection = myConnection;
> > myCommand.CommandText = "sp_DELETE_SOME_STUFF";
> > myCommand.CommandType = CommandType.StoredProcedure;
> > myConnection.Open();
> > myCommand.ExecuteNonQuery();
> > }
> >
> > The problem continued, obvious by monitoring in SQL Server(Management >
> > Current Activity > Process Info). Though my team's research indicated
the
> > code would be handled the same, we closed and disposed the connection in
the
> > finally block. Ex:
> >
> > try
> > {
> > SqlCommand myCommand = new SqlCommand();
> > myCommand.Connection = myConnection;
> > myCommand.CommandText = "sp_DELETE_SOMESTUFF";
> > myCommand.CommandType = CommandType.StoredProcedure;
> > myConnection.Open();
> > myCommand.ExecuteNonQuery();
> > }
> > finally
> > {
> > myConnection.Close();
> > myConnection.Dispose();
> > myConnection = null;
> > }
> >
> > To our amazement this worked and our users on the database have
decreased to
> > the level of our expectation.
> >
> > All I have read say these two methods are equals; What is the logic that
> > should be followed as to when to implement each technique? Are there
> > variables with in our server/database envronments that could cause this
> > difference?
> >
> > Many thanks!
> >

Quantcast