asp.net c# SqlDataSource timout problems

Question

I'm trying to extend the timeout of an SqlDataSource beyond 30 second (Seems to be the default). I'm trying to run a stored procedure that has to run through 100,000s of records. During busy periods it times out. I'm using ASP.NET 4.0 and IIS 6.0 on 2003 server.

Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I've tried to no avail to extend the timeout:

< asp:SqlDataSource ID="dsTest" EnableCaching="true" CacheDuration="604800" runat="server" ConnectionString="<%$ ConnectionStrings:SuperNARIC %>" SelectCommand="selectStatus" SelectCommandType="StoredProcedure" onselecting="dsTest_Selecting" >
    <SelectParameters>
        < asp:ControlParameter ControlID="ddlCar" Name="CountryID" PropertyName="SelectedValue" Type="Int32" />
    < /SelectParameters>
< /asp:SqlDataSource>



protected void dsTest_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.CommandTimeout = 300;
    }

Any help would be greatly appreciated.

Thanks

Answer 1

Like mentioned here, this worked for me.

You can increase the Timeout property like this

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
            e.Command.CommandTimeout = 0;
        }

Setting timeout to 0 means no timeout

Answer 2

There are two types of timeout: ConnectionTimeout and CommandTimeout:

ConnectionTimeout Determines the maximum time your app will wait to establish a connection to your server.

CommandTimeout: Max time allowed for a command to execute.

Make sure you set both. In the Comand:

 command.CommandTimeout = 300;

Note: This can be implemented in the Selecting event should your command be part of a DataSource. e.Command.CommandTimeout = 0; A value of 0 means to wait indefinitely.

And connection string:

SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder(connectionString);
cs.ConnectTimeout = 300;

Or:

<add name="MyConnectionString" connectionString="Data Source=.\SQLEXPRESS; Database=MyDB; Integrated Security=True;Pooling=True;connection timeout=30" providerName="System.Data.SqlClient" />

Note: Try setting the connection string timeout globally, perhaps in your config file.