ASP.NET/ADO.NET: handling many database connections inside a .NET object?

Question

We have a .NET object that reads / writes a lot to the database. Throughout the life cycle of this object (or the asp page that uses it), it can go to the database with a request / update anywhere from 1 to 10 times.

Instead of opening and closing a database connection every time an object needs to get into the database, it simply opens a database connection during instance creation, and then closes the connection during the object's completion event. Is this a bad practice?

It was assumed that since the object enters the database each time it is created (and usually several times), it would be better to just open the connection at the beginning of the life of the object, and then close it at the end.

Another alternative is to open and close the database connection before and after each request / operation.

What is the best practice to maximize performance?

**** **** update Thanks for the guys advice. Can anyone talk more about the method of opening / closing a connection inside the events of creation / completion of an object and the consequences of this?

Answer 1

Open the connection if necessary. ADO.NET has a built-in connection pool that works. You will not notice any performance issues if you do not do this in a loop with thousands of open / close.

edit See Should I keep sqlconnection at my data access level? for more information on the pitfalls of compound persistence.

Answer 2

Open and close each time ... Open immediately (as close as possible) to the line of code that actually performs the database operation, and immediately close it as soon as possible. When you do this this way, ADO.net does not actually close the connection, it simply returns it back to the ADO.net connection pool, where it sits and waits for the next connection request with the same conenction line. You are not taking on the ovberhead that actually recreates the connection every time ...

The only problem is that you make so many connection attempts asynchronously that you have exceeded the maximum number of connections in the pool ... and there are solutions to this problem using the System.Threading.ThreadPool class ...

Answer 3

To add to the trust in the pooling argument, keeping an open connection longer than necessary can actually reduce overall performance, since the connection pool cannot use this connection with other components that require a database connection.

So yes, open and close the connection as needed. Although, it would be even faster if you could send your requests in one call to exec.

Answer 4

Even when ado.net does not actually close the connection, when you do this: Conn.Close () it executes "sp_reset_connection" on the server, even if sp_reset_connection is an easy storage procedure, it generates some network traffic. So, for example, I would not recommend closing and opening a connection inside a loop.