Here is a sample setup to base my question:

  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.

If youa re trying to convert a BO type from one object type to another, this gets tricky.  It is possible, but will require some advanced reflection to get this done.  In essence, we do a bit of this when we translate a BO (i.e. a ChildFormDialog).  But in this instance, they are the same type on both sides.  To change the actual type of the object, the easiest way would be to follow a level of inheritance (Oracle inherits from the SQL DB BO) and then the partial classes would override the properties...this too could get tricky due to the same field names.

The best route would be to have all of this logic in a BO and change the dynamics of the BO to support both versions.  In other words, you could always bind to the SQL DB type.  Then in your base BO when a FillDataTAble or Save is called, the SQL would create an instance of the Oracle when trying to go to the server...or you could have a single BO instance and change the database.

This is not a simple question to answer, especially here on the forum like this.  But you are going to want to encapsulate this logic in the BO, not at the form level, otherwise it will be a nightmare to maintain your application.

To clarify your suggestion above:

The best route would be to have all of this logic in a BO and change the dynamics of the BO to support both versions.  In other words, you could always bind to the SQL DB type.  Then in your base BO when a FillDataTAble or Save is called, the SQL would create an instance of the Oracle when trying to go to the server...or you could have a single BO instance and change the database.

This is not a simple question to answer, especially here on the forum like this.  But you are going to want to encapsulate this logic in the BO, not at the form level, otherwise it will be a nightmare to maintain your application.

Are you suggesting that we would customize the generated BO from the SF Business Mapper to surround the SQL generated statements with IF statements to handle the Oracle syntax if the app.config setting is set to use Oracle?

If so, then we could never regenerate the BO or the changes would be overwritten. Is this a correct assumption?

 

 

If possible early today could you respond to my questions below.

To clarify your suggestion above:

The best route would be to have all of this logic in a BO and change the dynamics of the BO to support both versions.  In other words, you could always bind to the SQL DB type.  Then in your base BO when a FillDataTAble or Save is called, the SQL would create an instance of the Oracle when trying to go to the server...or you could have a single BO instance and change the database.

This is not a simple question to answer, especially here on the forum like this.  But you are going to want to encapsulate this logic in the BO, not at the form level, otherwise it will be a nightmare to maintain your application.

Are you suggesting that we would customize the generated BO from the SF Business Mapper to surround the SQL generated statements with IF statements to handle the Oracle syntax if the app.config setting is set to use Oracle?

If so, then we could never regenerate the BO or the changes would be overwritten. Is this a correct assumption?

No, this is not a correct assumption.  I am pretty confident that you and I have had this conversation on a different thread a while back.  But to recap, you would convert an SqlDbCommand into an OracleDbCommand in your BaseBO.  You already know if you are running in an Oracle or SQL Server environment.  There is absolutely no need to write all of your queries, etc. twice as this would be a poor design as open the door for a lot of potential bugs, not to mention testing.

You can override all of the necessary BO commands.  So you should have a Base BO that all of your BOs inherit from instead of inheriting directly from BusinessLayer.  In your BaseBO class, you will overwrite the FillDataTable command, and whichever other commands are necessary as you move down the road.  You will program all of your queries towards Oracle or Sql, whichever you prefer.  So for arguments sake, let's just assume you will program towards SQL Server.  When a FillDataTable is called, for example, you will be passing in an SqlDbCommand, all the time every time regardless of which backend you are running.  Then in your overwritten FillDataTable command in your BaseBO, you will know if you need to convert that SQLDbCommand into an OracleDbCommand (or whichever provider you are using).  This way all of the logic is encapsulated in the BaseBO and once it is tested, it is done.  This way you have 1 BO that represents a table...not one for SQL and one for Oracle....just one.  This will require a little bit of elbow grease, but this is the approach that we have taken on our medical software and it has worked well (we converted SqlDbCommands into OleDbCommands for VFP).

Trent

Based on my example, would it be possible for you to illustrate some code on how this is done? Or are you aware of something similar that I can use to get started?

Thanks

Based on your code, this would require me to spend a fair amount of time and just write it for you which outstrips the scope of this forum.  But here are the basic steps:

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.

Trent

This would be ideal if it were incorporated into the framework. As you pointed out, this will require quite a bit of work. Based on my example, I can't help but believe that it would be extremely helpful to many other members to see a detailed example of how this is actually done. Your expertise is highly valued by everyone on the forum.

Thanks

Ross

Could you show me a sample on how to do the above with the simple example I provided above?

Please respond ASAP to my prior question in this thread in that I need to report back to my Team. Thanks.