Database connection: Difference between revisions

Revision as of 16:15, 9 June 2015

Latest revision as of 13:14, 19 July 2025

(45 intermediate revisions by 34 users not shown)

Line 1:

{{Short description|Link between a client and database server}}

In [[computer science]], a '''database connection''' is the means by which a [[database server]] and its [[Client (computing)|client]] software communicate with each other. The term is used whether or not the client and the server are on different machines.

{{no inline sources|date=July 2025}}

A '''database connection''' is a facility in [[computer science]] that allows [[Client (computing)|client]] software to talk to [[database server]] software, whether on the same machine or not. A '''connection''' is required to send [[command (computing)|commands]] and receive answers, usually in the form of a result set.

Connections are a key concept in [[data-centric]] programming. Since some [[Database|DBMSs]]DBMS engines require considerable time to connect, [[connection pooling]] iswas usedinvented to improve performance. No command can be performed against a database without an "open and available" connection to it.

The client uses a database connection to send [[command(computing)|commands]] to and receive replies from the server. A database is stored as a file or a set of files on magnetic disk or tape, optical disk, or some other secondary storage device. The information in these files may be broken down into records, each of which consists of one or more fields.

Connections are built by supplying an underlying [[software driver|driver]] or [[providerdata modelprovider|provider]] with a [[connection string]], which is useda way toof addressaddressing a specific [[database]] or [[server (computing)|server]] and toinstance provideas instancewell andas user authentication credentials (for example, <code>'''''Server='''sql_box;'''Database='''Common;'''User ID='''uid;'''Pwd='''password;</code>''). Once a connection has been built it can be opened and closed at will, and properties (such as the command time-out length, or [[Database transaction|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.

Fields are the basic units of data storage, and each field typically contains information pertaining to one aspect or attribute of the entity described by the database. Records are also organized into tables that include information about relationships between its various fields. Although database is applied loosely to any collection of information in computer files, a database in the strict sense provides cross-referencing capabilities.

SomeMany databases, (such as [[PostgreSQL]],) only allow one operation to be performed at a time on each connection.{{citation needed|date=September 2012}} If a request for data (a [[SQL]] [[Select (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 [[Microsoft SQL Server|SQL Server 2005]] (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.

Connections are a key concept in [[data-centric]] programming. Since some [[Database|DBMSs]] require considerable time to connect, [[connection pooling]] is used 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 [[software driver|driver]] or [[provider model|provider]] with a [[connection string]], which is used to address a specific [[database]] or [[server (computing)|server]] and to provide instance and user authentication credentials (for example, <code>'''Server='''sql_box;'''Database='''Common;'''User ID='''uid;'''Pwd='''password;</code>).

Once a connection has been built, it can be opened and closed at will, and properties (such as the command time-out length, or [[Database transaction|transaction]], if one exists) can be set. The connection string consists of a set of key/value pairs, dictated by the data access interface of the data provider.

Some databases, such as [[PostgreSQL]], only allow one operation to be performed at a time on each connection.{{citation needed|date=September 2012}} If a request for data (a [[SQL]] [[Select (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, such as [[Microsoft SQL Server|SQL Server 2005]] (and later), do not impose this limitation. However, databases that allow multiple concurrent operations on each connection usually incur far more overhead than those that only allow one operation at a time.

== Pooling ==

Database connections are the f**ucking {{clarify span|[[Wikt:finite|finite]]|date=September 2012}} and [[time complexity|expensive]] and can take a disproportionately long time to create relative to the operations performed on them. It is very inefficient for an application to create, use, and close a database connection whenever it needs to update a database.

[[Connection pool|Connection pooling]]ing is a technique designed to alleviate this problem. A pool of database connections iscan be created and then shared among the applications that need to access the database. When an application needs database access, it requests a connection from the pool. When it is finished, it returns the connection to the pool, where it becomes available for use by other applications.

The connection object obtained from the connection pool is often a [[Adapter pattern|wrapper]] around the actual database connection. The wrapper handlesunderstands its relationship with the pool internally, 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 might instead return it to the pool. The application doesneed not need to 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 a [[Client–server model|client–serverclient/server architecture]], on the other hand, a persistent connection is typically used so that server state can be managed. This "state" includes server-side [[Cursor (databases)|cursors]], {{clarify span|temporary products|date=September 2012}}, 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.

It is desirable to set some limit on the number of connections in the pool. Using too many connections may just cause [[thrashing (computer science)|thrashing]] rather than get more useful work done. In case an operation is attempted and all connections are in use, the operation can block until a connection is returned to the pool, or an error may be returned.

== See also ==

* [[ActiveX Data Objects|ADO]]

* [[ADO.NET]]

* [[ODBC|ODBC (Open Database Connectivity)]]

* [[JDBC]]

* [[XQuery API for Java|XQJ]]

* [[RDBMS]]

== References ==

{{No footnotes|date=September 2009}}

* [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataidbconnectionclasstopic.asp IDbConnection Interface on MSDN]

* [https://web.archive.org/web/20080428080316/http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html Managing and Monitoring .NET Connections whitepaper.]

* [https://web.archive.org/web/20080501014734/http://betav.com/blog/billva/2006/06/getting_and_staying_connected_1.html Getting and Staying Connected whitepaper.]

* Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) Addison Wesley, William Vaughn, {{ISBN |978-0-321-24362-10321243621}}

* Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) Addison Wesley, William Vaughn, ISBN 978-0-321-24362-1

==External links==

* [http://ConnectionStrings.com ConnectionStrings.com]

{{Database}}

[[Category:Databases]]