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