/// <summary> /// Executes a Oracle query that returns a single scalar value as the result. /// </summary> /// <param name="commandText">The Oracle query to execute</param> /// <param name="parameters">Optional parameters to pass to the query</param> /// <returns></returns> public object QueryValue(string commandText, IEnumerable parameters) { object result; if (string.IsNullOrEmpty(commandText)) { throw new ArgumentException("Command text cannot be null or empty."); } try { EnsureConnectionOpen(); var command = CreateCommand(commandText, parameters); command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); result = command.Parameters["PA_CODIGO_ERROR"].Value; } catch (Exception ex) { FuncionesApoyo.LogEnArchivo(ex); result = null; } finally { EnsureConnectionClosed(); } return(result); }
public IEnumerable QueryValueList(string commandText, IEnumerable parameters) { if (string.IsNullOrEmpty(commandText)) { throw new ArgumentException("Command text cannot be null or empty."); } try { EnsureConnectionOpen(); var command = CreateCommand(commandText, parameters); command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.Data); FuncionesApoyo.LogEnArchivo(ex); return(parameters); } finally { EnsureConnectionClosed(); } return(parameters); }
/// <summary> /// Executes a non-query Oracle statement /// </summary> /// <param name="commandText">The Oracle query to execute</param> /// <param name="parameters">Optional parameters to pass to the query</param> /// <returns>The count of records affected by the Oracle statement</returns> public int Execute(string commandText, IEnumerable parameters) { int result; if (string.IsNullOrEmpty(commandText)) { throw new ArgumentException("Command text cannot be null or empty."); } try { EnsureConnectionOpen(); var command = CreateCommand(commandText, parameters); command.CommandType = CommandType.StoredProcedure; result = command.ExecuteNonQuery(); } catch (Exception ex) { FuncionesApoyo.LogEnArchivo(ex); result = -1; } finally { _connection.Close(); } return(result); }
/// <summary> /// Executes a SQL query that returns a list of rows as the result. /// </summary> /// <param name="commandText">The Oracle query to execute</param> /// <param name="parameters">Parameters to pass to the Oracle query</param> /// <returns>A list of a Dictionary of Key, values pairs representing the /// ColumnName and corresponding value</returns> public List <Dictionary <string, string> > Query(string commandText, IEnumerable parameters) { List <Dictionary <string, string> > rows; if (string.IsNullOrEmpty(commandText)) { throw new ArgumentException("Command text cannot be null or empty."); } try { EnsureConnectionOpen(); var command = CreateCommand(commandText, parameters); command.CommandTimeout = 30; using (var reader = command.ExecuteReader()) { rows = new List <Dictionary <string, string> >(); while (reader.Read()) { var row = new Dictionary <string, string>(); for (var i = 0; i < reader.FieldCount; i++) { var columnName = reader.GetName(i); var columnValue = reader.IsDBNull(i) ? null : reader.GetValue(i).ToString(); row.Add(columnName, columnValue); } rows.Add(row); } } } catch (Exception ex) { FuncionesApoyo.LogEnArchivo(ex); rows = null; } finally { EnsureConnectionClosed(); } return(rows); }
public int QueryTextValueInt(string commandText, IEnumerable parameters) { int result = -200; if (string.IsNullOrEmpty(commandText)) { throw new ArgumentException("Command text cannot be null or empty."); } try { EnsureConnectionOpen(); var command = CreateCommand(commandText, parameters); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); if (parameters != null) { foreach (OracleParameter p in parameters) { if (p.Direction == ParameterDirection.ReturnValue) { result = int.Parse(p.Value.ToString()); } } } } catch (Exception ex) { Console.WriteLine(ex.Data + " " + ex.InnerException); FuncionesApoyo.LogEnArchivo(ex); return(result); } finally { EnsureConnectionClosed(); } return(result); }
/// <summary> /// Executes a Oracle query that returns a single scalar value as the result. /// </summary> /// <param name="commandText">The Oracle query to execute</param> /// <param name="parameters">Optional parameters to pass to the query</param> /// <returns></returns> public int ExecProcResult(string commandText, IEnumerable parameters) { int result = -200; string nameOutputParameter = ""; if (string.IsNullOrEmpty(commandText)) { throw new ArgumentException("Command text cannot be null or empty."); } try { foreach (OracleParameter p in parameters) { if (p.Direction == ParameterDirection.Output) { nameOutputParameter = p.ParameterName; } } EnsureConnectionOpen(); var command = CreateCommand(commandText, parameters); command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); result = int.Parse(command.Parameters[nameOutputParameter].Value.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Data); FuncionesApoyo.LogEnArchivo(ex); } finally { EnsureConnectionClosed(); } return(result); }