How to limit number of ODBC connections from a .Net web application?

Question

I have created a .Net 3.5 web service in C# that communicates to a Progress OpenEdge database through ODBC.

This database has been set up to only allow 10 simultaneous connections on the user account the web service is using (third party restriction).

I am running into a huge problem when the web service gets a lot of simultaneous requests and tries to connect to the database for each of these requests. When the connection limit is exceeded, the database will reject new incoming connections for a couple of minutes.

The web service is running on windows server 2008. Connection pooling is enabled for the ODBC driver.

I somehow will have to restrict the number of connections that my web service is trying to make, but I don't know how. The Progress OpenEdge ODBC driver that I am using, does not support a maximum pool size argument.

I've been reading a lot of documentation from microsoft on the subject, but all I have been able to conclude is:

• Connection pooling is enabled by default
• Connection pooling cannot be configured from my application
• Default maximum pool size is 100
• There is no way to change the maximum pool size unless the driver you are using supports it

Can someone confirm this, and/or suggest a method to limit the number of connections in this situation? Thank you very much :)

Answer 1

You can try the WCF approach as mentioned or you could implement the connection pooling programmatically.

public interface IService
{
    void Execute(Action<IDbConnection> command);
    T Execute<T>(Func<IDbConnection, T> command);
}

public sealed class ConnectionManager : IService
{
    public const int MaxConnections = 10;

    private readonly string _connectionString;
    private readonly SemaphoreSlim _workers = new SemaphoreSlim(0, MaxConnections);

    public ConnectionManager(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void Execute(Action<IDbConnection> command)
    {
        lock(_workers)
        {
            using(var connection = new OdbcConnection(_connectionString))
            {
                connection.Open();
                command(connection);
            }
        }
    }

    public T Execute<T>(Func<IDbConnection, T> command)
    {
        lock(_workers)
        {
            using(var connection = new OdbcConnection(_connectionString))
            {
                connection.Open();
                return command(connection);
            }
        }
    }
}

The SemaphoreSlim will prevent more than 10 connections from opening assuming the code that is called doesn't try anything sneaky.