Check if SQL Connection is Open or Closed


Answers

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

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.




Zero means that nothing is selected. In case of Open 00001, In case Of Connecting 00010, In case of Open AND Connection 00011 ....

0 0 0 0 0 
B F E C O 

Open = 1,
Connecting = 2,
Executing = 4,
Fetching = 8,
Broken = 16,



.NET State of DB Connection

I believe in the original .NET 1.1 implementation they planed on making it a flags based enum, However I don't think that is how the current implementation is used if you look at the note in the remarks section

Note:
The values in this enumeration are not designed to be used as a set of flags.

You don't need to check for flags it is safe to do a direct == test. They did not remove the Flags attribute because it would break binary compatibility with .NET 1.1 which they won't do.




C#/SQL Error “Object not set to an instance of an object”

So the answer to the question (because I couldn't post more details and I really wanted to know of things to try): is that it's a simple Permissions issue with the account that is running the process not being able to insert records to the table. The comment that helped me get to that conclusion came from @cdonner (Thank you, again!) in wanting to see a stack trace. I realized that I could return a StackTrace in the console log instead of just the InnerException message and it (the SQL Error) was finally revealed.




I'd recommend this pattern: Get the SQL task request, Open the connection, perform the task, Close the connection, respond to the results.

Replace m_dbConnection with m_dbConnectionString.

Your app will be more reliable and any time you need to restart SQL, you won't have a ton of "are you sure -- there are 20 people connected" messages.







Tags