PREGUNTAS CONTESTADAS - C sharp

public DataSet InformeMensual(string TipoHonorario)

        {

            string[] nomParam = 

            {

                "In_TipoHonorario"

            };



            object[] vlrParam = 

            {

                TipoHonorario

            };

            return getDataSet("HonorarioNoc_PKG.HonorarioNoc", nomParam, vlrParam);

        }





public DataSet getDataset(string strSQL, string[] NombreParametros, object[] ValoresParametros)

        {

            DataSet ds = new DataSet();



            try

            {

                if (strTipo_Conexion == TipoCnn.Oracle)

                {

                    cmdo = cnno.CreateCommand();

                    cmdo.CommandText = strSQL;

                    cmdo.CommandType = CommandType.StoredProcedure;

                    this.setParametrosOracle(cmdo, getParamertosOracle(NombreParametros, ValoresParametros));

                    OracleDataAdapter da = new OracleDataAdapter(cmdo);

                    da.Fill(ds);

                }

                else

                {

                    cmd = cnn.CreateCommand();

                    cmd.CommandText = strSQL;

                    cmd.CommandTimeout = 99999999;

                    cmd.CommandType = CommandType.StoredProcedure;

                    this.setParametros(cmd, getParamertos(NombreParametros, ValoresParametros));

                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(ds);

                }



                return ds;

            }

            catch (Exception ex)

            {

                EscribirError(ex.ToString());

                return (null);

            }

        }





   private OracleParameter[] getParamertosOracle(string[] NombreParametros, object[] ValoresParametros)

        {

            OracleParameter[] arrParametros = new OracleParameter[NombreParametros.Length];



            for (int ic = 0; ic <= NombreParametros.Length - 1; ic++)

            {

                OracleParameter Parametro = new OracleParameter();

                Parametro.ParameterName = NombreParametros[ic];

                Parametro.Value = ValoresParametros[ic];

                arrParametros[ic] = Parametro;

                Parametro = null;

            }

            return arrParametros;

        }





private void setParametrosOracle(OracleCommand setCmd, OracleParameter[] setParameter)

        {

            if (setParameter != null)

            {

                for (int ic = 0; ic <= setParameter.Length - 1; ic++)

                {

                    setCmd.Parameters.Add(setParameter[ic]);

                }



            }



        }



EL PROCEDIMIENTO EN ORACLE ES:



CREATE OR REPLACE PACKAGE Body ADMSALUD.HonorarioNoc_PKG AS

   TYPE mes_arr IS TABLE OF VARchar2(10)

    INDEX BY BINARY_INTEGER;

    TYPE NOrden_arr IS TABLE OF VARCHAR2(10)

    INDEX BY BINARY_INTEGER;

    TYPE FecCirugia_arr IS TABLE OF date

    INDEX BY BINARY_INTEGER;

    TYPE HoraCirugia_arr IS TABLE OF VARCHAR2(5)

    INDEX BY BINARY_INTEGER;

    TYPE IdPaciente_arr IS TABLE OF VARCHAR2(13)

    INDEX BY BINARY_INTEGER;

    TYPE Paciente_arr IS TABLE OF VARCHAR2(83)

    INDEX BY BINARY_INTEGER;

    TYPE IdCirugia_arr IS TABLE OF VARCHAR2(13)

    INDEX BY BINARY_INTEGER;

    TYPE Cirujano_arr IS TABLE OF VARCHAR2(83)

    INDEX BY BINARY_INTEGER;

    TYPE CodPresta_arr IS TABLE OF VARCHAR2(8)

    INDEX BY BINARY_INTEGER;

    TYPE NPrestacion_arr IS TABLE OF VARCHAR2(255)

    INDEX BY BINARY_INTEGER;

    TYPE PHonorario_arr IS TABLE OF VARCHAR2(7)

    INDEX BY BINARY_INTEGER;

    TYPE Monto_arr IS TABLE OF NUMBER(11,2)

    INDEX BY BINARY_INTEGER;

    TYPE Sociedad_arr IS TABLE OF VARCHAR2(4)

    INDEX BY BINARY_INTEGER;

    TYPE HoraIni_arr IS TABLE OF VARCHAR2(5)

    INDEX BY BINARY_INTEGER;

    TYPE HoraFin_arr IS TABLE OF VARCHAR2(2)

    INDEX BY BINARY_INTEGER;

    TYPE HoraCompFin_arr IS TABLE OF VARCHAR2(5)

    INDEX BY BINARY_INTEGER;

    TYPE Servicio_arr IS TABLE OF VARCHAR2(8)

    INDEX BY BINARY_INTEGER;



