Hi, I have am interesting problem that I cannot think of a solution for.
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 :-(
Imports System.Data.SqlClient
Public Class Form1
Public Conn As New SqlConnection
Public Sub ConnDB()
Try
Conn.ConnectionString = "server = " & "wyvern" & "\" & ""
Conn.ConnectionString = Conn.ConnectionString & "; Database=" & "Forge"
Conn.ConnectionString = Conn.ConnectionString & ";integrated security=true"
Conn.Open() 'This continues to work even after I disconnect the LAN cable
Catch ex As Exception
MessageBox.Show(ex.Message, "Connection to SQL Server Failed!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Conn.Close()
Finally
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
ConnDB()
If Conn.State = 1 Then
MsgBox("You have successfully connected to SQL Server!", 0, "Test Successful!")
End If
If Conn.State = ConnectionState.Open Then
Conn.Close()
End If
End Sub
End Class