long running sql procedure hangs website

I have an MVC 4 web application that calls the business layer which calls a data layer method ExecuteSP asynchronously. The data layer method ExecuteSP calls a long running stored procedure like this:

public class MainDataLayer
{
    private Database database;  //Enterprise library 5 
    private DbCommand dbc;
    private List<SPParams> spParams;

    public MainDataLayer()
    {
        _database = DatabaseFactory.CreateDatabase("strConn");

        //set multiple sp params like the one below
         _spParams.Add(new SPParams() { ParamName = "@pID", ParamValue = ID }); 
    }

    public DataSet ExecuteSP()
    {

        dbc = database.GetStoredProcCommand('long_running_sp');
        dbc.CommandTimeout = 300;

        foreach (SPParams p in spParams)
        {
            dbc.Parameters.Add(new SqlParameter(p.ParamName, p.ParamValue));
        }

        DataSet ds = _database.ExecuteDataSet(dbc);

        return ds;
}
}


//this class is used in the class above for setting the sp parameters
public class SPParams
{
    public string ParamName { get; set; }
    public object ParamValue { get; set; }

}

The long running stored procedure has a few inserts and a few updates and the final time out call as below to simulate a long running process.

WAITFOR DELAY '0:02:00'

The problem is when I call this stored procedure thru my web application deployed on my test server from client pc A, it hangs all further calls from the same client or any other user calling the webpage until the 2 minutes in the stored procedure have elapsed. When I run the stored procedure directly from sql management studio and run another sql statement side by side, the stored procedure takes 2 minutes to execute and the other sql statement executes immediately. It doesnt look like there is something on the database side that I need to change. What could be the problem with my code?

Thanks

Answers


Need Your Help

How to save a resource from an executable jar file to local drive?

java jar save resources local

I wonder if there is a way to extract a resource file packaged in an executable jar file and save it to a local drive, so when a user downloads my jar file and double clicks on it, it will first sa...

Load template on click angular

javascript jquery angularjs angularjs-scope

Have tried loading the template on click of the button but some where approach is wrong templates are not loading.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.