PROCEDURE HonorarioNoc(

      in_tipohonorario   IN       VARCHAR2,

      in_fechaini        IN       VARCHAR2,

      in_fechafin        IN       VARCHAR2,

      mes                OUT      mes_arr,

      norden             OUT      norden_arr,

      feccirugia         OUT      feccirugia_arr,

      horacirugia        OUT      horacirugia_arr,

      idpaciente         OUT      idpaciente_arr,

      paciente           OUT      paciente_arr,

      idcirugia          OUT      idcirugia_arr,

      cirujano           OUT      cirujano_arr,

      codpresta          OUT      codpresta_arr,

      nprestacion        OUT      nprestacion_arr,

      phonorario         OUT      phonorario_arr,

      monto              OUT      monto_arr,

      sociedad           OUT      sociedad_arr,

      horaini            OUT      horaini_arr,

      horafin            OUT      horafin_arr,

      horacompfin        OUT      horacompfin_arr,

      servicio           OUT      servicio_arr,

      srv_message        OUT      VARCHAR2

   )

   AS

      srv_fetchstatus   NUMBER (1);

      srv_totalrows     NUMBER (8);

      srv_rowcount      NUMBER (8);



      CURSOR regrecursor

      IS

         SELECT nombremes (EXTRACT (MONTH FROM a.fecqrf)) AS mes,

                a.ordnumero AS norden, a.fecqrf AS feccirugia,

                a.horqrf AS horacirugia, e.pac_pac_rut AS idpaciente,

                   RTRIM (e.pac_pac_apellpater)

                || ' '

                || RTRIM (e.pac_pac_apellmater)

                || ' '

                || RTRIM (e.pac_pac_nombre) AS paciente,

                b.anesid AS idcirugia,

                   RTRIM (f.ser_pro_apellpater)

                || ' '

                || RTRIM (f.ser_pro_apellmater)

                || ' '

                || RTRIM (f.ser_pro_nombres) AS cirujano,

                c.pre_pre_codigo AS codpresta,

                h.pre_pre_descripcio AS nprestacion,

                c.porhonpor AS phonorario, c.ate_pre_montopagar AS monto,

                c.soccod AS sociedad, d.horaini AS horaini,

                SUBSTR (d.horafin, 1, 2) AS horafin,

                d.horafin AS horacompfin, a.ser_obj_codigo AS servicio

           FROM tabprgqrf a,

                tabprdrel b,

                tabprdreldet c,

                taborddetcir d,

                pac_paciente e,

                ser_profesiona f,

                pre_prestacion h,

                ate_prestacion k

          WHERE a.ordnumero = b.ordnumero

            AND a.ordnumero = c.ordnumero

            AND b.ordnumero = k.ordnumero

            AND b.pre_pre_codigo = k.ate_pre_codigo

            AND c.cnpqrgcod = in_tipohonorario

            AND b.pre_pre_codigo = c.pre_pre_codigo

            AND a.ordnumero = d.ordnumero

            AND a.pac_pac_numero = e.pac_pac_numero

            AND b.ser_pro_rut = f.ser_pro_rut

            AND h.pre_pre_codigo = c.pre_pre_codigo

            AND TRUNC (a.fecqrf) BETWEEN TO_DATE (in_fechaini, 'yyyy/mm/dd')

                                     AND TO_DATE (in_fechafin, 'yyyy/mm/dd')

            AND (   d.horaini BETWEEN ('19:00') AND ('23:59')

                 OR d.horaini BETWEEN ('00:00') AND ('05:59')

                )

            AND (   d.horafin BETWEEN ('19:00') AND ('23:59')

                 OR d.horafin BETWEEN ('00:00') AND ('05:59')

                );

   BEGIN

      srv_totalrows := TO_NUMBER (RTRIM (srv_message));

      srv_rowcount := 0;

      srv_fetchstatus := 0;

      srv_message := '1000000';



/*-----------------------------------------------------------------*/

/*------------------------ Begin User Code ------------------------*/

