Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Q: SqlConnection, Open/Close advantages / disadvantages

Reply
Thread Tools

Q: SqlConnection, Open/Close advantages / disadvantages

 
 
Martin Arvidsson, Visual Systems AB
Guest
Posts: n/a
 
      04-02-2008
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


 
Reply With Quote
 
 
 
 
sloan
Guest
Posts: n/a
 
      04-02-2008

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" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
 
Martin Arvidsson, Visual Systems AB
Guest
Posts: n/a
 
      04-02-2008
Thanx, will surley dig into it.

/Martin

"sloan" <(E-Mail Removed)> skrev i meddelandet
news:(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> 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
>>

>
>



 
Reply With Quote
 
George Ter-Saakov
Guest
Posts: n/a
 
      04-02-2008
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" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
Peter Bromberg [C# MVP]
Guest
Posts: n/a
 
      04-02-2008
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" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> > 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
> >

>
>
>

 
Reply With Quote
 
George Ter-Saakov
Guest
Posts: n/a
 
      04-02-2008
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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote
>> in
>> message news:(E-Mail Removed)...
>> > 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
>> >

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
What is the Advantages And Disadvantages of Norton Antivirus lotrza@gmail.com Computer Support 10 09-13-2010 09:07 PM
Panasonic Lumix G1 Kludge Combines all the disadvantages of a P&Swith the disadvantages of a D-SLR SMS Digital Photography 21 11-06-2008 04:54 PM
Advantages/Disadvantages of SATA Hard Drives? JTJersey Computer Information 3 09-10-2004 09:39 PM
Cisco 3550 without ip's, advantages and disadvantages? joeblow Cisco 5 06-01-2004 06:48 PM
Wide Angle Filters vs. Normal Ones.. Advantages/Disadvantages? Moe Digital Photography 7 02-19-2004 11:04 AM



Advertisments