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

15 posts, 1 answers
  1. Michael H
    Michael H avatar
    17 posts
    Member since:
    Feb 2010

    Posted 03 Jul 2012 Link to this post

    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? 
  2. Answer
    Ady
    Admin
    Ady avatar
    589 posts

    Posted 04 Jul 2012 Link to this post

    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.
  3. DevCraft banner
  4. Michael H
    Michael H avatar
    17 posts
    Member since:
    Feb 2010

    Posted 04 Jul 2012 Link to this post

    Thanks for the help Ady. The solution works for me.
  5. Gregory
    Gregory avatar
    9 posts
    Member since:
    May 2011

    Posted 23 Jul 2012 Link to this post

    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 ?!?
  6. Ady
    Admin
    Ady avatar
    589 posts

    Posted 26 Jul 2012 Link to this post

    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.
  7. Scott
    Scott avatar
    68 posts
    Member since:
    Nov 2011

    Posted 13 Aug 2012 Link to this post

    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?
  8. Gregory
    Gregory avatar
    9 posts
    Member since:
    May 2011

    Posted 13 Aug 2012 Link to this post

    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.
  9. Ady
    Admin
    Ady avatar
    589 posts

    Posted 16 Aug 2012 Link to this post

    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!
  10. Michael H
    Michael H avatar
    17 posts
    Member since:
    Feb 2010

    Posted 16 Aug 2012 Link to this post

    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. 
  11. Ady
    Admin
    Ady avatar
    589 posts

    Posted 20 Aug 2012 Link to this post

    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!
  12. Gregory
    Gregory avatar
    9 posts
    Member since:
    May 2011

    Posted 20 Aug 2012 Link to this post

    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.
  13. Gary
    Gary avatar
    18 posts
    Member since:
    Jun 2012

    Posted 21 Aug 2012 Link to this post

    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
  14. Ady
    Admin
    Ady avatar
    589 posts

    Posted 23 Aug 2012 Link to this post

    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!
  15. Mark
    Mark avatar
    7 posts
    Member since:
    Dec 2012

    Posted 23 Jul 2014 in reply to Ady Link to this post

    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.   
  16. Ady
    Admin
    Ady avatar
    589 posts

    Posted 25 Jul 2014 Link to this post

    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.
     
Back to Top
DevCraft banner