How to increase the query time?

Is there any possibility of increasing the query execution time out time in the program using C#.NET, Windows Forms?

In my program I am executing a query in a loop. On the first two iterations the query executes in 10 sec each time. On the third iteration it takes around 40-50 sec for the query to complete and now I am not able to extract data from that query, because a time out exception is raised and the execution flow goes to the catch block where the corresponding error message is displayed.

Is there any p开发者_JAVA百科ossibility of executing queries which take more than 30 sec using C#.NET in Windows application?

Below is my code:

try
{
    tab = "dbo.basic_sp_mst";
    error1 = "" + site + "." + orgname + "";
    ls = "" + site + "." + site + "." + tab + "";
    SqlDataAdapter da2 = new SqlDataAdapter("select * from " + ls + " where (last_updated_date between(getdate()-3) and (getdate()-1)) order by last_updated_date,bsp_item_cd", con);
    DataSet ds2 = new DataSet();
    da2.Fill(dt5);
    dt6 = dt5;

    SqlDataAdapter daa = new SqlDataAdapter("select a. bsp_item_cd ,a.bsp_mrp,a.last_updated_date,b.bsp_item_cd ,b.bsp_mrp,b.last_updated_date,c.name from basic_sp_mst a," + ls + " b,sys.servers c where  a.bsp_item_cd =  b.bsp_item_cd and a.bsp_mrp<> b.bsp_mrp and a.bsp_org_cd=" + s + " and c.name<>'001' and c.name='" + site + "'", con);......................(this is the main query) if this query is taking more than 30sec it is going to catch block.......

    DataSet dss = new DataSet();
    daa.Fill(dt7);
    dt8 = dt7;
}
catch (Exception ex)
{
    msg2 = " query time out error at store id:" + error1 + "";
    dataGridView8.Rows.Add(msg2);
}

Just increase CommandTimeout property of the SqlDataAdapter.

// Set timeout to 2 minutes
daa.SelectCommand.CommandTimeout=120;

This command goes after the new SqlDataAdapter definition and before the call to Fill.

Also, check if the table have indexes on the columns on your Where section of the Select command, to try to reduce the query time.

You also can use the MS SQL Server Profiler to receive recommendation on indexes that could improve the performance.