Database connection

From Wikipedia, the free encyclopedia

A Database connection is the method in computer science that allows client software to talk to database server software, whether these exist on the same machine or not. A connection is required to send commands and receive answers, usually in the form of a result set.

Connections are a key concept in data-centric programming. Since some DBMS engines require considerable time to connect connection pooling was invented to improve performance. No command can be performed against a database without an "open and available" connection to it.

Connections are built by supplying an underlying driver or provider with a connection string, which is a way of addressing a specific database or server and instance as well as user authentication credentials. ( Example: Server=sql_box;Database=Common;User ID=uid;Pwd=password; ) Once a connection has been built, it can be opened and closed at will, and properties ( such as the command timeout length, or transaction, if one exists ) can be set. The Connection String is composed of a set of key/value pairs as dictated by the data access interface and data provider being used.

Many databases ( such as SQL Server 2000 ) only allow one operation at a time to be performed on a connection. In other words, if a request for data ( a SQL Select statement ) is sent to the database, and a result set is returned, the connection is open, but not available, until the client finishes consuming the result set. Other databases like SQL Server 2005 and later don't impose this limitation. However, databases that provide multiple operations per connection usually incur far more overhead than those that permit only a single operation task at a time.

1 Pooling
2 References
3 See also
4 External links

[edit] Pooling

Connection Pooling is a technology that was originally invented for ODBC and COM-based ADO-accessed database applications where the connection took far too long to open. Subsequently, it was refined for use by OLE DB, ODBC and .NET Data Providers used by ASP and other server-hosted application architectures to improve application performance and reduce stress from the database server. Because connections are finite and expensive and can take a disproportionately long time to create, modern applications send a connection back to the pool when they finish with it (by executing the Close method on the Connection object), and take a connection from the pool when one is needed, if one is available (using the Connection Open method).

Each unique process (application domain), user and transaction gets a dedicated connection pool. This means an application suite might have many pools, each with dozens of connections. Typically, when a connection is "closed" by the application, the connection state remains until the connection is reused. The pool is discarded when the application domain is terminated.

This approach encourages the practice of opening a connection in an ASP application only when needed, and closing it as soon as the work is done, instead of an application holding a connection open during the entire course of its life. In this manner, a relatively small no.. of connections can service a large number of requests - often called multiplexing.

However, in a client/server architecture (as in Windows Forms applications), a persistent connection is typically used so that server state can be managed. This "state" includes server-side cursors, temporary products, connection-specific functional settings and more.

ASP application failures occur when the connection pool overflows. This can occur if the application fails to call the Connection Close method or the Connection object falls from scope before being closed. Overflow can also occur when the application requires more processing time than the server can provide given the workload--as when too many clients attempt to access the web site or one or more operations are blocked or simply inefficient.