Checking if an SQL connection is still valid

Question

4.80/5 (2 votes)

See more:

I have an VB.NET application. This Application is permanently resident in memory and minimized to the tray area because we use it very often. The problem comes in when we VPN into a client's site. These VPN connections trash all other network connections to and from our machines.

Then every time my VB.NET application refreshes its database, errors start occurring, because the machine has lost connectivity to the SQL server, however, these are not at the connection level, but only when the reader attempts to read.

I am checking for connection.open, but because of the SQL connection pooling, I just get a previous connection that was valid before the network was cut. Is there anyway to check if an actual SQL connection is valid, prior to re-issuing a connection from the pool?

I have created a separate app to test a solution to this. (below)
If there is no connection on the first attempt, I get an error, but if the first connection is valid and then I physically disconnect the LAN cable, I still get an alledged successful connection. I need to be sure that this connection is actually valid, or throw an exception that I can trap and handle prior to attemting transactions on the SQL server. If not, I will have to turn the pooling off, which will slow things down a bit :-(

C#

Imports System.Data.SqlClient
Public Class Form1
Public Conn As New SqlConnection

Private Sub OpenConnection
     if conn.state <> 1 then 'actually here the problem ***
        conn.open
     endif
End sub

End Class

*** - actually the connection already broken by disconnecting lan cable, but it shows OPEN

Posted 29-Oct-13 2:10am

Kodeeswaran V Duraisamy

Updated 21-Aug-16 4:46am

v5

Add a Solution

4 solutions

Solution 1

I think I would just keep a variable that counts how many times the exception get hits after opening a connection. If it's the first time the exception has been hit, instead of erroring out it would try again. The second time it would assume the error was something else and show me the error message.

----------- Update ----------------

Here is roughly what I am talking about:

VB

Dim intErrorCount As Integer = 0
Try
    'Perform a read
Catch ex As Exception
    Select Case intErrorCount
        Case 0
            'Reopen the connection
        Case Else
            'Handle the error
            MsgBox("Error: " & ex.Message)
    End Select
    intErrorCount += 1
End Try

Why does this not work for you? What is happening when you try to access the DB and the connection isn't really open? It just errors out, right?

Posted 29-Oct-13 11:07am

Kschuler

Updated 30-Oct-13 3:52am

v2

Comments

Kodeeswaran V Duraisamy 30-Oct-13 0:43am

i've update the question - pls go through it.

Kschuler 30-Oct-13 9:53am

I updated my solution.

Solution 3

I use some trick to solve this by

VB

Private Sub OpenConnection
      Keep_Alive(conn)
End sub

Private Sub Keep_Alive(ByRef Conn As SqlConnection)
    On Error Resume Next
    Conn.Open()
End Sub

Posted 31-Oct-13 20:10pm

Kodeeswaran V Duraisamy

Solution 4

Change 192.168.1.125 to your IP

VB.NET

Imports System.Net
Imports System.Net.Sockets

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If My.Computer.Network.Ping("192.168.1.125") = True Then
            MsgBox("Ping echo")
        Else
            MsgBox("No ping echo")
        End If
    End Sub


    Private Function IsSQLServerAvailable(ByVal ipaddress As String, ByVal Port As String) As Boolean
        Try
            Dim tcpCli1 As New TcpClient
            tcpCli1.SendTimeout = 15
            tcpCli1.Connect(ipaddress, Port)
            tcpCli1.Close()
            tcpCli1 = Nothing
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If IsSQLServerAvailable("192.168.1.125", "1433") = True Then
            MsgBox("Tested OK")
        Else
            MsgBox("Tested Not OK")
        End If
    End Sub
End Class

Posted 21-Aug-16 4:46am

Azlan Ismun

Comments

Richard MacCutchan 21-Aug-16 10:58am

This was answered almost 3 years ago. Please do not post in old questions.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bluesathish · Accepted Answer · 2013-10-30T21:34:00

Solution 2

Hi,
For Broken link i hope the following condition will works,

VB

If conn.State = ConnectionState.Broken Then
 'Do your codes
End If

Regards,
BlueSathish

Posted 30-Oct-13 21:34pm

bluesathish

Comments

Kodeeswaran V Duraisamy 1-Nov-13 1:56am

no, its not working - conn.state still shows open but when i read something it gives error! that's the question here.