Q: SqlConnection, Open/Close advantages / disadvantages

  • Thread starter Martin Arvidsson, Visual Systems AB
  • Start date
M

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
 
S

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.
 
M

Martin Arvidsson, Visual Systems AB

Thanx, will surley dig into it.

/Martin

sloan said:
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.
 
G

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.
 
G

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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top