ASP.NET - Types of Database Connection

There are two types of connections: connected and disconnected.

Connected Connection:

In a connected connection, a direct and continuous connection is maintained between the application and the database server. When the application requests data, it sends a query to the server and waits for a response. The server processes the query, retrieves the data, and returns it to the application. The connection remains open during this entire process, and the application can send additional queries to the server as needed.

Disconnected Connection:

In a disconnected connection, the application connects to the database server, retrieves the data it needs, and then disconnects from the server. The application can then manipulate the data locally, without a direct connection to the database. When the application needs to save changes, it reconnects to the database, updates the data, and then disconnects again.

The main advantage of a disconnected connection is that it reduces the amount of time that the application needs to maintain an open connection to the database server. This can be particularly useful for applications that need to work with large amounts of data, or that need to operate over unreliable or slow network connections.

One of the key technologies used to implement disconnected connections in ASP.NET is the ADO.NET DataSet object. The DataSet provides a disconnected, in-memory representation of the data retrieved from a database, along with support for performing updates and other operations on that data. The DataSet can be populated using a connected connection to a database, and then disconnected from the database to allow the application to work with the data locally. When the application is ready to save changes, it can reconnect to the database and update the data as needed.

Bound connections:

In a bound connection, the data source control is responsible for opening and closing the database connection. The data source control is also responsible for retrieving and updating data. The advantage of using a bound connection is that it reduces the amount of code that needs to be written, as the data source control takes care of most of the database-related tasks. The most common data source controls are the SqlDataSource and the ObjectDataSource.

Unbound connections:

In an unbound connection, the programmer is responsible for opening and closing the database connection. The programmer is also responsible for retrieving and updating data. The advantage of using an unbound connection is that it provides greater control over the database-related tasks. This makes it ideal for situations where more advanced or customized functionality is required.

Summary

1. A connected connection is when the connection is open and actively communicating with the database. In this case, the data is stored in the database, and the connection retrieves and manipulates the data in real-time.
2. A disconnected connection is when the connection is closed after retrieving data from the database. In this case, the data is copied from the database to a local cache or dataset, which is then used for data manipulation. Changes made to this local data will not affect the actual data in the database until the disconnected connection is reconnected and the changes are pushed to the database.
3. A bound connection refers to the process of binding data from a database to a control in a web page, such as a gridview or dropdown list. This can be done with both connected and disconnected connections.
4. An unbound connection refers to data that is not connected to a database, and instead, is generated on the fly, such as data entered into textboxes on a web form.