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