Oracle Data Access with MS DotNet -

GetaDataWithCommand.cs

/*   
Mapping a POCO - Direct Query (Command Text) and OracleDataReader 
*/
   public class Persona
    {
        public int Id { get; set; }
        public string PrimerApellido { get; set; }
        public string SegundoApellido { get; set; }
        public string ApellidoCasado { get; set; }
        public string PrimerNombre { get; set; }
        public string SegundoNombre { get; set; }
        public DateTime FechaNacimiento { get; set; }       
        public string LugarNacimiento { get; set; }
 
    }
	
	public class GetData
	{
		
		public static string constr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XX.XX.XX)(PORT=1529)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=SGCDES)));User Id=*******; Password=*******";

        public Persona GetPersonaById(string id)
        {
            var persona = new PersonaDTO();

            DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
            using (DbConnection conn = factory.CreateConnection())
            {
                conn.ConnectionString = constr;
                try
                {
                    conn.Open();
                    OracleCommand cmd = (OracleCommand)factory.CreateCommand();
                    cmd.Connection = (OracleConnection)conn;

                    cmd.AddRowid = true;
                    
                    cmd.CommandText =
                        "SELECT ID,PRIMER_APELLIDO,SEGUNDO_APELLIDO,APELLIDO_CASADO,PRIMER_NOMBRE,SEGUNDO_NOMBRE ,FECHA_NACIMIENTO,LUGAR_NACIMIENTO FROM PERSONAS WHERE REPLACE(NUMERO_DOCUMENTO,'-','') = REPLACE(:id,'-','')";


                    cmd.Parameters.Add(new OracleParameter("p_idDoc", id));
                    cmd.Parameters.Add("resultData",OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                    
                    cmd.CommandType = CommandType.Text;

                    OracleDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        persona.PrimerApellido = dataReader["PRIMER_APELLIDO"].ToString();
                        persona.SegundoApellido = dataReader["SEGUNDO_APELLIDO"].ToString();
                        persona.ApellidoCasado = dataReader["APELLIDO_CASADO"].ToString();
                        persona.PrimerNombre = dataReader["PRIMER_NOMBRE"].ToString();
                        persona.SegundoNombre = dataReader["SEGUNDO_NOMBRE"].ToString();
                        persona.FechaNacimiento = Convert.ToDateTime(dataReader["FECHA_NACIMIENTO"].ToString());
                        persona.LugarNacimiento = dataReader["LUGAR_NACIMIENTO"].ToString();
                    }
                    dataReader.Dispose();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
            }

            return persona;
        }
	}
	
	
	/*
	Direct Query (Command Text) with OracleDataAdapter and DataTable
	*/
	

        public List<int> GetSomething(string tipo)
        {
			
			DbProviderFactory factory  = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
			 
            List<int> IdsTransaccion = new List<int>();
            var querry = "SELECT ID_TRAN FROM tableX WHERE descripcion = '"+tipo+"'";
            using (DbConnection conn = factory.CreateConnection())
            {
                try
                {
                    DataTable dt = new DataTable();
                    conn.ConnectionString = constr;
                    conn.Open();

                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = (OracleConnection)conn;
                    cmd.CommandText = sp;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.Parameters.Add(new OracleParameter("tipo", tipo));

                    cmd.ExecuteNonQuery();
                    conn.Close();
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        IdsTransaccion.Add(Convert.ToInt32(dr["ID_TRAN"].ToString()));
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }

            }

            return IdsTransaccion;
			
		}
		/*-------------------------------------------------*/
  public class ParametersDataBase
    {
        public string ParamName { get; set; }
        public string ParamValue { get; set; }
    }
		
        public DataTable GetGenericData(string query, List<ParametersDataBase> parameters)
        {
            DataTable dt = new DataTable();
            DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
            try
            {
                using (OracleConnection dbconn = new OracleConnection(constr))
                {

                    DataSet userDataset = new DataSet();
                    var strQuery = query;
                    OracleCommand selectCommand = new OracleCommand(strQuery, dbconn);

                    if (parameters.Count > 0)
                    {
                        foreach (var param in parameters)
                        {
                            selectCommand.Parameters.Add(new OracleParameter(param.ParamName, param.ParamValue));
                        }
                    }

                    OracleDataAdapter adapter = new OracleDataAdapter(selectCommand);
                    adapter.Fill(dt);
                }

            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }

            return dt;
        }
		
