how to set command timeout for sqlhelper class?

Posted by Prabu_Spark under ASP.NET on 12/5/2012 | Points: 10 | Views : 22178 | Status : [Member] | Replies : 8
Hi sir,
how to set command timeout for sqlhelper class. I am getting error like
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
in
SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "UpdateFormData", Params);



Web.config:

<connectionStrings>
<clear/>
<add name="dmw_con" providerName="System.Data.SqlClient" connectionString="Data Source=LBIT00046;Initial Catalog=DMW_Reports;User ID=sa;password=welcome;Connect Timeout=200; pooling=true; Max Pool Size=600;Timeout=360"/>
<add name="ret_con" providerName="System.Data.SqlClient" connectionString="Data Source=lbit00046;Initial Catalog=Retailer_FormDB;User ID=sa;password=welcome;Connect Timeout=200; pooling=true; Max Pool Size=600;Timeout=360"/>
</connectionStrings>

With regards,
J.Prabu.
[Email:prbspark@gmail.com]



Responses

Posted by: Ranjeet_8 on: 12/5/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
You have to increase the timeout in the MyCommand object:
MyCommand.CommandTimeout = 60; // 1 minute, put 0 for infinite

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/5/2012 [Member] Bronze | Points: 25

Up
0
Down
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 500;


Add the command time out

Thanks
Pavan Kumar
Mark Answer if this fits the need

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Alexhitc8 on: 12/6/2012 [Member] Starter | Points: 25

Up
0
Down
Both above two answers will work. Addition to them, I just want to mention about the timeout number.
Timeout value is counted in second. Therefore 60 means 1 minute. Default timeout value is 30 seconds. As Ranjeet_8 replied use 0 for infinite.

Regards,
Alex.

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 12/6/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
you can set
MyCommand.CommandTimeout = 3600;
If a command finishes sooner, its done. If it takes longer than the time you allocate, it throws an exception.

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 12/6/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
Hi @Prabu_Spark
plz Mark As Answer if post will helps you.

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prabu_Spark on: 12/6/2012 [Member] Starter | Points: 25

Up
0
Down
Hello Ranjeet,
I am using Microsoft.ApplicationBlocks.dll file, i cant able to edit that dll file.
In my code , i used only two lines to call that function in that dll.


SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "UpdateFormData", Params);




With regards,
J.Prabu.
[Email:prbspark@gmail.com]

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Alexhitc8 on: 12/6/2012 [Member] Starter | Points: 25

Up
0
Down
Here is a useful link for this issue. According to it you should move to Database instead of SqlHelper.

http://stackoverflow.com/questions/8882369/timeout-issue-when-using-sql-helpermicrosoft-applicationblocks-data


Regards,
Alex.

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sandhyab on: 12/11/2012 [Member] Starter | Points: 25

Up
0
Down
use
sqlcommandobj.CommandTimeout = 6000; //Increase the time
I resolved this error by Using CommandTimeout to my sqlcommand.
SqlCommand cmd = new SqlCommand("[DBO].[USP_STXTRX_INSERT_ROW_WISE]", con);

cmd.CommandTimeout = 6000;

Thanks & Regards

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response