Database Connectivity

Database Connectivity

Most database interfaces depends on some database connection objects, which seem to be bridge between application code and database. Typically, a connection must be opened until the database operation command is executed. In fact, a display connection is often required to establish and execute commands. During the entire process executed by the command, the connection must be open, the result of the query will return a recordset, and turn off the database connection. If you are running in a transaction, you often bind your transaction on a specific connection. When it is running, this connection must always be opened until this transaction ends to close the connection.

In many environments, the overhead of establishing a connection is quite large, which requires a connection pool. In this case, the developer requests a connection to the connecting pool and releases after completion without instant creation and closing. Note that this is not to create a connection pool, because most platforms provide a connection pool, so you rarely need to implement the connection pool. If you have to implement the connection pool yourself, you must first check that the connection pool is really improved (the creation of the connection pool can be referred to the code [1]). Also note: More and more environments allow us to create new connections faster, so you don't need to use buffer pools.

The environment that provides the connection pool puts the connection pool behind a similar to create a new connection interface. With this method, you don't have to know if you get a new creation connection or allocated from the connection pool. Similarly, close a connection may not really turn off it, but just replace it to get and release from the connection pool.

Whether the cost of creating a connection is high or low, the connection must be managed because they are precious resources, they must be closed immediately. Also, if you are ongoing a transaction, you usually need to guarantee: In this particular transaction, each command is sent from the same connection.

The most common recommendation is to use one to the connection pool or the connection manager, display a connection, and through it to execute the database command, once the execution is performed, immediately turn it off. This proposal has brought two questions: First, ensure a connection can be obtained in any place; second, it is ensured that it will not be closed at the end (can be referred to the sample code [2]).

The modern environment provides an automatic memory management and garbage collection mechanism, so that a way to ensure connection is to use garbage collectors. In this manner, the object that is connected itself or references this connection will close the connection during garbage collection. The benefits of this are: it uses the same mechanism as memory management, which is equally convenient, and is not strange. The problem is that the connection is closed only when the garbage collector actually recovers memory, may have lost its last reference for this connection for a long time, the result is that the connection that is not referenced may be separated for a while. is closed. Overall, I don't like to rely on garbage collection mechanisms, and even other mechanisms, even display closing will be better. Of course, the garbage collection mechanism is still a good backup in the event of other mechanisms.

Connection management details Description are often characteristics of database interaction software, so the policies used are usually determined by the environment.

Create sample code for connecting pools [1]:

Using system;

Using system.timers;

Using system.collections;

Using system.data.sqlclient;

USING COMMON;

Namespace SQLDBConnection

{

///