Asp.net WebForms. Timeout error exceeded in SQL Server. How to solve?

A ASP.NET WebForms it uses the SQL Server 2008 I'm creating a screen that has a search field that works with the requests AJAX a a the Web-Service asmx.

To avoid bulk requests I added a delay in the method keyup do input:

$("#txtPesquisa").keyup(function () {
    delay(function () {
        directoryTreeRemake();
    }, 1000);
});

function directoryTreeRemake() {
    $("#directoryTree").fancytree("destroy");
    $("#directoryTree").fancytree({
        source: $.ajax({
            url: "/Servicos/PublicacaoServico.asmx/ObterDiretorios",
            data: {
                ...
            }
        }),
    });
}

I copied it delay function of an example on the web:

var delay = (function () {
    var timer = 0;
    return function (callback, ms) {
        clearTimeout(timer);
        timer = setTimeout(callback, ms);
    };
})();

Then, after each (1) second of the event keyup a request is made to the webMethod.

The application itself works normally with multiple users accessing at the same time, however, in my tests of this screen (mode debug), upon realizing that one query was fired while another was still being done, a database access error is generated:

Year exception of type ' System.InvalidOperationException ' occurred in System.Date.dll but was not handled in user code

Additional information: timeout expired. The timeout period was reached before a pool connection was obtained. This may have occurred because all connections in the pool were in use and the maximum pool size was reached.

The error happens exactly in the command Open():

public void Open()
{
    if (connection.State == ConnectionState.Closed)
        connection.Open();  // <-- onde o erro ocorre
}

On my computer I have a version of SQL Server obtained by Dreamspark, not the Express. Already where the application is installed is the version Express.

Requests to webMethod work, but only when requests are fired simultaneously, as I said, Does the error occur.

My Connection String:

 Data Source=(local); User Id=Usuario; Password=******;
 Initial Catalog=CRM; MultipleActiveResultSets=True

Connection class with the main methods used to access the bank:

public class DbConnection : IDisposable
{
    private SqlConnection connection = null;

    public DbConnection() {
        connection = new SqlConnection(ConfigurationManager
            .ConnectionStrings["DefaultConnection"].ConnectionString);
    }

    public void Dispose() {
        if (connection != null && connection.State == ConnectionState.Open)
            connection.Close();
        if (connection != null)
            connection.Dispose();
        connection = null;
    }

    public void Open() {
        if (connection.State == ConnectionState.Closed)
            connection.Open();
    }

    public void Close() {
        if (connection.State == ConnectionState.Open)
            connection.Close();
    }
    ....
}

All data access classes in this project are inherited from a class CustomDAO:

public class CustomDAO : IDisposable
{
    protected DbConnection dbConnection = null;

    public CustomDataAccess() {
        dbConnection = new DbConnection();
    }

    public void Dispose() {
        if (dbConnection != null)
            dbConnection.Dispose();
    }
}

What could be happening?

Author: Leonel Sanches da Silva, 2014-08-20

2 answers

Possibly the Pool is too short. Add the following to your Connection String:

Min Pool Size=5;Max Pool Size=250; Connect Timeout=3

This ensures that executed connections will not be closed ahead of time and that you can open at least 5 simultaneous connections.

Alternatively, you can also make the change by code, increasing the CommandTimeout of your SqlCommand for more than 30 seconds.

There is also the option to set more time for the execution of your request, which in Web Forms is around 90 seconds, resetting the ScriptTimeout property of the Class HttpServerUtility:

HttpServerUtility.ScriptTimeout = TimeSpan.FromMinutes(60).Seconds; 
 6
Author: Leonel Sanches da Silva, 2014-08-20 21:03:25

In fact the problem was the amount of connections with the SQL Server that were not being closed.

- mode debug I've put together a the break-point methods Dispose class DbConnection and CustomDAO, and with this, I have found that no stop was made in such methods, in which the connection should be terminated.

The classes were then not being released from memory, for some reason, and so the connections were not being closed.

Solved by closing the connections manually to the end of each method of the classes of the data access layer. With this the problems did not happen again.

Only what intrigues me is that it happened now, when I created this call to webMethod, and not before while the application was already in use.

 2
Author: , 2014-08-21 17:52:05