Resources
Data Access has been discontinued. Please refer to this page for more information.

ConnectionPool Configuration

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

The ConnectionPoolConfiguration class allows you the connection pool functionality. A connection pool is a set of database connections that are re-used by clients. Setting up a connection pool improves performance by eliminating client connection times and it is also the preferred method of handling database connections since it provides a more robust handling of error conditions and lost connections than direct manipulation of the connection objects. The Connection Pool functionality can be configured in two ways:

The ConnectionPoolConfiguration object is accessible through the ConnectionPool property that is exposed by the BackendConfiguration class.

public partial class EntitiesModel
{
   static EntitiesModel()
   {
       BackendConfiguration.ConnectionPoolConfiguration connectionPoolConfig = backend.ConnectionPool;
   }
}

Partial Public Class EntitiesModel
    Shared Sub New()
        Dim connectionPoolConfig As BackendConfiguration.ConnectionPoolConfiguration = backend.ConnectionPool
    End Sub
End Class

The ConnectionPoolConfiguration class exposes the following properties:

  • Pool - controls which ConnectionPool behavior should be used:
    • Integrated - the connection pool and management features of Telerik Data Access are used. Driver specific connection pooling is turned off and the connection string is modified. When Telerik Data Access Integrated Connection Pool pooling is set, Telerik Data Access will handle the Connection Pool internally. Additional features like reserved connections (used for the key generation mechanism) will be handled with these settings.
    • ADOManaged - plain ADO connections are used. The management features of Telerik Data Access are used. Driver specific connection pooling is usable.
    • ADO - plain ADO connections are used. The management features of Telerik Data Access are turned off. Driver specific connection pooling is usable.
  • ActiveConnectionTimeout - sets the number of seconds after which, active connections that have been inactive or busy are closed. The default value is 120 seconds. If you need connections to remain open for an unlimited timespan (until the OpenAccessContext.Dispose() method is called), you should set the connection timeout to 0.
  • BlockWhenFull - controls the behavior of the empty connection pool when a new connection is requested. If this property is set to True, threads attempting to obtain a connection will wait, until one is available. Default value is True.
  • Reserved - the number of connections to reserve for primary key generation. This prevents deadlocks on commit. Default value is set at 1.
  • MaxActive - sets the maximum number of connections to create for the connection pool. If you set this to 10 then Telerik Data Access will not open more than 10 connections to the database. You need to use the event log to monitor your production system to decide how many connections you need. This depends on the transaction mode (optimistic or pessimistic), the length of your transactions and the number of users. Default value is 10.

  • Isolation Level - controls the transaction locking and row versioning behavior. The isolation level describes the degree to which the data being updated is visible to other transactions.

    The Isolation Level configuration on model level is deprecated. It is recommended to use the ContextOptions.IsolationLevel property of OpenAccessContextBase. You can check the Managing Isolation Level article.

    • Unspecified - a different isolation level than the one specified is being used, but the level cannot be determined. If you set the IsolationLevel to Unspecified, the transaction executes according to the isolation level that is determined by the driver that is being used.
    • Chaos - the pending changes from more highly isolated transactions cannot be overwritten. Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.
    • Read Uncommitted - the Read Uncommitted isolation level allows a task to read data that can be in the middle of being modified by another transaction, uncommitted changes to data in the database. This is also known as dirty read. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.
    • Read Committed (Default) - the Read Committed isolation level specifies that a transaction can read only committed changes to data. It cannot read data that has been modified but not committed by other transactions. Data can be changed by other transactions between individual statements within the current transaction. If a transaction needs to read a row that has been modified by an incomplete transaction in another session, the transaction waits until the first transaction completes.
    • Repeatable Read - it means that the transaction will lock all the data that is used in the query and another transaction cannot update the data until the current transaction completes. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads.
    • Serializable - this is the most restrictive isolation level which prevents other user from updating or inserting rows into the data set until the transaction completes. The Serializable isolation level also specifies that statements cannot read uncommitted changes to data in the database.
    • Snapshot - in databases, Snapshot isolation specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. A transaction executing under snapshot isolation appears to operate on a personal snapshot of the database, taken at the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The transaction itself will successfully commit only if no updates are made to the data by any concurrent updates made since that snapshot.
  • InitSQL - specifies an SQL statement to be executed on each newly created connection. This can be used for application role restrictions. This property can be used in a generic way to provide a connection with a customer specific setup.
  • Integrated - gets the IntegratedConnectionPool settings for Telerik Data Access. This is only useful when using Pool=Integrated. The IntegratedConnectionPool class exposes the following properties:
    • TestInterval - sets the number of seconds between idle connection test runs and active connection timeout checks. Default value is 120 seconds.
    • MaxIdle - sets the maximum number of idle connections to be kept in the pool. Setting this to a number lower than the number set in MaxActive will cause the pool to shrink during idle periods. Default value is 10.
    • MinIdle - new connections are created by a background thread if the number of idle connections in the pool is less than this setting. This improves response time when the load on the server is increasing. Default value is 2.
    • TestOnAlloc - when this property is set to True, then each connection is validated before leaving the pool. This may have a serious negative impact on performance, so this property is set by default to False.
    • TestOnRelease - when this property is set to True then each connection is validated before being returned to the pool. This may have a serious negative impact on performance, so this property is set by default to False.
    • TestOnException - when this property is set to True then connections involved in an exception thrown by Telerik Data Access are validated before being returned to the pool. Default value is True.
    • TestWhenIdle - when this property is set to True then idle connections are tested periodically by a background thread. Connections that fail the validation process are discarded. Default value is True.
    • MaxConnectionAge - controls the maximum number of times a connection can be returned to the connection pool before it is closed to free accumulated resources. Default value is 1000.
    • ConnectRetryCount - if Telerik Data Access throws an exception for a connect() call the operation will be retried for the set number of times (0 for infinite, -1 for no retries). When the retries are exhausted the exception will propagate to the application. Default value is 30.
    • ConnectRetryIntervalMSec - sets the number of milliseconds the pool will sleep between retries for the connect() exceptions. Its default value is set at 1000 milliseconds, i.e, by default a connection will try for one second before giving up.
    • PreparedStatementCacheEnabled - specifies if PreparedStatemends per connection will be cached. Default value is True.
    • PreparedStatementCacheMax - the number of statements to store in the prepared statement cache. Default value is 0 = unlimited.
    • ValidateSQL - controls the SQL that is used to test connections for operation. Default value is null, i.e. it is automatically generated based on the backend.

