/// <summary>
/// Get the current database date/time
/// </summary>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The current database date/time</returns>
public static DateTime GetDateTime(DBOracleConnection connection = null, string connectionStringName = null)
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbOracleMedatadataDAL.GetDateTime());
}
}
/// <summary>
/// Creates and runs a command to retrieve a DataTable using a stored procedure
/// </summary>
/// <param name="commandText">Stored procedure name</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>DataTable with the query results</returns>
public static DataTable RetrieveDataTableSP(string commandText, DBOracleConnection connection = null, string connectionStringName = null, params DBOracleParameter[] parameters)
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbOracleMedatadataDAL.RetrieveDataTableSP(commandText: commandText, parameters: parameters));
}
}
/// <summary>
/// Get the names of the indexes for a specified owner
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>List of index names for the specified owner</returns>
public static Dictionary <string, string> GetIndexes(string owner, DBOracleConnection connection = null, string connectionStringName = null)
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbOracleMedatadataDAL.GetIndexes(owner: owner));
}
}
/// <summary>
/// Get the creation script for an index
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="indexName">Index name</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The creation script for the specified index</returns>
public static string GetCreationScriptIndex(string owner, string indexName, DBOracleConnection connection = null, string connectionStringName = null)
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbOracleMedatadataDAL.GetCreationScriptIndex(owner: owner, indexName: indexName));
}
}
/// <summary>
/// Creates and runs a command to execute a command text
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Number of affected rows</returns>
internal static int ExecuteNonQuery(DBOracleConnection connection, string commandText, bool isStoredProcedure, params DBOracleParameter[] parameters)
{
try
{
using (var command = new DBOracleCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
return(command.Command.ExecuteNonQuery());
}
}
catch (Exception ex)
{
ex.ConvertOracleException(isConnecting: false);
throw;
}
}
/// <summary>
/// Create a new prepared statement (used by DBOracleBaseDAL)
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
internal DBOraclePreparedStatement(DBOracleConnection connection, string commandText, bool isStoredProcedure, params DBOracleParameter[] parameters)
{
try
{
Command = DBOracleCommand.CreatePreparedStatement(
connection: connection,
commandText: commandText,
isStoredProcedure: isStoredProcedure,
parameters: parameters);
}
catch (Exception ex)
{
ex.ConvertOracleException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates a disposable container with a database connection, creating a new connection if necessary
/// </summary>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
protected static T Create <T>(DBOracleConnection connection, string connectionStringName) where T : DBOracleConnection
{
var newConnection = (T)Activator.CreateInstance(type: typeof(T), nonPublic: true);
if (connection is null)
{
newConnection.DisposeConnection = true;
newConnection.Connection = DBOracleConnectionInstance.Open(connectionStringName);
}
else
{
newConnection.DisposeConnection = false;
newConnection.Connection = connection.Connection;
}
return(newConnection);
}
/// <summary>
/// Creates a disposable container with a database connection, creating a new connection if necessary
/// </summary>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
public static DBOracleConnection Create(DBOracleConnection connection, string connectionStringName)
{
var newConnection = new DBOracleConnection();
if (connection is null)
{
newConnection.DisposeConnection = true;
newConnection.Connection = DBOracleConnectionInstance.Open(connectionStringName);
}
else
{
newConnection.DisposeConnection = false;
newConnection.Connection = connection.Connection;
}
return(newConnection);
}
/// <summary>
/// Creates a new prepared statement (used by DBOraclePreparedStatement)
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
internal static DBOracleCommand CreatePreparedStatement(DBOracleConnection connection, string commandText, bool isStoredProcedure, params DBOracleParameter[] parameters)
{
DBOracleCommand command = null;
try
{
command = new DBOracleCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters);
command.Command.Prepare();
return(command);
}
catch (Exception ex)
{
command?.Close();
ex.ConvertOracleException(isConnecting: false);
throw;
}
}
/// <summary>
/// Get next value from a database sequence
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="sequenceName">Sequence name</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The next value from a database sequence</returns>
public static decimal GetSequenceNextValue(string owner, string sequenceName, DBOracleConnection connection = null, string connectionStringName = null)
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbOracleMedatadataDAL.GetSequenceNextValue(owner: owner, sequenceName: sequenceName));
}
}
/// <summary>
/// Get the creation script for an object
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="objectName">Object name</param>
/// <param name="objectType">Object type (see Oracle documentation for valid values)</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The creation script for the specified object</returns>
public static string GetCreationScript(string owner, string objectName, string objectType, DBOracleConnection connection = null, string connectionStringName = null)
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbOracleMedatadataDAL.GetCreationScript(owner: owner, objectName: objectName, objectType: objectType));
}
}
/// <summary>
/// Creates and runs a command to retrieve a DataTable using a command text
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>DataTable with the command text results</returns>
internal static DataTable GetDataTable(DBOracleConnection connection, string commandText, bool isStoredProcedure, params DBOracleParameter[] parameters)
{
try
{
using (var command = new DBOracleCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
OracleDataAdapter dataAdapter;
try
{
dataAdapter = new OracleDataAdapter(command.Command);
}
catch (Exception ex)
{
try
{
Log.Debug(DBOracleLocalizedText.DBOracleCommand_GetDataSet_DataAdapterError, ex);
}
catch (Exception)
{
// Nothing to do
}
throw;
}
bool suppressDisposeException = false;
try
{
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
return(dataTable);
}
catch (Exception ex)
{
try
{
Log.Debug(DBOracleLocalizedText.DBOracleCommand_GetDataSet_DataRetrieveError, ex);
}
catch (Exception)
{
// Nothing to do
}
suppressDisposeException = true;
throw;
}
finally
{
try
{
dataAdapter.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBOracleLocalizedText.DBOracleCommand_DataAdapter_Disposal_Error, ex);
}
catch (Exception)
{
// Nothing to do
}
if (!suppressDisposeException)
{
throw;
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertOracleException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates and runs a command to execute a query command text that returns a single row
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="connection">Database connection</param>
/// <param name="dataFiller">Function to convert the returned row into the return type object</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="nullValue">Value to be returned if the query command text does not return any row</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Command text result, or nullValue if none</returns>
internal static T RetrieveDataItem <T>(DBOracleConnection connection, Func <DBOracleDataReader, T> dataFiller, string commandText, bool isStoredProcedure, T nullValue = default(T), params DBOracleParameter[] parameters)
{
try
{
using (var command = new DBOracleCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
var oracleDataReader = command.Command.ExecuteReader(behavior: CommandBehavior.SingleRow);
DBOracleDataReader dataReader = null;
try
{
dataReader = new DBOracleDataReader(oracleDataReader);
return((oracleDataReader.Read())
? dataFiller(dataReader)
: nullValue);
}
finally
{
dataReader?.Cleanup();
try
{
oracleDataReader?.Close();
}
catch (Exception ex)
{
try
{
Log.Error(DBOracleLocalizedText.DBOracleCommand_CloseDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
try
{
oracleDataReader?.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBOracleLocalizedText.DBOracleCommand_DisposeDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertOracleException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates and runs a command to execute a command text that returns multiple rows
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="connection">Database connection</param>
/// <param name="dataFiller">Function to convert the returned rows into the return type object</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>List of objects of the return type</returns>
internal static List <T> RetrieveDataList <T>(DBOracleConnection connection, Func <DBOracleDataReader, T> dataFiller, string commandText, bool isStoredProcedure, params DBOracleParameter[] parameters)
{
try
{
using (var command = new DBOracleCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
var oracleDataReader = command.Command.ExecuteReader();
DBOracleDataReader dataReader = null;
List <T> rowList = null;
try
{
dataReader = new DBOracleDataReader(oracleDataReader);
rowList = new List <T>();
while (oracleDataReader.Read())
{
rowList.Add(dataFiller(dataReader));
}
return(rowList);
}
catch (Exception)
{
rowList?.Clear(); // This method fills the internal array with zeros to help the gc
throw;
}
finally
{
dataReader?.Cleanup();
try
{
oracleDataReader?.Close();
}
catch (Exception ex)
{
try
{
Log.Error(DBOracleLocalizedText.DBOracleCommand_CloseDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
try
{
oracleDataReader?.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBOracleLocalizedText.DBOracleCommand_DisposeDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertOracleException(isConnecting: false);
throw;
}
}
/// <summary>
/// Get the length of a database column
/// </summary>
/// <param name="owner">Database owner</param>
/// <param name="tableName">Table name</param>
/// <param name="columnName">Column name</param>
/// <param name="useCache">Use column length cache (default is true, use false only to detect changes in the database structure)</param>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
/// <returns>The length of the database column; null if the owner, the table or the column does not exist</returns>
public static int?GetColumnLength(string owner, string tableName, string columnName, bool useCache = true, DBOracleConnection connection = null, string connectionStringName = null)
{
if (!useCache)
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
return(dbOracleMedatadataDAL.GetColumnLength(owner: owner, tableName: tableName, columnName: columnName));
}
}
owner = owner.ToUpper();
if (!GetColumnLength_Cache.TryGetValue(owner, out var ownerCache))
{
using (var dbOracleMedatadataDAL = DBOracleMetadataDAL.Create(connection: connection, connectionStringName: connectionStringName))
{
ownerCache = dbOracleMedatadataDAL.GetColumnLengthTable(owner);
}
lock (GetColumnLength_Lock)
{
if (!GetColumnLength_Cache.TryGetValue(owner, out var ownerCacheAux))
{
var newCache = new Dictionary <string, Dictionary <string, Dictionary <string, int> > >(GetColumnLength_Cache);
newCache[owner] = ownerCache;
GetColumnLength_Cache = newCache;
}
}
}
return(ownerCache.GetOrDefault(tableName.ToUpper())
?.GetOrDefault(columnName.ToUpper()));
}
/// <summary>
/// Creates a new DBOracleMetadataDAL, opening a new connection if necessary
/// </summary>
/// <param name="connection">Database connection (null for a new connection)</param>
/// <param name="connectionStringName">Connection string name (must not be null if connection is null)</param>
public new static DBOracleMetadataDAL Create(DBOracleConnection connection, string connectionStringName)
{
return(Create <DBOracleMetadataDAL>(connection: connection, connectionStringName: connectionStringName));
}
private DBOracleCommand(DBOracleConnection connection, string commandText, bool isStoredProcedure, params DBOracleParameter[] parameters)
{
if (connection is null)
{
throw new ArgumentNullException(DBOracleLocalizedText.DBOracleCommand_NullConnection);
}
try
{
Command = connection.Connection.Connection.CreateCommand();
Command.Transaction = connection.Connection.Transaction;
Command.BindByName = true;
Command.CommandType = (isStoredProcedure) ? CommandType.StoredProcedure : CommandType.Text;
Command.CommandText = commandText;
}
catch (Exception ex)
{
try
{
Log.Debug(DBOracleLocalizedText.DBOracleCommand_Error, ex);
}
catch (Exception)
{
// Nothing to do
}
Close();
throw;
}
string lastParameterName = null;
try
{
foreach (DBOracleParameter parameter in parameters)
{
if (parameter is null)
{
try
{
Log.Error(string.Concat(DBOracleLocalizedText.DBOracleCommand_NullParameter, Environment.NewLine, Environment.StackTrace));
}
catch (Exception)
{
// Nothing to do
}
continue;
}
lastParameterName = parameter.Parameter.ParameterName;
Command.Parameters.Add(parameter.Parameter);
}
}
catch (Exception ex)
{
try
{
Log.Debug(string.Format(DBOracleLocalizedText.DBOracleCommand_BindError, lastParameterName), ex);
}
catch (Exception)
{
// Nothing to do
}
Close();
throw;
}
}
