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.

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.

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?

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).

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

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.

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

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. 

Couple of questions.

1. On a Strataframe Maintenance form when is the FillDataTable method called? IE.. when to user SEARCHES on the Browse?, When the user clicks on SAVE?

2. Currently it appears that the Overridden FillDataTable method is not being executed from the BO that inherits from my BaseBO form although it appears that everything is set as you suggested. Is there something else I need to do?

Hi Ross,

You don't need to insert any code, just make sure your form is using a BO which is subclassed from your based BO which has the FillDataTable method override.

There are a small number of intrinsic calls such as FillByParent that use changedatatable, but these use QueryInformation which is, by nature, completely database independent and, as such, don't need to go through your translation routines.

The base bo is called : TrustedVALETBaseBO and inherits from MicroFour.StrataFrame.Business.BusinessLayer

using MicroFour.StrataFrame.Business;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using System.Text;
using System.Data.Common;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Data.OracleClient;

namespace TrustedVALET_BO
{
    [Serializable()]
    public partial class TrustedVALETBaseBO : MicroFour.StrataFrame.Business.BusinessLayer
    {
        public enum DatabaseType : int
        {
            SQLServer = 0,
            Oracle = 1
        }

        #region Constructors

        /// <summary>
        /// Initializes a new instance of the BusinessObject1 class.
        /// </summary>
        public TrustedVALETBaseBO()
            : base()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Initializes a new instance of the BusinessObject1 class.
        /// </summary>
        /// <param name="container">The IContainer to which this business object will be added.</param>
        public TrustedVALETBaseBO(IContainer container)
            : base()
        {
            container.Add(this);

            InitializeComponent();
        }

        /// <summary>
        /// Initializes a new instance of the BusinessObject1 class.
        /// </summary>
        /// <param name="info">The SerializationInfo for the object to create.</param>
        /// <param name="context">The StreamingContext for the source stream.</param>
        protected TrustedVALETBaseBO(SerializationInfo info, StreamingContext context)
            : base(info, context)
        {
            InitializeComponent();
        }

        #endregion

        #region Data Retrieval Methods

        #endregion

        #region Event Handlers

        /// <summary>
        /// Checks the business rules on the current row
        /// </summary>
        /// <param name="e"></param>
        /// <remarks></remarks>
        private void TrustedVALETBaseBO_CheckRulesOnCurrentRow(CheckRulesEventArgs e)
        {

        }

        /// <summary>
        /// Sets the default values for a new row
        /// </summary>
        /// <remarks></remarks>
        private void TrustedVALETBaseBO_SetDefaultValues()
        {
           
        }

        #endregion

 
        private static DatabaseType _DatabaseQueryType =  DatabaseType.SQLServer ;

        public static DatabaseType DatabaseQueryType
        {
            get { return _DatabaseQueryType; }
            set { _DatabaseQueryType = value; }
        }

        private OracleCommand ConvertSqlToOracleCommand(SqlCommand cmd)
        {
     //  inserted next 2 lines just to have the code compile
            System.Data.OracleClient.OracleCommand strOr = null;
            return strOr;
            //-- Convert the SqlCommand into an Oracle Command
        }

        //-- Override the base FillDataTable and add a test to see if the command should be converted to Oracle
        public override void FillDataTable(DbCommand cmd)
        {
            if (TrustedVALETBaseBO.DatabaseQueryType ==  DatabaseType.Oracle)
                cmd = ConvertSqlToOracleCommand((SqlCommand)cmd);

                //  added write to see if this is being executed
  Console.WriteLine("Base class Constructor");

            base.FillDataTable(cmd);
            
        }

    }
}

The actual database bo for the table is: tbl_BargeClass_1 and inherits from TrustedVALETBaseBO

using MicroFour.StrataFrame.Business;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Runtime.Serialization;

using System.Text;

using MicroFour.StrataFrame.Security;

namespace TrustedVALET_BO

{

[Serializable()]

//public partial class tbl_BargeClass_1 : MicroFour.StrataFrame.Business.BusinessLayer

public partial class tbl_BargeClass_1 : TrustedVALETBaseBO

#region Constructors

/// <summary>

/// Initializes a new instance of the BusinessObject1 class.

/// </summary>

public tbl_BargeClass_1()

: base()

{

InitializeComponent();

}

/// <summary>

/// Initializes a new instance of the BusinessObject1 class.

/// </summary>

/// <param name="container">The IContainer to which this business object will be added.</param>

public tbl_BargeClass_1(IContainer container)

: base()

{

container.Add(this);

InitializeComponent();

}

/// <summary>

/// Initializes a new instance of the BusinessObject1 class.

/// </summary>

/// <param name="info">The SerializationInfo for the object to create.</param>

/// <param name="context">The StreamingContext for the source stream.</param>

protected tbl_BargeClass_1(SerializationInfo info, StreamingContext context)

: base(info, context)

{

InitializeComponent();

}

#region Data Retrieval Methods

#region Event Handlers

/// <summary>

/// Checks the business rules on the current row

/// </summary>

/// <param name="e"></param>

/// <remarks></remarks>

private void tbl_BargeClass_1_CheckRulesOnCurrentRow(CheckRulesEventArgs e)

{

}

/// <summary>

/// Sets the default values for a new row

/// </summary>

/// <remarks></remarks>

private void tbl_BargeClass_1_SetDefaultValues()

{

this.BARGECLASS_ENTDT = DateTime.Now;

this.BARGECLASS_USERE = SecurityBasics.CurrentUser.UserName;

}

private void tbl_BargeClass_1_BeforeSave(MicroFour.StrataFrame.Data.BeforeSaveUndoEventArgs e)

{

this.BARGECLASS_UPDT = DateTime.Now;

this.BARGECLASS_USERU = SecurityBasics.CurrentUser.UserName;

}

}

}

The SF Maintenance form has the tbl_BargeClass_1 BO on the form. This is a typical Maint Form where it has a browse where the user searches and there is no code which I coded to fill th BO other than what the SF framework does internally.

Your BrowseDialog probably thinks that it should be querying an SQLServer database. I think that if you are going to be exchanging databases, you might need to build a way to determine the datasource in the SetDataSources of your Program.cs.

Ensure that your datasources are setup to use an OracleDataSourceItem.