ASP.NET/ADO.NET: Handling many database connections inside a .NET Object?

We have a .NET object that does a lot of reading/writing with the database. Throughout the life cycle of this object (or the asp page that uses it), it may hit the database with a query/update anywhere from 1 to 10 times.

Instead of opening and closing a database connection every time the object needs to hit the database, it simply opens a connection to the database during instantiation and then closes the connection during the object's termination event. Is that a bad practice?

The assumption was that since the object hits the database every time it's instantiated (and usually multiple times), it would be better to just open a connection at the beginning of the object's life and then close it at the end.

The other alternative is to open and close the database connection before and after each query/operation.

What's the best practice here in order to maximize performance?

**update** Thanks for the tips guys. Can anyone speak more to the method of opening/closing a connection inside an object's instantiation/termination events and the repercussions of doing so?

Answers


Open an close the connection as needed. ADO.NET has built in connection pooling that works. You will not notice any performance issues unless you are doing this in a loop with thousands of open/closes.

edit See Should I persist a sqlconnection in my data access layer? for more information as to the pitfalls of connection persistence.


Need Your Help

Struts2+Spring3+Tiles3

spring hibernate struts2 tiles2 struts2-spring-plugin

I would like to integrate tiles 3 to my web application spring3+struts2+hibernate,

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.