What are best practices on managing database connections in .NET?

Question

Regarding best practice for managing database connections in a .NET application -- I know that, in general, it's bad to pass around a connection object.

However, I have some specific curiosities:

---

1. I have two instances of business objects, of different classes, in a parent-child relationship (the child is private.) Which of the following is best?

• Keep one private static connection open and shared, used by both objects, and left open until the parent is disposed.

• Keep two private static connections open, one for each object, not to be closed until the object is disposed.

• Do not keep static connections; open and subsequently close a new connection for every method that requires it. However, most of my methods only run 1-3 queries, so this seems inefficient... ?

---

2. My second question is essentially the same, but for a single form. What's best here?

• Keep one private static connection open and shared for the lifetime of the form.

• Do not keep a static connection; open and subsequently close a connection for every method in the form that requires it (again, a mere 1-3 queries per method.)

Answer 1

My understanding is that connections should only stay open as long as needed. Most of the time I've seen connections in Using statements, similar to

using (DBConnection db = new DBConnection(connectString))
{
    //do stuff
}

Answer 2

This link may be helpful: Best Practices for Using ADO.NET

Here's an interesting excerpt.

For best performance, keep connections to the database open only when required. Also, reduce the number of times you open and close a connection for multiple operations.

I've always followed the practice of opening connections in a using block, so that the Dispose method (and hence the Close method) is always called without my worrying about it. Using this approach I've never encountered a situation where poor performance was linked either to excessive concurrent connections or excessive setup or tear down operations.

Answer 3

(Was a comment)...

The theory is you should not be accessing the database from your business logic - it should be in a separate data-access class. (Say for example in the future you need to store them offline in XML, or use Oracle rather than SQL Server... you don't want to re-write your business logic!)

Your business objects should not have database connections associated with them. The connection should be opened in some DAL factory-type method, the object retrieved/built, then the connection closed and the object returned.

The business objects themselves should contain business logic fields and methods, which might call back to the Data Access Layer, which should create a new database connection for each DAL method.

Your inefficiency fears can be put to rest by using Connection Pooling, which means that if you open and close a connection hundreds of times, chances are they will all use the same one. But you should not be keeping database connections hanging around at all - especially not as members on a class.

Hope that helps!

Answer 4

In answer to both questions, if you are using something that has connection pooling, like ADO.NET, you should code your queries to keep the connection open as short as possible. I.e. open and subsequently close a new connection for every method that requires it.. When you close the connection it will be returned to the connection pool and reused on a subsequent query and thus you will not incur a performance penalty by opening and closing a bunch of connections. The advantage is that you won't risk leaking connections that you forgot to close and in the long run, you'll have fewer simultaneous connections open than if you keep connections open for long periods of time. It doesn't matter whether the application is a Windows form instead of a Web form: keep connections open as short as possible.