Managing Connections

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. It assumes that you have resolved the Database class you require and stored a reference in the variable named db.

For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

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

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

'Usage
Dim sql As String = "Select ProductID, ProductName From Products"
Dim cmd As DbCommand = db.GetSqlStringCommand(sql)

' No need to open the connection; just make the call.
Dim customerDataSet As DataSet = db.ExecuteDataSet(cmd)

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 disposed.

Note

If you fail to close the DbDataReader, you can cause random and hard-to-find errors in your code—particularly when you are operating under an implicit transaction created within a TransactionScope context. You should always ensure that your application closes the DbDataReader in a timely fashion, either by explicitly closing the reader using 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. It assumes that you have resolved the Database class you require and stored a reference in the variable named db.

For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

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

'Usage
Dim cmd As DbCommand = db.GetSqlStringCommand("Select Name, Address From Customers")

Using reader As IDataReader = db.ExecuteReader(cmd)
  ' Process results
End Using