Check if SQL Connection is Open or Closed

Question

How do you check if it is open or closed I was using

 if (SQLOperator.SQLCONNECTION.State.Equals("Open"))

however, even the State is 'Open' it fails on this check.

This question and all the answers don't really make any sense: `SqlConnection.State` just tells you whether the connection has been opened in the past, it does not tell you if the server is actually available. And it isn't necessary anyway, you just need to create a new connection, and dispose afterwards with `using` — Charlieface, Oct 01 '21 at 12:12

score 211 · Answer 1 · edited Jan 05 '19 at 08:51

211

You should be using SqlConnection.State

e.g,

using System.Data;

if (myConnection != null && myConnection.State == ConnectionState.Closed)
{
   // do something
   // ...
}

edited Jan 05 '19 at 08:51

PoLáKoSz

355
1
6
7

answered Aug 04 '11 at 15:15

2

+1 exactly - use the `SqlConnectionState` enum as an enum and not turn it into a string..... – marc_s Aug 04 '11 at 15:19
6

Should've added `using System.Data;` in the answer, IMHO. I forgot this namespace (had `using System.Data.SqlClient`) and couldn't figure out how to get `ConnectionState` as a keyword until I added it. Hope this helps someone. – vapcguy Sep 09 '15 at 13:42
Does this work if the *server* (or something between the local machine and the server) closed the connection? – jpmc26 Jul 21 '16 at 22:35
Wouldn't it be better to say `if (myConnection == null || myConnection.State == ConnectionState.Closed) { //Connection is closed } else { //Connection is open in some way }`? This way if the connection is null it's also "closed". – Arvo Bowen Jan 04 '17 at 02:16

score 57 · Answer 2 · answered Mar 28 '14 at 19:10

57

Here is what I'm using:

if (mySQLConnection.State != ConnectionState.Open)
{
    mySQLConnection.Close();
    mySQLConnection.Open();
}

The reason I'm not simply using:

if (mySQLConnection.State == ConnectionState.Closed)
{
    mySQLConnection.Open();
}

Is because the ConnectionState can also be:

Broken, Connnecting, Executing, Fetching

In addition to

Open, Closed

Additionally Microsoft states that Closing, and then Re-opening the connection "will refresh the value of State." See here http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.state(v=vs.110).aspx

answered Mar 28 '14 at 19:10

therealjumbo

1,079
1
10
14

You should test if ``mySQLConnection.State != ConnectionState.Open && mySQLConnection.State != ConnectionState.Connecting`` to avoid resets with slow connections, shouldn't you? – caligari Apr 07 '14 at 09:19
@caligari While true, no guarantee exists for DbConnection, so if programming to the abstract DbConnection, be careful. – John Zabroski Apr 18 '14 at 16:30
1

I personally think this is the kind of problem you solve by avoiding. I can maybe see the use case for this code snippet in a stateful application layer, but never on the Web? – John Zabroski Apr 18 '14 at 16:32
John, that is exactly the use case for this code. Code running a server application that could be serving pages, connecting to another REST server whatever. I don't see any case where I would connect to a server database in the client side code in a web application. – therealjumbo Apr 18 '14 at 21:20
There is one giant caveats with that: 1) with Local Storage now becoming a thing, sooner or later (already?) web apps that use local storage are going to be using a database in that storage. If not now, they will shortly. The other thing here is, my code probably isn't properly generalized to be used in a large application. My main focus is embedded programming, so I'm still learning on the server side. – therealjumbo Apr 18 '14 at 21:23
I am trying to use the above code but it keeps saying connection is open. What I would like to do is check if connection is open for my application only... – Si8 Jun 20 '14 at 16:07
if (connection != null && connection.State == System.Data.ConnectionState.Closed) connection.Open(); – Oct 25 '21 at 18:15

score 30 · Answer 3 · answered Dec 18 '15 at 14:19

30

The .NET documentation says: State Property: A bitwise combination of the ConnectionState values

So I think you should check

!myConnection.State.HasFlag(ConnectionState.Open)

instead of

myConnection.State != ConnectionState.Open

because State can have multiple flags.

answered Dec 18 '15 at 14:19

csname1910

1,195
11
14

I wonder why this is enum with flags. Since the value of Close item of this enum is zero then State.HasFlag(ConnectionState.Close) will return true for any value. For me it means that i should check as "!= Close" – Ivan Feb 18 '16 at 13:29
3

