Managing Connections

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The latest Enterprise Library information can be found at the Enterprise Library site.

Database connections are a limited resource, and proper management of them is essential for scalable applications. It is good practice to keep connections open only as long as they are needed and to close them as soon as practical. By design, most of the Database class methods handle the opening and closing of connections to the database on each call. Therefore, the application code does not need to include code for managing connections. (By default, and for performance reasons, ADO.NET returns connections to the connection pool without closing them. Therefore, you do not need to cache your Database objects.)

For example, the ExecuteDataSet method returns a DataSet object that contains all the data. This gives you your own local copy. The call to ExecuteDataSet opens a connection, populates a DataSet, and closes the connection before returning the result.

The following code demonstrates the use of the ExecuteDataSet method.

Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "Select ProductID, ProductName From Products";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); 

// No need to open the connection; just make the call.
DataSet customerDataSet = db.ExecuteDataSet(dbCommand);

However, there are other cases where it is unclear when to close the connection. An example is the ExecuteReader method. This method returns an object that implements the IDataReader interface. The Database base class has a default implementation that returns a DbDataReader object. DbDataReader objects are designed to read specific portions of the data as needed, which requires an open connection. In other words, it is unknown when the application no longer needs the DbDataReader. If the Data Access Application Block methods close the connection before returning the DbDataReader, the DbDataReader becomes useless to the client code. Instead, the DbDataReader methods indicate to the underlying ADO.NET call to automatically close the connection when the DbDataReader is finished. In this situation, it is considered a best practice for the application to ensure that the DbDataReader is closed in a timely fashion, either by explicitly closing the reader with the DbDataReader.close method or by forcing the disposal of the DbDataReader, which results in the Close method being called.

The following code demonstrates a call to the ExecuteReader method. The using statement (Using in Visual Basic) ensures that the DbDataReader object is disposed, which closes the DbDataReader object.

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetSqlStringCommand("Select Name, Address From Customers");
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
// Process results
} 

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The latest Enterprise Library information can be found at the Enterprise Library site.
Show: