/// <summary>
/// Returns the current sequence value for the given Sequence from the DB.
/// </summary>
/// <param name="ASequenceName">Name of the Sequence.</param>
/// <param name="ATransaction">An instantiated Transaction in which the Query
/// to the DB will be enlisted.</param>
/// <param name="ADatabase">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT last_value FROM " + ASequenceName + "", ATransaction)));
}
/// <summary>
/// Returns the next Sequence Value for the given Sequence from the DB. - IMPORTANT: This increasing of the
/// Value of the Sequence PERSISTS in the PostgreSQL implmentation even if the DB Transction gets rolled back!!!
/// --> See https://wiki.openpetra.org/index.php/PostgreSQL:_Sequences_Not_Tied_to_DB_Transactions
/// </summary>
/// <param name="ASequenceName">Name of the Sequence.</param>
/// <param name="ATransaction">An instantiated Transaction in which the Query
/// to the DB will be enlisted.</param>
/// <param name="ADatabase">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction)));
}
/// <summary>
/// Creates a DbCommand object.
/// This formats the sql query for PostgreSQL, and transforms the parameters.
/// </summary>
/// <param name="ACommandText"></param>
/// <param name="AConnection"></param>
/// <param name="AParametersArray"></param>
/// <param name="ATransaction"></param>
/// <returns>Instantiated NpgsqlCommand.</returns>
public DbCommand NewCommand(ref string ACommandText, DbConnection AConnection, DbParameter[] AParametersArray, TDBTransaction ATransaction)
{
DbCommand ObjReturn = null;
ACommandText = FormatQueryRDBMSSpecific(ACommandText);
if (TLogging.DL >= DBAccess.DB_DEBUGLEVEL_TRACE)
{
TLogging.Log("Query formatted for PostgreSQL: " + ACommandText);
}
#if WITH_POSTGRESQL_LOGGING
if (FDebugLevel >= DB_DEBUGLEVEL_TRACE)
{
NpgsqlEventLog.Level = LogLevel.Debug;
NpgsqlEventLog.LogName = NPGSQL_LOGFILENAME;
TLogging.Log("NpgsqlEventLog.LogName: " + NpgsqlEventLog.LogName);
}
#endif
NpgsqlParameter[] NpgsqlParametersArray = null;
if ((AParametersArray != null) &&
(AParametersArray.Length > 0))
{
// Check for characters that indicate a parameter in query text
if (ACommandText.IndexOf('?') == -1)
{
foreach (DbParameter param in AParametersArray)
{
if (string.IsNullOrEmpty(param.ParameterName))
{
throw new EDBParameterisedQueryMissingParameterPlaceholdersException(
"Question marks (?) must be present in query text if nameless Parameters are passed in");
}
}
}
if (AParametersArray != null)
{
NpgsqlParametersArray = (NpgsqlParameter[])ConvertOdbcParameters(AParametersArray, ref ACommandText);
}
}
ObjReturn = new NpgsqlCommand(ACommandText, (NpgsqlConnection)AConnection);
if (NpgsqlParametersArray != null)
{
// add parameters
foreach (DbParameter param in NpgsqlParametersArray)
{
ObjReturn.Parameters.Add(param);
}
}
return(ObjReturn);
}
/// <summary>
/// Creates a DbCommand object.
/// This formats the sql query for SQLite, and transforms the parameters.
/// </summary>
/// <param name="ACommandText"></param>
/// <param name="AConnection"></param>
/// <param name="AParametersArray"></param>
/// <param name="ATransaction"></param>
/// <returns>Instantiated SqliteCommand.</returns>
public DbCommand NewCommand(ref string ACommandText, DbConnection AConnection, DbParameter[] AParametersArray, TDBTransaction ATransaction)
{
DbCommand ObjReturn = null;
ACommandText = FormatQueryRDBMSSpecific(ACommandText);
if (TLogging.DL >= DBAccess.DB_DEBUGLEVEL_TRACE)
{
TLogging.Log("Query formatted for SQLite: " + ACommandText);
}
SqliteParameter[] SQLiteParametersArray = null;
if ((AParametersArray != null) &&
(AParametersArray.Length > 0))
{
if (AParametersArray != null)
{
SQLiteParametersArray = (SqliteParameter[])ConvertOdbcParameters(AParametersArray, ref ACommandText);
}
}
ObjReturn = ((SqliteConnection)AConnection).CreateCommand();
((SqliteCommand)ObjReturn).CommandText = ACommandText;
if (SQLiteParametersArray != null)
{
// add parameters
foreach (DbParameter param in SQLiteParametersArray)
{
ObjReturn.Parameters.Add(param);
}
}
return(ObjReturn);
}
/// <summary>
/// Creates a DbCommand object.
/// This formats the sql query for MySQL, and transforms the parameters.
/// </summary>
/// <param name="ACommandText"></param>
/// <param name="AConnection"></param>
/// <param name="AParametersArray"></param>
/// <param name="ATransaction"></param>
/// <returns>Instantiated MySqlCommand.</returns>
public DbCommand NewCommand(ref string ACommandText, DbConnection AConnection, DbParameter[] AParametersArray, TDBTransaction ATransaction)
{
DbCommand ObjReturn = null;
ACommandText = FormatQueryRDBMSSpecific(ACommandText);
if (TLogging.DL >= DBAccess.DB_DEBUGLEVEL_TRACE)
{
TLogging.Log("Query formatted for MySQL: " + ACommandText);
}
MySqlParameter[] MySQLParametersArray = null;
if ((AParametersArray != null) &&
(AParametersArray.Length > 0))
{
// Check for characters that indicate a parameter in query text
if (ACommandText.IndexOf('?') == -1)
{
foreach (DbParameter param in AParametersArray)
{
if (string.IsNullOrEmpty(param.ParameterName))
{
throw new EDBParameterisedQueryMissingParameterPlaceholdersException(
"Question marks (?) must be present in query text if nameless Parameters are passed in");
}
}
}
if (AParametersArray != null)
{
MySQLParametersArray = (MySqlParameter[])ConvertOdbcParameters(AParametersArray, ref ACommandText);
}
}
ObjReturn = new MySqlCommand(ACommandText, (MySqlConnection)AConnection);
ObjReturn.CommandTimeout = TAppSettingsManager.GetInt32("Server.DBCommandTimeout", 3600);
if (MySQLParametersArray != null)
{
// add parameters
foreach (DbParameter param in MySQLParametersArray)
{
ObjReturn.Parameters.Add(param);
}
}
return(ObjReturn);
}
/// <summary>
/// Creates a DbCommand object.
/// This formats the sql query for Progress ODBC, and transforms the parameters.
/// </summary>
/// <param name="ACommandText"></param>
/// <param name="AConnection"></param>
/// <param name="AParametersArray"></param>
/// <param name="ATransaction"></param>
/// <returns>Instantiated OdbcCommand.</returns>
public DbCommand NewCommand(ref string ACommandText, DbConnection AConnection, DbParameter[] AParametersArray, TDBTransaction ATransaction)
{
DbCommand ObjReturn = null;
ACommandText = FormatQueryRDBMSSpecific(ACommandText);
if (ATransaction == null)
{
ObjReturn = new OdbcCommand(ACommandText, (OdbcConnection)AConnection);
}
else
{
ObjReturn = new OdbcCommand(ACommandText, (OdbcConnection)AConnection, (OdbcTransaction)ATransaction.WrappedTransaction);
}
if (AParametersArray != null)
{
// add parameters
foreach (DbParameter param in AParametersArray)
{
ObjReturn.Parameters.Add(param);
}
}
return(ObjReturn);
}