/*-----------------------------------------------------------------*/

      BEGIN

         IF NOT regrecursor%ISOPEN

         THEN

            OPEN regrecursor;

         END IF;



                  

         

         LOOP

         

         FETCH regrecursor

             INTO mes (srv_rowcount + 1), 

                  norden (srv_rowcount + 1),

                  feccirugia (srv_rowcount + 1),

                  horacirugia (srv_rowcount + 1),

                  idpaciente (srv_rowcount + 1), 

                  paciente (srv_rowcount + 1),

                  idcirugia (srv_rowcount + 1), 

                  cirujano (srv_rowcount + 1),

                  codpresta (srv_rowcount + 1),

                  nprestacion (srv_rowcount + 1),

                  phonorario (srv_rowcount + 1), 

                  monto (srv_rowcount + 1),

                  sociedad (srv_rowcount + 1), 

                  horaini (srv_rowcount + 1),

                  horafin (srv_rowcount + 1), 

                  horacompfin (srv_rowcount + 1),

                  servicio (srv_rowcount + 1); 

                  

                 

                  

                  exit when  regrecursor%notfound; 

         

        /*    IF regrecursor%NOTFOUND

            THEN

               CLOSE regrecursor;



               EXIT;

            END IF;*/



            srv_rowcount := srv_rowcount + 1;

            srv_fetchstatus := 1;

         END LOOP;



         IF regrecursor%ISOPEN

         THEN

            CLOSE regrecursor;

         END IF;



         IF srv_rowcount > 0

         THEN

            srv_message :=

                  SUBSTR (srv_message, 1, 6) || '1'

                  || SUBSTR (srv_message, 8);

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            srv_message :=

                  SUBSTR (srv_message, 1, 1)

               || '38000'

               || srv_fetchstatus

               || 'Se produjo un ERROR';

      END;

      

/*------------------------- End User Code -------------------------*/

/*-----------------------------------------------------------------*/

      <>

      NULL;

      



END HonorarioNoc;  

end HonorarioNoc_PKG;

/

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;



namespace Programa_7

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }



        private void button1_Click(object sender, EventArgs e)

        {

            string cad_cnx = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersLaptopDocuments6 SemestreControl NumericoPosibles programasBase_Programa_7.xlsx;Extended Properties=""Excel 8.0;HDR=NO;IMEX=0;""");

            OleDbConnection Cnx = new OleDbConnection(cad_cnx);



            try

            {

                Cnx.Open();

            }



            catch

            {

                MessageBox.Show("Imposible conectar");

            }



            OleDbCommand Cmd = new OleDbCommand();

            Cmd.Connection = Cnx;

            OleDbTransaction Trans = Cnx.BeginTransaction();

            Cmd.Transaction = Trans;



            try

            {

                string consulta = "UPDATE [Hoja2$] SET Dato = @valor";

                OleDbParameter ptr = new OleDbParameter("@valor", textBox1.Text);

                Cmd.CommandText = consulta;

                Cmd.Parameters.Add(ptr);

                Cmd.ExecuteNonQuery();



                Trans.Commit();

            }



            catch

            {

                Trans.Rollback();

            }



            finally

            {

                Cnx.Close();

            }

            

            Form2 frm2 = new Form2();

            frm2.Show();

        }

    }

}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;



namespace Programa_7

{

    public partial class Form2 : Form

    {

        public Form2()

        {

            InitializeComponent();

        }



        private void Form2_Load(object sender, EventArgs e)

        {

            

        }



        private void button1_Click(object sender, EventArgs e)

        {

            string cad_cnx = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersLaptopDocuments6 SemestreControl NumericoPosibles programasBase_Programa_7.xlsx;Extended Properties=""Excel 8.0;HDR=NO;IMEX=0;""");

            OleDbConnection Cnx = new OleDbConnection(cad_cnx);



            try

            {

                Cnx.Open();

            }



            catch

            {

                MessageBox.Show("Imposible conectar");

            }



            OleDbDataAdapter DtA = new OleDbDataAdapter();

            OleDbCommand Cmd = new OleDbCommand();

            Cmd.Connection = Cnx;

            DataSet DtSet = new DataSet();



            try

            {

                string consulta = "select * from [Hoja1$]";

                DtA = new OleDbDataAdapter(consulta, cad_cnx);

                DtA.Fill(DtSet);

                dataGridView1.DataSource = DtSet.Tables[0];

            }



            finally

            {

                Cnx.Close();

            }

        }



        private void button2_Click(object sender, EventArgs e)

        {

        }

    }

}