问题描述

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

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?

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;
    }
}

推荐答案

由于数据量很大，这条命令大约需要2分钟才能返回数据

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

可能是糟糕的设计.考虑在这里使用分页.

Probably, Bad Design. Consider using paging here.

默认连接时间是 30 秒，如何增加这个时间

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

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

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

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

这篇关于增加 SQL 命令的命令超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！