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

Answer 1

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.

Answer 2

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.

Answer 3

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.