Connection Pooling, Dispose/Close/Using

Discussion in 'ASP .Net' started by =?Utf-8?B?UGllcnNvbiBD?=, Oct 14, 2004.

  1. 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!
    =?Utf-8?B?UGllcnNvbiBD?=, Oct 14, 2004
    #1
    1. Advertising

  2. 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!
    >
    =?Utf-8?B?Q293Ym95IChHcmVnb3J5IEEuIEJlYW1lcikgLSBN, Oct 15, 2004
    #2
    1. Advertising

  3. =?Utf-8?B?UGllcnNvbiBD?=

    bruce barker Guest

    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" <> wrote
    in message news:...
    > 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!
    > >
    bruce barker, Oct 15, 2004
    #3
  4. =?Utf-8?B?UGllcnNvbiBD?=

    Scott Allen Guest

    Hi Pieson:

    The behavior is odd, because the Dispose implementation in
    SqlConnection will call Close if the connection is in an open state.

    Perhaps one of the methods was missed in the first pass of clean up?

    --
    Scott
    http://www.OdeToCode.com/blogs/scott/

    On Thu, 14 Oct 2004 14:17:04 -0700, Pierson C <Pierson
    > 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!
    Scott Allen, Oct 15, 2004
    #4
  5. Thanks All for the prompt response!

    The odd thing was that all of the using blocks we used were leaving
    connections open. It makes me shy away from implementing that syntax. Is
    there a performance degredation from calling Close() and Dispose() in the
    finally block? Would this explicitly close the pool?

    Pierson

    "Scott Allen" wrote:

    > Hi Pieson:
    >
    > The behavior is odd, because the Dispose implementation in
    > SqlConnection will call Close if the connection is in an open state.
    >
    > Perhaps one of the methods was missed in the first pass of clean up?
    >
    > --
    > Scott
    > http://www.OdeToCode.com/blogs/scott/
    >
    > On Thu, 14 Oct 2004 14:17:04 -0700, Pierson C <Pierson
    > > 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!

    >
    >
    =?Utf-8?B?UGllcnNvbiBD?=, Oct 15, 2004
    #5
  6. Using the finally block is really the only way to make certain that the code
    is executed. The finally block is called regardless of whether an exception
    occcurred or not. Calling both Close() and Dispose() is redundant. Dispose()
    will close the Connection.

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    I get paid good money to
    solve puzzles for a living

    "Pierson C" <> wrote in message
    news:D...
    > Thanks All for the prompt response!
    >
    > The odd thing was that all of the using blocks we used were leaving
    > connections open. It makes me shy away from implementing that syntax. Is
    > there a performance degredation from calling Close() and Dispose() in the
    > finally block? Would this explicitly close the pool?
    >
    > Pierson
    >
    > "Scott Allen" wrote:
    >
    > > Hi Pieson:
    > >
    > > The behavior is odd, because the Dispose implementation in
    > > SqlConnection will call Close if the connection is in an open state.
    > >
    > > Perhaps one of the methods was missed in the first pass of clean up?
    > >
    > > --
    > > Scott
    > > http://www.OdeToCode.com/blogs/scott/
    > >
    > > On Thu, 14 Oct 2004 14:17:04 -0700, Pierson C <Pierson
    > > > 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!

    > >
    > >
    Kevin Spencer, Oct 15, 2004
    #6
  7. So it is true to say that the when implementing using command with
    connections, within the block call Close() as soon as possible? This is
    contrary to much of what I have read(the using block itself is an alternative
    to explicitly calling Close), this would be the culprit of our site's screwy
    behavior.

    Pierson

    "Cowboy (Gregory A. Beamer) - MVP" wrote:

    > 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!
    > >
    =?Utf-8?B?UGllcnNvbiBD?=, Oct 15, 2004
    #7
  8. =?Utf-8?B?UGllcnNvbiBD?=

    Scott Allen Guest

    It would not close the pool but it would 'Close' the connection, or
    return the connection to the free pool depending on the settings
    (pooled by default), and this is the behavior you want. I don't
    understand how it could not be working for you with the using clause

    --.
    Scott
    http://www.OdeToCode.com/blogs/scott/

    On Fri, 15 Oct 2004 07:05:22 -0700, Pierson C
    <> wrote:

    >Thanks All for the prompt response!
    >
    >The odd thing was that all of the using blocks we used were leaving
    >connections open. It makes me shy away from implementing that syntax. Is
    >there a performance degredation from calling Close() and Dispose() in the
    >finally block? Would this explicitly close the pool?
    >
    >Pierson
    >
    Scott Allen, Oct 15, 2004
    #8
  9. SqlConnection.Dispose simply clears the connection string and calls Close.
    Anyone can verify this by taking a look at the code with Reflector or some
    other similar tool.

    These two are functionally equivalent:

    using (SqlConnection myConnection = new
    SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
    {
    SqlCommand myCommand = new SqlCommand();
    myCommand.Connection = myConnection;
    myCommand.CommandText = "sp_DELETE_SOME_STUFF";
    myCommand.CommandType = CommandType.StoredProcedure;
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    }

    and

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

    Calling SqlConnection.Close in the finally block is redundant. Setting the
    myConnection reference to null is unnecessary and has no effect in practice.

    Your observation of connections leaks with the 'using' statement is very
    strange and is contrary to MS documentation
    (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/h
    tml/adonetbest.asp) and to my experience. As far as I know, the
    'using'-statement is still the recommended way of ensuring that your
    SqlConnections get cleaned up properly (at least for C# users, VB.NET coders
    have no option but to use try/finally). What actually happens in the clean
    up depends on connection settings. If connection pooling is on, the physical
    connection is returned to the connection pool, otherwise it is closed.

    Without seeing it with my own eyes, I'd say something else must be happening
    somewhere that is causing the leaks.

    Regards,
    Sami

    "Pierson C" <> wrote in message
    news:...
    > So it is true to say that the when implementing using command with
    > connections, within the block call Close() as soon as possible? This is
    > contrary to much of what I have read(the using block itself is an

    alternative
    > to explicitly calling Close), this would be the culprit of our site's

    screwy
    > behavior.
    >
    > Pierson
    >
    > "Cowboy (Gregory A. Beamer) - MVP" wrote:
    >
    > > 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!
    > > >
    Sami Vaaraniemi, Oct 18, 2004
    #9
  10. =?Utf-8?B?UGllcnNvbiBD?=

    speedy

    Joined:
    Nov 26, 2008
    Messages:
    1
    Finally block

    You should never call Close or dispose in finally block, finally block is for unmanaged object only.

    seach for connection pooling using sql server 2005 on the msdn
    speedy, Nov 26, 2008
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SS
    Replies:
    2
    Views:
    452
    Trond-Eirik Kolloen
    Jul 3, 2003
  2. Antonio Concepcion
    Replies:
    3
    Views:
    2,723
    Antonio Concepcion
    Feb 17, 2005
  3. Simon Harris

    Conn.Close & Conn.Dispose

    Simon Harris, May 31, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    5,966
    Karl Seguin
    Jun 1, 2005
  4. jobs
    Replies:
    5
    Views:
    501
    Anthony Jones
    Nov 17, 2007
  5. SS

    Should I close a connection in a dispose method?

    SS, Jul 3, 2003, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    158
    Duray AKAR
    Jul 3, 2003
Loading...

Share This Page