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

[Michael H]

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? 

[Ady]

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

[Michael H]

Thanks for the help Ady. The solution works for me.

[Gregory]

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 ?!?

[Ady]

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

[Scott]

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?

[Gregory]

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.

[Ady]

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

[Michael H]

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. 

[Ady]

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

[Gregory]

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.

[Gary]

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

[Ady]

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

[Mark]

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.   

[Ady]

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