2

In examples of interacting with the database in ASP.NET I always see 

using ( SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString) )
{
   // ... Do some stuff
}

but I'm wondering, shouldn't I just be connected all the time? It doesn't make sense why I would want all the overhead involved in opening and closing connections. Shouldn't con be like a static member of my model?

user5648283
  • 5,913
  • 4
  • 22
  • 32
  • 2
    You want Connection Pooling; http://stackoverflow.com/questions/1973151 – Dave Anderson Jan 19 '16 at 02:09
  • 2
    No, you do not want to do this. Refer some answers [here](http://stackoverflow.com/questions/9811899/how-should-one-maintain-a-database-connection-in-an-asp-net-mvc-application) and [here](http://stackoverflow.com/questions/11659228/is-it-expensive-to-keep-database-connections-open) –  Jan 19 '16 at 02:13

2 Answers2

12

SqlConnection is not thread-safe.

Since ASP.Net requests can arrive on multiple threads, you must not do that.

SqlConnection is already pooled; you don't have anything to worry about.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
2

In my experience, you do not want to leave a connection open if you are not using it. It is much cleaner to use a Using statement when connecting to a DB. There is a good discussion here... https://stackoverflow.com/questions/7072583/is-it-ok-to-leave-a-sql-connection-open

Community
  • 1
  • 1
Zach
  • 640
  • 1
  • 6
  • 16