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