Q: SqlConnection, Open/Close advantages / disadvantages

[Martin Arvidsson, Visual Systems AB]

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

 

[sloan]

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]

Thanx, will surley dig into it.

/Martin

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.

 

[George Ter-Saakov]

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.

 

[Peter Bromberg [C# MVP]]

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]

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.