Connection Pools and the "Using" keyword

[MLynch]

I have a question regarding whether to use "Using" when connecting to SQL
Server via ASP.NET

I noticed in Microsoft's PetShop reference application they used the "Using"
keyword to ensure proper disposal of the connection object. But by
employing the "Using" keyword do you negate the benefits of the DB
Connection Pool. Or are connection pools even used in ASP.NET due the
statelessness of the web requests?

So to use using or not in asp.net?

- M Lynch

--- USE THIS ---

using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("spFunction", conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}

--- OR JUST THIS ---

try
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("spFunction", conn);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
}
finally
{
conn.Close();
}

 

[William \(Bill\) Vaughn]

"Using" is a C# construct that essentially closes/disposes of objects
created within the Using scope. No, it does not negate the operation of the
pool. Actually, by ensuring that the connections are closed before leaving
scope it helps ensure that connections are not accidentally orphaned in the
pool.

In your code examples, I like the Finally approach. It's safer as it closes
the connection if things go wrong. I would, however add code to ensure that
the connection object exists before trying to open it...

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Kevin Yu [MSFT]]

Hi M Lynch,

I agree with William's idea that the using statement is only a C# language
specification to ensure the proper disposal of objects. However, connection
pool will not be affected by this.

For more information, please refer to the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/csref/html/
vclrfusingstatement.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: "William \(Bill\) Vaughn" <[email protected]>
| References: <#[email protected]>
| Subject: Re: Connection Pools and the "Using" keyword
| Date: Wed, 29 Oct 2003 13:45:43 -0800
| Lines: 71
| Organization: Beta V Corporation
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: smtp.betav.com 209.20.250.241
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:64850
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| "Using" is a C# construct that essentially closes/disposes of objects
| created within the Using scope. No, it does not negate the operation of
the
| pool. Actually, by ensuring that the connections are closed before leaving
| scope it helps ensure that connections are not accidentally orphaned in
the
| pool.
|
| In your code examples, I like the Finally approach. It's safer as it
closes
| the connection if things go wrong. I would, however add code to ensure
that
| the connection object exists before trying to open it...
|
| hth
|
| --
| ____________________________________
| William (Bill) Vaughn
| Author, Mentor, Consultant
| MVP, hRD
| www.betav.com
| Please reply only to the newsgroup so that others can benefit.
| This posting is provided "AS IS" with no warranties, and confers no
rights.
| __________________________________
|
| | > I have a question regarding whether to use "Using" when connecting to
SQL
| > Server via ASP.NET
| >
| > I noticed in Microsoft's PetShop reference application they used the
| "Using"
| > keyword to ensure proper disposal of the connection object. But by
| > employing the "Using" keyword do you negate the benefits of the DB
| > Connection Pool. Or are connection pools even used in ASP.NET due the
| > statelessness of the web requests?
| >
| > So to use using or not in asp.net?
| >
| > - M Lynch
| >
| > --- USE THIS ---
| >
| > using (SqlConnection conn = new SqlConnection(connectionString))
| > {
| > SqlCommand command = new SqlCommand("spFunction", conn);
| > command.CommandType = CommandType.StoredProcedure;
| > conn.Open();
| > command.ExecuteNonQuery();
| > conn.Close();
| > }
| >
| > --- OR JUST THIS ---
| >
| > try
| > {
| > SqlConnection conn = new SqlConnection(connectionString);
| > SqlCommand command = new SqlCommand("spFunction", conn);
| > command.CommandType = CommandType.StoredProcedure;
| > conn.Open();
| > command.ExecuteNonQuery();
| > }
| > finally
| > {
| > conn.Close();
| > }
| >
| >
| >
| >
| >
| >
|
|
|

 

[Pablo Castro [MS]]

As Bill says, it actually HELPs ensure the connection pooler gets its
connections right away. (otherwise the app logic may miss the
connection.Close() and that connection would be leaked)

Just to clarify: if you're using a "using" statement for the connection
object, there is NO need for a finally block to close the connection, nor to
close the connection inside the "using" block. "using" will provide the same
guarantees that "finally" provides on calling Dispose() on the connection.


--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.