This is a migrated thread and some comments may be shown as answers.

How to set CommandTimeout for Stored Procedures called as a Domain Method

14 Answers 532 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Michael H
Top achievements
Rank 2
Michael H asked on 03 Jul 2012, 08:53 PM
We have quite a few stored procedures that we call in our application as Domain Methods. Some of them run for longer than the default 30 second command timeout in which case an exception is raised. How can we increase this timeout value? 

14 Answers, 1 is accepted

Sort by
0
Accepted
Ady
Telerik team
answered on 04 Jul 2012, 12:51 PM
Hi Michael,

 Using the generated method on the context to execute the stored procedure does not allow modifying the 'CommandTimeout' on the command. Instead you can use the OACommand to execute the stored procedure. Here is how you code could look like below
using (OAConnection connection = context.Connection)
 {
      OACommand command = connection.CreateCommand();
      command.CommandTimeout = 60;
      command.CommandType = System.Data.CommandType.StoredProcedure;
      //set the command text and parameters   
      command.ExecuteNonQuery();
}
Please be sure to dispose the OAConnection instance as shown above. Also if you are making any changes in the stored procedures (insert,updates etc) you would need to call context.SaveChanges() to commit the transaction that is started by the context (during the call to context.Connection)

Hope this helps.Do get back in case you need further assistance.

Regards,
Ady
the Telerik team
OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
0
Michael H
Top achievements
Rank 2
answered on 04 Jul 2012, 07:31 PM
Thanks for the help Ady. The solution works for me.
0
Gregory
Top achievements
Rank 1
answered on 23 Jul 2012, 09:41 PM
Am I missing something?  what happens when I have to regenerate the context again?  I'll have to go through and update all the storedprocedure places to add the command.CommandTimeout ?!?
0
Ady
Telerik team
answered on 26 Jul 2012, 04:11 PM
Hi Gregory,

 The generated code uses the  OpenAccessContextBase.ExecuteQuery method to execute the stored procedures. In case you need control over the CommandTimeout , you should create a partial class of the context class and add a new method there which contains the code I have specified in the earlier post.

Hope this helps. Do get back in case you need further assistance.

Greetings,
Ady
the Telerik team
OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
0
Scott
Top achievements
Rank 2
answered on 13 Aug 2012, 04:04 PM
So basically... if we need to use a longer command timeout, we must use our own implementation of what the Domain Methods generates for us??

Is there a future intention to make the timeout configurable so we can use the generated code? Seems like such a small thing, that we basically can copy/paste the generated domain method, but have to use our own implementation of ExecuteNonQuery? Or am i missing something?
0
Gregory
Top achievements
Rank 1
answered on 13 Aug 2012, 04:40 PM
This would also apply for the executereader, but I couldn't figure out how to overwrite the ExecuteReader.
I'm really surprised other people haven't noticed this before as well? 

I would also like to see a configurable parameter for setting commandtimeout at a global level and then on the individual procedure / domain method being able to set a custom one.
0
Ady
Telerik team
answered on 16 Aug 2012, 10:38 AM
Hello Scott and Gregory,

 At the moment there is no way to specify the command timeout value so that the generated code takes it into account. You would need to explicitly create your own method as previously pointed out.
Thank you for your input; we will consider allowing to specify the command timeout while generating the method. I will update this thread on the decision we take and the corresponding progress.

Regards,
Ady
the Telerik team
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Michael H
Top achievements
Rank 2
answered on 16 Aug 2012, 03:20 PM
Hi Ady,

Thanks for looking into this option. Although the initial suggested method works for us, we really enjoy the speed of generating and implementing domain methods. As such, we really hope to see the command timeout option for domain methods in an upcoming build. 
0
Ady
Telerik team
answered on 20 Aug 2012, 02:47 PM
Hi Michael,

 When you execute a stored procedure using the generated method, the context uses a connection from the OpenAccess maintained connection pool. This connection has a certain timeout value (by default - 120 seconds) after which the connection is forcibly closed. Providing just the 'CommandTimeout' on the command might not work if the connection times out before. (The connection will be forcible closed after 120 seconds even if the CommandTimeout is larger)
We will review various use cases including customer feedback and implement the appropriate feature.
Once again, thank you for your feedback.

Kind regards,
Ady
the Telerik team
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Gregory
Top achievements
Rank 1
answered on 20 Aug 2012, 03:09 PM
I'd be good if I could do a public new ExecuteReader like I've done for ExecuteQuery and ExecuteNonQuery. Once the issue with setting the ConnectionTimeout is resolved (see my support ticket) then I won't have to manually update the generated Context code everytime someone makes a change to the diagram.
0
Gary
Top achievements
Rank 1
answered on 21 Aug 2012, 07:19 PM
I would also like to be able to set the command timeout, some reports just take longer than 30 seconds to create.  I want to be able to create domain methods and set the command timeout.

Thanks,
Gary
0
Ady
Telerik team
answered on 23 Aug 2012, 10:41 AM
Hello Gary,

 Thank you for your input. I will update this thread when we have further information regarding this feature.

Greetings,
Ady
the Telerik team
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Mark
Top achievements
Rank 1
answered on 23 Jul 2014, 04:16 PM
There is an article which details setting the CommandTimeout on the model level through the designer:  http://docs.telerik.com/data-access/developers-guide/crud-operations/developer-guide-crud-command-timeout  Does this not apply to stored procedures?  We have a number of stored procedures hooked up through auto-generated domain methods.  Some of these stored procedures exceed the default 30 second timeout, but setting the CommandTimeout on a global level doesn't seem to have any impact - sprocs taking long than 30 seconds still timeout.   
0
Ady
Telerik team
answered on 25 Jul 2014, 09:39 AM
Hello Mark,

 Setting the 'CommandTimeout' value on the 'BackendConfiguration.Runtime' is obeyed for the methods on the context that execute the stored procedure. So it should work .
Can you provide me with the call stack? You could also execute the stored procedure by explicitly creating the command object as demonstrated in this example. You can then explicitly set the 'CommandTimeout'

Do let me know if that helps.

Regards,
Ady
Telerik
 
OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
 
Tags
Development (API, general questions)
Asked by
Michael H
Top achievements
Rank 2
Answers by
Ady
Telerik team
Michael H
Top achievements
Rank 2
Gregory
Top achievements
Rank 1
Scott
Top achievements
Rank 2
Gary
Top achievements
Rank 1
Mark
Top achievements
Rank 1
Share this question
or