/// <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; } }