public List <listarCliente> buscarRutCLiente(string rut)
{
try
{
Conexion con = new Conexion();
OracleConnection cn = con.getConexion();
cn.Open();
OracleCommand cmd = new OracleCommand("FN_BUSCARRUT_CLIENTE", cn);
cmd.CommandType = CommandType.StoredProcedure;
List <listarCliente> listacli = new List <listarCliente>();
OracleParameter output = cmd.Parameters.Add("C_CLIENTESBR", OracleDbType.RefCursor);
output.Direction = ParameterDirection.ReturnValue;
OracleParameter para = new OracleParameter("PARAMETRO_RUT", OracleDbType.Int32);
para.Direction = ParameterDirection.Input;
para.Value = rut;
cmd.Parameters.Add(para);
cmd.ExecuteNonQuery();
OracleDataReader lecturacliente = ((OracleRefCursor)output.Value).GetDataReader();
while (lecturacliente.Read())
{
listarCliente cli = new listarCliente();
cli.rut = lecturacliente.GetString(0);
cli.nombres = lecturacliente.GetString(1);
cli.apellidos = lecturacliente.GetString(2);
int index = lecturacliente.GetOrdinal("DIRECCION_CLIENTE");
if (!lecturacliente.IsDBNull(index))
{
cli.direccion = lecturacliente.GetString(3);
}
else
{
cli.direccion = "NO HAY REGISTRO";
}
cli.fono = lecturacliente.GetInt32(4);
cli.email = lecturacliente.GetString(5);
cli.comuna = lecturacliente.GetString(6);
listacli.Add(cli);
}
output.Dispose();
cmd.Dispose();
cn.Dispose();
con = null;
return(listacli);
}
catch (Exception e)
{
throw;
}
}
private void btnLookup2_Click(object sender, System.EventArgs e)
{
// get the employee email and phone based on the
// first name and last name
// there are no duplicate first name / last name
// combinations in the table
// we will pass the first name and last name as
// bind variables using BindByName
OracleCommand cmdEmpInfo = new OracleCommand();
// the :p_last and :p_first are our bind variable placeholders
cmdEmpInfo.CommandText = "select email, phone_number from employees where first_name = :p_first and last_name = :p_last";
cmdEmpInfo.Connection = oraConn;
// we will use bind by name here
cmdEmpInfo.BindByName = true;
OracleParameter p1 = new OracleParameter();
OracleParameter p2 = new OracleParameter();
// the ParameterName value is what is used when
// binding by name, not the name of the variable
// in the code
// notice the ":" is not included as part of the
// parameter name
p1.ParameterName = "p_first";
p2.ParameterName = "p_last";
p1.Value = lblFirstName.Text;
p2.Value = lblLastName.Text;
// add our parameters to the parameter collection
// for the command object
// we will add them in "reverse" order since we are
// binding by name and not position
cmdEmpInfo.Parameters.Add(p2);
cmdEmpInfo.Parameters.Add(p1);
// get our data reader
OracleDataReader dataReader = cmdEmpInfo.ExecuteReader();
// get the results - our query will only return 1 row
// since we are using known unique values for the first
// and last names
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
p1.Dispose();
p2.Dispose();
dataReader.Dispose();
cmdEmpInfo.Dispose();
}
public List <ListaProducto> buscarnombProducto(string nomb)
{
try
{
Conexion con = new Conexion();
OracleConnection cn = con.getConexion();
cn.Open();
OracleCommand cmd = new OracleCommand("FN_BUSCARNOMB_PRODUCTO", cn);
cmd.CommandType = CommandType.StoredProcedure;
List <ListaProducto> listatipocl = new List <ListaProducto>();
OracleParameter output = cmd.Parameters.Add("C_PRODUCTOBUS", OracleDbType.RefCursor);
output.Direction = ParameterDirection.ReturnValue;
OracleParameter para = new OracleParameter("PARAMETRO_NOMB", OracleDbType.Varchar2);
para.Direction = ParameterDirection.Input;
para.Value = nomb;
cmd.Parameters.Add(para);
cmd.ExecuteNonQuery();
OracleDataReader lecturatipocl = ((OracleRefCursor)output.Value).GetDataReader();
while (lecturatipocl.Read())
{
ListaProducto pro = new ListaProducto();
pro.idproducto = lecturatipocl.GetString(0);
pro.nombproducto = lecturatipocl.GetString(1);
int index = lecturatipocl.GetOrdinal("FECHA_VENC_PRODUCTO");
if (!lecturatipocl.IsDBNull(index))
{
pro.fecha_vencproducto = Convert.ToDateTime(lecturatipocl.GetDateTime(2)).ToString("dd/MM/yyyy");
}
else
{
pro.fecha_vencproducto = "00-00-0000";
}
pro.preciounitario = lecturatipocl.GetInt32(3);
pro.stock_producto = lecturatipocl.GetInt32(4);
pro.stock_critic = lecturatipocl.GetInt32(5);
pro.provee = lecturatipocl.GetString(6);
pro.tipo = lecturatipocl.GetString(7);
listatipocl.Add(pro);
}
output.Dispose();
cmd.Dispose();
cn.Dispose();
con = null;
return(listatipocl);
}
catch (Exception e)
{
throw;
}
}
public List <Colaborador> listarUsuarios()
{
try
{
Conexion objCone = new Conexion();
OracleConnection cn = objCone.getConexion();
cn.Open();
OracleCommand cmd = new OracleCommand("FN_COLABORADOR", cn);
cmd.CommandType = CommandType.StoredProcedure;
List <Colaborador> lista = new List <Colaborador>();
OracleParameter output = cmd.Parameters.Add("C_COLABORA", OracleDbType.RefCursor);
output.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
OracleDataReader reader = ((OracleRefCursor)output.Value).GetDataReader();
if (reader.HasRows)
{
while (reader.Read())
{
Colaborador cola = new Colaborador();
cola.Rut_cola = reader.GetString(0);
cola.Nombre = reader.GetString(1);
cola.Apellido = reader.GetString(2);
cola.TipoUsu = reader.GetString(3);
cola.Nomusuario = reader.GetString(4);
cola.Password = reader.GetString(5);
cola.Activo = reader.GetInt32(6);
lista.Add(cola);
}
}
else
{
Colaborador cola = new Colaborador();
cola.Rut_cola = "";
cola.Nombre = "";
cola.Apellido = "";
cola.TipoUsu = "";
cola.Nomusuario = "";
cola.Password = "";
cola.Activo = 0;
lista.Add(cola);
}
cn.Close();
output.Dispose();
cmd.Dispose();
cn.Dispose();
objCone = null;
return(lista);
}
catch (Exception)
{
throw;
}
}
public List <ListaUsuarios> listaUsu()
{
try
{
Conexion objCone = new Conexion();
OracleConnection cn = objCone.getConexion();
cn.Open();
OracleCommand cmd = new OracleCommand("FN_USUARIOS", cn);
cmd.CommandType = CommandType.StoredProcedure;
List <ListaUsuarios> lista = new List <ListaUsuarios>();
OracleParameter output = cmd.Parameters.Add("C_USUARIO", OracleDbType.RefCursor);
output.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
OracleDataReader reader = ((OracleRefCursor)output.Value).GetDataReader();
if (reader.HasRows)
{
while (reader.Read())
{
ListaUsuarios usua = new ListaUsuarios();
usua.rut = reader.GetString(0);
usua.nombre = reader.GetString(1);
usua.apellido = reader.GetString(2);
usua.email = reader.GetString(3);
usua.cargo = reader.GetString(4);
usua.nomusu = reader.GetString(5);
usua.password = reader.GetString(6);
lista.Add(usua);
}
}
else
{
ListaUsuarios usua = new ListaUsuarios();
usua.rut = "--";
usua.nombre = "--";
usua.apellido = "--";
usua.email = "--";
usua.nomusu = "--";
usua.password = "******";
lista.Add(usua);
}
cn.Close();
output.Dispose();
cmd.Dispose();
cn.Dispose();
objCone = null;
return(lista);
}
catch (Exception)
{
throw;
}
}
public List <listaOrdenCompra> ordenLista()
{
try
{
Conexion objCone = new Conexion();
OracleConnection cn = objCone.getConexion();
cn.Open();
OracleCommand cmd = new OracleCommand("FN_ORDENCOMPRA", cn);
cmd.CommandType = CommandType.StoredProcedure;
List <listaOrdenCompra> lista = new List <listaOrdenCompra>();
OracleParameter output = cmd.Parameters.Add("C_ORDENCOMPRA", OracleDbType.RefCursor);
output.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
OracleDataReader reader = ((OracleRefCursor)output.Value).GetDataReader();
if (reader.HasRows)
{
while (reader.Read())
{
listaOrdenCompra ord = new listaOrdenCompra();
ord.id_orden = reader.GetInt32(0);
ord.provee = reader.GetString(1);
ord.nombre_cola = reader.GetString(2);
ord.nom_produ = reader.GetString(3);
ord.cantidad = reader.GetInt32(4);
lista.Add(ord);
}
}
else
{
listaOrdenCompra ord = new listaOrdenCompra();
ord.id_orden = 0;
ord.provee = "";
ord.nombre_cola = "";
ord.nom_produ = "";
ord.cantidad = 0;
lista.Add(ord);
}
cn.Close();
output.Dispose();
cmd.Dispose();
cn.Dispose();
objCone = null;
return(lista);
}
catch (Exception)
{
throw;
}
}
private void btnLookup_Click(object sender, System.EventArgs e)
{
// perform this action if we are connected
if (oraConn.State == ConnectionState.Open)
{
// sql statement to look up a player based on
// the player number
// we use a bind variable for the player number
string sqlLookup = "select last_name, ";
sqlLookup += "first_name, ";
sqlLookup += "position, ";
sqlLookup += "club ";
sqlLookup += "from squad ";
sqlLookup += "where player_num = :player_num";
// create our command object and set properties
OracleCommand cmdLookup = new OracleCommand();
cmdLookup.CommandText = sqlLookup;
cmdLookup.Connection = oraConn;
// create our parameter object for the player number
// in our sample we are assuming a number is
// entered in the player number text box
OracleParameter pPlayerNum = new OracleParameter();
pPlayerNum.OracleDbType = OracleDbType.Decimal;
pPlayerNum.Value = Convert.ToDecimal(txtPlayerNum.Text);
// add the parameter to the collection
cmdLookup.Parameters.Add(pPlayerNum);
// execute the sql statement and populate the text
// boxes if a record is returned
OracleDataReader dataReader = cmdLookup.ExecuteReader();
if (dataReader.Read())
{
txtLastName.Text = dataReader.GetString(0);
txtFirstName.Text = dataReader.GetString(1);
txtPosition.Text = dataReader.GetString(2);
txtClub.Text = dataReader.GetString(3);
}
else
{
MessageBox.Show("No record for Player Number Found", "No Record Found");
}
// explictly close and dispose our objects
dataReader.Close();
dataReader.Dispose();
pPlayerNum.Dispose();
cmdLookup.Dispose();
}
}
static void update(OracleConnection con)
{
Console.Clear();
Console.WriteLine("Digite o ID");
string id_string = Console.ReadLine();
Console.Clear();
Console.WriteLine("Digite o novo nome");
string nome = Console.ReadLine();
OracleCommand cmd = con.CreateCommand();
OracleTransaction txn = con.BeginTransaction();
try
{
cmd.CommandText = "UPDATE " + Program.db_name +
" SET name = :1 WHERE id = :2";
OracleParameter name = new OracleParameter();
name.OracleDbType = OracleDbType.Char;
name.Value = nome;
cmd.Parameters.Add(name);
OracleParameter id = new OracleParameter();
id.OracleDbType = OracleDbType.Int32;
id.Value = Int32.Parse(id_string);
cmd.Parameters.Add(id);
Console.WriteLine("Comando executado com sucesso!");
cmd.ExecuteNonQuery();
txn.Commit();
name.Dispose();
id.Dispose();
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
txn.Rollback();
}
cmd.Dispose();
Console.WriteLine("Pressione ENTER para continuar...");
Console.ReadLine();
}
static void Main(string[] args)
{
using (OracleConnection con = new OracleConnection())
{
// Connect string
con.ConnectionString = "Data Source=<data source>;" +
"User Id=hr;Password=<password>;";
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
// Set application context values after opening the connection
con.ClientId = "Alex";
con.ActionName = "Retrieve Employee Names";
con.ModuleName = "HR .NET App";
con.ClientInfo = "Version 1";
cmd.BindByName = true;
//Use the command to display employee names from
// the EMPLOYEES table
cmd.CommandText = "select first_name from employees where department_id = :id";
// Assign id to the department number 20
OracleParameter id = new OracleParameter("id", 20);
cmd.Parameters.Add(id);
//Execute the command and use DataReader to display the data
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Employee First Name: " + reader.GetString(0));
}
// Before closing connection, view the application context values in the database.
// Execute using SYS user: SELECT ACTION, CLIENT_IDENTIFIER, CLIENT_INFO, MODULE, USERNAME FROM V$SESSION WHERE USERNAME='******';
Console.ReadLine();
id.Dispose();
reader.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
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);
}
public List <Customer> GetAllCustomers()
{
List <Customer> customerList = new List <Customer>();
DataSet ds = new DataSet();
using (OracleConnection conn = _connectionDao.GetConnection())
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "MY_TEST_PACKAGE.GET_ALL_CUSTOMERS";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parPrefcursor = cmd.Parameters.Add("PRC", OracleDbType.RefCursor,
ParameterDirection.Output);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
parPrefcursor.Dispose();
conn.Close();
}
#if DEBUG
catch (OracleException oraEx)
{
throw new DatabaseException(oraEx.Message, ExceptionConstants.CommonUserExceptionMessage);
}
#endif
catch (Exception ex)
{
throw new DatabaseException(ex.Message, ExceptionConstants.CommonUserExceptionMessage);
}
}
if (ds.HaveAnyRows())
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
customerList.Add(GetCustomer(ds.Tables[0].Rows[i]));
}
}
else
{
customerList.Add(new Customer());
}
return(customerList);
}
static void Main(string[] args)
{
using (OracleConnection con = new OracleConnection())
{
// Connecting using proxy authentication
// Add data souce and HR password
con.ConnectionString = "Data Source=<data source>;" +
"User Id=hr;Password=<password>;" +
"Proxy User Id=appserver;Proxy Password=appserver;";
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
//Use the command to display employee names from
// the EMPLOYEES table
cmd.CommandText = "select first_name from employees where department_id = :id";
// Assign id to the department number 20
OracleParameter id = new OracleParameter("id", 20);
cmd.Parameters.Add(id);
//Execute the command and use DataReader to display the data
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Employee First Name: " + reader.GetString(0));
}
// Before closing connection, view the sessions created for the real user and proxy user.
// Execute using SYS user: SELECT SID, USERNAME FROM V$SESSION WHERE USERNAME='******' OR USERNAME='******';
Console.ReadLine();
id.Dispose();
reader.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
private void btnLookup1_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
// get the employee name based on the employee id
// we will pass the employee id as a bind variable
OracleCommand cmdEmpName = new OracleCommand();
// the :p_id is our bind variable placeholder
cmdEmpName.CommandText = "select first_name, last_name from employees where employee_id = :p_id";
cmdEmpName.Connection = oraConn;
OracleParameter p_id = new OracleParameter();
// here we are setting the OracleDbType
// we could set this as DbType as well and
// the Oracle provider will infer the correct
// OracleDbType
p_id.OracleDbType = OracleDbType.Decimal;
p_id.Value = Convert.ToDecimal(selectedItem.ToString());
// add our parameter to the parameter collection
// for the command object
cmdEmpName.Parameters.Add(p_id);
// get our data reader
OracleDataReader dataReader = cmdEmpName.ExecuteReader();
// get the results - our query will only return 1 row
// since we are using the primary key
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
p_id.Dispose();
dataReader.Dispose();
cmdEmpName.Dispose();
}
}
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);
}
public List <ListarProveedor> buscarRubProveedor(string rub)
{
try
{
Conexion con = new Conexion();
OracleConnection cn = con.getConexion();
cn.Open();
OracleCommand cmd = new OracleCommand("FN_BUSCARRUBRO_PROVEEDOR", cn);
cmd.CommandType = CommandType.StoredProcedure;
List <ListarProveedor> listpro = new List <ListarProveedor>();
OracleParameter output = cmd.Parameters.Add("C_PROVEEBUSc", OracleDbType.RefCursor);
output.Direction = ParameterDirection.ReturnValue;
OracleParameter para = new OracleParameter("PARAMETRO_RUBRO", OracleDbType.Varchar2);
para.Direction = ParameterDirection.Input;
para.Value = rub;
cmd.Parameters.Add(para);
cmd.ExecuteNonQuery();
OracleDataReader lecturaproveedor = ((OracleRefCursor)output.Value).GetDataReader();
while (lecturaproveedor.Read())
{
ListarProveedor prove = new ListarProveedor();
prove.idproveedor = lecturaproveedor.GetInt32(0);
prove.nombreprove = lecturaproveedor.GetString(1);
prove.fonoprove = lecturaproveedor.GetInt32(2);
prove.rubroprove = lecturaproveedor.GetString(3);
listpro.Add(prove);
}
output.Dispose();
cmd.Dispose();
cn.Dispose();
con = null;
return(listpro);
}
catch (Exception e)
{
throw;
}
}
static void Main(string[] args)
{
string constr = "User Id=hr;Password=hr;Data Source=localhost:1521/orcl;" +
"Pooling=true;Min Pool Size=20; Max Pool Size=100;";
OracleConnection con = new OracleConnection(constr);
con.Open();
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("select country_name ");
sbSQL.Append("from countries ");
sbSQL.Append("where country_id = :country_id");
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = sbSQL.ToString();
OracleParameter p_country_id = new OracleParameter();
p_country_id.OracleDbType = OracleDbType.Varchar2;
p_country_id.Value = "UK";
cmd.Parameters.Add(p_country_id);
OracleDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
Console.WriteLine("Country Name: {0}", dr.GetOracleString(0));
}
Console.WriteLine("Press 'Enter' to continue");
Console.ReadLine();
dr.Dispose();
p_country_id.Dispose();
cmd.Dispose();
con.Dispose();
}
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);
}
static void create(OracleConnection con)
{
Console.Clear();
Console.WriteLine("Digite o nome");
string nome = Console.ReadLine();
OracleCommand cmd = con.CreateCommand();
OracleTransaction txn = con.BeginTransaction();
try
{
cmd.CommandText = "INSERT INTO " + Program.db_name +
" (name) VALUES (:1)";
OracleParameter name = new OracleParameter();
name.OracleDbType = OracleDbType.Char;
name.Value = nome;
cmd.Parameters.Add(name);
cmd.ExecuteNonQuery();
txn.Commit();
name.Dispose();
Console.WriteLine("Comando executado com sucesso!");
}
catch (OracleException ex)
{
Console.WriteLine(ex.Message);
txn.Rollback();
}
cmd.Dispose();
Console.WriteLine("Pressione ENTER para continuar...");
Console.ReadLine();
}
public Customer GetCustomerByPhone(string cusPhone)
{
DataSet ds = new DataSet();
using (OracleConnection conn = _connectionDao.GetConnection())
using (var cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = "MY_TEST_PACKAGE.GET_CUSTOMER_BY_MOBILE";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("PCUSPHONE", OracleDbType.Varchar2, cusPhone, ParameterDirection.Input);
OracleParameter parRefcursor = cmd.Parameters.Add("PRC", OracleDbType.RefCursor,
ParameterDirection.Output);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
parRefcursor.Dispose();
conn.Close();
}
#if DEBUG
catch (OracleException oraEx)
{
throw new DatabaseException(oraEx.Message, ExceptionConstants.CommonUserExceptionMessage);
}
#endif
catch (Exception ex)
{
throw new DatabaseException(ex.Message, ExceptionConstants.CommonUserExceptionMessage);
}
}
if (!ds.HaveAnyRows())
{
return(new Customer());
}
return(GetCustomer(ds.Tables[0].Rows[0]));
}
void writeAppTable(string p_message, OracleConnection p_conn)
{
// this is the sql that we will use to insert a
// row into the app_exceptions table.
// we use a bind variable for the message column.
// sysdate and user functions supplied by the database
// they get the current date/time and username respectively.
string l_sql = "insert into app_exceptions " +
"(exception_date, username, exception_message) " +
"values (sysdate, user, :1)";
// bind the message passed as a parameter
// we defined the column in the table as varchar2(512)
// so ensure we use a message that is no greater than
// 512 bytes in length
OracleParameter oraParam = new OracleParameter();
oraParam.OracleDbType = OracleDbType.Varchar2;
oraParam.Direction = ParameterDirection.Input;
if (p_message.Length < 513)
{
oraParam.Value = p_message;
}
else
{
oraParam.Value = p_message.Substring(1, 512);
}
// create the command object, add the bind parameter
// and insert.
OracleCommand oraCmd = new OracleCommand(l_sql, p_conn);
oraCmd.Parameters.Add(oraParam);
oraCmd.ExecuteNonQuery();
// explicitly dispose our objects
oraParam.Dispose();
oraCmd.Dispose();
}
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 <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);
}
public void AgregarParametro(string Nombre,
object Valor,
OracleDbType Tipo,
ParameterDirection Direccion,
bool Retorna = false)
{
OracleParameter objParameter;
using (objParameter = new OracleParameter(Nombre, Tipo, Direccion))
{
if (Valor != null)
{
//if (Valor.GetType().Equals(typeof(string)))
if (Valor is string)
{
//if (Valor.Equals(string.Empty))
/*
* if (string.IsNullOrWhiteSpace(Valor.ToString()))
* {
* objParameter.Size = 200;
*
* } else {
* objParameter.Size = Valor.ToString().Length;
* }
*/
objParameter.Size = (string.IsNullOrWhiteSpace(Valor.ToString()) ? 200 : Valor.ToString().Length);
}
objParameter.Value = Valor;
}
objSBQuery.Append(Nombre + "--> '" + Valor + "';");
objOracleCommand.Parameters.Add(objParameter);
objParameter.Dispose();
}
}
private void call_procedure(OracleConnection con)
{
// create the command object and set attributes
OracleCommand cmd = new OracleCommand("league_rc.get_table", con);
cmd.CommandType = CommandType.StoredProcedure;
// create parameter object for the cursor
OracleParameter p_refcursor = new OracleParameter();
// this is vital to set when using ref cursors
p_refcursor.OracleDbType = OracleDbType.RefCursor;
p_refcursor.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_refcursor);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.Write(reader.GetDecimal(0).ToString() + ",");
Console.Write(reader.GetString(1) + ",");
Console.Write(reader.GetDecimal(2).ToString() + ",");
Console.Write(reader.GetDecimal(3).ToString() + ",");
Console.Write(reader.GetDecimal(4).ToString() + ",");
Console.Write(reader.GetDecimal(5).ToString() + ",");
Console.Write(reader.GetDecimal(6).ToString() + ",");
Console.WriteLine(reader.GetDecimal(7).ToString());
}
Console.WriteLine();
reader.Close();
reader.Dispose();
p_refcursor.Dispose();
cmd.Dispose();
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
// perform this action if we are connected
if (oraConn.State == ConnectionState.Open)
{
// sql statement to delete a record using a bind
string sqlDelete = "delete from squad where player_num = :p_num";
// create our command object and set properties
OracleCommand cmdDelete = new OracleCommand();
cmdDelete.CommandText = sqlDelete;
cmdDelete.Connection = oraConn;
// create our parameter object for the player number
// in our sample we are assuming a number is
// entered in the player number text box
OracleParameter pPlayerNum = new OracleParameter();
pPlayerNum.OracleDbType = OracleDbType.Decimal;
pPlayerNum.Value = Convert.ToDecimal(txtPlayerNum.Text);
// add the parameters to the collection
cmdDelete.Parameters.Add(pPlayerNum);
// execute the delete statement
cmdDelete.ExecuteNonQuery();
MessageBox.Show("Record Deleted Successfully", "Record Deleted");
// reset the form
btnReset_Click(sender, e);
// explictly dispose our objects
pPlayerNum.Dispose();
cmdDelete.Dispose();
}
}
public List <ListarProveedor> listarProvedores()
{
try
{
Conexion con = new Conexion();
OracleConnection cn = con.getConexion();
cn.Open();
OracleCommand cmd = new OracleCommand("FN_LISTAR_PROVEEDORES", cn);
cmd.CommandType = CommandType.StoredProcedure;
List <ListarProveedor> listpro = new List <ListarProveedor>();
OracleParameter output = cmd.Parameters.Add("C_PROVEEDORL", OracleDbType.RefCursor);
output.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
OracleDataReader lecturaproveedor = ((OracleRefCursor)output.Value).GetDataReader();
while (lecturaproveedor.Read())
{
ListarProveedor prove = new ListarProveedor();
prove.idproveedor = lecturaproveedor.GetInt32(0);
prove.nombreprove = lecturaproveedor.GetString(1);
prove.fonoprove = lecturaproveedor.GetInt32(2);
prove.rubroprove = lecturaproveedor.GetString(3);
listpro.Add(prove);
}
output.Dispose();
cmd.Dispose();
cn.Dispose();
con = null;
return(listpro);
}
catch (Exception e)
{
throw;
}
}
public static List <ADQ_COMUN> GenerarOracleCommandCursor_Combo(string SP)
{
Conexion objConexion = new Conexion();
OracleConnection cn = objConexion.getConexion("CONEXION_INGRESOS");
cn.Open();
OracleCommand cmd = cn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = SP;
OracleParameter par1 = new OracleParameter();
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_GRUPO = Convert.ToString(dr["Id"]);
objCombo.DESCRIPCION = Convert.ToString(dr["Descripcion"]);
listarCombo.Add(objCombo);
}
cn.Close();
par1.Dispose();
cmd.Dispose();
cn.Dispose();
objConexion = null;
return(listarCombo);
}
protected void generate_LPN(string org_id)
{
using (OracleConnection conn = new OracleConnection())
{
try
{
conn.ConnectionString = EBSDM13A;
conn.Open();
string stored_procedure = "DECLARE "
+ "p_lpn_id NUMBER; "
+ "p_lpn_number VARCHAR2(50); "
+ "BEGIN "
+ "DBMS_OUTPUT.ENABLE; "
+ "xxpoc_goods_in.create_lpn(" + org_id + ", p_lpn_id, p_lpn_number); "
+ "DBMS_OUTPUT.PUT_LINE(p_lpn_number); "
+ "END;";
string anonymous_block = "BEGIN "
+ "DBMS_OUTPUT.GET_LINES(:1, :2); "
+ "END;";
// used to indicate number of lines to get during each fetch
const int NUM_TO_FETCH = 1;
// used to determine number of rows fetched in anonymous pl/sql block
int numLinesFetched = 0;
// simple loop counter used below
int i = 0;
// create command and execute the stored procedure
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = stored_procedure;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
// create parameter objects for the anonymous pl/sql block
OracleParameter p_lines = new OracleParameter("", OracleDbType.Varchar2, NUM_TO_FETCH, "", ParameterDirection.Output);
p_lines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_lines.ArrayBindSize = new int[NUM_TO_FETCH];
// set the bind size value for each element
for (i = 0; i < NUM_TO_FETCH; i++)
{
p_lines.ArrayBindSize[i] = 32000;
}
// this is an input output parameter...
// on input it holds the number of lines requested to be fetched from the buffer
// on output it holds the number of lines actually fetched from the buffer
OracleParameter p_numlines = new OracleParameter("", OracleDbType.Decimal, "", ParameterDirection.InputOutput);
// set the number of lines to fetch
p_numlines.Value = NUM_TO_FETCH;
// set up command object and execute anonymous pl/sql block
cmd.CommandText = anonymous_block;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(p_lines);
cmd.Parameters.Add(p_numlines);
cmd.ExecuteNonQuery();
// get the number of lines that were fetched (0 = no more lines in buffer)
numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();
// as long as lines were fetched from the buffer...
while (numLinesFetched > 0)
{
// write the text returned for each element in the pl/sql
// associative array to the console window
for (i = 0; i < 1; i++)
{
lpnnum = (p_lines.Value as OracleString[])[0].ToString();
txtBoxLPN.Text = lpnnum;
ViewState["VS_lpnnum"] = lpnnum;
}
// re-execute the command to fetch more lines (if any remain)
cmd.ExecuteNonQuery();
// get the number of lines that were fetched (0 = no more lines in buffer)
numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();
}
// clean up
p_numlines.Dispose();
p_lines.Dispose();
cmd.Dispose();
}
catch (OracleException ex)
{
Debug.WriteLine("Exception Message: " + ex.Message);
Debug.WriteLine("Exception Source: " + ex.Source);
}
}
}
private void LoadBlobCustomBackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
OracleTransaction transaction = _oracleConnection.BeginTransaction(); // transakcja ładowania plików
OracleCommand command = _oracleConnection.CreateCommand(); // polecenie dla połączenia
// parametr dla polecenia ładowania pliku do blob - binarna zawartość pliku
OracleParameter blobDataParameter = new OracleParameter
{
OracleDbType = OracleDbType.Blob,
ParameterName = "blobDataParameter"
};
try
{
int fileCounter = 0; // licznik plików załadowanych. potrzebny do statusu i obliczenia procent ładowania
int filesSize = 0; // łączny rozmiar wszystkich załadowanych plików
int filesSizeAll = _blobFiles.Aggregate(0, (current, bFile) => current + bFile.FileSize);
// dla każdego wskazanego pliku
foreach (BlobFile blobFile in _blobFiles)
{
fileCounter++; // licznik załadowanych plików
string fileFullName = blobFile.FullFileName; // pełna nazwa pliku włączenie ze ścieżką
string fileShortName = blobFile.FileName; // tylko nazwa pliku
int fileSize = blobFile.FileSize; // rozmiar pliku
filesSize += fileSize; // zsumuj wielkość plików
toolStripStatusLabel.Text = $"Ładowanie pliku {fileCounter}/{_blobFiles.Count} [{filesSize / 1024}/{filesSizeAll / 1024} MB]: {fileFullName} [{Math.Round(fileSize / 1024.0, 2) } MB]";
// ----------------------------------------------------------------------------
// pobierz zawartość pliku
// ----------------------------------------------------------------------------
FileStream fs = new FileStream(fileFullName, FileMode.Open, FileAccess.Read);
byte[] blobDataValue = new byte[fs.Length];
fs.Read(blobDataValue, 0, Convert.ToInt32(fs.Length));
fs.Close();
// ----------------------------------------------------------------------------
// ----------------------------------------------------------------------------
// ładowanie pliku do BLOB
// ----------------------------------------------------------------------------
command.CommandText = "SELECT blob.kdok_plikisq.nextval FROM dual"; // pobranie sekwencji dla ładowanego rekordu BLOB
int idFileSq = int.Parse(command.ExecuteScalar().ToString());
// polecenie dodania pliku do blob
command.CommandText = $"INSERT INTO blob.kdok_pliki (id_file, pieczec_pliku, typ_pliku, data, data_d) VALUES({idFileSq}, {idFileSq}, '{fileShortName}', :blobDataParameter, to_date('{dateTimePickerDataD.Text}', 'YYYY-MM-DD HH24:MI:SS'))";
command.Parameters.Clear(); // przygotowanie parametru polecenia
command.Parameters.Add(blobDataParameter); // którym jest plik binarny
blobDataParameter.Value = blobDataValue; // ze skanem
command.ExecuteNonQuery(); // wykonaj polecenie
// ----------------------------------------------------------------------------
// ----------------------------------------------------------------------------
// ładowanie rekordu do KDOK_WSK
// ----------------------------------------------------------------------------
command.CommandText = "SELECT ewid4.kdok_wsksq.nextval FROM dual"; // pobranie sekwencji dla ładowanego rekordu kdok_wsk
int idDokSq = int.Parse(command.ExecuteScalar().ToString());
int idGr = Convert.ToInt32(textBoxDokId.Text);
string path = blobFile.FullFileName;
int idRodzDok = Convert.ToInt32(textBoxIdRodzDok.Text);
if (idRodzDok == 0)
{
idRodzDok = blobFile.PrefixId;
}
int userId = Convert.ToInt32(textBoxUserId.Text);
string wl = string.Empty;
Invoke(new MethodInvoker(() => wl = comboBoxWl.Text));
wl = wl.Substring(0, wl.IndexOf(" -", StringComparison.Ordinal));
command.CommandText = "INSERT INTO KDOK_WSK(ID_DOK, WL, ID_GR, ID_FILE, PATH, ID_RODZ_DOK, OPIS, USER_ID, DATA_D, USERM_ID, DATA_M) " +
$"VALUES({idDokSq}, '{wl}', {idGr}, {idFileSq}, '{path}', {idRodzDok}, '', {userId}, to_date('{dateTimePickerDataD.Text}', 'YYYY-MM-DD HH24:MI:SS'), {userId}, to_date('{dateTimePickerDataD.Text}', 'YYYY-MM-DD HH24:MI:SS'))";
command.ExecuteNonQuery();
// ----------------------------------------------------------------------------
int percentage = (fileCounter * 100) / dataGridView.Rows.Count; // oblicz procentowe zaawansowanie ładowania
loadBlobCustomBackgroundWorker.ReportProgress(percentage); // zaraportuj zaawansowanie
}
transaction.Commit(); // zatwierdzenie transakcji ładowania
toolStripStatusLabel.Text = $"Załadowano {dataGridView.Rows.Count} plików o łącznym rozmiarze {filesSize / 1024} MB";
}
catch (Exception exception)
{
transaction.Rollback(); // w przypadku wystąpienia błędu wycofaj transakcje
toolStripStatusLabel.Text = exception.Message + "\n" + command.CommandText;
}
finally
{
blobDataParameter.Dispose();
command.Dispose();
transaction.Dispose();
}
}
static void Main(string[] args)
{
//Demo: Basic ODP.NET Core application to connect, query, and return
// results from an OracleDataReader to a console
//Enter user name and password
string conString = "User Id=hr;Password=<password>;" +
//Connect to an Oracle DB with Easy Connect (Plus) or a net service name
"Data Source=<Easy Connect (Plus) or net service name>;";
using (OracleConnection con = new OracleConnection(conString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
//Use the command to display employee names from the EMPLOYEES table
cmd.CommandText = "select first_name from employees where department_id = :id";
//Assign id to the department number 20
OracleParameter id = new OracleParameter("id", 20);
cmd.Parameters.Add(id);
//Execute the command and use DataReader to display the data
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Employee First Name: " + reader.GetString(0));
}
id.Dispose();
reader.Dispose();
Console.WriteLine();
//Demo: Batch SQL and REF Cursors
// Anonymous PL/SQL block embedded in code - executes in one DB round trip
//Reset OracleCommand for use in next demo
cmd.Parameters.Clear();
cmd.BindByName = false;
cmd.CommandText = "DECLARE a NUMBER:= 20; " +
"BEGIN " +
"OPEN :1 for select first_name,department_id from employees where department_id = 10; " +
"OPEN :2 for select first_name,department_id from employees where department_id = a; " +
"OPEN :3 for select first_name,department_id from employees where department_id = 30; " +
"END;";
cmd.CommandType = CommandType.Text;
//ODP.NET has native Oracle data types, such as Oracle REF
// Cursors, which can be mapped to .NET data types
//Bind REF Cursor Parameters for each department
//Select employees in department 10
OracleParameter p1 = cmd.Parameters.Add("refcursor1",
OracleDbType.RefCursor);
p1.Direction = ParameterDirection.Output;
//Select employees in department 20
OracleParameter p2 = cmd.Parameters.Add("refcursor2",
OracleDbType.RefCursor);
p2.Direction = ParameterDirection.Output;
//Select employees in department 30
OracleParameter p3 = cmd.Parameters.Add("refcursor3",
OracleDbType.RefCursor);
p3.Direction = ParameterDirection.Output;
//Execute batched statement
cmd.ExecuteNonQuery();
//Let's retrieve the three result sets with DataReaders
OracleDataReader dr1 =
((OracleRefCursor)cmd.Parameters[0].Value).GetDataReader();
OracleDataReader dr2 =
((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader();
OracleDataReader dr3 =
((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader();
//Let's retrieve the results from the DataReaders
while (dr1.Read())
{
Console.WriteLine("Employee Name: " + dr1.GetString(0) + ", " +
"Employee Dept:" + dr1.GetDecimal(1));
}
Console.WriteLine();
while (dr2.Read())
{
Console.WriteLine("Employee Name: " + dr2.GetString(0) + ", " +
"Employee Dept:" + dr2.GetDecimal(1));
}
Console.WriteLine();
while (dr3.Read())
{
Console.WriteLine("Employee Name: " + dr3.GetString(0) + ", " +
"Employee Dept:" + dr3.GetDecimal(1));
}
//Clean up
p1.Dispose();
p2.Dispose();
p3.Dispose();
dr1.Dispose();
dr2.Dispose();
dr3.Dispose();
Console.WriteLine("Press 'Enter' to continue");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
}
void ShowResultSet(OracleDataReader reader, OracleCommand command)
{
//it is included dbms_output
command.CommandText = "begin dbms_output.enable (32000); end;";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
bool headerPrinted = false;
int count = 1;
while (reader.Read())
{
if (!headerPrinted)
{
Console.WriteLine(@"<table class=""sqloutput""><tbody><tr><th> </th>");
for (int i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetName(i);
Console.WriteLine(@"<th>{0}</th>", string.IsNullOrEmpty(name) ? "(No column name)" : name);
}
Console.WriteLine("</tr>");
headerPrinted = true;
}
Console.WriteLine(@"<tr><td>{0}</td>", count++);
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader[i] == DBNull.Value)
{
Console.WriteLine(@"<td><i>NULL</i></td>");
}
else
{
if (reader[i] as string == null && reader[i] as IEnumerable != null)
{
string res = "";
foreach (var a in (reader[i] as IEnumerable))
res += Convert.ToString(a);
Console.WriteLine(@"<td>{0}</td>", res);
}
else
{
Console.WriteLine(@"<td>{0}</td>", HttpUtility.HtmlEncode(reader[i]));
}
}
}
Console.WriteLine("</tr>");
}
if (headerPrinted)
Console.WriteLine("</tbody></table>");
// create parameter objects for the anonymous pl/sql block
int NUM_TO_FETCH = 8;
OracleParameter p_lines = new OracleParameter("", OracleDbType.Varchar2, NUM_TO_FETCH, "", ParameterDirection.Output);
p_lines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_lines.ArrayBindSize = new int[NUM_TO_FETCH];
// set the bind size value for each element
for (int i = 0; i < NUM_TO_FETCH; i++)
{
p_lines.ArrayBindSize[i] = 32000;
}
// this is an input output parameter...
// on input it holds the number of lines requested to be fetched from the buffer
// on output it holds the number of lines actually fetched from the buffer
OracleParameter p_numlines = new OracleParameter("", OracleDbType.Decimal, "", ParameterDirection.InputOutput);
// set the number of lines to fetch
p_numlines.Value = NUM_TO_FETCH;
// set up command object and execute anonymous pl/sql block
command.CommandText = "begin dbms_output.get_lines(:1, :2); end;";
command.CommandType = CommandType.Text;
command.Parameters.Add(p_lines);
command.Parameters.Add(p_numlines);
command.ExecuteNonQuery();
// get the number of lines that were fetched (0 = no more lines in buffer)
int numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();
// as long as lines were fetched from the buffer...
while (numLinesFetched > 0)
{
// write the text returned for each element in the pl/sql
// associative array to the console window
for (int i = 0; i < numLinesFetched; i++)
{
string out_string = (string)(p_lines.Value as OracleString[])[i];
if (!string.IsNullOrEmpty(out_string))
{
Console.WriteLine(System.Web.HttpUtility.HtmlEncode(out_string));
}
}
// re-execute the command to fetch more lines (if any remain)
command.ExecuteNonQuery();
// get the number of lines that were fetched (0 = no more lines in buffer)
numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();
}
// clean up
p_numlines.Dispose();
p_lines.Dispose();
}
public static void StatementCacheTest(int iterations)
{
// used to track execution duration
DateTime timeStart;
DateTime timeEnd;
double totalSeconds;
// the connection object to use for the test
OracleConnection con = new OracleConnection(with_cache);
con.Open();
// the command object used for no caching test
// initial test does not use statement caching
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.AddToStatementCache = false;
cmd.CommandText = "select mensaje from gen_mensajes where id = :1";
// parameter object for the bind variable
OracleParameter p_id = new OracleParameter();
p_id.OracleDbType = OracleDbType.Decimal;
p_id.Value = 1;
// add parameter to the collection for the command object
cmd.Parameters.Add(p_id);
// the data reader for this test
OracleDataReader dr;
// display simple prompt text
Console.WriteLine("Beginning Statement Cache Test with {0} iterations...", iterations.ToString());
// capture test start time for no caching test
timeStart = DateTime.Now;
// loop creating a connection with no statement caching
// number of loops is determined by the iterations parameter
for (int i = 0; i < iterations; i++) {
dr = cmd.ExecuteReader();
dr.Read();
dr.Dispose();
}
// capture test end time for no pooling test
timeEnd = DateTime.Now;
// calculate total seconds for this test
totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
// display time used for no caching test
Console.WriteLine(" No Statement Caching: {0} total seconds.", totalSeconds.ToString());
// create new command object used for caching test
cmd.Parameters.Clear();
cmd.Dispose();
cmd = new OracleCommand();
cmd.Connection = con;
cmd.AddToStatementCache = true;
//cmd.CommandText = "select data from fetch_test where id = :1";
cmd.CommandText = "select mensaje from gen_mensajes where id = :1";
// add parameter to the collection for the command object
cmd.Parameters.Add(p_id);
// capture test start time for pooling test
timeStart = DateTime.Now;
// loop creating a connection with statement caching
// number of loops is determined by the iterations parameter
for (int i = 0; i < iterations; i++) {
dr = cmd.ExecuteReader();
dr.Read();
dr.Dispose();
}
// capture test end time for caching test
timeEnd = DateTime.Now;
// calculate total seconds for this test
totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
// display time used for caching test
Console.WriteLine(" With Statement Caching: {0} total seconds.", totalSeconds.ToString());
Console.WriteLine();
// clean up objects
p_id.Dispose();
cmd.Dispose();
con.Dispose();
}
protected void imgReceive_Click(object sender, ImageClickEventArgs e)
{
get_data(null, null);
int LC_orgid = 140;
using (OracleConnection conn = new OracleConnection())
{
try
{
conn.ConnectionString = EBSDM13A;
conn.Open();
//OracleCommand cmd = new OracleCommand(sql, conn);
string stored_procedure = "DECLARE "
+ "l_errbuf VARCHAR2(1000); "
+ "l_retcode VARCHAR2(10); "
+ "l_receipt_num NUMBER; "
+ "BEGIN "
+ "DBMS_OUTPUT.ENABLE; "
+ "xxpoc_goods_in.main "
+ "(l_errbuf, "
+ "l_retcode, "
+ "L_RECEIPT_NUM, "
+ LC_orgid + ", "
+ supp + ", "
+ poid + ", "
+ polineid + ", "
+ qty + ", "
+ "'" + lotnum + "'" + ", "
+ "'" + lpnnum + "'"
+ ");"
+ " DBMS_OUTPUT.PUT_LINE('Error Buf is ' || L_ERRBUF);"
+ " DBMS_OUTPUT.PUT_LINE('Return Code is ' || L_RETCODE);"
+ " DBMS_OUTPUT.PUT_LINE('Receipt Num ' || L_RECEIPT_NUM);"
+ "END;";
Debug.Write(stored_procedure);
string anonymous_block = "BEGIN "
+ "DBMS_OUTPUT.GET_LINES(:1, :2); "
+ "END;";
// used to indicate number of lines to get during each fetch
const int NUM_TO_FETCH = 4;
// used to determine number of rows fetched in anonymous pl/sql block
int numLinesFetched = 0;
// simple loop counter used below
int i = 0;
// create command and execute the stored procedure
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = stored_procedure;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
// create parameter objects for the anonymous pl/sql block
OracleParameter p_lines = new OracleParameter("", OracleDbType.Varchar2, NUM_TO_FETCH, "", ParameterDirection.Output);
p_lines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_lines.ArrayBindSize = new int[NUM_TO_FETCH];
// set the bind size value for each element
for (i = 0; i < NUM_TO_FETCH; i++)
{
p_lines.ArrayBindSize[i] = 32000;
}
// this is an input output parameter...
// on input it holds the number of lines requested to be fetched from the buffer
// on output it holds the number of lines actually fetched from the buffer
OracleParameter p_numlines = new OracleParameter("", OracleDbType.Decimal, "", ParameterDirection.InputOutput);
// set the number of lines to fetch
p_numlines.Value = NUM_TO_FETCH;
// set up command object and execute anonymous pl/sql block
cmd.CommandText = anonymous_block;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(p_lines);
cmd.Parameters.Add(p_numlines);
cmd.ExecuteNonQuery();
// get the number of lines that were fetched (0 = no more lines in buffer)
numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();
// as long as lines were fetched from the buffer...
while (numLinesFetched > 0)
{
// write the text returned for each element in the pl/sql
// associative array to the console window
for (i = 0; i < 1; i++)
{
Debug.WriteLine(i);
errbuff = (p_lines.Value as OracleString[])[1].ToString();
retcode = (p_lines.Value as OracleString[])[2].ToString();
recnum = (p_lines.Value as OracleString[])[3].ToString().Remove(0,12);
}
// re-execute the command to fetch more lines (if any remain)
cmd.ExecuteNonQuery();
// get the number of lines that were fetched (0 = no more lines in buffer)
numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();
}
// clean up
p_numlines.Dispose();
p_lines.Dispose();
cmd.Dispose();
//MessageBox.Show("RECEIPT SUCCESSFUL. RECEIPT NUMBER IS: " + recNum);
showmsg("TRANSACTION SUCCESSFUL<br /><br />RECEIPT NUMBER <strong>" + recnum + "</strong>");
}
catch (OracleException ex)
{
Debug.WriteLine("Exception Message: " + ex.Message);
Debug.WriteLine("Exception Source: " + ex.Source);
//MessageBox.Show("RECEIPT UNSUCCESSFUL. RETURN CODE IS: " + retcode + " AND ERROR IS: " + errbuff);
showmsg("TRANSACTION FAILED<br /><br />RETURN CODE <strong>" + retcode + "</strong> ERROR <strong>" + errbuff + "</strong>");
}
}
Page_Load(null, null);
}
private DataTable GetTableReader(string library, string select, string[] _nParametros, object[] _vParametros)
{
DataTable dtTmp = new DataTable();
using (OracleConnection conn = new OracleConnection(_Security.cadena))
{
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
OracleCommand cmd = new OracleCommand(SelectCommand.GetCommand(library, select, _nParametros, _vParametros), conn);
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
switch (select)
{
case "FDB_LEER_ANEXOS_DIAN":
{
cmd.CommandType = CommandType.StoredProcedure;
break;
}
default:
{
cmd.CommandType = CommandType.Text;
break;
}
}
//Debido a que el stored procedure recibe un parámetro de salida creamos un objeto de tipo OracleParameter y lo definimos que será de salida y de tipo RefCursor.
OracleParameter p_refcursor = new OracleParameter();
p_refcursor.OracleDbType = OracleDbType.RefCursor;
p_refcursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_refcursor);
for (int i = 0; i < _nParametros.Length; i++)
{
cmd.Parameters.Add(parametroInt(_nParametros[i], _vParametros[i]));
}
try
{
cmd.ExecuteNonQuery();//El método executeNonQuery ejecuta el stored procedure y éste nos devolverá el cursor ya abierto el cual tiene identificado el conjunto de filas a recuperar.
OracleRefCursor cursor = (OracleRefCursor)p_refcursor.Value;
OracleDataReader dr = cursor.GetDataReader();//Se crea el objeto DataReader mediante el valor obtenido por el cursor (OracleParameter) para recorrer la información y cargarla a nuestro arreglo.
/* En muchas situaciones es recomendable aumentar el tamaño en ventaja de recoger la información más rápido siendo más eficientes. Esto se realiza con la siguiente línea de código:
* dr.FetchSize = cmd.RowSize * 100;
* En este caso esperamos recibir la información en bloques de 100 filas, por lo cual si una tabla tiene 1000 filas se harán 10 viajes de la capa de base de datos a la capa cliente para obtener la información completa de la tabla.
* La vista V$SQL tiene los campos: executions, fetches y rows_processed los cuales nos pueden ayudar a definir la cantidad de filas hacer retornadas en un fetch de manera eficiente.
* Por ejemplo: Si obtenemos el ratio de rows_processed/executions nos daría la cantidad de filas promedio obtenidas en una ejecución del query.
* El ratio de fetches/executions nos entrega la cantidad de fetchs en cada ejecución.
* Obteniendo ambos ratios tenemos la cantidad de filas y fetchs de cada ejecución del query los cuales podrían ser reducidos ampliando la cantidad de filas a traer en cada operación de fetch.
*/
FieldInfo fi = dr.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);
fi = dr.GetType().GetField("m_rowSize", BindingFlags.Instance);
//Existe un bug con el atributo RowSize del objeto Command ya que devuelve siempre el valor de 0. Para evitar el bug y conseguir el tamaño en bytes de una fila se ha implementado las siguientes líneas de código.
int rowsize = 0;
try
{
//int rowsize = Convert.ToInt32(fi.GetValue(dr));
rowsize = dr.FieldCount * 100;
}
catch (Exception ex)
{
rowsize = 1515;
}
//Un fetch es un conjunto de filas que recoge la capa de aplicación de la base de datos mientras recorre un cursor. Por default este valor es 64 KB, es decir en bloques de 64 KB se va obteniendo todas las filas de un cursor.
dr.FetchSize = rowsize * 100;
while (dr.Read())
{
/*EConsulta objConsulta = new EConsulta();
* objConsulta.ORCA_NUMERO_NB = double.Parse(dr["ORCA_NUMERO_NB"].ToString());*/
}
p_refcursor.Dispose();
cmd.Dispose();
}
catch (OracleException ex)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
PrcsOracleException(SelectCommand.GetCommand(library, select, _nParametros, _vParametros), ex);
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
PrcsException(library, SelectCommand.GetCommand(library, select, _nParametros, _vParametros), ex, _nParametros, _vParametros);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(dtTmp);
}
private void EjecutarDLLOracle(String tipo)
{
string objeto = comboBoxDDL.SelectedItem.ToString();
using (OracleConnection oraConn = new OracleConnection("data source=" + orc.database + ";user id=" + orc.user + ";password="******"dbms_metadata.get_ddl";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter clobparam = new OracleParameter();
clobparam.OracleDbType = OracleDbType.Clob;
clobparam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(clobparam);
OracleParameter parmObjectType = new OracleParameter();
parmObjectType.OracleDbType = OracleDbType.Varchar2;
parmObjectType.ParameterName = "OBJECT_TYPE";
parmObjectType.Value = tipo;
cmd.Parameters.Add(parmObjectType);
OracleParameter parmObjectName = new OracleParameter();
parmObjectName.OracleDbType = OracleDbType.Varchar2;
parmObjectName.ParameterName = "NAME";
parmObjectName.Value = objeto;
cmd.Parameters.Add(parmObjectName);
OracleParameter parmObjectOwner = new OracleParameter();
parmObjectOwner.OracleDbType = OracleDbType.Varchar2;
parmObjectOwner.ParameterName = "SCHEMA";
parmObjectOwner.Value = orc.user.ToUpper();
cmd.Parameters.Add(parmObjectOwner);
cmd.ExecuteNonQuery();
this.cuadroMostrarDDL.Text = (((Oracle.DataAccess.Types.OracleClob)clobparam.Value).Value);
clobparam.Dispose();
}
}
}
