1. You have a SQL Server Database called "MyDatabase" with a single table "tblCustomer" which has 2 columns "CustNo" and CustName". Ran through the SF Business Mapper creating a BO called MyDatabaseBO which generates a DLL for MyDatabaseBO.

  2. You have an Oracle Database called "MyDatabase" with a single table "tblCustomer" which has 2 columns "CustNo" and CustName". Ran through the SF Business Mapper creating a BO called MyDatabaseBO_ORACLE which generates a DLL for MyDatabaseBO_ORACLE.

  3. You generate an SF Windows Forms project similar to the the security sample and add the following to it:

      a. You add an application key field to the app.config called "UseOracleType" and set it's default value to false.

      b. You then generate a SF Maintenance form and drag the tblCustomer BO from the SQL BO (MyDatabaseBO) to the form and get things running against the SQL BO. In this casde most of the clients using the Application are SQL clients.

   4.  When I generate the Windows Application, there is a reference to the MyDatabaseBO DLL and not the MyDatabaseBO_ORACLE DLL.

Now we have a client that wants to use ORACLE and not SQL. For now, lets not address the issue of RBS which must be SQL, but just the database for the data.

My thoughts are then to set the Application Key field in the app.config to "UseOracleType" = true.

The user start the application but when they click on the form to display the Customer Maintenance form, I want to use the MyDatabaseBO_ORACLE DLL in place of the MyDatabaseBO DLL. The naming conventions on all the table names and column names in both DLLs are identical.

Here is the question?

Is there a way when any form loads, to programatically check the app.config "UseOracleType" and if "true" to programmatically change the BO from MyDatabaseBO to MyDatabaseBO_ORACLE. My thought here is that all table names and column names are identical? If you what event for the form would you use.

1. Create a base business object
2. Inherit all of your application BOs from this base BO
3. Within the base BO, override the FillDataTable method.  You will want to massage the command before being passed into the base class.
   
   
   Public Overrides Sub FillDataTable(ByVal CommandToExecute As System.Data.Common.DbCommand)
       MyBase.FillDataTable(Me.MassageCommand(command))
   End Sub
   
   
4. The MassageCommand Method takes the native DbTypes into account to see if they need to be converted (this has to do with some internal logic of our BOs, you may need to provide additional logic here for Oracle support):
Private Function MassageCommand(ByVal Command As DbCommand) As DbCommand
'-- Get a reference to the command
Dim cmd As SqlCommand = DirectCast(Command, SqlCommand)

'-- If this business object needs to talk to Oracle, then convert the command, else, just execute it
If Me.ShouldNativeDbTypesBeConverted Then
    '-- Replace the char fields before converting the command
    For Each field As CharEnumField In _CharFieldsToUpdate
        field.ReplaceCharFieldWithEnumField(cmd)
    Next

    Return Me.ConvertCommand(cmd)
Else
    Return Command
End If
End Function



5. Within this command, you can see the ConvertCommand, this is where the meat of the conversion will take place.  You may need some RegEx logic here, but if you predominantly use sprocs, it makes this part easier.
   
   

   ''' <summary>
   ''' Converts the specified SqlCommand object into an equivalent OleDbCommand object.
   ''' </summary>
   Private Function ConvertCommand(ByVal Command As SqlCommand) As OleDbCommand
   '-- Establish locals
   Dim loReturn As New OleDbCommand()
   Dim loMatch As Match

   '-- Set the basic properties on the oledb command
   loReturn.CommandText = Command.CommandText
   loReturn.CommandType = Command.CommandType

   '-- Update the parameters for the command
   '-- Find all of the parameters within the text & replace them
   loMatch = _ParameterRegex.Match(Command.CommandText)
   loReturn.CommandText = _ParameterRegex.Replace(Command.CommandText, "?")

   '-- Replace the matches in the text and build the parameters for the command
   While loMatch.Success
       '-- Add the new parameter to the collection
       loReturn.Parameters.Add(ConvertParameter(Command.Parameters(loMatch.Value)))

       '-- Go to the next match
       loMatch = loMatch.NextMatch()
   End While

   '-- Return the new command
   Return loReturn
   End Function

In the last example, you can see that this is converting an SqlDbCommand into an OleDbCommand (geared towards VFP).  This is where you will have to spend a fair amount of your time, but this is the approach that you will want to take.