Which strategy about connection management should we use when developing an application?

Question

Which one is better while developing win-based application which uses a Database as its data store ? What about web-based applications?

1) when user loads he first form of an application, the global connection opens and by closing the last form of the application, the connection closes and disposes.

2) for every form within the application, there is a local connection (form scope) and when user wants to performs an operation like insert, update, delete, search, ... the application uses the connection and by unloading the form, the connection also closes and disposes.

3) for every operation within a form of an application, there is a local connection (procedure scope) and when user wants to performs an operation like insert, update, delete, search, ... the application uses procedure connection and at the end of every procedure within the form, the connection also closes and disposes.

Answer 1

Go with #3

You should try to only ever keep connections open for just as long as is required.

Also have a look at

• Understanding Connection Pooling
• SQL Server Connection Pooling (ADO.NET)

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

Answer 2

This is quite a broad question. But usually, for any database server and application environment, opening and keeping a new connection is an expensive operation. That's why you definitely don't want to open multiple connections from a single client, and should stick to process-scope for connections.

In a desktop application using a database server, strategy for handling it's single connection depends a lot on the DB usage pattern. Say, if the app reads or writes something a lot within 5 minutes, and then just does nothing with the DB for hours, it makes no sense to keep the connection open all the time (assuming there are many other clients). You may introduce some kind of time-out for closing a connection.

The Web server situation depends a lot on the used technology. Say, in PHP every request is a "fresh start" WRT database connection. You open and close a connection for each mouse click. While popular Java application servers have DB connections pool, reusing the same connection instances for many HTTP request handling threads.