Open a question http://stackoverflow.com/questions/35483542/net-state-of-db-connection – Ivan Feb 18 '16 at 14:42
4

NOTE: I feel it's necessary to mention that Ivan's link mentions that you SHOULD NOT use this as flags. See this specific answer: https://stackoverflow.com/a/35484965/2499090 – Brent Rittenhouse Sep 13 '17 at 19:33

score 13 · Answer 4 · edited Nov 13 '12 at 20:54

13

Check if a MySQL connection is open

ConnectionState state = connection.State;
if (state == ConnectionState.Open)
{
    return true;
}
else
{
    connection.Open();
    return true;
}

edited Nov 13 '12 at 20:54

j0k

22,600
28
79
90

answered Nov 13 '12 at 20:35

Louie Bacaj

1,417
13
18

1

What's the purpose of returning always true? At this point, make the method void. Only, check if the connection is not open and if so open it. And... why writing 2 times `return true;` ? put it in the end of the method, outside the `if`/`else`! – Massimiliano Kraus Jan 18 '17 at 11:23
In case of network problems these would give a wrong answer. you cannot be sure that open will indeed open. – user613326 Feb 14 '18 at 11:16
@user613326 actually, it wouldn't. There is no error handling in the example code, so any problems while connecting will simply throw and leave you to handle the exception. The returned value is therefore correct. – Tom Lint Aug 06 '20 at 09:38

score 8 · Answer 5 · edited Nov 02 '12 at 07:45

8

you can also use this

if (SQLCON.State == ConnectionState.Closed)
{
     SQLCON.Open();
}

edited Nov 02 '12 at 07:45

Alessandro Minoccheri

35,521
22
122
171

answered Nov 02 '12 at 07:25

bitu pascal

81
1
1

1

`using System.Data;` for anyone that didn't know, or didn't know why it wasn't working – Paul C Aug 08 '13 at 09:24

GBGOLC · Answer 6 · 2017-06-16T09:05:15.207

7

This code is a little more defensive, before opening a connection, check state. If connection state is Broken then we should try to close it. Broken means that the connection was previously opened and not functioning correctly. The second condition determines that connection state must be closed before attempting to open it again so the code can be called repeatedly.

// Defensive database opening logic.

if (_databaseConnection.State == ConnectionState.Broken) {
    _databaseConnection.Close();
}

if (_databaseConnection.State == ConnectionState.Closed) {
    _databaseConnection.Open();
}

edited Jun 16 '17 at 09:05

answered Jun 08 '17 at 17:50

GBGOLC

520
8
7

Just being a little more defensive. – GBGOLC Jun 08 '17 at 17:51
1

**From review queue:** May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post. See also [Explaining entirely code-based answers](https://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers). – help-info.de Jun 08 '17 at 18:22

score 4 · Answer 7 · answered Aug 27 '15 at 05:19

4

To check the database connection state you can just simple do the following

if(con.State == ConnectionState.Open){}

answered Aug 27 '15 at 05:19

Mani

2,391
5
37
81

score 4 · Answer 8 · answered Aug 06 '18 at 22:35

To check OleDbConnection State use this:

if (oconn.State == ConnectionState.Open)
{
    oconn.Close();
}

State return the ConnectionState

public override ConnectionState State { get; }

Here are the other ConnectionState enum

public enum ConnectionState
    {
        //
        // Summary:
        //     The connection is closed.
        Closed = 0,
        //
        // Summary:
        //     The connection is open.
        Open = 1,
        //
        // Summary:
        //     The connection object is connecting to the data source. (This value is reserved
        //     for future versions of the product.)
        Connecting = 2,
        //
        // Summary:
        //     The connection object is executing a command. (This value is reserved for future
        //     versions of the product.)
        Executing = 4,
        //
        // Summary:
        //     The connection object is retrieving data. (This value is reserved for future
        //     versions of the product.)
        Fetching = 8,
        //
        // Summary:
        //     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.)
        Broken = 16
    }

score -5 · Answer 9 · edited May 20 '12 at 09:03

-5

I use the following manner sqlconnection.state

if(conexion.state != connectionState.open())
   conexion.open();

edited May 20 '12 at 09:03

Pascal MARTIN

395,085
80
655
663

answered May 19 '12 at 13:28

william

1

7

`connectionState.open()` doesn't exist; did you mean `ConnectionState.Open`? – Peter Ritchie May 19 '12 at 17:51

Check if SQL Connection is Open or Closed

9 Answers9

Linked