context: //
  // DO NOT REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  //
  // @Authors:
  //       timop
  //
  // Copyright 2004-2013 by OM International
  //
  // This file is part of OpenPetra.org.
  //
  // OpenPetra.org is free software: you can redistribute it and/or modify
  // it under the terms of the GNU General Public License as published by
  // the Free Software Foundation, either version 3 of the License, or
  // (at your option) any later version.
  //
  // OpenPetra.org is distributed in the hope that it will be useful,
  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  // GNU General Public License for more details.
  //
  // You should have received a copy of the GNU General Public License
  // along with OpenPetra.org.  If not, see <http://www.gnu.org/licenses/>.
  //
  using System;
  using System.Text;
  using System.Collections;
  using System.Collections.Specialized;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.Odbc;
  using Ict.Common;
  using Ict.Common.DB;
  using Ict.Common.Data.Exceptions;
  using Ict.Common.Exceptions;
  using Ict.Common.Verification;
  
  namespace Ict.Common.Data
  {
      /// <summary>
      /// This is the base class for the data access store.
      /// It mainly contains static methods.
      /// </summary>
      public class TTypedDataAccess
      {
          private const string STR_INDENTATION = "    ";
  
          /// name of the column used for tracking changes, one ID per change
          public const string MODIFICATION_ID = "s_modification_id_t";
  
          /// <summary>
          /// who has last modified the row
          /// </summary>
          public const string MODIFIED_BY = "s_modified_by_c";
  
          /// <summary>
          /// when was the last change to the row
          /// </summary>
          public const string MODIFIED_DATE = "s_date_modified_d";
  
          /// <summary>
          /// who has originally created this row
          /// </summary>
          public const string CREATED_BY = "s_created_by_c";
  
          /// <summary>
          /// when was this row originally created
          /// </summary>
          public const string CREATED_DATE = "s_date_created_d";
  
          /// <summary>
          /// indicates whether a row has been deleted
          /// </summary>
          public static DateTime MODIFICATION_ID_DELETEDROW_INDICATOR = DateTime.MaxValue;
  
          /// <summary>
          /// the max for sqlite is 500. for postgresql it could be higher.
          /// </summary>
          private const int MAX_SQL_PARAMETERS = 450;
  
          private static int FRowCount;
  
          /// <summary>
          /// how many rows are in the current query or have been processed
          /// this is used for the Progress bar, to watch the current status
          /// </summary>
          public static Int32 RowCount
          {
              get
              {
                  return FRowCount;
              }
  
              set
              {
                  FRowCount = value;
              }
          }
  
          /// <summary>
          /// This function returns true if the column name is not one of the special columns, that
          /// are written automatically: modified date and by, created date and by, modification id
          ///
          /// </summary>
          /// <returns>true if this is no special auto column</returns>
          public static bool NoDefaultColumn(String AColumnName)
          {
              return (AColumnName != MODIFICATION_ID) && (AColumnName != MODIFIED_BY) && (AColumnName != MODIFIED_DATE)
                     && (AColumnName != CREATED_BY) && (AColumnName != CREATED_DATE);
          }
  
          /// <summary>
          /// This function returns the modification details of a row
          ///
          /// </summary>
          /// <returns>void</returns>
          public static void GetStoredModification(short ATableId,
              string[] AColumnNames,
              int[] APrimKeyColumnOrdList,
              DataRow ADataRow,
              DB.TDBTransaction ATransaction,
              out DateTime AModificationID,
              out String AModifiedBy,
              out System.DateTime AModifiedDate,
              bool ATreatRowAsAdded = false)
          {
              String SqlString = "SELECT " + MODIFICATION_ID + ", " + MODIFIED_BY + ", " + MODIFIED_DATE +
                                 " FROM PUB_" + TTypedDataTable.GetTableNameSQL(ATableId) +
                                 " WHERE ";
              Boolean First = true;
  
              foreach (int PrimKeyOrd in APrimKeyColumnOrdList)
              {
                  if (First == true)
                  {
                      First = false;
                  }
                  else
                  {
                      SqlString = SqlString + " AND ";
                  }
  
                  SqlString = SqlString + AColumnNames[PrimKeyOrd] + " = ?";
              }
  
              OdbcParameter[] Parameters = new OdbcParameter[APrimKeyColumnOrdList.Length];
  
              DataRowVersion WhichVersion = DataRowVersion.Original;
  
              if ((ADataRow.RowState == DataRowState.Added)
                  || (ATreatRowAsAdded))
              {
                  WhichVersion = DataRowVersion.Current;
              }
  
              Int32 Counter = 0;
  
              foreach (int i in APrimKeyColumnOrdList)
              {
                  Parameters[Counter] = CreateOdbcParameter(ATableId, i, ADataRow[i, WhichVersion].GetType());
                  Parameters[Counter].Value = ADataRow[i, WhichVersion];
                  Counter++;
              }
  
              DataTable table = DBAccess.GDBAccessObj.SelectDT(SqlString, TTypedDataTable.GetTableNameSQL(ATableId), ATransaction, Parameters);
              AModificationID = DateTime.MinValue;
              AModifiedBy = "";
              AModifiedDate = DateTime.MinValue;
  
              if (table.Rows.Count == 1)
              {
                  if (table.Rows[0][0] != System.DBNull.Value)
                  {
                      AModificationID = Convert.ToDateTime(table.Rows[0][0]);
                  }
  
                  if (table.Rows[0][1] != System.DBNull.Value)
                  {
                      AModifiedBy = (string)table.Rows[0][1];
                  }
  
                  if (table.Rows[0][2] != System.DBNull.Value)
                  {
                      AModifiedDate = (DateTime)(table.Rows[0][2]);
                  }
              }
              else if (table.Rows.Count == 0)
              {
                  AModificationID = MODIFICATION_ID_DELETEDROW_INDICATOR;
              }
          }
  
          /// <summary>
          /// This function is called by the DataStore (SubmitChanges).
          /// it will not update the modification id.
          /// </summary>
          public static void InsertRow(
              short ATableId,
              ref DataRow ADataRow,
              String ACurrentUser,
              StringBuilder AInsertStatement,
              List <OdbcParameter>AInsertParameters)
          {
              string[] Columns = TTypedDataTable.GetColumnStringList(ATableId);
              string query = GenerateInsertClause("PUB_" +
                  TTypedDataTable.GetTableNameSQL(ATableId),
                  Columns,
                  ADataRow,
                  true);
              List <OdbcParameter>parameters =
                  GetParametersForInsertClause(ATableId, ref ADataRow, Columns.Length, ACurrentUser, true);
  
              if (AInsertStatement.Length > 0)
              {
                  AInsertStatement.Append(",");
                  AInsertStatement.Append(query.Substring(query.IndexOf("VALUES (") + "VALUES".Length));
              }
              else
              {
                  AInsertStatement.Append(query);
              }
  
              AInsertParameters.AddRange(parameters);
          }
  
          /// <summary>
          /// This function is called by the DataStore (SubmitChanges).
          /// it will update the modification id.
          /// </summary>
          public static void InsertRow(
              short ATableId,
              ref DataRow ADataRow,
              DB.TDBTransaction ATransaction,
              String ACurrentUser,
              bool ATreatRowAsAdded)
          {
              string[] Columns = TTypedDataTable.GetColumnStringList(ATableId);
              string query = GenerateInsertClause("PUB_" +
                  TTypedDataTable.GetTableNameSQL(ATableId),
                  Columns,
                  ADataRow, false);
              List <OdbcParameter>parameters =
                  GetParametersForInsertClause(ATableId, ref ADataRow, Columns.Length, ACurrentUser, false);
  
              if (0 == DBAccess.GDBAccessObj.ExecuteNonQuery(query, ATransaction, parameters.ToArray()))
              {
                  throw new EDBSubmitException("[TTypedDataAccess.InsertRow] Problems INSERTing a row", eSubmitChangesOperations.eInsert);
              }
  
              DateTime LastModificationId;
              string LastModifiedBy;
              DateTime LastModifiedDate;
              int[] PrimKeyColumnOrdList = TTypedDataTable.GetPrimaryKeyColumnOrdList(ATableId);
  
              GetStoredModification(ATableId,
                  Columns,
                  PrimKeyColumnOrdList,
                  ADataRow,
                  ATransaction,
                  out LastModificationId,
                  out LastModifiedBy,
                  out LastModifiedDate,
                  ATreatRowAsAdded);
  
              ADataRow[MODIFICATION_ID] = LastModificationId;
          }
  
          /// <summary>
          /// This function is called by the DataStore (SubmitChanges)
          ///
          /// </summary>
          /// <returns>void</returns>
          public static void UpdateRow(
              short ATableId,
              bool AThrowAwayAfterSubmitChanges,
              ref DataRow ADataRow,
              DB.TDBTransaction ATransaction,
              String ACurrentUser)
          {
              string[] Columns = TTypedDataTable.GetColumnStringList(ATableId);
              int[] PrimKeyColumnOrdList = TTypedDataTable.GetPrimaryKeyColumnOrdList(ATableId);
              string DBTableName = TTypedDataTable.GetTableNameSQL(ATableId);
              DateTime LastModificationId;
              String LastModifiedBy;
              System.DateTime LastModifiedDate;
  
              // First try to update with a where clause with the modification id
              if (0 == DBAccess.GDBAccessObj.ExecuteNonQuery(GenerateUpdateClause("PUB_" + DBTableName,
                          Columns,
                          ADataRow,
                          PrimKeyColumnOrdList), ATransaction,
                      GetParametersForUpdateClause(ATableId, ref ADataRow, PrimKeyColumnOrdList, Columns.Length, ACurrentUser)))
              {
                  // Was not able to update the row,
                  // the database has a different modification id on that row.
                  // trying now the other way
  
                  DateTime OriginalModificationID;
                  DateTime CurrentModificationID;
  
                  // check if modification id of the changed row is the same as currently stored in the database
                  GetStoredModification(ATableId,
                      Columns,
                      PrimKeyColumnOrdList,
                      ADataRow,
                      ATransaction,
                      out LastModificationId,
                      out LastModifiedBy,
                      out LastModifiedDate);
  
                  if (LastModificationId != MODIFICATION_ID_DELETEDROW_INDICATOR)
                  {
                      if ((ADataRow[MODIFICATION_ID,
                                    DataRowVersion.Original] == System.DBNull.Value) || (ADataRow[MODIFICATION_ID, DataRowVersion.Original] == null))
                      {
                          OriginalModificationID = DateTime.MinValue;
                      }
                      else
                      {
                          OriginalModificationID = Convert.ToDateTime(ADataRow[MODIFICATION_ID, DataRowVersion.Original]);
                      }
  
                      if ((ADataRow[MODIFICATION_ID,
                                    DataRowVersion.Current] == System.DBNull.Value) || (ADataRow[MODIFICATION_ID, DataRowVersion.Current] == null))
                      {
                          CurrentModificationID = DateTime.MinValue;
                      }
                      else
                      {
                          CurrentModificationID = Convert.ToDateTime(ADataRow[MODIFICATION_ID, DataRowVersion.Current]);
                      }
  
                      if (OriginalModificationID == LastModificationId)
                      {
                          if (CurrentModificationID != LastModificationId)
                          {
                              // the modification id has been increased in a previous SubmitChanges, but then the transaction has been rolled back
                              ADataRow[MODIFICATION_ID] = LastModificationId;
                              CurrentModificationID = LastModificationId;
                          }
                      }
  
                      // check if modification id has been changed and committed to the database, but AcceptChanges has not been applied
                      if (OriginalModificationID != CurrentModificationID)
                      {
                          throw new EDBSubmitException(
                              "[TTypedDataAccess.UpdateRow] Developer should fix this: Forgot to call AcceptChanges on table " + DBTableName,
                              eSubmitChangesOperations.eUpdate);
                      }
  
                      if (LastModificationId != OriginalModificationID)
                      {
                          throw new EDBConcurrencyException(
                              "[TTypedDataAccess.UpdateRow] Cannot update row of table " + DBTableName + " because the row has been edited by user " +
                              LastModifiedBy,
                              "update",
                              DBTableName,
                              LastModifiedBy,
                              LastModifiedDate);
                      }
  
                      int RowsChanged = DBAccess.GDBAccessObj.ExecuteNonQuery(GenerateUpdateClause("PUB_" + DBTableName,
                              Columns,
                              ADataRow,
                              PrimKeyColumnOrdList), ATransaction,
                          GetParametersForUpdateClause(ATableId, ref ADataRow, PrimKeyColumnOrdList, Columns.Length, ACurrentUser));
  
                      if (RowsChanged == 0)
                      {
                          throw new EDBSubmitException("[TTypedDataAccess.UpdateRow] Cannot UPDATE row due to problems most likely with the timestamp",
                              eSubmitChangesOperations.eUpdate);
                      }
                  }
                  else
                  {
                      throw new EDBConcurrencyNoRowToUpdateException(
                          "[TTypedDataAccess.UpdateRow] Cannot update row of table " + DBTableName +
                          " because the row is not present in the DB. This means that either this row has been deleted from the DB before you tried to update it, or"
                          +
                          " that the row wasn't present in the DB at all before you tried to update it.",
                          DBTableName,
                          "",
                          DateTime.MinValue);
                  }
              }
  
              if (!AThrowAwayAfterSubmitChanges)
              {
                  GetStoredModification(ATableId,
                      Columns,
                      PrimKeyColumnOrdList,
                      ADataRow,
                      ATransaction,
                      out LastModificationId,
                      out LastModifiedBy,
                      out LastModifiedDate);
  
                  ADataRow[MODIFICATION_ID] = LastModificationId;
              }
          }
  
          /// <summary>
          /// This function is called by the DataStore (SubmitChanges)
          ///
          /// </summary>
          /// <returns>void</returns>
          public static void DeleteRow(
              short ATableId,
              DataRow ADataRow,
              DB.TDBTransaction ATransaction)
          {
              string[] Columns = TTypedDataTable.GetColumnStringList(ATableId);
              int[] PrimKeyColumnOrdList = TTypedDataTable.GetPrimaryKeyColumnOrdList(ATableId);
              string DBTableName = TTypedDataTable.GetTableNameSQL(ATableId);
  
              DateTime LastModificationId;
              String LastModifiedBy = "";
              System.DateTime LastModifiedDate;
  
              // check if modification id of the changed row is the same as currently stored in the database
              GetStoredModification(ATableId,
                  Columns,
                  PrimKeyColumnOrdList,
                  ADataRow,
                  ATransaction,
                  out LastModificationId,
                  out LastModifiedBy,
                  out LastModifiedDate);
  
              object OriginalModificationIdObject = ADataRow[MODIFICATION_ID, DataRowVersion.Original];
              DateTime OriginalLastModificationID = DateTime.MinValue;
  
              if (OriginalModificationIdObject != System.DBNull.Value)
              {
                  OriginalLastModificationID = Convert.ToDateTime(OriginalModificationIdObject);
              }
  
              if (LastModificationId != OriginalLastModificationID)
              {
                  throw new EDBConcurrencyException(
                      "Cannot delete row of table " + DBTableName + " because the row has been edited by user " + LastModifiedBy,
                      "delete",
                      DBTableName,
                      LastModifiedBy,
                      LastModifiedDate);
              }
  
              if (0 == DBAccess.GDBAccessObj.ExecuteNonQuery(GenerateDeleteClause("PUB_" + DBTableName,
                          Columns,
                          PrimKeyColumnOrdList), ATransaction,
                      GetParametersForDeleteClause(ATableId, ADataRow, PrimKeyColumnOrdList)))
              {
                  throw new EDBSubmitException("[TTypedDataAccess.DeleteRow] Problems DELETing a row", eSubmitChangesOperations.eDelete);
              }
          }
  
          /// <summary>
          /// This function expects a StringCollection that can be filled, empty or nil.
          /// It also needs the list of the primary key columns, because otherwise the returned row will fail the constraints.
          /// It will return a proper Select Clause.
          /// </summary>
          /// <returns>the Select Clause
          /// </returns>
          public static String GenerateSelectClause(StringCollection AFieldList, short ATableID, bool AIncludeTableNames = false)
          {
              String ReturnValue = String.Empty;
              string Tablename = String.Empty;
  
              ReturnValue = "SELECT ";
  
              if ((AFieldList == null) || (AFieldList.Count == 0))
              {
                  ReturnValue += '*';
              }
              else
              {
                  if (AIncludeTableNames)
                  {
                      Tablename = TTypedDataTable.GetTableNameSQL(ATableID);
  
                      foreach (var Field in AFieldList)
                      {
                          ReturnValue += Tablename + "." + Field + ",";
                      }
  
                      // strip off trailing ','
                      ReturnValue = ReturnValue.Substring(0, ReturnValue.Length - 1);
                  }
                  else
                  {
                      ReturnValue += StringHelper.StrMerge(AFieldList, ',');
                  }
  
                  string[] PrimKeyColumnStringList = TTypedDataTable.GetPrimaryKeyColumnStringList(ATableID);
  
                  foreach (string primKeyColumn in PrimKeyColumnStringList)
                  {
                      if ((!AFieldList.Contains(primKeyColumn)))
                      {
                          ReturnValue += ',';
  
                          if (AIncludeTableNames)
                          {
                              ReturnValue += Tablename + ".";
                          }
  
                          ReturnValue += primKeyColumn;
                      }
                  }
  
                  if ((!AFieldList.Contains(MODIFICATION_ID)))
                  {
                      ReturnValue += ',';
  
                      if (AIncludeTableNames)
                      {
                          ReturnValue += Tablename + ".";
                      }
  
                      ReturnValue += MODIFICATION_ID;
                  }
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// This function expects a StringCollection that can be filled, empty or nil.
          /// It also needs the list of the primary key columns, because otherwise the returned row will fail the constraints.
          /// It will prefix all fields with the given tablename. needed for selecting from join statements.
          /// It will return a proper Select Clause.
          /// If a field has already a dot in the name, the ATablename is not put before it: That way you can do SUM(PUB_table.p_something).
          /// </summary>
          /// <returns>the Select Clause
          /// </returns>
          public static String GenerateSelectClauseLong(String ATableName, StringCollection AFieldList, short ATableId)
          {
              String ReturnValue = "SELECT ";
  
              string[] PrimaryKeyFields = TTypedDataTable.GetPrimaryKeyColumnStringList(ATableId);
  
              if ((AFieldList == null) || (AFieldList.Count == 0))
              {
                  ReturnValue = ReturnValue + ATableName + ".*";
              }
              else
              {
                  foreach (string s in AFieldList)
                  {
                      if (ReturnValue != "SELECT ")
                      {
                          ReturnValue = ReturnValue + ',';
                      }
  
                      if (s.IndexOf('.') == -1)
                      {
                          ReturnValue = ReturnValue + ATableName + '.' + s;
                      }
                      else
                      {
                          ReturnValue = ReturnValue + s;
                      }
                  }
  
                  foreach (string primkeyField in PrimaryKeyFields)
                  {
                      if ((!AFieldList.Contains(primkeyField)))
                      {
                          if (primkeyField.IndexOf('.') == -1)
                          {
                              ReturnValue = ReturnValue + ',' + ATableName + '.' + primkeyField;
                          }
                          else
                          {
                              ReturnValue = ReturnValue + ',' + primkeyField;
                          }
                      }
                  }
  
                  if ((!AFieldList.Contains(MODIFICATION_ID)))
                  {
                      ReturnValue = ReturnValue + ',' + ATableName + '.' + MODIFICATION_ID;
                  }
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// This function expects an empty table that contains all existing columns,
          /// and a datarow that has a string or an empty value for each column.
          /// It will return a Where clause, using the given values.
          /// </summary>
          /// <param name="AColumnNames">the column names</param>
          /// <param name="ADataRow">the data row with the the columns for the where clause</param>
          /// <param name="ATemplateOperators">Every template field can have an operator; the default version always used = or LIKE</param>
          /// <returns>the Where Clause
          /// </returns>
          public static String GenerateWhereClause(string[] AColumnNames, DataRow ADataRow, StringCollection ATemplateOperators)
          {
              String ReturnValue = "";
              Int32 Counter;
              Int32 CounterOperator;
  
              ReturnValue = "";
              CounterOperator = 0;
  
              for (Counter = 0; Counter <= ADataRow.ItemArray.Length - 1; Counter += 1)
              {
                  if (ADataRow[Counter] != System.DBNull.Value)
                  {
                      if (ReturnValue.Length == 0)                     //first time around
                      {
                          ReturnValue = " WHERE ";
                      }
                      else
                      {
                          ReturnValue = ReturnValue + " AND ";
                      }
  
                      ReturnValue = ReturnValue + AColumnNames[Counter];
  
                      if ((ATemplateOperators == null) || (CounterOperator >= ATemplateOperators.Count))
                      {
                          if (ADataRow[Counter].GetType() == typeof(String))
                          {
                              ReturnValue = ReturnValue + " LIKE ";
                          }
                          else
                          {
                              ReturnValue = ReturnValue + " = ";
                          }
                      }
                      else
                      {
                          ReturnValue = ReturnValue + ' ' + ATemplateOperators[CounterOperator] + ' ';
                      }
  
                      ReturnValue = ReturnValue + '?';
                      CounterOperator = CounterOperator + 1;
                  }
                  else
                  {
                      if ((ATemplateOperators != null) && (ATemplateOperators.Count == ADataRow.ItemArray.Length))
                      {
                          CounterOperator = CounterOperator + 1;
                      }
                  }
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// This function expects an empty table that contains all existing columns,
          /// and a datarow that has a string or an empty value for each column.
          /// It will return a Where clause, using the given values.
          /// </summary>
          /// <returns>the Where Clause
          /// </returns>
          public static String GenerateWhereClause(string[] AColumnNames, DataRow ADataRow)
          {
              return GenerateWhereClause(AColumnNames, ADataRow, null);
          }
  
          /// <summary>
          /// This function expects an empty table that contains all existing columns,
          /// and search criteria.
          /// It will return a Where clause, using the given values.
          /// </summary>
          /// <param name="ASearchCriteria"></param>
          /// <returns>the Where Clause
          /// </returns>
          public static String GenerateWhereClause(TSearchCriteria[] ASearchCriteria)
          {
              String ReturnValue = "";
  
              foreach (TSearchCriteria searchcriterium in ASearchCriteria)
              {
                  if (ReturnValue.Length == 0)                     //first time around
                  {
                      ReturnValue = " WHERE ";
                  }
                  else
                  {
                      ReturnValue += " AND ";
                  }
  
                  ReturnValue += searchcriterium.fieldname + " " + searchcriterium.comparator + " ?";
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// this function generates a simple where clause with the given fieldnames
          /// </summary>
          /// <param name="AFieldNames"></param>
          /// <returns></returns>
          private static String GenerateWhereClause(string[] AFieldNames)
          {
              String ReturnValue = "";
  
              foreach (string columnname in AFieldNames)
              {
                  if (ReturnValue.Length == 0)                     //first time around
                  {
                      ReturnValue = " WHERE ";
                  }
                  else
                  {
                      ReturnValue += " AND ";
                  }
  
                  ReturnValue += columnname + " = ?";
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// This function generates a where clause for a JOIN SQL Query
          /// </summary>
          /// <returns></returns>
          private static String GenerateWhereClauseForJoin(string ATableName,
              string AOtherTableName,
              string[] APKFieldNames,
              string[] AOtherPKFieldNames)
          {
              String ReturnValue = "";
  
              if (APKFieldNames == null)
              {
                  throw new ArgumentException("Argument 'APKFieldNames' must not be null");
              }
  
              if (AOtherPKFieldNames == null)
              {
                  throw new ArgumentException("Argument 'AOtherPKFieldNames' must not be null");
              }
  
              if (APKFieldNames.Length != AOtherPKFieldNames.Length)
              {
                  throw new ArgumentException("Argument 'APKFieldNames' and 'AOtherPKFieldNames' must contain the same number of items");
              }
  
              for (int Counter = 0; Counter < APKFieldNames.Length; Counter++)
              {
                  if (ReturnValue.Length == 0)                     //first time around
                  {
                      ReturnValue = " WHERE ";
                  }
                  else
                  {
                      ReturnValue += " AND ";
                  }
  
                  if (!APKFieldNames[Counter].StartsWith(AOtherTableName + "."))
                  {
                      ReturnValue += AOtherTableName + ".";
                  }
  
                  ReturnValue += APKFieldNames[Counter] + " = ";
  
                  if (!AOtherPKFieldNames[Counter].StartsWith(ATableName + "."))
                  {
                      ReturnValue += ATableName + ".";
                  }
  
                  ReturnValue += AOtherPKFieldNames[Counter];
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// this function generates a where clause for the primary key of the given table
          /// </summary>
          /// <param name="ATableId"></param>
          /// <returns></returns>
          public static String GenerateWhereClauseFromPrimaryKey(short ATableId)
          {
              return GenerateWhereClause(TTypedDataTable.GetPrimaryKeyColumnStringList(ATableId));
          }
  
          /// <summary>
          /// this function generates a where clause for the unique key of the given table
          /// </summary>
          /// <param name="ATableId"></param>
          /// <returns></returns>
          public static String GenerateWhereClauseFromUniqueKey(short ATableId)
          {
              return GenerateWhereClause(TTypedDataTable.GetUniqueKeyColumnStringList(ATableId));
          }
  
          /// <summary>
          /// This function expects a string list of all existing columns,
          /// and a datarow that has a value or an empty value for each column.
          /// It will return a Where clause, using the given values.
          /// It does not contain the WHERE keyword.
          /// It uses the long form, table.fieldname
          ///
          /// </summary>
          /// <param name="ATableName">the table that the where clause is generated for</param>
          /// <param name="ATableId">id of the table to get the list of all columns of that table</param>
          /// <param name="ADataRow">contains the values for the where clause</param>
          /// <param name="ATemplateOperators">Every template field can have an operator; the default version always used = or LIKE</param>
          /// <returns>the Where Clause
          /// </returns>
          public static String GenerateWhereClauseLong(String ATableName, short ATableId, DataRow ADataRow, StringCollection ATemplateOperators)
          {
              String ReturnValue;
              Int32 Counter;
              Int32 CounterOperator;
  
              ReturnValue = "";
              Counter = 0;
              CounterOperator = 0;
              string[] ColumnNames = TTypedDataTable.GetColumnStringList(ATableId);
  
              while (Counter < ADataRow.ItemArray.Length)
              {
                  if (ADataRow[Counter] != System.DBNull.Value)
                  {
                      ReturnValue = ReturnValue + " AND ";
                      ReturnValue = ReturnValue + ATableName + '.' + ColumnNames[Counter];
  
                      if ((ATemplateOperators == null) || (CounterOperator >= ATemplateOperators.Count))
                      {
                          if (ADataRow[Counter].GetType() == typeof(String))
                          {
                              ReturnValue = ReturnValue + " LIKE ";
                          }
                          else
                          {
                              ReturnValue = ReturnValue + " = ";
                          }
                      }
                      else
                      {
                          ReturnValue = ReturnValue + ' ' + ATemplateOperators[CounterOperator] + ' ';
                      }
  
                      ReturnValue = ReturnValue + '?';
                      CounterOperator = CounterOperator + 1;
                  }
                  else
                  {
                      if ((ATemplateOperators != null) && (ATemplateOperators.Count == ADataRow.ItemArray.Length))
                      {
                          CounterOperator = CounterOperator + 1;
                      }
                  }
  
                  Counter = Counter + 1;
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// This function expects a string list of all existing columns,
          /// and a criteria list of values to search for.
          /// It will return a Where clause, using the given values.
          /// It does not contain the WHERE keyword.
          /// It uses the long form, table.fieldname
          ///
          /// </summary>
          /// <param name="ATableName">the table that the where clause is generated for</param>
          /// <param name="ASearchCriteria"></param>
          /// <returns>the Where Clause
          /// </returns>
          public static String GenerateWhereClauseLong(String ATableName, TSearchCriteria[] ASearchCriteria)
          {
              String ReturnValue = "";
  
              foreach (TSearchCriteria searchcriterium in ASearchCriteria)
              {
                  ReturnValue += " AND " + ATableName + '.' + searchcriterium.fieldname + ' ' + searchcriterium.comparator + " ?";
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// This function expects a string array with the names of all existing columns,
          /// and a datarow that has a value or an empty value for each column.
          /// It will return a Where clause, using the given values.
          /// It does not contain the WHERE keyword.
          /// It uses the long form, table.fieldname
          ///
          /// </summary>
          /// <param name="ATableName">the table that the where clause is generated for</param>
          /// <param name="ATableId">id of the table to get the list of all columns of that table</param>
          /// <param name="ADataRow">contains the values for the where clause</param>
          /// <returns>the Where Clause
          /// </returns>
          public static String GenerateWhereClauseLong(String ATableName, short ATableId, DataRow ADataRow)
          {
              return GenerateWhereClauseLong(ATableName, ATableId, ADataRow, null);
          }
  
          /// <summary>
          /// This function expects a list of string, where the first string is either "order by" or "group by",
          /// and the rest of the string is e.g. "p_partner.p_partner_key_n ASC"
          /// It will return the appropriate part of the sql query
          /// </summary>
          /// <param name="AOrderBy">String list with instructions how to order or group the result</param>
          /// <returns>the order by / group by clause
          /// </returns>
          public static String GenerateOrderByClause(StringCollection AOrderBy)
          {
              String ReturnValue;
              Int32 Counter;
  
              ReturnValue = "";
  
              if (AOrderBy != null)
              {
                  ReturnValue = ' ' + AOrderBy[0] + ' ';
  
                  for (Counter = 1; Counter <= AOrderBy.Count - 1; Counter += 1)
                  {
                      if (Counter > 1)
                      {
                          ReturnValue = ReturnValue + ',';
                      }
  
                      ReturnValue = ReturnValue + AOrderBy[Counter];
                  }
              }
  
              return ReturnValue;
          }
  
          /// <summary>
          /// This function creates an odbc parameter with the correct type, based on the obj which comes from a DataRow
          ///
          /// </summary>
          /// <param name="ATableId">Table that we are operating on</param>
          /// <param name="AColumnNr">which column should the parameter be generated for</param>
          /// <param name="AType">the value that needs to be converted to OdbcParameter</param>
          /// <returns>the Odbc parameter</returns>
          public static OdbcParameter CreateOdbcParameter(short ATableId, Int32 AColumnNr, Type AType)
          {
              OdbcParameter ReturnValue;
              OdbcType newType;
  
              System.Int32 Length;
              Length = -1;
              newType = OdbcType.Int;
  
              if (AType == typeof(System.Int64))
              {
                  newType = OdbcType.Decimal;
                  Length = 10;
              }
              else if (AType == typeof(double))
              {
                  newType = OdbcType.Decimal;
                  Length = 24;
              }
              else if (AType == typeof(bool))
              {
                  newType = OdbcType.Bit;
              }
              else if (AType == typeof(String))
              {
                  newType = OdbcType.VarChar;
  
                  // The documentation says:
                  // \\ begin quote
                  // The Length property returns the number of Char objects in this instance,
                  // not the number of Unicode characters.
                  // The reason is that a Unicode character might be represented by more than one Char.
                  // Use the System.Globalization.StringInfo class to work with each Unicode character instead of each Char.
                  // \\ end quote
                  // I wonder if Mono behaves different than Windows?
                  // I should find the maximum number for this field specifically;
                  // multiplying by 2 is not safe with multibyte characters?
                  // Length := (obj as System.String).Length  2;
                  Length = TTypedDataTable.CreateOdbcParameter(ATableId, AColumnNr).Size;
  
                  if (Length == 0)
                  {
                      Length = 1;
                  }
              }
              else if (AType == typeof(System.DateTime))
              {
                  newType = OdbcType.Date;
              }
              else if (AType == typeof(System.Int32))
              {
                  newType = OdbcType.Int;
              }
              else if (AType == typeof(System.Decimal))
              {
                  // hardly ever used... p_person.p_believer_since_year_i number(4)
                  newType = OdbcType.Decimal;
                  Length = 24;
              }
  
              if (Length == -1)
              {
  
gt:                 ReturnValue = new OdbcParameter("", newType);