By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Q: SqlConnection, Open/Close advantages / disadvantages

P: n/a
Hi!

I am currently developing my first webapplication using .net (2.x)

In a couple examples seen in the helpfile that came with VS2008
i see that they are opening a connection to the sql server.
Issue a command or two, then close the connection.

My question is, wouldn't it be better to have One connection open during the
current session and when the session end, close the connection.

Or perhaps not.

Pros. / Cons gladley taken for me to learn, links, comments etc.

Regards

Martin
Apr 2 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a

Open Late.
Use Quickly.
Close Soon.

...........

Do not wire up a connection to a session or any other kind of trick.

...

Look up "Connection Pooling" "Asp.Net" and you'll have 1,000 thing to read
about it.

"Martin Arvidsson, Visual Systems AB" <ma**************@vsab.netwrote in
message news:ev**************@TK2MSFTNGP05.phx.gbl...
Hi!

I am currently developing my first webapplication using .net (2.x)

In a couple examples seen in the helpfile that came with VS2008
i see that they are opening a connection to the sql server.
Issue a command or two, then close the connection.

My question is, wouldn't it be better to have One connection open during
the
current session and when the session end, close the connection.

Or perhaps not.

Pros. / Cons gladley taken for me to learn, links, comments etc.

Regards

Martin

Apr 2 '08 #2

P: n/a
How many users do you want to have in your Web application?
One.... You can do anything you want....

1000.... You can not have 1000 connections to SQL server. That is sure way
to kill server...

Do not worry about it. .NET does not close connections internally. It pools
them so when you open connection it grabs already opened one before from the
pool. When you close it - connection is simply returned to the pool

Search ".NET pooling connection" in google to get more info.
George.

"Martin Arvidsson, Visual Systems AB" <ma**************@vsab.netwrote in
message news:ev**************@TK2MSFTNGP05.phx.gbl...
Hi!

I am currently developing my first webapplication using .net (2.x)

In a couple examples seen in the helpfile that came with VS2008
i see that they are opening a connection to the sql server.
Issue a command or two, then close the connection.

My question is, wouldn't it be better to have One connection open during
the
current session and when the session end, close the connection.

Or perhaps not.

Pros. / Cons gladley taken for me to learn, links, comments etc.

Regards

Martin

Apr 2 '08 #3

P: n/a
I'm not sure that is entirely accurate. If you .Close() a SqlConnection
object, this returns it to the pool but not in an opened state.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net
"George Ter-Saakov" wrote:
How many users do you want to have in your Web application?
One.... You can do anything you want....

1000.... You can not have 1000 connections to SQL server. That is sure way
to kill server...

Do not worry about it. .NET does not close connections internally. It pools
them so when you open connection it grabs already opened one before from the
pool. When you close it - connection is simply returned to the pool

Search ".NET pooling connection" in google to get more info.
George.

"Martin Arvidsson, Visual Systems AB" <ma**************@vsab.netwrote in
message news:ev**************@TK2MSFTNGP05.phx.gbl...
Hi!

I am currently developing my first webapplication using .net (2.x)

In a couple examples seen in the helpfile that came with VS2008
i see that they are opening a connection to the sql server.
Issue a command or two, then close the connection.

My question is, wouldn't it be better to have One connection open during
the
current session and when the session end, close the connection.

Or perhaps not.

Pros. / Cons gladley taken for me to learn, links, comments etc.

Regards

Martin


Apr 2 '08 #4

P: n/a
Of course it's in opened state... (in terms of SQL server).
Next time you 'open' it from .NET its reinitializing it though just in case
there was a transaction level (or staff like that) set on that
connection...(not sure what exactly is gets reinitialized though but
defently ut's much faster than to open new connection)

That is why you need to be very careful when using temp tables in SQL when
work from ASP.NET. Connections are never closed (per say) and temp table is
not deleted even if you have con.Close() statement. So next time you do
something like (pseducode)
con.Open(..)
con.Execute("'CREATE TABLE #tmpTable..");
it might blow up... cause tmpTable already exists....
PS: Connection do get closed sometimes... Connection pool supports so called
connection's "age" (I think it's a 5 minute by default) and they do get
closed after reaching that age. It's pretty much same idea as with pool
recycling.


PPS: If you are not using distributed transaction it's recommended to add
Enlist=False to connection strings. Saves some runtime by not joining
connection to MTS.

George.

"Peter Bromberg [C# MVP]" <pb*******@yahoo.NoSpamMaam.comwrote in message
news:4E**********************************@microsof t.com...
I'm not sure that is entirely accurate. If you .Close() a SqlConnection
object, this returns it to the pool but not in an opened state.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net
"George Ter-Saakov" wrote:
>How many users do you want to have in your Web application?
One.... You can do anything you want....

1000.... You can not have 1000 connections to SQL server. That is sure
way
to kill server...

Do not worry about it. .NET does not close connections internally. It
pools
them so when you open connection it grabs already opened one before from
the
pool. When you close it - connection is simply returned to the pool

Search ".NET pooling connection" in google to get more info.
George.

"Martin Arvidsson, Visual Systems AB" <ma**************@vsab.netwrote
in
message news:ev**************@TK2MSFTNGP05.phx.gbl...
Hi!

I am currently developing my first webapplication using .net (2.x)

In a couple examples seen in the helpfile that came with VS2008
i see that they are opening a connection to the sql server.
Issue a command or two, then close the connection.

My question is, wouldn't it be better to have One connection open
during
the
current session and when the session end, close the connection.

Or perhaps not.

Pros. / Cons gladley taken for me to learn, links, comments etc.

Regards

Martin



Apr 2 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.