473,320 Members | 2,092 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Connection Pooling

I have a static function in a class, everytime I call this function, I am
creating a SQLconnection, open it, use it, and null it, All my functions and
application logic is like this,

Every connection is creating self connection object and null it after the
some process,

Is this wrong ?

One of my friend told me about "Connection Pooling", and I am confused
enough, Only one connection can be used in all part of the applicaton
(whether web or windows application)

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public class Langs

{

public Langs()

{

}

public static void GetLanguageList()

{

SqlConnection ConnLocal = new
SqlConnection(DataFace.GetSQLConnectionString());

try

{

SqlCommand CommLocal = new SqlCommand();

CommLocal.Connection = ConnLocal;

CommLocal.CommandText += "SELECT bla bla bla";

ConnLocal.Open();

SqlDataReader rdLocal =
CommLocal.ExecuteReader(CommandBehavior.CloseConne ction);

if (rdLocal.HasRows)

{

while (rdLocal.Read())

{

}

if (rdLocal.IsClosed != false)

{

rdLocal.Close();

}

}

catch (Exception Exx)

{

throw Exx

}

finally

{

ConnLocal = null;

}

}

}
Oct 8 '07 #1
15 3239
On Oct 8, 8:02 am, "Sylvie" <s...@sss.sswrote:
I have a static function in a class, everytime I call this function, I am
creating a SQLconnection, open it, use it, and null it
Setting a local variable to null just before exiting the method is
completely pointless.
You should be *closing* the connection, not just setting a variable to
null.

The easiest way to do this is with a "using" statement:

using (SqlConnection conn = new SqlConnection(...))
{
...
}

As for connection pooling - that happens transparently. You don't need
to code for it.

Jon

Oct 8 '07 #2
If I close the connection before set it to null, My approach is correct ?

Closing and disposing connection is really clears the connection from the
pool ?

Or Is there a way to use old connections ?

Thanks for all

"Jon Skeet [C# MVP]" <sk***@pobox.com>, iletide sunu yazdi
news:11*********************@g4g2000hsf.googlegrou ps.com...
On Oct 8, 8:02 am, "Sylvie" <s...@sss.sswrote:
>I have a static function in a class, everytime I call this function, I am
creating a SQLconnection, open it, use it, and null it

Setting a local variable to null just before exiting the method is
completely pointless.
You should be *closing* the connection, not just setting a variable to
null.

The easiest way to do this is with a "using" statement:

using (SqlConnection conn = new SqlConnection(...))
{
...
}

As for connection pooling - that happens transparently. You don't need
to code for it.

Jon

Oct 8 '07 #3
Sylvie wrote:
I have a static function in a class, everytime I call this function, I am
creating a SQLconnection, open it, use it, and null it, All my functions and
application logic is like this,

Every connection is creating self connection object and null it after the
some process,

Is this wrong ?
The code you posted probably works okay, but it does suggest some
misunderstanding of how .NET and C# work.

In particular, nulling the local variable ConnLocal has no useful
effect. What you really want to do there is call ConnLocal.Dispose().
You've already specified that the connection be closed after the command
is executed, so it's likely that most if not all resources that need to
be disposed have already been freed up, but you really should do both
when you are done with the connection. You're only doing the Close() bit.

The docs say that Close() and Dispose() are functionally equivalent but
IMHO they are not semantically equivalent, and I've seen at least one
thread that suggests that in the future they could wind up not being the
same.

As long as I'm providing suggestions, a more-readable alternative to
something like this:

SqlConnection ConnLocal = ...;

try
{
}
catch (Exception Exx)
{
throw Exx;
}
finally
{
ConnLocal.Dispose();
}

would be:

using (SqlConnection ConnLocal = ...)
{
}

Also note that your catch clause is unnecessary. It does nothing but
rethrow the exception, which would happen anyway without that clause.
As an added bonus, if I recall correctly having the exception rethrown
at that point resets the stack trace in the exception instance, making
it harder to figure out what actually went wrong.

Now, as far as the connection pooling goes...
One of my friend told me about "Connection Pooling", and I am confused
enough, Only one connection can be used in all part of the applicaton
(whether web or windows application)
Assuming you are consistently creating the same connection over and
over, it usually makes sense to just create the connection once and
reuse it. The thing I don't understand here with respect to your
question is that, as far as I know, connection pooling already happens
by default when you use a SqlConnection object.

This article has some more information on the implementation of
connection pooling in .NET:
http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx

I believe that unless you specifically disable pooling, it happens
automatically. So it's not really clear to me what is your friend may
have suggested you should do differently.

Pete
Oct 8 '07 #4
On Oct 8, 8:27 am, "Sylvie" <s...@sss.sswrote:
If I close the connection before set it to null, My approach is correct ?
Close the connection but don't bother setting the variable to null.
It's about to go out of scope anyway - it doesn't make any difference
to anything.

I would strongly advise the use of "using" statements though - you
should use that for your SqlDataReader too.
Closing and disposing connection is really clears the connection from the
pool ?

Or Is there a way to use old connections ?
Closing the connection *returns* it to the pool, so it can be reused.
You have to do more work to close the underlying connection, which you
typically don't want to do anyway.

Jon

Oct 8 '07 #5
Sylvie wrote:
If I close the connection before set it to null, My approach is correct ?
There's no point in setting it to null. You are already closing the
connection. You just need to Dispose() it as well. The using()
statement accomplishes that in the same way that putting an explicit
call to Dispose() in the finally clause of a try/finally block would.
Closing and disposing connection is really clears the connection from the
pool ?
No. Closing the connection returns the connection to the connection
pool so that it can be reused. It will stay there for some period of
time, so that if you use the same connection string shortly after, no
new connection needs to be created. The one you used before is given
back to you.
Or Is there a way to use old connections ?
Yes. See above.

Pete
Oct 8 '07 #6
Closing and disposing connection is really clears the connection from the
pool ? Or Is there a way to use old connections ?
Pooling happens *undernearth* the managed connection class
(SqlConnection in this case). Open() will attempt to grab a connection
from the pool; Close() / Dispose() merely returns the underlying
connection to the pool for re-use. As Jon observers, the set-to-null
is unnecessary. The important thing is remembering to Close() /
Dipose() the connection, and again as Jon observers, "using" is the
simplest way to do this robustly (i.e. on success and on failure).

Typical code would be (to extend Jon's):

using (SqlConnection conn = new SqlConnection(...))
{
// perhaps prepare a command while closed
conn.Open();
// use the connection
conn.Close(); // the Dipose() from "using" will also
// handle this, but I like to balance ;-p
}

Marc

Oct 8 '07 #7
Peter Duniho wrote:
Sylvie wrote:
>If I close the connection before set it to null, My approach is correct ?

There's no point in setting it to null. You are already closing the
connection.
Ugh. I think the above two sentences would make more sense written as
one: "There's no point in setting it to null, and you are already
closing the connection."

The way I wrote it the first time, it almost made it look like I was
suggesting that setting the variable to null has the effect of closing
the connection. That's definitely not what I meant to write. Sorry!
Oct 8 '07 #8
On Oct 8, 8:47 am, Marc Gravell <marc.grav...@gmail.comwrote:

<snip>
Typical code would be (to extend Jon's):

using (SqlConnection conn = new SqlConnection(...))
{
// perhaps prepare a command while closed
conn.Open();
// use the connection
conn.Close(); // the Dipose() from "using" will also
// handle this, but I like to balance ;-p
}
I can understand the preference for balance, but then I would end up
worrying if I didn't have the Close call in a finally block (with the
try coming after the Open) - otherwise the balance is gone. At that
point, given that the Close call is unnecessary, I think it becomes
more hassle than it's worth.

Jon

Oct 8 '07 #9
Thanks for your response,

I am ok now, ( I ll send this thread link to my friend :) )

But, if it s unnecessary to use throw statement in catch block, How can I
handle the error

In my webb application, throw or not, exception goes to Application Error
event and I simply transfer this Exception to my Specific error page, after
this I clear the error

In Windows Application, what may be the equivalent of this ?

I think, next thing that I wanna discover is Exception Handling

Thanks for all
"Peter Duniho" <Np*********@NnOwSlPiAnMk.com>, iletide sunu yazdi
news:13************@corp.supernews.com...
Sylvie wrote:
>I have a static function in a class, everytime I call this function, I am
creating a SQLconnection, open it, use it, and null it, All my functions
and application logic is like this,

Every connection is creating self connection object and null it after the
some process,

Is this wrong ?

The code you posted probably works okay, but it does suggest some
misunderstanding of how .NET and C# work.

In particular, nulling the local variable ConnLocal has no useful effect.
What you really want to do there is call ConnLocal.Dispose(). You've
already specified that the connection be closed after the command is
executed, so it's likely that most if not all resources that need to be
disposed have already been freed up, but you really should do both when
you are done with the connection. You're only doing the Close() bit.

The docs say that Close() and Dispose() are functionally equivalent but
IMHO they are not semantically equivalent, and I've seen at least one
thread that suggests that in the future they could wind up not being the
same.

As long as I'm providing suggestions, a more-readable alternative to
something like this:

SqlConnection ConnLocal = ...;

try
{
}
catch (Exception Exx)
{
throw Exx;
}
finally
{
ConnLocal.Dispose();
}

would be:

using (SqlConnection ConnLocal = ...)
{
}

Also note that your catch clause is unnecessary. It does nothing but
rethrow the exception, which would happen anyway without that clause. As
an added bonus, if I recall correctly having the exception rethrown at
that point resets the stack trace in the exception instance, making it
harder to figure out what actually went wrong.

Now, as far as the connection pooling goes...
>One of my friend told me about "Connection Pooling", and I am confused
enough, Only one connection can be used in all part of the applicaton
(whether web or windows application)

Assuming you are consistently creating the same connection over and over,
it usually makes sense to just create the connection once and reuse it.
The thing I don't understand here with respect to your question is that,
as far as I know, connection pooling already happens by default when you
use a SqlConnection object.

This article has some more information on the implementation of connection
pooling in .NET:
http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx

I believe that unless you specifically disable pooling, it happens
automatically. So it's not really clear to me what is your friend may
have suggested you should do differently.

Pete

Oct 8 '07 #10
Thanks for your response,

If I need 2 connections with the same Connection string ? How can I create
the second one ?

I know that there are some situations that active connection couldnt be used
cos of the "connection is in use" problem

"Jon Skeet [C# MVP]" <sk***@pobox.com>, iletide sunu yazdi
news:11**********************@y42g2000hsy.googlegr oups.com...
On Oct 8, 8:27 am, "Sylvie" <s...@sss.sswrote:
>If I close the connection before set it to null, My approach is correct ?

Close the connection but don't bother setting the variable to null.
It's about to go out of scope anyway - it doesn't make any difference
to anything.

I would strongly advise the use of "using" statements though - you
should use that for your SqlDataReader too.
>Closing and disposing connection is really clears the connection from the
pool ?

Or Is there a way to use old connections ?

Closing the connection *returns* it to the pool, so it can be reused.
You have to do more work to close the underlying connection, which you
typically don't want to do anyway.

Jon

Oct 8 '07 #11
On Oct 8, 9:33 am, "Sylvie" <s...@sss.sswrote:
Thanks for your response,

If I need 2 connections with the same Connection string ? How can I create
the second one ?

I know that there are some situations that active connection couldnt be used
cos of the "connection is in use" problem
Just create another connection - it will create a new physical one if
it needs to, or take one from the pool if it can.

Jon

Oct 8 '07 #12
On Oct 8, 9:19 am, "Sylvie" <s...@sss.sswrote:
Thanks for your response,

I am ok now, ( I ll send this thread link to my friend :) )

