SqlConnection Time out error through ASP.NET WEB API

Question

I am trying to execute a stored procedure (query) from the ASP Web API. I have developed it (command timeout is 300 seconds).

At the beginning, calling the stored procedure from Web API is very fast, but after a few days when calling the same WEB API, it gives me time out error (shown below).

Meanwhile if I try to execute the same stored procedure with the same parameter even at the same time directly from SQL Server Management Studio, it is executed within seconds.

The code :

public List<ScheduledTitles> GetScheduledTitles(CriteriaFields _criteria)
{
        try
        {
            System.Data.DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            using (SqlConnection con = new SqlConnection(_strDBConnection))
            {
                con.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = con;
                comm.CommandText = "spQuery_1";
                comm.Parameters.Add(new SqlParameter() { ParameterName = "Product_Line_ID", SqlDbType = SqlDbType.Int, Value = _criteria.ProductLineID == null ? (object)System.DBNull.Value : _criteria.ProductLineID });

                comm.CommandType = CommandType.StoredProcedure;
                da.SelectCommand = comm;
                comm.CommandTimeout = 300;
                da.Fill(ds);
            }
            .
            .
            . 

            return scheduledTitlesS.ToList<ScheduledTitles>();
       }
       catch (Exception exc)
       { 
            throw exc;
       }
    }

The error message:

The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at HERS_SearchQuery.Data.DataAccess.SQLServer.HERSScheduleRepository.GetScheduledTitles(CriteriaFields _criteria) in C:\Users\nah\Documents\Visual Studio 2010\Projects\HERS_SchedulerSearch\HERS_SearchQuery.Data\DataAccess\SQLServer

Have you tried closing the connection after filling the dataadapter? — Tim
Like Tim mentioned, it seems like your connections aren't closing; meaning that the number of connections may be tying up your SQL calls. Use con.Close() with a finally in your Try Catch statements — sunnysidedown916
Please show what _strDBConnection looks like. Make sure "context connection = false" — MikeG
@Tim: I thing no need to close the connection explicitly as using (SqlConnection con = new SqlConnection(_strDBConnection)) block will be converted into a normal try.. finally block by compiler calling IDisposable object in the finally. — Avijit
First, get rid of that try/catch block. It has no effect other than to make it look like the exception came from the "throw". Second, please just put your SqlCommand and SqlDataAdapter into using blocks and see what happens. — John Saunders

Paolo Costa Paolo Costa · Accepted Answer · 2014-12-29T20:21:01

I had a similar issue a couple of years ago, I solved it by using the option

WITH RECOMPILE

in the strored procedure. It happened that SQL SERVER was caching an execution plan that was not efficient for all parameters.

Of course I don't know whether this can solve your problem, but I think it's worth to try.

SqlConnection Time out error through ASP.NET WEB API

1 Answers