Search code examples
.netasp.netdatabase-connectivity

Question regarding SQL connection in a web application

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

Solution

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