Alex Rivera | Logout

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

Asked 2010-07-15T18:41:52.143
12

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.)

.net database-connection connection-pooling
Edit
Report

1 Answer

8

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.

answered 2010-07-15T19:14:31.130

Your Answer