Question regarding SQL connection in a web application

Question

I have a doubt regarding the sql database connection that we open for performing CRUD statements on the database in the context of web application (a web store) in asp.net

What is advisable

to keep the database connection open (when user logs into his profile) and close when the application is closed or session is expired

or

To open a database connection when required perform operations and as soon as the command/stored procedure is executed close it.

I have read some where that a lot of resources are consumed to open and close the database connection every time.

I need your advice

score 2 · Accepted Answer · edited Sep 30 '22 at 05:03

2

By default, MS SQL server uses connection pooling, so connections (using the exact same connection string) get assigned to a live connection kept in the pool by the server. You should open and close your connections to keep things tidy on your side.

edited Sep 30 '22 at 05:03

Jan

3,825
3
31
51

answered Apr 10 '11 at 17:38

Ron Harlev

16,227
24
89
132

score 2 · Answer 2 · answered Apr 10 '11 at 18:02

2

I would always recommend closing a connection after use. Your site can handle more traffic this way, since connections are not sitting idle. The overhead in opening a connection is minuscule.

answered Apr 10 '11 at 18:02

James Allen

819
7
12

score 1 · Answer 3 · answered Apr 10 '11 at 18:13

1

Open only when you need it, and close immediately after.

In case of often usage, connection will be pooled, and in fact stay be opened, but up to SQL Server decision. That will gain you performance being transparent for the code.

answered Apr 10 '11 at 18:13

abatishchev

98,240
88
296
433

Question regarding SQL connection in a web application

3 Answers3