C# 不能将 CommandTimeout 设置为超过 90 秒

Cannot set CommandTimeout to longer than 90 seconds

提问by Andy Woodward

I have an asp.net web app written with C# code behind in Visual Studio 2008.

我有一个在 Visual Studio 2008 后面用 C# 代码编写的 asp.net web 应用程序。

I have a SQL query that queries a SQL Server database on another server. When I run the query it times out after 90 seconds. I have tried all sorts of different settings.

我有一个 SQL 查询，用于查询另一台服务器上的 SQL Server 数据库。当我运行查询时，它在 90 秒后超时。我尝试了各种不同的设置。

I've trawled the internet but still cannot find an answer. I have a line in my code to set CommandTimeoutfor the query. If I set it to CommandTimeout = 1;the query will time out after 1 second, if I set it to CommandTimeout = 90;the query will timeout after 90 seconds.

我已经在互联网上搜索过，但仍然找不到答案。我的代码中有一行用于设置CommandTimeout查询。如果我将它设置为CommandTimeout = 1;查询将在 1 秒后CommandTimeout = 90;超时，如果我将其设置为查询将在 90 秒后超时。

This is all good but my query takes approx. 150 seconds to run. If I change the code to CommandTimeout = 200;the query still times out after 90 seconds. It seems I can only change the timeout when it is less than 90 seconds. Anything above 90 seconds still times out at 90 seconds.

这一切都很好，但我的查询需要大约。运行 150 秒。如果我将代码更改为CommandTimeout = 200;查询仍然在 90 秒后超时。似乎我只能在小于 90 秒时更改超时。任何超过 90 秒的时间仍然会在 90 秒时超时。

This is driving me mad. Is there another setting somewhere that is overriding my code?

这让我发疯。是否有其他设置覆盖了我的代码？

Here is my code

这是我的代码

// bind the data to the Gridview
private void BindTaskList()
{
    string startDate = StartDate.Text;
    string endDate = EndDate.Text;

    // Create a connection string referring to the connection string from web.config file
    string conStr = ConfigurationManager.ConnectionStrings["Docupro_ReportingConnectionString"].ConnectionString;

    SqlConnection sqlConnection = new SqlConnection(conStr);

    // This is the SQL query and must be in one long line
    SqlCommand sqlCommand = new SqlCommand("SELECT T5.DisplayName AS 'User', T2.LongName AS 'Print Type', SUM(T1.Quantity) AS 'Total Quantity', '￡'+CONVERT(varchar, SUM(T1.Amount), 3) AS 'Total Cost' FROM tblTransaction T1 JOIN tblItem T2 ON T1.ItemID = T2.ItemID JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID JOIN tblTender T4 ON T1.TenderID = T4.TenderID JOIN tblCustomer T5 ON T4.CustomerID = T5.CustomerID JOIN tblTerminal T6 on T1.TerminalID = T6.TerminalID JOIN tblStation t7 on T6.StationID = t7.StationID WHERE (TransactionDateTime BETWEEN @StartDate AND @EndDate)AND T3.LongName = 'Not Assigned' GROUP BY T5.DisplayName, T2.LongName ORDER BY T5.DisplayName", sqlConnection);

    // Create the parameters from the text boxes and drop down list
    sqlCommand.Parameters.Add(new SqlParameter("@StartDate", startDate));
    sqlCommand.Parameters.Add(new SqlParameter("@EndDate", endDate));

    // Set the command timeout to 200 seconds to allow for long queries
    sqlCommand.CommandTimeout = 200;
    sqlConnection.Open();

    // Create a DataSet to fill with data
    SqlDataAdapter myAdapter = new SqlDataAdapter(sqlCommand);
    DataSet myDataSet = new DataSet();
    myAdapter.Fill(myDataSet);

    // Turn off GridView Footer
    GridView1.ShowFooter = false;

    // Fill the GridView with the DataSet
    GridView1.DataSource = myDataSet;
    GridView1.DataBind();
}

Many thanks in anticipation

非常感谢期待

Andy

安迪

Error message is:

错误信息是：

Sys.WebForms.PageRequestManagerTimeoutException: The server request timed out ScriptResource.axd
Code:0

Sys.WebForms.PageRequestManagerTimeoutException：服务器请求超时 ScriptResource.axd
代码：0

回答by Freddie Fabregas

Have you tried setting it to 0?

您是否尝试将其设置为0？

Remarks

评论

A value of 0 indicates no limit, and should be avoided in a CommandTimeout because an attempt to execute a command will wait indefinitely.

值 0 表示没有限制，应该在 CommandTimeout 中避免，因为尝试执行命令将无限期等待。

Here'sthe reference.

这是参考。

回答by usr

The error message shows that the timeout comes from ASP.NET (not from ADO.NET). Set Server.ScriptTimeout=200.

错误消息显示超时来自 ASP.NET（而不是来自 ADO.NET）。设置Server.ScriptTimeout=200。

Interpreting the error message is the first step to debugging any error. Don't just stop when you read "timeout". Read and interpret everything.

解释错误消息是调试任何错误的第一步。当你读到“超时”时，不要只是停下来。阅读和解释一切。

回答by El Ronnoco

FWITW...

FWITW...

I had an error (not ASP related -this was within a console application) and I found that setting the CommandTimeout aftersetting any Parameters didn't seem to take effect (even though the property was updated.)

我有一个错误（与 ASP 无关 - 这是在控制台应用程序中），我发现在设置任何参数后设置CommandTimeout似乎没有生效（即使属性已更新。）

eg

例如

SqlCommand comm = new SqlCommand(conn, proc);
comm.Parameters.AddWithValue("@a",123);
comm.CommandTimeout = 300;
comm.ExecuteReader(); //Times out at 30 seconds

but

但

SqlCommand comm = new SqlCommand(conn, proc);
comm.CommandTimeout = 300;
comm.Parameters.AddWithValue("@a",123);
comm.ExecuteReader(); //Times out at 300 seconds

I did actually have a function returning the SqlCommand and then it was executed when returned, but I am guessingthis simplified version would have the same result.

我确实有一个返回 SqlCommand 的函数，然后在返回时执行它，但我猜这个简化版本会有相同的结果。

In summary

总之

Try setting the CommandTimeout property immediately after newing-up the SqlCommand.

尝试在更新 SqlCommand 后立即设置 CommandTimeout 属性。

回答by Raven

Exist TimeOut in SQLcommand and exist TimeOut in SqlConnection

SQLcommand 中存在 TimeOut 和 SqlConnection 中存在 TimeOut

                ServerPath = "Data Source=" + myDr["server1"].ToString();
                ServerPath += ";Initial Catalog=" + myDr["catalog1"].ToString();
                ServerPath += ";uid=" + myDr["username"].ToString();
                ServerPath += ";pwd=" + myDr["password1"].ToString();
                ServerPath += ";Connect Timeout=" + TimeOuted.ToString();



 conn1.ConnectionString = ServerPath;