Handling Dead Connections in a Pool in ADO.NET

So, what happens when an idle connection loses its physical connection to the database? For example, suppose the database server is rebooted or the network experiences a temporary interruption. When the application attempts to connect using an existing DbConnection object from a pool, it could receive errors because the physical connection to the database has been lost.

DataDirect ADO.NET data providers handle this situation transparently to the user. The application does not receive any errors on the DbConnection.Open() attempt because the data provider simply returns a connection from a connection pool. The first time the DbConnection object is used to execute a SQL statement (for example, through one of the DataReader execution methods or the DataAdapter.Fill method), the data provider detects that the physical connection to the server has been lost and attempts to reconnect to the server before executing the SQL statement. If the data provider can reconnect to the server, the result of the SQL execution is returned to the application; no errors are returned to the application. The data provider uses the connection failover options, if enabled, when attempting this seamless reconnection. For more information about how DataDirect ADO.NET data providers implement connection failover, refer to "Failover Support in DataDirect Connect for ADO.NET Data Providers."

NOTE: Because the data providers can attempt to reconnect to the database server when executing SQL statements, connection errors can be returned to the application on a statement execution. If the data providers cannot reconnect to the server (for example, the server is still down), the execution method throws an error indicating that the reconnect attempt failed, along with specifics about the reason the connection failed.

DataDirect's method of handling dead connections in connection pools allows for the maximum performance of the connection pooling mechanism. In contrast, some data providers periodically ping the server with a dummy SQL statement while the connections remain idle. Others ping the server when the application requests the use of the connection from the connection pool. Both of these approaches add roundtrips to the database server and ultimately slow down the normal operation of the application.