C# 增加 SQL 命令的命令超时

Increasing the Command Timeout for SQL command

提问by CHRISTOPHER MCCONVILLE

I have a little problem and hoping someone can give me some advice. I am running a SQL command, but it appears it takes this command about 2 mins to return the data as there is a lot of data. But the default connection time is 30 secs, how do I increase this, and apply it to this command?

我有一个小问题，希望有人能给我一些建议。我正在运行 SQL 命令，但由于数据量很大，此命令似乎需要大约 2 分钟才能返回数据。但是默认的连接时间是 30 秒，我如何增加它，并将它应用到这个命令？

public static DataTable runtotals(string AssetNumberV, string AssetNumber1V)
{
    DataTable dtGetruntotals;

    try
    {
        dtGetruntotals = new DataTable("Getruntotals");

        //SqlParameter AssetNumber = new SqlParameter("@AssetNumber", SqlDbType.VarChar, 6);
        //AssetNumber.Value = AssetNumberV; 

        SqlParameter AssetNumber = new SqlParameter("@AssetNumber", SqlDbType.VarChar, 10);
        AssetNumber.Value = AssetNumberV;

        SqlParameter AssetNumber1 = new SqlParameter("@AssetNumber1", SqlDbType.VarChar, 10);
        AssetNumber1.Value = AssetNumber1V;

        SqlCommand scGetruntotals = new SqlCommand("EXEC spRunTotals @AssetNumber,@AssetNumber1 ", DataAccess.AssetConnection); 
        // scGetruntotals.Parameters.Add(AssetNumber);
        scGetruntotals.Parameters.Add(AssetNumber);
        scGetruntotals.Parameters.Add(AssetNumber1);

        SqlDataAdapter sdaGetruntotals = new SqlDataAdapter();
        sdaGetruntotals.SelectCommand = scGetruntotals;
        sdaGetruntotals.Fill(dtGetruntotals);

        return dtGetruntotals;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error Retriving totals Details: Processed with this error:" + ex.Message);
        return null;
    }
}

采纳答案by Ehsan

it takes this command about 2 mins to return the data as there is a lot of data

由于有大量数据，此命令大约需要 2 分钟才能返回数据

Bad Design. Consider using paging here.

糟糕的设计。考虑在此处使用分页。

default connection time is 30 secs, how do I increase this

默认连接时间是 30 秒，我该如何增加它

As you are facing a timout on your command, therefore you need to increase the timout of your sql command. You can specify it in your command like this

由于您的命令面临超时，因此您需要增加sql 命令的超时。您可以像这样在命令中指定它

// Setting command timeout to 2 minutes
scGetruntotals.CommandTimeout = 120;

回答by Ajay

Add timeout of your SqlCommand. Please note time is in second.

添加您的SqlCommand. 请注意时间以秒为单位。

// Setting command timeout to 1 second
scGetruntotals.CommandTimeout = 1;

回答by Akash Srivastava

Since it takes 2 mins to respond, you can increase the timeout to 3 mins by adding the below code

由于响应需要 2 分钟，您可以通过添加以下代码将超时增加到 3 分钟

scGetruntotals.CommandTimeout = 180;

Note: the parameter value is in seconds.

注意：参数值以秒为单位。

回答by Pranav Kulshrestha

Setting CommandTimeout to 120 is not recommended. Try using pagination as mentioned above. Setting CommandTimeout to 30 is considered as normal. Anything more than that is consider bad approach and that usually concludes something wrong with the Implementation. Now the world is running on MiliSeconds Approach.

不建议将 CommandTimeout 设置为 120。尝试使用上面提到的分页。将 CommandTimeout 设置为 30 被认为是正常的。除此之外的任何事情都被认为是糟糕的方法，并且通常会得出实施有问题的结论。现在全世界都在使用 MiliSeconds 方法。

回答by Emam

Setting command timeout to 2 minutes.

将命令超时设置为 2 分钟。

 scGetruntotals.CommandTimeout = 120;

but you can optimize your stored Procedures to decrease that time! like

但是您可以优化您的存储过程以减少该时间！喜欢

• removing courser or while and etc
• using paging
• using #tempTable and @variableTable
• optimizing joined tables

• 删除 courser 或 while 等
• 使用分页
• 使用 #tempTable 和 @variableTable
• 优化连接表