0

I know you can set the Connection Timeout parameter on the ConnectionString, however, it sets the minimum timeout time and not the maximum.

My application works connected to a remote database and it needs to gray out the screen and wait for the connection to come back if it had been lost. I already do this fine, but sometimes when the connection is lost it takes more than 30 seconds to it realize the connection is lost, even with a minimum Connetion Timeout parameter value.

I detect the connection is lost because I have a thread doing a polling on the database every 0.5 second, and I wait for it to throw the "A network-related or instance-specific error occurred while establishing a connection to SQL Server" exception.

I want to know if there is a nice way of setting a maximum timeout or other alternative to it detect faster that the connection is lost.

Edit: I didn't know that also existed the CommandTimeout. It doesn't work perfectly but works better. It have to be set on every new DataContext object but it's fine. I set to a value X and it seems to always take X + 5 seconds to throw a Timeout exception.

Improve this question
8
  • What do you mean for maximum connection timeout and what is the difference with the minimum connection timeout? Commented Dec 19, 2013 at 11:57
  • @Steve When I set it to 5, for example, it means it will not timeout before 5 seconds, that's what I mean by being minimum, and it's the way it works... it will throw the excpetion on an arbitrary time after 5 seconds. A maximum timeout would mean that it would throw the excpetion on the exact moment it surpassed 5 seconds of waiting. Commented Dec 19, 2013 at 12:01
  • hm can it maybe confuse things that your server polling interval is much smaller than your timeout? I think it should be the other way around. Do you open a new connection each time you poll? Or is the polling independent from the connection itself? Did you try to reproduce this locally without a remote server? Because the sql connection "randomly" terminating at "some time" after the timeout-interval does not seem right imho. Commented Dec 19, 2013 at 12:36
  • @DrCopyPaste Well, inside the polling thread it have a method that makes a query, so it doesn't matter the polling interval as it will be locked in this method until the exception is thrown or it succeds. Commented Dec 19, 2013 at 12:41
  • 2
    @DrCopyPaste, no, the connectiontimeout is the timeout for the connection Open() call. It doesn't apply to commands at all. The CommandTimeout applies to the Execute or ExecuteScalar calls. It doesn't apply to connections at all. Commented Dec 19, 2013 at 15:34

2 Answers 2

Reset to default
3

the ConnectionTimeout is the timeout for the connection Open() call. It doesn't apply to commands at all. The CommandTimeout applies to the Execute or ExecuteScalar calls. It doesn't apply to connections at all.

To apply the CommandTimeout to all commands you have to do it every time you create a Command object.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

The best way I found was to create another class to detect when the connection was lost.

This class would create 2 threads, one that calls a method to check if the database exists and then set a flag to true; and another that would check if the flag was true after a certain amount of time. If it was still false after the certain amount of time, it means that the database connection was lost and the first thread is locked waiting for the timeout. This way I don't need to wait for the timeout to happen to detect it.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.