Centralized Connection String

[radnam]

Hello,
I am developing a software which will connect to a SQL SERVER situated at Head Quarters over a VPN connection.But for times when VPN is disconnected I want my applications at the branches to connect to a local Sql Server installed at a local server.

Now all the applications at each of the local machines have their connectionstring stored in their App.config file.

When the VPN is disconnected we have to change each of the connection strings on all the applications in each machine. Is there a way to do it automatically switch the connection string depending on whether the Server is reachable or not, and to store the connectionstring at centralised location. Someone told me that .Net remoting might due the trick can someone share more light on it and is there a simpler solution.

And How can i constantly check whether the Server at the H.O is reachable or not without using too much resource ?

I am using VB 2008 and SQL SERVER 2008.

Please Help

[radnam]

Please help me ..........

[crapulency]

Hi radnam. Looks like you need two sqlconnection objects, one local, one for vpn, and you need to be able to select one or the other based on vpn availablity. Also, you probably want to avoid waiting forever for db connection timetouts when the vpn db is unreachable. So, consider a couple of things:

1)Pinging the remote machine first before trying to open a db connection. WIthout knowing about your set up, its hard to know if this could work but I think it help a lot with time and resources

2)Using a background worker thread to do your checking.

Here is a little pinger class that does both.

Public Class Pinger
    Public Address As String
    Public Interval As Integer
    Public PingTimeOut As Integer
    Private _connected As Boolean = False
    Private WithEvents _worker As System.ComponentModel.BackgroundWorker

    Public Event ConnectionChanged(ByVal Connected As Boolean)

    Public ReadOnly Property Connected As Boolean
        Get
            Return _connected
        End Get
    End Property

    Public Sub New(ByVal Address As String, ByVal Interval As Integer, ByVal PingTimeout As Integer)
        Me.Address = Address
        Me.Interval = Interval
        Me.PingTimeOut = PingTimeout
        _worker = New System.ComponentModel.BackgroundWorker
        _worker.WorkerReportsProgress = True
        _worker.RunWorkerAsync()
    End Sub

    Public Function Ping() As Boolean
        Dim result As Boolean = False
        Try
            result = My.Computer.Network.Ping(Address, PingTimeOut)
        Catch pEx As Net.NetworkInformation.PingException
        Catch Ex As Exception
        End Try
        Return result
    End Function

    Private Sub _worker_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles _worker.DoWork
        _worker.ReportProgress(100, Ping)
        Threading.Thread.Sleep(Interval)
    End Sub

    Private Sub _worker_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles _worker.ProgressChanged
        Dim result As Boolean = Convert.ToBoolean(e.UserState)
        If result <> _connected Then
            _connected = result
            RaiseEvent ConnectionChanged(_connected)
        End If
    End Sub

    Private Sub _worker_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles _worker.RunWorkerCompleted
        _worker.RunWorkerAsync()
    End Sub
End Class

Could be used something like this:

Public Class Form1
    Dim WithEvents _pinger As New Pinger("192.168.2.1", 10000, 1000)

    Private _conVPN As New SqlClient.SqlConnection("VPNConnectionString")
    Private _conLocal As New SqlClient.SqlConnection("LocalConnectionString")
    Public ActiveConnection As SqlClient.SqlConnection

    Private Sub _pinger_ConnectionChanged(ByVal Connected As Boolean) Handles _pinger.ConnectionChanged
        If Connected = True Then
            ActiveConnection = _conVPN
        Else
            ActiveConnection = _conLocal
        End If
    End Sub
End Class

WHen your app starts, create the pinger and it will constantly check in the background to see if the VPN becomes available. Whenever it does, you can switch your connection.
Obviously this needs tweeking, comments and error handling etc. Not a complete solution but hope it helps with ideas