Example: Custom code to communicate with an outrigger database

This code sample uses a custom method, IDORuntime.Context.CreateOutriggerApplicationDB to provide direct access to an outrigger database. The database must first be linked to your Mongoose application as described in Including data from an external database into a Mongoose-based application.

      public int IdoLinkOtherDbColPopulateSp( string linkedDatabase, string tableName, string optimisticColumnName, byte databaseType, string profileName, string infobar )
      {
         int result = 0;
         if ( databaseType == 1 ) // SQL Server
         {
            if ( !DoSqlServerColumnsSp( linkedDatabase, tableName, infobar ) )
               return 16;
            else
               return 0;
         }
         
         if (!DeletePreviousColumns( linkedDatabase, tableName, infobar ) )
            return 16;
         using ( ApplicationDB db = IDORuntime.Context.CreateOutriggerApplicationDB( profileName ) )
         {
            IDbCommand cmd = db.Connection.CreateCommand();
            IDbDataParameter parm;
            
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = @"
SELECT
  cols.COLUMN_NAME  
 , cols.COLUMN_NAME As ViewColumnName
 , CASE WHEN DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'VARCHAR2', 'NVARCHAR2') THEN 1 ELSE 0 END AS IsCharacterColumn
 , CASE WHEN xx.TABLE_NAME IS NULL THEN 0 ELSE 1 END AS IsKeyColumn
, cols.DATA_TYPE
, CASE WHEN cols.DATA_PRECISION IS NULL THEN cols.DATA_LENGTH ELSE DATA_PRECISION END
, cols.DATA_SCALE
FROM user_tab_cols cols
LEFT OUTER JOIN (
select ucc.table_name, ucc.column_name
from user_constraints uc
inner join user_cons_columns ucc on
  ucc.table_name = uc.table_name
and ucc.constraint_name = uc.constraint_name
and uc.constraint_type = 'P'
 ) xx ON
xx.TABLE_NAME = cols.TABLE_NAME
AND xx.COLUMN_NAME = cols.COLUMN_NAME
WHERE cols.TABLE_NAME = :ptable
";
            parm = cmd.CreateParameter();
            parm.ParameterName = "ptable";
            parm.Value = tableName.ToUpper();
            cmd.Parameters.Add( parm );
            cmd.CommandType = CommandType.Text;
            cmd.Connection = db.Connection;
            IDataReader colReader = cmd.ExecuteReader();
            while (colReader.Read() )
            {
               string colName = colReader.GetString(0);
               string viewColName = colReader.GetString(1);
               byte isCharacter = colReader.GetByte(2);
               byte isKey = colReader.GetByte(3);
               byte isOptimisticLock = 0;
               string propertyDataType = colReader.GetString(4);
               int propertyLength = colReader.GetInt32(5);
               int? propertyScale = colReader.GetInt32(6);
               if ( colReader.IsDBNull(6) )
                  propertyScale = null;
               if ( optimisticColumnName == colName )
                  isOptimisticLock = 1;
               if (!InsertOneColumn( linkedDatabase, tableName, colName, viewColName, isCharacter, isKey, isOptimisticLock, propertyDataType, propertyLength, propertyScale, infobar ) )
               {
                  break;
               }
            }
         }
         return result;
      }