public List <E_Ruta> ListarRutasDisponibles(string buscar)
{
conexion.Open();
OracleCommand cmd = conexion.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_CARGARRUTADISPONIBLE";
OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter("v_ruta", buscar);
cmd.Parameters.Add(par2);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader read = cursor.GetDataReader();
List <E_Ruta> Listar = new List <E_Ruta>();
while (read.Read())
{
Listar.Add(new E_Ruta
{
Id_ruta = read.GetInt32(0),
Ruta = read.GetString(1)
});
}
conexion.Close();
return(Listar);
}
private bool ReasonsFromCursor(OracleRefCursor cursor, ref List <sRejectReasons> reasons)
{
if (cursor is System.DBNull)
{
_error = "No rejection reasons given.";
return(false);
}
OracleDataReader rd = cursor.GetDataReader();
sRejectReasons rsn;
while (rd.Read())
{
rsn.mID = (int)rd.GetDecimal(0);
rsn.mDescription = rd.GetString(1);
reasons.Add(rsn);
}
rd.Close();
rd.Dispose();
if (reasons.Count <= 0)
{
_error = "No reject reasons found, contact helpdesk.";
return(false);
}
return(true);
}
public List <E_ChoferCompleto> CargarChoferDisponible(string buscar)
{
conexion.Open();
OracleCommand cmd = conexion.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_CARGARCHOFERDISPONIBLE";
OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter("v_nombre", buscar);
cmd.Parameters.Add(par2);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader read = cursor.GetDataReader();
List <E_ChoferCompleto> Listar = new List <E_ChoferCompleto>();
while (read.Read())
{
Listar.Add(new E_ChoferCompleto
{
Cedula = read.GetString(0),
Nombre = read.GetString(1),
Apellido = read.GetString(2),
Fecha_nacimiento = read.GetDateTime(3)
});
}
conexion.Close();
return(Listar);
}
private bool TypesFromCursor(OracleRefCursor cursor, ref List <sCaptureTypes> types)
{
// Types may be none for test photos
//if (cursor is System.DBNull)
//{
// _error = "No vehicle types given.";
// return false;
//}
if (cursor is System.DBNull)
{
return(true);
}
OracleDataReader rd = cursor.GetDataReader();
sCaptureTypes typ;
while (rd.Read())
{
typ.mID = (int)rd.GetDecimal(0);
typ.mCode = (string)rd.GetString(1);
typ.mType = rd.GetString(2);
typ.mAmount = rd.GetDecimal(3);
typ.mDescription = rd.GetString(4);
typ.mBeskrywing = rd.GetString(5);
types.Add(typ);
}
rd.Close();
rd.Dispose();
//return (types.Count > 0); Types may be none for test photos
return(true);
}
internal DataSet GetSentResultForOutPut(Guid iroid)
{
OracleDataReader dataReader;
this.cmd.Parameters.Clear();
this.cmd.CommandType = CommandType.StoredProcedure;
this.cmd.CommandText = "PLM_DQ_DOSSIER.GetSentResultForOutPut";
this.cmd.Parameters.Add(":Iroid", OracleDbType.Raw).Value = iroid.ToByteArray();
OracleParameter parameter = this.cmd.Parameters.Add(":rSent", OracleDbType.RefCursor, ParameterDirection.Output);
OracleParameter parameter2 = this.cmd.Parameters.Add(":rReCover", OracleDbType.RefCursor, ParameterDirection.Output);
this.cmd.ExecuteNonQuery();
OracleRefCursor cursor = parameter.Value as OracleRefCursor;
OracleRefCursor cursor2 = parameter2.Value as OracleRefCursor;
DataTable table = new DataTable("FF");
DataTable table2 = new DataTable("HS");
DataSet set = new DataSet();
if (!cursor.IsNull)
{
dataReader = cursor.GetDataReader();
table.Load(dataReader);
set.Tables.Add(table);
}
if (!cursor2.IsNull)
{
dataReader = cursor2.GetDataReader();
table2.Load(dataReader);
set.Tables.Add(table2);
}
set.AcceptChanges();
return(set);
}
public virtual void GetAllCategories(out OracleRefCursor myrc)
{
myrc = null;
var result = ExecuteMethodCall(this, (MethodInfo)MethodBase.GetCurrentMethod(), myrc);
myrc = (OracleRefCursor)result.GetParameterValue(0);
}
public IDataReader ExecuteStoredProcedureWithResultSet(IDbCommand cmd, string readerParamName)
{
cmd.CommandType = CommandType.StoredProcedure;
// Due to a limitation of the .NET Oracle driver, the parameter names must not include the prefix
string paramPrefix = databaseServices.ExecutionService.ParameterPrefix;
for (int i = 0; i < cmd.Parameters.Count; i++)
{
IDbDataParameter parameter = (IDbDataParameter)cmd.Parameters[i];
parameter.ParameterName = FixParameterPrefixForStoredProcedure(parameter.ParameterName, paramPrefix);
}
if (readerParamName.IsNullOrEmpty())
{
return(ExecuteReader(cmd));
}
readerParamName = FixParameterPrefixForStoredProcedure(readerParamName, paramPrefix);
IDbDataParameter readerParam = new OracleParameter(readerParamName, OracleDbType.RefCursor);
SetParameterDirection(readerParam, ParameterDirection.Output);
cmd.Parameters.Add(readerParam);
ExecuteNonQuery(cmd);
OracleRefCursor rc = (OracleRefCursor)readerParam.Value;
return(rc.GetDataReader());
}
public override OracleDataReader ExecuteDataReaderRefCur(string procname, ProcParam procParam, int indexRefCur)
{
Stopwatch _stopwatch = null;
OracleDataReader reader = null;;
try
{
this.executionStartTime = DateTime.Now;
_stopwatch = new Stopwatch();
_stopwatch.Start();
OracleCommand cmd = base._getCommand(procParam);
_stopwatch.Stop();
OracleRefCursor refCur = (OracleRefCursor)procParam.Parameters[indexRefCur].Value;
reader = refCur.GetDataReader();
this.executionStopTime = DateTime.Now;
}
catch (Exception ex)
{
base.lastException = ex;
throw ex;
}
finally
{
base.ExecuteTime = this.executionStopTime - this.executionStartTime;
_stopwatch = null;
}
return(reader);
}
internal DataSet GetTSDForPrint(Guid useroid, string docId, string wkName)
{
this.cmd.Parameters.Clear();
this.cmd.CommandType = CommandType.StoredProcedure;
this.cmd.Parameters.Add(":DocId", OracleDbType.Varchar2).Value = string.IsNullOrEmpty(docId) ? "" : docId;
this.cmd.Parameters.Add(":wkName", OracleDbType.Varchar2).Value = string.IsNullOrEmpty(wkName) ? "" : wkName;
this.cmd.Parameters.Add(":useroid", OracleDbType.Raw).Value = useroid.ToByteArray();
OracleParameter parameter = this.cmd.Parameters.Add(":rTsd", OracleDbType.RefCursor, ParameterDirection.Output);
this.cmd.CommandText = "PLM_DQ_DOSSIER.GetPrintQuick";
this.cmd.Prepare();
this.cmd.ExecuteNonQuery();
OracleRefCursor cursor = parameter.Value as OracleRefCursor;
DataTable table = new DataTable("TSD");
if (!cursor.IsNull)
{
table.Load(cursor.GetDataReader());
}
DataSet set = new DataSet();
set.Tables.Add(table);
set.AcceptChanges();
return(set);
}
private bool OfficersFromCursor(OracleRefCursor cursor, ref List <sOfficerInfo> officers)
{
if (cursor is System.DBNull)
{
_error = "No officers given.";
return(false);
}
OracleDataReader rd = cursor.GetDataReader();
sOfficerInfo off;
while (rd.Read())
{
off.mID = (int)rd.GetDecimal(0);
off.mExternID = rd.GetString(1);
off.mName = rd.GetString(2);
off.mSurname = rd.GetString(3);
off.mCredentialID = (long)rd.GetDecimal(4);
officers.Add(off);
}
rd.Close();
rd.Dispose();
if (officers.Count <= 0)
{
_error = "No officers loaded, contact helpdesk.";
return(false);
}
return(true);
}
private bool VehicleFromCursor(OracleRefCursor cursor, ref sCaseInfo vehicle)
{
if (cursor is System.DBNull)
{
_error = "No vehicle info given.";
return(false);
}
OracleDataReader rd = cursor.GetDataReader();
vehicle.mOffencePlace = vehicle.mOffenceDate = string.Empty;
if (rd.Read())
{
vehicle.mOffenceSpeed = (int)rd.GetDecimal(0);
vehicle.mOffenceZone = (int)rd.GetDecimal(1);
vehicle.mOffenceDate = rd.GetString(2);
vehicle.mOffencePlace = rd.GetString(3);
vehicle.mPrevRejectID = (int)rd.GetDecimal(4);
vehicle.mVehicleRegNo = rd.IsDBNull(5) ? string.Empty : rd.GetString(5);
}
rd.Close();
rd.Dispose();
if ((vehicle.mOffencePlace.Length <= 0) || (vehicle.mOffenceDate.Length <= 0))
{
_error = "No vehicle data loaded, contact helpdesk.";
return(false);
}
return(true);
}
public void GetCategoryAndProducts(out OracleRefCursor cursor1, out OracleRefCursor cursor2)
{
cursor1 = null;
cursor2 = null;
var result = ExecuteMethodCall(this, (MethodInfo)MethodBase.GetCurrentMethod(), cursor1, cursor2);
cursor1 = (OracleRefCursor)result.GetParameterValue(0);
cursor2 = (OracleRefCursor)result.GetParameterValue(1);
}
protected override void FromOracleParamInternal(OracleParameter param)
{
if (IsNull(param.Value) == true)
{
ParamValue.Clear();
}
else
{
ParamValue.Clear();
OracleRefCursor cursor = (OracleRefCursor)param.Value;
OracleDataReader reader = cursor.GetDataReader();
ParamValue.Load(reader);
}
}
public IList <User> GetUsers()
{
OracleCommand cmd = new OracleCommand("get_function.get_users", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
cmd.Parameters.Add(new OracleParameter("v_users", OracleDbType.RefCursor, ParameterDirection.Output));
cmd.Parameters["v_users"].Direction = ParameterDirection.ReturnValue;
OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd);
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
OracleRefCursor t = (OracleRefCursor)cmd.Parameters[0].Value;
OracleDataReader rd = t.GetDataReader();
var list = new List <User>();
while (rd.Read())
{
list.Add(new User()
{
Id = rd.GetInt32(0),
Email = rd.GetString(1),
FirstName = rd.GetString(2),
LastName = rd.GetString(3),
PhoneNumber = rd.GetString(4),
Password = rd.GetString(5)
});
}
return(list);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return(null);
}
finally
{
DataBaseConnection.GetDbInstance().CloseDBConnection();
}
}
public User GetUserByEmailAndPassword(string email, string password)
{
OracleCommand cmd = new OracleCommand("user_methods.user_login", connection)
{
CommandType = CommandType.StoredProcedure
};
OracleParameter v_attraction_type = new OracleParameter("v_user",
OracleDbType.RefCursor, ParameterDirection.ReturnValue);
cmd.Parameters.Add(v_attraction_type);
cmd.Parameters.Add("v_email", OracleDbType.Varchar2, ParameterDirection.Input).Value = email;
cmd.Parameters.Add("v_password", OracleDbType.Varchar2, ParameterDirection.Input).Value = password;
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
OracleRefCursor t = (OracleRefCursor)cmd.Parameters["v_user"].Value;
OracleDataReader rd = t.GetDataReader();
if (rd.Read())
{
User user = new User()
{
Id = rd.GetInt32(0),
FirstName = rd.GetString(1),
LastName = rd.GetString(2),
Email = rd.GetString(3),
PhoneNumber = rd.GetString(5)
};
return(user);
}
return(null);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
}
finally
{
DataBaseConnection.GetDbInstance().CloseDBConnection();
}
return(null);
}
public IList <AttractionType> GetAttractionTypes()
{
OracleCommand cmd = new OracleCommand("get_function.get_attractiontype", connection)
{
CommandType = System.Data.CommandType.StoredProcedure
};
cmd.Parameters.Add(new OracleParameter("l_rc", OracleDbType.RefCursor, ParameterDirection.Output));
cmd.Parameters["l_rc"].Direction = ParameterDirection.ReturnValue;
OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd);
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
OracleRefCursor t = (OracleRefCursor)cmd.Parameters[0].Value;
OracleDataReader rdr = t.GetDataReader();
var list = new List <AttractionType>();
while (rdr.Read())
{
System.Diagnostics.Debug.WriteLine(rdr.GetInt32(0) + rdr.GetString(1) + rdr.GetString(2) + rdr.GetString(3));
list.Add(new AttractionType()
{
Id = rdr.GetInt32(0),
Title = rdr.GetString(1),
Description = rdr.GetString(2),
ImagePath = rdr.GetString(3)
});
}
return(list);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
return(null);
}
finally
{
DataBaseConnection.GetDbInstance().CloseDBConnection();
}
}
public static List <ADQ_COMUN> GenerarOracleCommandCursor_Combo(string SP, string[] Parametros, object[] Valores)
{
Conexion objConexion = new Conexion();
OracleConnection cn = objConexion.getConexion();
cn.Open();
OracleCommand cmd = cn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = SP;
OracleParameter par1 = new OracleParameter();
if (Parametros != null)
{
for (int i = 0; i <= Parametros.Length - 1; i++)
{
cmd.Parameters.Add(Parametros[i], OracleDbType.Varchar2).Value = Valores[i];
}
}
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(par1);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader dr = cursor.GetDataReader();
List <ADQ_COMUN> listarCombo = new List <ADQ_COMUN>();
while (dr.Read())
{
ADQ_COMUN objCombo = new ADQ_COMUN();
objCombo.ID = Convert.ToString(dr[0]);
objCombo.DESCRIPCION = Convert.ToString(dr[1]);
listarCombo.Add(objCombo);
}
cn.Close();
par1.Dispose();
cmd.Dispose();
cn.Dispose();
objConexion = null;
return(listarCombo);
}
// カーソルを取得
private void ResetCursor()
{
if (cursor != null)
{
cursor.Dispose();
}
command.CommandText = "BEGIN OPEN :1 FOR SELECT * FROM (" + Sql + "); end;";
OracleParameter p_rc = command.Parameters.Add(
"p_rc",
OracleDbType.RefCursor,
DBNull.Value,
ParameterDirection.Output);
command.ExecuteNonQuery();
command.Parameters.Clear();
cursor = p_rc.Value as OracleRefCursor;
}
public AttractionType GetAttractionTypeByTitle(string title)
{
OracleCommand cmd = new OracleCommand("get_function.get_attractiontypedata", connection)
{
CommandType = CommandType.StoredProcedure
};
OracleParameter v_attraction_type = new OracleParameter("v_attraction_type",
OracleDbType.RefCursor, ParameterDirection.ReturnValue);
cmd.Parameters.Add(v_attraction_type);
cmd.Parameters.Add("v_title", OracleDbType.Varchar2, ParameterDirection.Input).Value = title;
try
{
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
cmd.ExecuteNonQuery();
OracleRefCursor t = (OracleRefCursor)cmd.Parameters["v_attraction_type"].Value;
OracleDataReader rd = t.GetDataReader();
if (rd.Read())
{
AttractionType attraction = new AttractionType()
{
Id = rd.GetInt32(0),
Title = rd.GetString(1),
Description = rd.GetString(2),
ImagePath = rd.GetString(3)
};
return(attraction);
}
return(null);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
}
finally
{
DataBaseConnection.GetDbInstance().CloseDBConnection();
}
return(null);
}
public override DataSet ExecuteReader(string ParamOut, string TableSpaceName)
{
OracleDataReader reader = null;
DataSet ds = new DataSet();
try
{
OracleParameter paramReturn = new OracleParameter(ParamOut, OracleDbType.RefCursor, ParameterDirection.Output);
((OracleCommand)base.Command).Parameters.Add(paramReturn);
int result = ((OracleCommand)base.Command).ExecuteNonQuery();
OracleRefCursor refCur = (OracleRefCursor)paramReturn.Value;
reader = refCur.GetDataReader();
do
{
DataTable schema = reader.GetSchemaTable();
DataTable dt = new DataTable(TableSpaceName);
foreach (DataRow dr in schema.Rows)
{
dt.Columns.Add(new DataColumn(dr["ColumnName"].ToString(), Type.GetType(dr["DataType"].ToString())));
}
while (reader.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dr[i] = (Object)reader.GetValue(i);
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
} while (reader.NextResult());
}
catch (Exception ex)
{
LoggingHelper.Debug("-----Oracle ExecuteReader with ParamOut Error----");
LoggingHelper.Debug(ex.Message);
return(null);
}
return(ds);
}
public List <Empleado> getAllEmpleados()
{
// Realizar la conexion a la base de datos
Conexion.Conexion extD11 = new Conexion.Conexion();
OracleConnection cone = extD11.getConexion();
cone.Open(); // Abre la conexion a la base de datos
OracleCommand cmd = cone.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "wilson1.pgk_municipalidad_empleados.sp_get_all_empleados";
OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader dr = cursor.GetDataReader();
List <Empleado> listEmpleado = new List <Empleado>();
while (dr.Read())
{
Empleado emp = new Empleado
{
cedula = Convert.ToInt32(dr["cedula"]),
nombre = Convert.ToString(dr["nombre"]),
apellido = Convert.ToString(dr["apellido"]),
telefono = Convert.ToString(dr["telefono"]),
direccion = Convert.ToString(dr["direccion"]),
salario = Convert.ToInt32(dr["salario"]),
id_cargo = Convert.ToInt32(dr["id_cargo"]),
id_municipalidad = Convert.ToInt32(dr["id_municipalidad"]),
};
listEmpleado.Add(emp);
}
cone.Close();
par1.Dispose();
cmd.Dispose();
cone.Dispose();
return(listEmpleado);
}
internal DataSet GetBpmNameByUserOid(Guid useroid)
{
this.cmd.Parameters.Clear();
this.cmd.CommandType = CommandType.StoredProcedure;
this.cmd.CommandText = "PLM_DQ_DOSSIER.GetBpmNameByUserOid";
this.cmd.Parameters.Add(":userOid ", OracleDbType.Raw).Value = useroid.ToByteArray();
OracleParameter parameter = this.cmd.Parameters.Add(":rGetBpmName", OracleDbType.RefCursor, ParameterDirection.Output);
this.cmd.Prepare();
this.cmd.ExecuteNonQuery();
OracleRefCursor cursor = parameter.Value as OracleRefCursor;
DataSet set = new DataSet();
OracleDataReader dataReader = cursor.GetDataReader();
DataTable table = new DataTable("BPM");
table.Load(dataReader);
set.Tables.Add(table);
return(set);
}
internal DataSet GetDrawingForTsOutput(Guid iroid)
{
this.cmd.Parameters.Clear();
this.cmd.CommandType = CommandType.StoredProcedure;
this.cmd.Parameters.Add(":Iroid", OracleDbType.Raw).Value = iroid.ToByteArray();
OracleParameter parameter = this.cmd.Parameters.Add(":rTsd", OracleDbType.RefCursor, ParameterDirection.Output);
this.cmd.CommandText = "PLM_DQ_DOSSIER.GetDrawingForTsOutput";
this.cmd.Prepare();
this.cmd.ExecuteNonQuery();
OracleRefCursor cursor = parameter.Value as OracleRefCursor;
DataSet set = new DataSet();
DataTable table = new DataTable("TSD");
table.Load(cursor.GetDataReader());
set.Tables.Add(table);
set.AcceptChanges();
return(set);
}
public List <Municipalidad> getAllMunicipalidades()
{
// Realizar la conexion a la base de datos
Conexion.Conexion extD11 = new Conexion.Conexion();
OracleConnection cone = extD11.getConexion();
cone.Open(); // Abre la conexion a la base de datos
OracleCommand cmd = cone.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "wilson1.pgk_municipalidad_empleados.sp_get_all_municipalidades";
OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader dr = cursor.GetDataReader();
List <Municipalidad> listMunicipalidades = new List <Municipalidad>();
while (dr.Read())
{
Municipalidad muni = new Municipalidad
{
id_municipalidad = Convert.ToInt32(dr["id_municipalidad"]),
nombre_municipalidad = Convert.ToString(dr["nombre_municipalidad"]),
direccion = Convert.ToString(dr["direccion"]),
telefono = Convert.ToString(dr["telefono"]),
correo_electronico = Convert.ToString(dr["correo_electronico"]),
id_ciudad = Convert.ToInt32(dr["id_ciudad"])
};
listMunicipalidades.Add(muni);
}
cone.Close();
par1.Dispose();
cmd.Dispose();
cone.Dispose();
return(listMunicipalidades);
}
private bool FileNumbersFromCursor(OracleRefCursor cursor, ref List <int> fileNumbers)
{
if (cursor is System.DBNull)
{
_error = "No file numbers given.";
return(false);
}
OracleDataReader rd = cursor.GetDataReader();
while (rd.Read())
{
fileNumbers.Add((int)rd.GetDecimal(0));
}
rd.Close();
rd.Dispose();
return(fileNumbers.Count > 0);
}
/// <summary>
/// Get 3 Ref Cursors with a PL/SQL
/// </summary>
/// <param name="con"></param>
/// <returns></returns>
public static OracleRefCursor[] Get3RefCursors(OracleCommand cmd)
{
// 1. Get 3 OracleParameters as REF CURSORs
// Set the command
// Bind
// select * from multimedia_tab
OracleParameter p1 = cmd.Parameters.Add("refcursor1",
OracleDbType.RefCursor);
p1.Direction = ParameterDirection.ReturnValue;
// select * from emp
OracleParameter p2 = cmd.Parameters.Add("refcursor2",
OracleDbType.RefCursor);
p2.Direction = ParameterDirection.Output;
// select * from dept
OracleParameter p3 = cmd.Parameters.Add("refcursor3",
OracleDbType.RefCursor);
p3.Direction = ParameterDirection.Output;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e.Message);
}
OracleRefCursor[] refCursors = new OracleRefCursor[3];
refCursors[0] = (OracleRefCursor)p1.Value;
refCursors[1] = (OracleRefCursor)p2.Value;
refCursors[2] = (OracleRefCursor)p3.Value;
return(refCursors);
}
public override object ExecuteReader(string ParamOut)
{
OracleDataReader reader = null;
try
{
OracleParameter paramReturn = new OracleParameter(ParamOut, OracleDbType.RefCursor, ParameterDirection.Output);
((OracleCommand)base.Command).Parameters.Add(paramReturn);
int result = ((OracleCommand)base.Command).ExecuteNonQuery();
OracleRefCursor refCur = (OracleRefCursor)paramReturn.Value;
reader = refCur.GetDataReader();
}
catch (Exception ex)
{
LoggingHelper.Debug("-----Oracle ExecuteReader with ParamOut Error----");
LoggingHelper.Debug(ex.Message);
return(null);
}
return(reader);
}
public List <Ciudad> getAllCiudades()
{
// Realizar la conexion a la base de datos
Conexion.Conexion extD11 = new Conexion.Conexion();
OracleConnection cone = extD11.getConexion();
cone.Open(); // Abre la conexion a la base de datos
OracleCommand cmd = cone.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "wilson1.pgk_municipalidad_empleados.sp_get_all_ciudades";
OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader dr = cursor.GetDataReader();
List <Ciudad> listCiudades = new List <Ciudad>();
while (dr.Read())
{
Ciudad ciudad = new Ciudad
{
id_ciudad = Convert.ToInt32(dr["id_ciudad"]),
nombre = dr["nombre"].ToString(),
ubicacion_geografica = dr["ubicacion_geografica"].ToString()
};
listCiudades.Add(ciudad);
}
cone.Close();
par1.Dispose();
cmd.Dispose();
cone.Dispose();
return(listCiudades);
}
public List <E_Autobus> ListarAutobusAsignados(string buscar)
{
conexion.Open();
OracleCommand cmd = conexion.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_CARGARAUTOBUSASIGNADOS";
OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter("v_modelo", buscar);
cmd.Parameters.Add(par2);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader read = cursor.GetDataReader();
List <E_Autobus> Listar = new List <E_Autobus>();
while (read.Read())
{
Listar.Add(new E_Autobus
{
Id_autobus = read.GetInt32(0),
Cedula = read.GetString(1),
Nombre = read.GetString(2),
Apellido = read.GetString(3),
Marca = read.GetString(4),
Modelo = read.GetString(5),
Placa = read.GetString(6),
Color = read.GetString(7),
Año = read.GetString(8)
});
}
conexion.Close();
return(Listar);
}
public List <Cargo> getAllCargos()
{
// Realizar la conexion a la base de datos
Conexion.Conexion extD11 = new Conexion.Conexion();
OracleConnection cone = extD11.getConexion();
cone.Open(); // Abre la conexion a la base de datos
OracleCommand cmd = cone.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "wilson1.pgk_municipalidad_empleados.sp_get_all_cargos";
OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleDbType.RefCursor;
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
cmd.ExecuteNonQuery();
OracleRefCursor cursor = (OracleRefCursor)par1.Value;
OracleDataReader dr = cursor.GetDataReader();
List <Cargo> listCargo = new List <Cargo>();
while (dr.Read())
{
Cargo car = new Cargo
{
id_cargo = Convert.ToInt32(dr["id_cargo"]),
nombre_cargo = Convert.ToString(dr["nombre_cargo"]),
descripcion = Convert.ToString(dr["descripcion"])
};
listCargo.Add(car);
}
cone.Close();
par1.Dispose();
cmd.Dispose();
cone.Dispose();
return(listCargo);
}
private KeyValuePair<ResultInfo, IReadOnlyList<ColumnHeader>> AcquireRefCursor(OracleCommand command, OracleRefCursor refCursor, RefCursorInfo refCursorInfo)
{
var reader = refCursor.GetDataReader();
var resultInfo = new ResultInfo($"RefCursor{reader.GetHashCode()}", refCursorInfo.CursorName, ResultIdentifierType.UserDefined);
_commandReaders.Add(resultInfo, new CommandReader { Reader = reader, Command = command, RefCursorInfo = refCursorInfo } );
return new KeyValuePair<ResultInfo, IReadOnlyList<ColumnHeader>>(resultInfo, GetColumnHeadersFromReader(reader));
}
// カーソルを取得
private void ResetCursor()
{
if (cursor != null) cursor.Dispose();
command.CommandText = "BEGIN OPEN :1 FOR SELECT * FROM (" + Sql + "); end;";
OracleParameter p_rc = command.Parameters.Add(
"p_rc",
OracleDbType.RefCursor,
DBNull.Value,
ParameterDirection.Output);
command.ExecuteNonQuery();
command.Parameters.Clear();
cursor = p_rc.Value as OracleRefCursor;
}
