Increasing the Command Timeout for SQL command

Question

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

Answer 1

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.

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

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; 

Answer 2

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

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