0

In my method, if I find that connection (System.Data.SqlClient.SqlConnection) which I received has

  • State = Closed
    or
  • State = Broken

which of the following approaches is correct?

  1. Call Open() to reconnect and continue with execution of subsequent statements. (Of course, I can also give the Open() pre-configured number of retries.)

  2. Throw such a connection away as unrecoverable and get brand new one.

(If these approaches are equal, I would prefer the first as easier to implement.)

miroxlav
  • 11,796
  • 5
  • 58
  • 99
  • possible duplicate of [Opening SqlConnection that has been closed already](http://stackoverflow.com/questions/13992504/opening-sqlconnection-that-has-been-closed-already) – Banana Jan 27 '15 at 15:45
  • and [this](http://stackoverflow.com/questions/4439409/open-close-sqlconnection-or-keep-open). and [This](http://stackoverflow.com/questions/596339/re-opening-sqlconnection-instance) – Banana Jan 27 '15 at 15:46
  • @Banana - thanks for help with search. I did not find them. I'll leave this question posted as a benefit for others as a redirection to ones you posted. – miroxlav Jan 27 '15 at 15:55

1 Answers1

0

(ConnectionState.Broken) The connection to the data source is broken. This can occur only after the connection has been opened. A connection in this state may be closed and then re-opened. (This value is reserved for future versions of the product.)

i.e. you can close and re-open the connection with Open(). If State is closed than you should throw an exception

Phate01
  • 2,499
  • 2
  • 30
  • 55
  • I don't understand your last sentence why I should throw an exception. From paragraph you quoted it looks like `Broken` is not always supported. So I think in such cases I'll get `Closed` also in case of `Broken` connection. – miroxlav Jan 27 '15 at 15:57
  • Then you can simply treat the broken state as closed. Anyway usually if you get a closed connection as a parameter of a method you throw an exception more correctly than re open the connection from inside the method. It's not the job of the method to re-connect – Phate01 Jan 27 '15 at 16:16
  • I think of wrapper method which inside calls standard `SqlClient.SqlCommand()` along with auto-reconnect added as feature. I don't see added value in throwing exception (and leaving all started work) if possible connection repair is at hand. It this way of thinking wrong? Anyway, thanks for the answer and welcome to the [SO]! – miroxlav Jan 27 '15 at 16:55
  • Thanks for welcoming! It depends on what you are gonna do, but generally methods perform one single action. Maybe you can try to reconnect and throw an exception in case of failure – Phate01 Jan 28 '15 at 08:34
  • Yes, this is what I do. Few retries and then an exception. – miroxlav Jan 28 '15 at 09:05