ADO.NET SqlConnection Management

  • Thread starter Thread starter Jeff S
  • Start date Start date
J

Jeff S

I have a class that is used exclusively for database access. What is the
recommended method of opening and closing connections in this scenario.
Should I create/open the SqlConnection in a class constructor and then close
it in a destructor? Alternatively should I open a new SqlConnection during
every method call and then explicitly close it at the end of the method
call? What are the important factors I should consider?

Thoughts, recommendations opinions, considerations, facts?

Thanks!
 
to avoid resource leaks, any function/method that opens the connection
should close it (in a finally block). this means you should not return
connections or datareaders from functions. you can pass them down, but not
up. you should use this rule with all objects that allocate unmanged
resources (implement dispose).

the same care should be taken with com objects to be sure you marshal the
release.

-- bruce (sqlwork.com)
 
<<<<you can pass them down, but not up>>>>

I'm not clear on what this means - can you explain?

Thanks!
 
I've read that ADO.NET does a good job with connection pooling, so unlike some previous schools of thought that said you should hang onto a connection if the system could spare the resources because it was so expensive to go get another one, now you can open and close when you need to. I've gotten in the habit of opening and closing with each discrete chunk of code, but have never done any serious benchmarking of that against an approach where, say, you open the connection at a session level and just keep using it. I'm hoping some others will weigh in here

----- Jeff S wrote: ----

I have a class that is used exclusively for database access. What is th
recommended method of opening and closing connections in this scenario
Should I create/open the SqlConnection in a class constructor and then clos
it in a destructor? Alternatively should I open a new SqlConnection durin
every method call and then explicitly close it at the end of the metho
call? What are the important factors I should consider

Thoughts, recommendations opinions, considerations, facts

Thanks
 
Back
Top