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