asp.net c#SqlDataSource 超时问题

asp.net c# SqlDataSource timout problems

提问by user1024416

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.

我正在尝试将 SqlDataSource 的超时时间延长到 30 秒以上（似乎是默认值）。我正在尝试运行一个必须运行 100,000 条记录的存储过程。在繁忙期间它超时。我在 2003 服务器上使用 ASP.NET 4.0 和 IIS 6.0。

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

谢谢

回答by Schenz

Timeout is generally set in your connection string. See http://www.connectionstrings.com/for complete examples.

超时通常在您的连接字符串中设置。有关完整示例，请参阅http://www.connectionstrings.com/。

回答by Nikhil Agrawal

Connect Timeout/Connection Timeout/Timeout Default Value - 15 sec The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Valid values are greater than or equal to 0 and less than or equal to 2147483647.

连接超时/连接超时/超时 默认值 - 15 秒 在终止尝试并生成错误之前等待连接到服务器的时间长度（以秒为单位）。有效值大于或等于 0 且小于或等于 2147483647。

string myconstr = "Data Source=(local);Initial Catalog=AdventureWorks;"
    + "Integrated Security=SSPI;Connection Timeout=30"

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.100).aspx

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.100).aspx

回答by saluce

You need to ensure both your CommandTimeoutand your ConnectionString's Connect Timeoutare set to prevent timing out on long-running stored procedures. If you don't set the connection timeout, you'll timeout before the stored procedure is finished, even if the stored procedure command itself hasn't timed out.

您需要确保您CommandTimeout和您ConnectionString的Connect Timeout都设置为防止长时间运行的存储过程超时。如果你没有设置连接超时，你会在存储过程完成之前超时，即使存储过程命令本身没有超时。

回答by Ulises

There are two types of timeout: ConnectionTimeoutand CommandTimeout:

有两种类型的超时：ConnectionTimeout和CommandTimeout：

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

ConnectionTimeout确定您的应用程序等待与您的服务器建立连接的最长时间。

CommandTimeout: Max time allowed for a command to execute.

CommandTimeout：允许执行命令的最长时间。

Make sure you set both. In the Comand:

确保你设置了两者。在COMAND：

 command.CommandTimeout = 300;

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

注意：Selecting如果您的命令是数据源的一部分，这可以在事件中实现。e.Command.CommandTimeout = 0;值0意味着无限期地等待。

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.

注意：尝试全局设置连接字符串超时，可能在您的配置文件中。

回答by Akshay Khandelwal

The Maximum Connection Time Out Value can be 2147483647. Try setting the Connection Timeout value into your Connection string in your Web Config as below

最大连接超时值可以是 2147483647。尝试将连接超时值设置到您的 Web 配置中的连接字符串中，如下所示

<connectionStrings> <add name="ConnectionString" connectionString="Data Source=144681;Initial Catalog=Customer;Persist Security Info=True;User ID=xxxxx;Password=yyyyy" providerName="System.Data.SqlClient" /> </connectionStrings>

<connectionStrings> <add name="ConnectionString" connectionString="Data Source=144681;Initial Catalog=Customer;Persist Security Info=True;User ID=xxxxx;Password=yyyyy" providerName="System.Data.SqlClient" /> </connectionStrings>

回答by anon

There's a timeout for both the SQL and the page response time.

SQL 和页面响应时间都有超时。

Server.ScriptTimeout = 120; 
e.Command.CommandTimeout = 120;

回答by Harry

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

将超时设置为 0 表示没有超时

回答by Bonfix Ngetich

This is what worked for me:

这对我有用：

  <script runat="server">
    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
    e.Command.CommandTimeout = 0;
    }
    </script>


<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="Your Connection String" 
ProviderName="Teradata.Client.Provider"
SelectCommand="A SELECT COMMAND THAT TAKES A LONG TIME"
DataSourceMode="DataSet"
onselecting="SqlDataSource1_Selecting">