Configuring the Connection Pool Functionality via Code

To configure the connection pool functionality in the code, you need to extend your context class with another partial class with the same name and implement the CustomizeBackendConfiguration partial method. Using the config argument you can setup the ConnectionPool configuration. For example:

public partial class EntitiesModel
{
    static partial void CustomizeBackendConfiguration(ref BackendConfiguration config)
    {
        config.ConnectionPool.Pool = ConnectionPoolType.Integrated;
        config.ConnectionPool.ActiveConnectionTimeout = 240;
        config.ConnectionPool.BlockWhenFull = false;
        config.ConnectionPool.Reserved = 3;
        config.ConnectionPool.MaxActive = 14;
        config.ConnectionPool.IsolationLevel = IsolationLevel.ReadUncommitted; //Deprecated
        config.ConnectionPool.Integrated.TestInterval = 240;
        config.ConnectionPool.Integrated.MaxIdle = 11;
        config.ConnectionPool.Integrated.MinIdle = 3;
        config.ConnectionPool.Integrated.TestOnAlloc = true;
        config.ConnectionPool.Integrated.TestOnRelease = true;
        config.ConnectionPool.Integrated.TestOnException = false;
        config.ConnectionPool.Integrated.TestWhenIdle = false;
        config.ConnectionPool.Integrated.MaxConnectionAge = 2000;
        config.ConnectionPool.Integrated.ConnectRetryCount = 40;
        config.ConnectionPool.Integrated.ConnectRetryIntervalMSec = 2000;
        config.ConnectionPool.Integrated.PreparedStatementCacheEnabled = false;
        config.ConnectionPool.Integrated.PreparedStatementCacheMax = 2;
    }
}

Partial Public Class EntitiesModel

    Private Shared Sub CustomizeBackendConfiguration(ByRef config As BackendConfiguration)
        config.ConnectionPool.Pool = ConnectionPoolType.Integrated
        config.ConnectionPool.ActiveConnectionTimeout = 240
        config.ConnectionPool.BlockWhenFull = False
        config.ConnectionPool.Reserved = 3
        config.ConnectionPool.MaxActive = 14
        config.ConnectionPool.IsolationLevel = IsolationLevel.ReadUncommitted 'Deprecated
        config.ConnectionPool.Integrated.TestInterval = 240
        config.ConnectionPool.Integrated.MaxIdle = 11
        config.ConnectionPool.Integrated.MinIdle = 3
        config.ConnectionPool.Integrated.TestOnAlloc = True
        config.ConnectionPool.Integrated.TestOnRelease = True
        config.ConnectionPool.Integrated.TestOnException = False
        config.ConnectionPool.Integrated.TestWhenIdle = False
        config.ConnectionPool.Integrated.MaxConnectionAge = 2000
        config.ConnectionPool.Integrated.ConnectRetryCount = 40
        config.ConnectionPool.Integrated.ConnectRetryIntervalMSec = 2000
        config.ConnectionPool.Integrated.PreparedStatementCacheEnabled = False
        config.ConnectionPool.Integrated.PreparedStatementCacheMax = 2
    End Sub

End Class

Configuring the Connection Pool Functionality via Config File

Another way to configure the connection pool functionality is to define the configuration in the App/web config file. For example:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <configSections>
   <section name="openAccessConfiguration"
            type="Telerik.OpenAccess.Config.OpenAccessConfigSectionHandler, Telerik.OpenAccess"
            requirePermission="false" />
 </configSections>
 <openAccessConfiguration>
   <backendConfiguration name="ConnectionPoolConfiguration">
     <connectionPool pool="Integrated"
                     activeConnectionTimeout="240"
                     blockWhenFull="false"
                     reserved="3"
                     maxActive="14"
                     isolationLevel="ReadUncommitted">
     <!-- The isolationLevel option is deprecated-->
       <integrated testInterval="240"
                   maxIdle="11"
                   minIdle="3"
                   testOnAlloc="true"
                   testOnRelease="true"
                   testOnException="false"
                   testWhenIdle="false"
                   maxConnectionAge="2000"
                   connectRetryCount="40"
                   connectRetryIntervalMSec="2000"
                   preparedStatementCacheEnabled="false"
                   preparedStatementCacheMax="2">
       </integrated>
     </connectionPool>
   </backendConfiguration>
 </openAccessConfiguration>
 <connectionStrings>
 </connectionStrings>
</configuration>

Again, you need to extend your context class with another partial class with the same name and add the following static constructor:

public partial class EntitiesModel
{
   static EntitiesModel()
   {
       BackendConfiguration.MergeBackendConfigurationFromConfigFile(
            backend,
            ConfigurationMergeMode.ConfigFileDefinitionWins,
            "ConnectionPoolConfiguration" );
   }
}

Partial Public Class EntitiesModel
    Shared Sub New()
        BackendConfiguration.MergeBackendConfigurationFromConfigFile(backend, ConfigurationMergeMode.ConfigFileDefinitionWins, "ConnectionPoolConfiguration")
    End Sub
End Class