Public Shared Function GetDbConnection(ByVal DatabaseType As String) As SqlClient.SqlConnection ' Try Dim blnConnect As Boolean = False Dim con As SqlClient.SqlConnection = Nothing ''CONNECTION_STR_CONFIGURE = ConfigurationSettings.AppSettings("CONFIGURE").ToString ' check if the dbConnection is still active ' if not active, set the dbConnection to Nothing and call CreateDbConnection() again While Not blnConnect Try Select Case DatabaseType Case "SEA" If IsNothing(con_SEA) Then con_SEA = New SqlClient.SqlConnection(DatabaseConnection.CONNECTION_STR_SEA) End If con = con_SEA Case "MASTER" If IsNothing(con_MASTER) Then con_MASTER = New SqlClient.SqlConnection(DatabaseConnection.CONNECTION_STR_MASTER) End If con = con_MASTER Case "SECURITY" If IsNothing(con_SECURITY) Then con_SECURITY = New SqlClient.SqlConnection(DatabaseConnection.CONNECTION_STR_SECURITY) End If con = con_SECURITY Case "ACCOUNT" If IsNothing(con_ACCOUNT) Then con_ACCOUNT = New SqlClient.SqlConnection(DatabaseConnection.CONNECTION_STR_ACCOUNT) End If con = con_ACCOUNT Case "ACCOUNT_TAX" If IsNothing(con_ACCOUNT_TAX) Then con_ACCOUNT_TAX = New SqlClient.SqlConnection(DatabaseConnection.CONNECTION_STR_ACCOUNT_TAX) End If con = con_ACCOUNT_TAX Case "ACCOUNT_NONTAX" If IsNothing(con_ACCOUNT_NONTAX) Then con_ACCOUNT_NONTAX = New SqlClient.SqlConnection(DatabaseConnection.CONNECTION_STR_ACCOUNT_NONTAX) End If con = con_ACCOUNT_NONTAX Case "ACCOUNT_ALL" If IsNothing(con_ACCOUNT_ALL) Then con_ACCOUNT_ALL = New SqlClient.SqlConnection(DatabaseConnection.CONNECTION_STR_ACCOUNT_ALL) End If con = con_ACCOUNT_ALL End Select Dim retryCount As Integer Dim retryLimit As Integer = 1000 If Not con.State = ConnectionState.Connecting Then con.Close() retryCount = 0 Do While True Try con.Open() Exit Do Catch ex As SqlException retryCount += 1 If retryCount >= retryLimit Then Throw End If ' Thread.CurrentThread.Sleep(1) End Try Loop ElseIf con.State = ConnectionState.Broken Then con.Close() retryCount = 0 Do While True Try con.Open() Exit Do Catch ex As SqlException retryCount += 1 If retryCount >= retryLimit Then Throw End If ' Thread.CurrentThread.Sleep(1) End Try Loop ElseIf con.State = ConnectionState.Closed Then retryCount = 0 Do While True Try con.Open() Exit Do Catch ex As SqlException retryCount += 1 If retryCount >= retryLimit Then Throw End If ' Thread.CurrentThread.Sleep(1) End Try Loop End If blnConnect = True Catch err As Exception MessageBox.Show(err.Message, "DTSClass - GetDbConnection -" & DatabaseType, MessageBoxButtons.OK, MessageBoxIcon.Error) Dim Result As DialogResult Result = MessageBox.Show("Connection Error Occur!Retry the connection?", "Comfirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If Windows.Forms.DialogResult.Yes Then blnConnect = False Else blnConnect = True End If End Try End While