增加命令超时的SQL命令 [英] Increasing the Command Timeout for SQL command

问题描述

我有一个小问题，希望有人可以给我一些建议。我运行一个SQL命令，但似乎需要这条命令约2分钟，返回的数据是有大量的数据。但是默认的连接时间为30秒，我该如何提高这一点，并将其应用到这个命令？

 公共静态数据表runtotals（字符串AssetNumberV，串AssetNumber1V）
{
    数据表dtGetruntotals;

    尝试
    {
        dtGetruntotals =新的DataTable（Getruntotals）;

        //的SqlParameter AssetNumber =新的SqlParameter（@ AssetNumber，SqlDbType.VarChar，6）;
        //AssetNumber.Value = AssetNumberV;

        的SqlParameter AssetNumber =新的SqlParameter（@ AssetNumber，SqlDbType.VarChar，10）;
        AssetNumber.Value = AssetNumberV;

        的SqlParameter AssetNumber1 =新的SqlParameter（@ AssetNumber1，SqlDbType.VarChar，10）;
        AssetNumber1.Value = AssetNumber1V;

        SqlCommand的scGetruntotals =新的SqlCommand（EXEC spRunTotals @ AssetNumber，@ AssetNumber1，DataAccess.AssetConnection）;
        // scGetruntotals.Parameters.Add（AssetNumber）;
        scGetruntotals.Parameters.Add（AssetNumber）;
        scGetruntotals.Parameters.Add（AssetNumber1）;

        SqlDataAdapter的sdaGetruntotals =新的SqlDataAdapter（）;
        sdaGetruntotals.SelectCommand = scGetruntotals;
        sdaGetruntotals.Fill（dtGetruntotals）;

        返回dtGetruntotals;
    }
    赶上（例外前）
    {
        的MessageBox.show（错误Retriving合计详细信息：加工处理此错误：+ ex.Message）;
        返回null;
    }
}
 

解决方案

  

它需要这个命令约2分钟，返回的数据是有大量数据的

糟糕的设计。这里考虑使用分页。

  

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

当你面对你的命令在timout，因此，你需要增加你的<一的timout href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx">sql命令。你可以在你的命令像这样指定它

  //设置命令超时2分钟
scGetruntotals.CommandTimeout = 120;
 

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

解决方案

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

Bad Design. Consider using paging here.

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

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

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

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