But, if it s unnecessary to use throw statement in catch block, How can I
handle the error
You weren't handling the error - you were rethrowing it anyway. That's
(pretty much) the same as not catching it in the first place.
In my webb application, throw or not, exception goes to Application Error
event and I simply transfer this Exception to my Specific error page, after
this I clear the error

In Windows Application, what may be the equivalent of this ?
Well, that's tricky - because most of the time you're on the UI
thread, without a single point of stack to handle things. There's the
Application.ThreadException event, but whether or not you can continue
with the application after an exception has occurred will really
depend on what you were doing at the time.
I think, next thing that I wanna discover is Exception Handling
I would strongly suggest stopping database and web application coding
until you've got a strong grasp of the basics of C# and .NET.

Jon

Oct 8 '07 #13
I know that there are some situations that active connection couldnt be used
cos of the "connection is in use" problem
For completeness, on SQL-Server 2005 and above there is something
called Multiple Active Result Sets (MARS), which allows you to execute
commands on an in-use connection - however, you may want to assess
(based on your situation) whether this is appropriate. You enable MARS
simply by changing the connection string
(MultipleActiveResultSets=True).

Note, however, that there are alternatives; often, the above might be
(ab)used to fetch additional data for each row (i.e. header/lines, or
a lookup based on a code). A more effecient strategy might be to bring
back the data either directly (via some JOINs), or perhaps as a second
result grid which you then use to fix-up the original data. This means
that you do much fewer round-trips (a significant saving).

