How to force refresh on connection pool managed by data access

[Anwar]

Hi,

I have a long-running rest service which uses Telerik Data Access for backend db access. I have configured a connection pool using the visual designer as explained in the following docs:

http://docs.telerik.com/data-access/feature-reference/tools/model-settings-dialog/backend-settings/feature-ref-tools-visual-designer-model-settings-backend-conn-pool

 All seems to work fine for a week or so and then somehow the pool's connections are no longer working (I get timeout exceptions from the data access layer). I am currently using Firebird as a backend db and I suspect this issue has something to do with firebird itself. When I restart the service all works fine again. 

I would like to avoid these restarts. Therefore, is there a simple way (in configuration or using the API) to force a "flush" of the connection pool. In order words I would like to release the pool entirely every 24h for example and create it again afresh.

Thanks,

Anwar

 

[Thomas]

Hi Anwar,

currently we do not offer a connection expiration after a given time. What we offer though is to close the connection after a certain amount of activities (Maximum Connection Age).
I think your best option is to close the connection directly after usage, which can be achieved by choosing the 'Use ADO Connection Pool' option. In this way, OpenAccess will not create it's own pool but use the ADO driver facilities for pooling. Thus the connections will be closed logically much earlier.

Regards,
Thomas
Telerik

 

[Anwar]

Hi Thomas,

Tried to switch to ADO connection pool but things were actually worse. Is there a  way to determine why I am having the root problem which is connection timeouts? This issue makes the whole rest service basically unusable. Any suggestions on how to configure the data access layer would be welcome. Also if I could reset all the connections through code, that would be also an option.

 Thanks!

[Anwar]

Hi Thomas,

 You can ignore my previous post. It seems that the debugger was messing up everything :)

So I switched to ADO connection pool in the mapping settings. I've also enabled logging so that I can monitor the state of the connections and it seems that for the moment everything works fine...connections are released correctly.

I would still like to be able to trigger periodically a refresh of the connection pool just to be on the safe side. In particular I would like to be able to call the following function:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.clearpool(v=vs.110).aspx 

 

 

 

[Thomas]

Hi Anwar,

glad to hear that. I think the trick is to make the ADO pool work good for you. You should consider those
connection string elements to tweak the behavior to your needs:

"Connection lifetime" 
"Pooling"
"MinPoolSize"
"MaxPoolSize"

Please see http://www.firebirdsql.org/en/net-examples-of-use/ and the Firebird documentation for more information.

Using a method on SqlClient like ClearPool will not work, as it is specific the MS SQL Server connections, not Firebird connections. You will need to use something like FbConnection::ClearAllPools(). Please make sure that you are using the right type (the one that DataAccess uses) and not a different compiled version; Reflection might be your friend here.

Regards,
Thomas
Telerik

 

[Anwar]

Hi Thomas,

So I have updated my connection string to include the following parameters:

Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;

I am also using a connection timeout of 10s as shown in the following code (whenever I get a timeout exception, my code calls FbConnection.ClearAllPools():

User user;

 try{
      CREW_INFO crewInfo = (from c in dbContext.CREW_INFOs.WithOption(new QueryOptions() { CommandTimeout = 10 })
                                                       where c.WEB_LOGIN == login && c.WEB_PWD_CLEAR == password
                                                       select c).FirstOrDefault();

                    if (crewInfo != null)
                    {
                        user.FirstName = crewInfo.CREW_FNAME;
                        user.LastName = crewInfo.CREW_LNAME_1;
                        user.CrewCode = crewInfo.CREW_CDE;
                    }
                }

catch (TimeoutException e){

     // log exception

    FbConnection.ClearAllPools();
}

The idea is to refresh the connection pool whenever a timeout happens. Is the previous code the right approach? Is there a specific Telerik exception I should catch or the generic TimeoutException is sufficient (Ideally I would want to log as much information as possible for the reason of the timeout)?

Finally FbConnection.ClearAllPools() is a static method. I did not understand by what you meant in getting the right type? As long as the backend is Firebird, Can I simply call FbConnection.ClearAllPools() in my code?

 

Thanks!

 

 

 

 

 

[Thomas]

Hello Anwar,

I would use the OpenAccessException which is the base class of our exception hierarchy instead of a TimeoutException. I would also create another static method that wraps the FbConnection.ClearAllPools() 
so that there is a single place where you pass through in cases of errors.

What I meant with 'right type' is the following: When you link against the FB Ado driver in order to call ClearAllPools, the application is bound to a version. This version might be a different one from the version of the FB Ado driver that OpenAccess uses (We load dynamically based on the DbProviderFactories available). In this case, there could be two type FbConnection types in the AppDomain, and ClearAllPools would show no effect. Therefore I think it is better to use a bit of reflection to find the FbConnection type that is actually used by OpenAccess and issue the ClearAllPools() call on that type. 

Regards,
Thomas
Telerik

 

[Anwar]

Thomas,

 Thanks for the update. I hava a single reference to FirebirdSql.Data.FireBirdClient v3.0.2.0 which is the version that Telerik Data Access uses. If I remember correctly, any other version of the Assembly raises an exception at runtime. 

Could you kindly post a code snippet illustrating how to determine the "right" FbConnection at runtime?

 

Thanks!

[Thomas]

Hi Anwar,

in case there is only one version : fine, no action needed. If you prefer to use a more dynamic approach, you could use something along these lines:

private static Type FindLoadedType(string name)
{
    foreach (var a in AppDomain.CurrentDomain.GetAssemblies())
    {
        var t = a.GetType(name, false, false);
        if (t != null)
            return t;
    }
    return null;
}

You need to use the full name of the type, like "FirebirdSql.Data.FirebirdClient.FbConnection" as argument and you should not call this method before a connection was made. From that, normal reflection should be used to invoke the static method.

Regards,
Thomas
Telerik