problem withAsync SqlComman

Question

I have problem with Timeout, when I run a command through app, a timeout exception is thrown, but when I run it directly in sql there is no timeout exception!

my SP take about 11 min when I run it directly. for solving this issue, I found below code here, but It doesn't work properly! Immediately after beginExecute, IAsyncResult.iscomplete become true !!!!

where is the problem ?

IAsyncResult result = command.BeginExecuteNonQuery();

    int count = 0;
    while (!result.IsCompleted)
    {
        Console.WriteLine("Waiting ({0})", count++);
        System.Threading.Thread.Sleep(1000);
    }
    Console.WriteLine("Command complete. Affected {0} rows.",
    command.EndExecuteNonQuery(result));

regards

Answer 1

A connection string will default to a 15 second timeout. See on MSDN.

You can change the timeout on the connection string to last longer (connection timeout=600, for a 10 minute timeout).

See this site for more about connection strings.

Having said that, you should look at optimizing your database and/or stored procedure. 11 minutes for a stored procedure is very very long. Do you have the correct indexes on your tables? Is you stored procedure written in the most optimal way?

Update:

Have you made sure you are using the correct command and that the results are correct? IsComplete being true almost immediately suggests that the command has indeed finished.

Answer 2

Increase the command timeout instead (SqlCommand.CommandTimeout) which by default is 30 seconds.