Marc

Oct 8 '07 #14
Oh heck, Jon.
The OP can certainly proceed with learning the basics of C# and the
Framework while continuing to create ASP.NET and database - related
applications.

The important thing is to be sure to study and learn, and not get stuck in
the rut of bad or uninformed coding habits.
-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

"Jon Skeet [C# MVP]" wrote:
On Oct 8, 9:19 am, "Sylvie" <s...@sss.sswrote:
Thanks for your response,

I am ok now, ( I ll send this thread link to my friend :) )

But, if it s unnecessary to use throw statement in catch block, How can I
handle the error

You weren't handling the error - you were rethrowing it anyway. That's
(pretty much) the same as not catching it in the first place.
In my webb application, throw or not, exception goes to Application Error
event and I simply transfer this Exception to my Specific error page, after
this I clear the error

In Windows Application, what may be the equivalent of this ?

Well, that's tricky - because most of the time you're on the UI
thread, without a single point of stack to handle things. There's the
Application.ThreadException event, but whether or not you can continue
with the application after an exception has occurred will really
depend on what you were doing at the time.
I think, next thing that I wanna discover is Exception Handling

I would strongly suggest stopping database and web application coding
until you've got a strong grasp of the basics of C# and .NET.

Jon

Oct 8 '07 #15
Peter Bromberg [C# MVP] <pb*******@yahoo.yohohhoandabottleofrum.com>
wrote:
Oh heck, Jon.
The OP can certainly proceed with learning the basics of C# and the
Framework while continuing to create ASP.NET and database - related
applications.

The important thing is to be sure to study and learn, and not get stuck in
the rut of bad or uninformed coding habits.
It's certainly important to study and learn, but I *do* think it's
important to have some fundamentals under your belt before you go on to
relatively difficult things like web apps and database access.
Otherwise when something goes wrong, you can't tell whether the
underlying reason is something difficult in the "big" technology or a
problem with basic understandings.

There's another reason, as well: the OP now knows (hopefully) about the
using statement, and that it should be used for resource clean-up -
that setting a variable to null isn't going to cut it. They now have to
go through their entire code-base and find all the other resource
acquisitions. It's just so much *easier* to take a bit of time learning
some things up-front instead of having to rework all the code that's
been written with a flawed understanding.

This used to happen more in the Java newsgroups than it does in C#
these days - I wrote it up (fairly crudely):
http://pobox.com/~skeet/java/learning.html

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Oct 8 '07 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: Rob Nicholson | last post by:
We're getting an occasional occurrence of the following error when two users try and open the same record in our ASP.NET app: "There is already an open DataReader associated with this Connection...
1
by: Lenny Shprekher | last post by:
Hi, I am getting issues that Oracle collecting opened sessions (connections) from my webservice using regular System.Data.OleDb.OleDbConnection object. I am guessing that this is connection...
7
by: Mrinal Kamboj | last post by:
Hi , I am using OracleConnection object from Oracle ODP.net provider and following is the behaviour which i am finding bit strange : To start with my argument is based on followings facts : ...
3
by: Martin B | last post by:
Hallo! I'm working with C# .NET 2.0, implementing Client/Server Applications which are connecting via Network to SQL-Server or Oracle Databases. To stay independent from the underlaying Database...
2
by: JimLad | last post by:
Hi, In an existing ASP/ASP.NET 1.1 app running on IIS 6, I need to RELIABLY pass the logged in username through to the SQL Server 2000 database for auditing purposes. The current method is...
16
by: crbd98 | last post by:
Hello All, Some time ago, I implemented a data access layer that included a simple connectin pool. At the time, I did it all by myself: I created N connections, each connection associated with...
20
by: fniles | last post by:
I am using VS2003 and connecting to MS Access database. When using a connection pooling (every time I open the OLEDBCONNECTION I use the exact matching connection string), 1. how can I know how...
3
by: fniles | last post by:
In the Windows application (using VB.NET 2005) I use connection pooling like the following: In the main form load I open a connection using a connection string that I stored in a global variable...
0
viswarajan
by: viswarajan | last post by:
Introduction This article is to go in deep in dome key features in the ADO.NET 2 which was shipped with VS 2005. In this article I will go trough one of the key features which is the Connection...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.