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.

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);

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "Select ProductID, ProductName From Products"
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

' No need to open the connection; just make the call.
Dim customerDataSet As DataSet = 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
} 

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()

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

Using dataReader As IDataReader = db.ExecuteReader(dbCommand)

  ' Process results

End Using