/*--------------------------------------------------------------------------------------------------------------------*/		
/*--------------------------------------------------------------------------------------------------------------------*/	
/*--------------------------------------------------------------------------------------------------------------------*/		
/*--------------------------------------------------------------------------------------------------------------------*/		


/* WITH SP */

  public bool UpdateDataPersona(PersonaDTO persona, int lastSeqUpdate)
        {
            var success = false;
            if (persona == null || string.IsNullOrEmpty(persona.NumeroDocumento))
                return success;

            DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
            var query = "SP_UPD_PERSONAS";


            using (DbConnection conn = factory.CreateConnection())
            {
                conn.ConnectionString = constr;
                try
                {
                    conn.Open();
                    OracleCommand cmd = (OracleCommand)factory.CreateCommand();
                    cmd.Connection = (OracleConnection)conn;
                    cmd.AddRowid = true;
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new OracleParameter("p_segundo_apellido", persona.SegundoApellido));
                    cmd.Parameters.Add(new OracleParameter("p_primer_apellido", persona.PrimerApellido));
                    cmd.Parameters.Add(new OracleParameter("p_apellido_casado", persona.ApellidoCasado));
                    cmd.Parameters.Add(new OracleParameter("p_primer_nombre", persona.PrimerNombre));
                    cmd.Parameters.Add(new OracleParameter("p_segundo_nombre", persona.SegundoNombre));
                    cmd.Parameters.Add(new OracleParameter("p_lugar_nacimiento", persona.LugarNacimiento));

                    cmd.Parameters.Add("p_fecha_nacimiento", OracleDbType.Date).Value = persona.FechaNacimiento;

                    cmd.Parameters.Add(new OracleParameter("p_codigo_nacionalidad", persona.CodigoNacionalidad));


                    cmd.Parameters.Add(new OracleParameter("p_numero_documento", persona.NumeroDocumento));

                    cmd.ExecuteNonQuery();
                    // int rowsUpdated = cmd.ExecuteNonQuery();

                    //  if (rowsUpdated > 0) success = true;

                }
                catch (Exception ex)
                {
                    //var exMessage = ex.Message;
                    //throw;
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
            }

            return success;
        }
		
		
		
/*  with package with result data*/


        public string GetImagen(string dui)
        {
            string result = "";
 

           // DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");

            DataTable dt = new DataTable();
            try
            {
                OracleConnection con = new OracleConnection(constr);
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "ELSV_LAV_PKG_PERSONADATA.GetDuiImageString";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("p_idDoc", OracleDbType.Varchar2).Value = dui;
                cmd.Parameters.Add("resultData", OracleDbType.RefCursor).Direction
                    = ParameterDirection.Output;

                OracleDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    OracleClob clob = dataReader.GetOracleClob(0);

                    if (!String.IsNullOrEmpty(clob.Value))
                    {
                        result = clob.Value;
                    }
                }
                dataReader.Dispose();
                cmd.Dispose();
                con.Close();
                con.Dispose();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }

           


            
            //query = "SELECT doc_img_string,id_docto FROM elsv_lav_docto_pago  WHERE REPLACE(id_docto,'-','') =  REPLACE(:id,'-','') ";

            

            //using (DbConnection conn = factory.CreateConnection())
            //{
            //    conn.ConnectionString = constr;
            //    try
            //    {
            //        conn.Open();
            //        OracleCommand cmd = (OracleCommand)factory.CreateCommand();
            //        cmd.Connection = (OracleConnection)conn;
            //        cmd.CommandText = query;
            //        cmd.Parameters.Add(new OracleParameter("id", dui));
            //        OracleDataReader dataReader = cmd.ExecuteReader();
            //        while (dataReader.Read())
            //        {
            //            OracleClob clob = dataReader.GetOracleClob(0);

            //            if (!String.IsNullOrEmpty(clob.Value))
            //            {
            //                result = clob.Value;
            //            }
            //        }
            //        dataReader.Dispose();
            //        cmd.Dispose();

            //    }
            //    catch (Exception ex)
            //    {
            //        Console.WriteLine(ex.Message);
            //        Console.WriteLine(ex.StackTrace);
            //    }
            //}
            return result;
        }