A database connection is a facility in
computer science
Computer science is the study of computation, automation, and information. Computer science spans theoretical disciplines (such as algorithms, theory of computation, information theory, and automation) to Applied science, practical discipli ...
that allows
client
Client(s) or The Client may refer to:
* Client (business)
* Client (computing), hardware or software that accesses a remote service on another computer
* Customer or client, a recipient of goods or services in return for monetary or other valuable ...
software to talk to
database server
A database server is a server which uses a database application that provides database services to other computer programs or to computers, as defined by the client–server model. Database management systems (DBMSs) frequently provide database- ...
software, whether 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
In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required.
Connection pools are used to enhance the performance of executing comm ...
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 In computing, a connection string is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection. Whilst commonly used for ...
, which is a way of addressing a specific
database
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases sp ...
or
server
Server may refer to:
Computing
*Server (computing), a computer program or a device that provides functionality for other programs or devices, called clients
Role
* Waiting staff, those who work at a restaurant or a bar attending customers and su ...
and instance as well as user authentication credentials (for 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 time-out 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
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
) only allow one operation to be performed at a time on each connection. 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 for other operations until the client finishes consuming the result set. Other databases, like
SQL Server 2005 The history of Microsoft SQL Server begins with the first Microsoft SQL Server database product – SQL Server v1.0, a 16-bit relational database for the OS/2 operating system, released in 1989.
Versions
Detailed history Genesis
On June 12, 198 ...
(and later), do not 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.
Pooling
Database connections are finite and
expensive and can take a disproportionately long time to create relative to the operations performed on them. It is inefficient for an application to create, use, and close a database connection whenever it needs to update a database.
Connection pool
In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required.
Connection pools are used to enhance the performance of executing comm ...
ing is a technique designed to alleviate this problem. A pool of database connections can be created and then shared among the applications that need to access the database.
The connection object obtained from the connection pool is often a wrapper around the actual database connection. The wrapper understands its relationship with the pool, and hides the details of the pool from the application. For example, the wrapper object can implement a "close" method that can be called just like the "close" method on the database connection. Unlike the method on the database connection, the method on the wrapper may not actually close the database connection, but instead return it to the pool. The application need not be aware of the connection pooling when it calls the methods on the wrapper object.
This approach encourages the practice of opening a connection in an application only when needed, and closing it as soon as the work is done, rather than holding a connection open for the entire life of the application. In this manner, a relatively small number of connections can service a large number of requests. This is also called
multiplexing
In telecommunications and computer networking, multiplexing (sometimes contracted to muxing) is a method by which multiple analog or digital signals are combined into one signal over a shared medium. The aim is to share a scarce resource - a ...
.
In a client/server architecture, on the other hand, 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 so on.
An application failure occurs when the connection pool overflows. This can occur if all of the connections in the pool are in use when an application requests a connection. For example, the application may use a connection for too long when too many clients attempt to access the web site or one or more operations are blocked or simply inefficient.
See also
*
ADO
*
ADO.NET
*
ODBC (Open Database Connectivity)
*
JDBC
Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. I ...
*
RDBMS
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relation ...
References
IDbConnection Interface on MSDN* Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) Addison Wesley, William Vaughn, {{ISBN, 978-0321243621
External links
ConnectionStrings.com
Databases