Serenity: What's the proper way to reuse database connections in Serenity?

[UlyssesAlves]

I'm currently working on a Serenity based web application which is getting unresponsive after some time it has been started. Our hosting service (Azure) diagnostics shows there's a high count of TCP connections being opened, which eventually prevents the application from responding to subsequent requests.

So I'm looking at the source code of this web application now to try and find out where it may be making this uncontrolled use of TCP connections. Since it's a web app, it doesn't make much (if any) use of web client objects, so my best bet is that these TPC connections are probably the SQL Connections the application opens to connect to the database on those requests in which it's needed to retrieve/persist data.

I can see in many of our web app requests the use of the following code:

using (var connection = SqlConnections.NewByKey("Default"))

By looking at Serenity source code, if I'm not wrong it looks like this call always creates a new database connection rather then reusing some connection from the connection pool, right?

/// <summary>
/// Creates a new connection for specified connection key.
/// </summary>
/// <param name="connectionKey">The connection key.</param>
/// <returns>A new connection</returns>
public static IDbConnection NewByKey(string connectionKey)
{
    var connectionSetting = GetConnectionString(connectionKey);
    var connection = connectionSetting.ProviderFactory.CreateConnection();
    connection.ConnectionString = connectionSetting.ConnectionString;

    var profiler = Dependency.TryResolve<IConnectionProfiler>();
    if (profiler != null)
        return new WrappedConnection(profiler.Profile(connection), connectionSetting.Dialect);

    return new WrappedConnection(connection, connectionSetting.Dialect);
}

If that's the case, I wonder then what would be the proper Serenity way to get connection strings from the pool, without creating new connections for the same connection string every time the web app needs to connect to the database.

Looking for the answer by myself I could find there's another method on Serenity source code, called "TryGetConnectionString" shown below:

/// <summary>
/// Tries to get a connection string by its key.
/// </summary>
/// <param name="connectionKey">The connection key.</param>
/// <returns>Connection string</returns>
public static ConnectionStringInfo TryGetConnectionString(string connectionKey)
{
    ConnectionStringInfo connection;
    if (!connections.TryGetValue(connectionKey, out connection))
    {
        var newConnections = new Dictionary<string, ConnectionStringInfo>(connections);

        var configuration = Dependency.TryResolve<IConfigurationManager>();
        if (configuration == null)
            return null;

        var connectionSetting = configuration.ConnectionString(connectionKey);
        if (connectionSetting == null)
            return null;

        connection = newConnections[connectionKey] = new ConnectionStringInfo(connectionKey, connectionSetting.Item1, 
            connectionSetting.Item2);

        connections = newConnections;
    }

    return connection;
}

It's pretty clear from the source code of this method "TryGetConnectionString" that it tries to first get an existing connection and then creates and returns a new connection in case one doesn't still exist and there's a valid connection string configured with the key passed as an argument to the method.

So, is this the proper way to reuse connection strings on Serenity applications? Does this method "TryGetConnectionString" make use of the connection pool to prevent the unecessary creation of new connections for the same connection string repeatedly? Or does "SqlConnections.NewByKey" method also makes use of the connection pool even though the source for this method looks like to be always creating new connections each time it is called?

From what you Serenity guys tell me, it may be that I could resolve this TCP Connections exhaustion issue by simply changing the way our web app creates the connections, changing it from using "SqlConnections.NewByKey" to "SqlConnections.TryGetConnectionString". Is that so?

[kilroyFR]

I dont have the answer but that's a damn good question !

[JohnRanger]

Hi @UlyssesAlves ,
I also did run into the same problem - but with using our local SQL server from SF - and it was the SQL connections on server side which did run out - not the TCP connections. It was caused because I didn't immediately close the SQL connection after I did the necessary queries.

So I do now at the end of every method which creates a new connection:

var connection1 = SqlConnections.NewByKey("default"); // *** Creating the connection ***
.
.
.
var Result1 = connection1.Query(query1).FirstOrDefault();  // *** Do something with the connection ***
.
.
.
connection1.Close();  // *** CLOSE the connection immediately ***

This has solved this problem. The SF app - where I had this problem runs now for months without recycling. No more SQL connection exhausting.

With kind regards,

John

[UlyssesAlves]

@JohnRanger Your observation is valid and also important.

But in my case the connections are being created in “using” statements, so they might be being closed automatically after the code leaves the “using” code block, that is, after the connection object is disposed.

What I’m really looking for is a way to reuse existing connections, maybe from a connection pool, without creating new ones for the same database every time, as I’ve explained above.

[haoas]

"using" is not the issues..

[JohnRanger]

@haoas ,

thanks for the link to this excellent description of how connection pooling works.

@UlyssesAlves : According to the above MS docs, it looks that connection pooling is automatically used by design - except you have disabled it. And when you do the connection "opening" within a using statement, the connection is returned to the connection pool at the end of the using statement - to be reused again.

So the problem with the many connections is probably something else.

With kind regards,

John

[volkanceylan]

It uses standard .net connection pooling, and its normal to have around 100 connections, the idea behind connection pooling is to keep the connection open to reuse. The number depends on the connection pooling system calculations.