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. An application that attempts to connect using an existing Connection object from a pool could receive errors because the physical connection to the database has been lost.
The ADO.NET Client handles this situation transparently to the user. The application does not receive any errors on the Connection.Open() attempt because the ADO.NET Client simply returns a connection from a connection pool. The first time the Connection object is used to execute a SQL statement (for example, through one of the DataReader execution methods or the DataAdapter.Fill method), the ADO.NET Client 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 ADO.NET Client can reconnect to the server, the result of the SQL execution is returned to the application; no errors are returned to the application. The ADO.NET Client uses the connection failover options, if enabled, when attempting this seamless reconnection. See Using Connection Pooling for information about configuring the ADO.NET Client to connect to a backup server when the primary server is not available.
Note: Because the ADO.NET Client can attempt to reconnect to the database server when executing SQL statements, connection errors can be returned to the application when a statement is executed. If the ADO.NET Client cannot reconnect to the server (for example, because 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.
Progress DataDirect's method of handling dead connections in connection pools allows for the maximum performance out of the connection pooling mechanism. Some data providers periodically ping the server with a dummy SQL statement while the connections sit idle. Other data providers ping the server when the application requests the use of the connection from the connection pool. Both of these approaches add round trips to the database server and ultimately slow down the application during normal operation of the application is occurring.
