I know this could sound a little confusing, but there's a functional request from our customer to be done like this. So here we go.

We are using the adapter aproach. So every time we need to get a new instance of an DataAccessAdapter (adapter) we use a shared method that returns the new instance of the adapter reading the connection string from a configuration file.

The problem is that the password stored in the connection string could be changed by a process not managed by the application. The process then stores the new connection string somewhere and by consuming a web-service somehow lets me check the new connection string (this process is secured and we are not allowed to change anything from it)

According to this behaviour we realized that the best way to avoid this would be inheriting the DataAccessAdapter class and override the OpenConnection method in the inherited class. This way, if the connection to be open fails, we get the new connection string from the web-service and replace the connection string with the new one, store it in my new connection string in the configuration file and retry the connection. If this time fails, we don´t try again.

This is the custom adapter class:

    Public Class Adapter
        Inherits DataAccessAdapter

        Public Sub New(ByVal connectionString As String)
            MyBase.New(connectionString)
        End Sub

        Public Overrides Sub OpenConnection()
            Dim retry As Boolean = False
            Try
                MyBase.OpenConnection() '' (1)
            Catch ex As Exception
                retry = True  '' (2)
            End Try
            If retry Then
                Try
                    ' Change the conection string
                    MyBase.ConnectionString = GetNewConnectionString.Get()  '' (3)

                    ' Change the conection string in the configuration file

                    ' Retry the OpenConnection method
                    MyBase.OpenConnection()  '' (4)
                Catch innerEx As Exception
                    Throw innerEx  '' (5)
                End Try
            End If
        End Sub

    End Class

Theoretically this should work fine, but when we try to reopen the connection we get the same error we've got the first time we had a connection error. What I mean is: In (1) we are sure we have a wrong connection string. So in (2) we get the Oracle's wrong user name/password exception. So in (3) we set the correct connection. Before (4) is executed, we check the connection string and it's correct, but when we execute (4) we get the same error in (5) as we had in (2)...

Ideas?

T.I.A.

We've found out that if we change the connection string from the ActiveConnection of the adapter (6) everything works fine. But we don't like this aproach.

            If retry Then
                Try
                    ' Change the conection string
                    MyBase.ConnectionString = GetNewConnectionString.Get() '' (3)

                    ' Change the conection string in the configuration file

                    ' Retry the OpenConnection method
                    Me.GetActiveConnection().ConnectionString = Me.ConnectionString  '' (6)

                    MyBase.OpenConnection() '' (4)
                Catch innerEx As Exception
                    Throw innerEx '' (5)
                End Try
            End If

Is there a better way to do this?

T.I.A.

Call CloseConnection before change the connection string and call Open again.

I've changed it and still got the same problem:

            If retry Then
                Try
                    Me.CloseConnection()

                    ' Change the conection string
                    MyBase.ConnectionString = GetNewConnectionString.Get() '' (3)

                    ' Retry the OpenConnection method
                    MyBase.OpenConnection() '' (4)

                    ' Change the conection string in the configuration file

                Catch innerEx As Exception
                    Throw innerEx '' (5)
                End Try
            End If

I would remove this from outside of the DataAccessAdapter itself - ie create a DataAccessAdapterFactory class that first attempts to create an Adapter with the first connection string, and if this fails creates a new one with the back up connection string.

This has the advantage of keeping this functionality outside of the Adapter class, as well as solving your current issue.

Matt