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

User · Answer

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

User · Answer

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

User · Answer

Setting command timeout to 2 minutes    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

User · Answer

Add timeout of your SqlCommand  Please note time is in second      Setting command timeout to 1 second scGetruntotals CommandTimeout   1

User · Answer

Since it takes 2 mins to respond  you can increase the timeout to 3 mins by adding the below code  scGetruntotals CommandTimeout   180    Note   the parameter value is in seconds

[c#] Increasing the Command Timeout for SQL command

Examples related to c#

Examples related to sql

Examples related to ado.net

Examples related to connection-timeout