SqlConnection State property mistaken behavior  
Author Message
João Ferreirinha





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

Hello,

I don’t know if this should be like this, but if so it is bringing some problems that I think could be common for everyone which works with remote database servers.

I have a regular client – server system which has several windows applications on desktops communicating with a data server which supports my shared storage and database.

The situation is that network is very poor and fails constantly. When I start my application I can accomplish connection but sometimes in the middle of the day the network is down. The problem is that after make the first connection this one stays pooled and later when I try to open it, everything appears to be fine, I check connection state and the result is open, but when I execute the query an exception is generated and the message is "sql server does not exist".

How can I make sure that I really have a connection with SQL Server before try to execute the statement

Below is a sample code of what I am trying explain.

1: Dim sqlCon As New SqlClient.SqlConnection
2: Dim sqlAd As New SqlClient.SqlDataAdapter
3:
4: Dim sConnectionString As String
5: Dim sCommand As String
6: Dim ds As New DataSet
7:
8: sConnectionString = "Persist Security Info=False;Integrated Security=False;database=MyDataBase;server=DataServer;User Id=MyUser;Pwd=MyPassword"
9: sCommand = "SELECT * FROM MyTable"
10:
11: sqlCon.ConnectionString = sConnectionString
12:
13: Try
14: sqlCon.Open()
15:
16: If sqlCon.State = ConnectionState.Open Then
17: sqlAd.SelectCommand = New SqlClient.SqlCommand(sCommand, sqlCon)
18: sqlAd.Fill(ds)
19: End If
20:
21: sqlCon.Close()
22: Catch ex As SqlClient.SqlException
23: MsgBox(ex.Message)
24: Catch ex As SystemException
25: MsgBox(ex.Message)
26: End Try
27:
28: Return ds


.NET Development36  
 
 
Luis D. Rojas





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

Hi,

The StateChange event is not raised unless you explicitly call Close or Dispose, so your property state will remain the same.


This doesn't help if the network cable is yanked out  or the  physicall connection fails.

just use the connection object to verify that: execute a command, if it crashes with a "the connection is dead" message, the connection is dead.. this is what try/catch is for.. even if its just to say to the user "sorry.. the database appears to be dead.

Best Regards



 
 
Joao Ferreirinha





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

Hi Diego,

Thank you for the answer.

After a couple of hours thinking about this issue it came to me the solution that you are suggesting, but I was surprised that Microsoft did not presented any property like IsConnected or SQLCanConnect which makes a question to the engine and this one answer to him (a light and easy question/answer).

I don’t like this solution because when I have about 100 users executing some standard statement like update or select (to do this verification) on database server before do some operation increases network traffic and slows application performance.

Is there any workaround better than this

Best regards


 
 
Luis D. Rojas





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

I dont think so, the first thing that come to my mind is maybe to use the ping.exe program and check if the connection is up.

Another way is to use typed datasets for doing some jobs and if there is no connection, so do the change in the dataset, and as soon as it gets back, you update your datasource with the DS.Update method

Best Regards



 
 
Matt Neerincx





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

If you think about this at a really high level, there is no good asnwer to checking state of connection prior to executing statement.

Primary reason is you can check the state of the connection and it is 100% good and then 1 millisecond later it can drop.

So in the end you will still need to have code to handle exceptions that occur when the connection goes bad, even if you check the connection prior to running the sql statement.

To test a connection you could write a simple function like so:

bool FConnectionIsGood(SqlConnection conn) {
using (SqlCommand cmd = conn.GetCommand()) {
cmd.CommandText = "select 1"; cmd.CommandType = CommandType.Text;
try { cmd.ExecuteScalar(); } catch (Exception) { return false; }
}
return true;
}

But what is the point of having this function because as soon as this function returns 1 millisecond later the connection can drop. Also every time you use a connection you do some extra wasteful work checking the connection and do this 1000s of times when you don't need to do it for the 1 time that it might save using a bad connection.

Sort of the same argument for using exception handling versus checking error codes over and over. It's more efficient to just execute the code without any checks and in the exceptional case let the exception handling code do it's work.



 
 
Matt Neerincx





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

Although there is a follow up idea you could do this work on a background thread so that the net effect was that you would more periodically check the connection state but in the end this will still generate much more network traffic and CPU impact on the back end server so it's not recommended.

 
 
Joao Ferreirinha





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

Thank you both for the answers.


Commenting Matt answer, I agree totally with your point of view, but when I post this thread I did not explained the true objective of this to turn it simpler.

The real scenario is that I am connecting to Database Engine via a Web Service, and sometimes I have this web service on a Web Server and database engine in a Data Server, so when I have connection problems I don’t know if the problem it is between the client and web server or web server and data server. At this time I am upgrading these components to have methods to track where problems are.


Just one more question. In near future we are going to implement this application in oracle, and my doubt is if the Statement “SELECT 1” works in both engines SQL Server and Oracle

Best Regards


 
 
Matt Neerincx





PostPosted: .NET Framework Data Access and Storage, SqlConnection State property mistaken behavior Top

Yes about the only decent solution is to write a common exception handler that all the webmethods call and have the exception handler "classify" failures as connectivity failures based on the error codes or error messages and report a common "Database server is down" error. The problem with this is most drivers including SQL driver don't report a consistent set of errors for connectivity errors, instead they react to lower level errors like network disconnects by reporting the underlying error transparently. This is good for geeky people who understand network layer but not so good for writing clean higher level code.

I recall from my days working with WebSphere and WebLogic and JBoss these app servers would have a special string you set to use as the "test the connection" string for each driver. So what I would do is follow this simple design and write your application to read the "test the connection" string from application configuration file so you can easily reconfigure it for various vendor's drivers. Off the top I think select 1 will work with Oracle but I'm not sure.