private void CargarDatos() { if (Pro_Conexion.State != ConnectionState.Open) { Pro_Conexion.Open(); } string sentencia = "SELECT * FROM area_servicio.ft_view_ultima_tasa_cambio();"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { lblVenta.Text = pgDr.GetString("venta"); lblCompra.Text = pgDr.GetString("compra"); } pgDr.Close(); sentencia = null; pgComando.Dispose(); } catch (Exception Exc) { MessageBox.Show("Algo salió mal en el momento de cargar la tasa de cambio. " + Exc.Message); } }
public static DateTime ObtenerHoraServidor(PgSqlConnection pConexion) { DateTime v_resultado = Convert.ToDateTime(null); if (pConexion.State != System.Data.ConnectionState.Open) { pConexion.Open(); } string sentencia = "SELECT * FROM arca_tesoros_conf.ft_view_variables_tiempo();"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, pConexion); try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { v_resultado = pgDr.GetDateTime("fecha_hora_servidor"); } pgDr.Close(); sentencia = null; pgComando.Dispose(); pgComando = null; return(v_resultado); } catch (Exception Exc) { Log_Excepciones.CapturadorExcepciones(Exc, "Utilidades.cs", "ObtenerHoraServidor"); return(Convert.ToDateTime(null)); } }
private void ObtenerActividad() { if (Pro_Conexion.State != ConnectionState.Open) { Pro_Conexion.Open(); } string sentencia = @"SELECT * FROM arca_tesoros.ft_proc_obtener_actividad_del_dia (:p_id_area_atencion)"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_id_area_atencion", PgSqlType.Int).Value = Pro_ID_Area_Atencion; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { Pro_ID_Actividad = pgDr.GetInt32("id_actividad"); } pgDr.Close(); } catch (Exception Exc) { Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "ObtenerActividad"); } }
private void CargarDatosColaborador() { if (Pro_Conexion.State != ConnectionState.Open) { Pro_Conexion.Open(); } string sentencia = @"SELECT * FROM arca_tesoros.ft_view_ficha_ingreso (:p_id_colaborador)"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_id_colaborador", PgSqlType.Int).Value = Pro_ID_Colaborador; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { txtNombreColaborador.Text = pgDr.GetString("nombre_colaborador"); txtIdentidadColaborador.Text = pgDr.GetString("numero_identidad"); txtFuerzasEspeciales.Text = pgDr.GetString("area_atencion"); v_ruta_fotografia = pgDr.GetString("direccion_fotografia"); } pgDr.Close(); } catch (Exception Exc) { Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "CargarDatosColaborador"); } }
public void CargarDatos() { v_lista_noticias.Clear(); if (Pro_Conexion.State != ConnectionState.Open) { Pro_Conexion.Open(); } string sentencia = @"SELECT * FROM area_servicio.ft_view_noticias_cliente (:p_id_cliente_servicio);"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); while (pgDr.Read()) { v_lista_noticias.Add(pgDr.GetString("texto_noticia")); } pgDr.Close(); sentencia = null; pgComando.Dispose(); } catch (Exception Exc) { MessageBox.Show("Algo salió mal en el momento de obtener lista de noticias. " + Exc.Message); } }
public List <Estatus> Estatus(DateTime fecha1, DateTime fecha2) { fecha1 = Convert.ToDateTime(fecha1.ToShortDateString()); fecha2 = Convert.ToDateTime(fecha2.ToShortDateString()); fecha1 = Convert.ToDateTime(fecha1); fecha2 = Convert.ToDateTime(fecha2); List <Estatus> lista = new List <Estatus>(); using (PgSqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = " select*from Modifica"; using (PgSqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read() == true) { DateTime fechaRegistro = dr.GetDateTime("fecha"); if ((fechaRegistro >= fecha1) && (fechaRegistro <= fecha2)) { lista.Add(new Estatus(dr.GetDecimal("modificaciones"), dr.GetString("tipo"), dr.GetDateTime("fecha"))); } } dr.Close(); } } return(lista); }
static void Count(PgSqlConnection connection) { DataTable datatable = new DataTable(); PgSqlCommand command = connection.CreateCommand(); command.CommandText = @"SELECT COUNT (_uid) FROM public._gps_log"; Console.WriteLine("Starting asynchronous retrieval of data..."); IAsyncResult cres = command.BeginExecuteReader(); if (cres.IsCompleted) { Console.WriteLine("Completed."); } else { Console.WriteLine("Have to wait for operation to complete..."); } PgSqlDataReader myReader = command.EndExecuteReader(cres); try { // printing the column names for (int i = 0; i < myReader.FieldCount; i++) { Console.Write(myReader.GetName(i).ToString() + "\t"); datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string)); } Console.Write(Environment.NewLine); while (myReader.Read()) { DataRow dr = datatable.NewRow(); for (int i = 0; i < myReader.FieldCount; i++) { Console.Write(myReader.GetString(i) + "\t"); dr[i] = myReader.GetString(i); } datatable.Rows.Add(dr); Console.Write(Environment.NewLine); //Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t"); } } finally { myReader.Close(); } foreach (DataRow row in datatable.Rows) // Loop over the rows. { Console.WriteLine("--- Row ---"); // Print separator. foreach (var item in row.ItemArray) // Loop over the items. { Console.Write("Item: "); // Print label. Console.WriteLine(item); // Invokes ToString abstract method. } } Console.WriteLine("############"); Console.WriteLine(datatable.Rows[0].ItemArray[0].ToString()); }
private void ObtenerFusiblesIndicadores() { if (Pro_Conexion.State != ConnectionState.Open) { Pro_Conexion.Open(); } string sentencia = "SELECT * FROM arca_tesoros.ft_proc_obtiene_fusibles_indicadores();"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { v_conteo_fusible_solicitudes = pgDr.GetInt32("conteo_solicitudes"); v_conteo_fusible_cumpleanieros = pgDr.GetInt32("conteo_cumpleanieros"); if (v_conteo_fusible_cumpleanieros == 0) { fusibleCumpleanios.Visible = false; } else { fusibleCumpleanios.Visible = true; } } pgDr.Close(); } catch (Exception Exc) { } }
private void ObtenerInformacionActividad() { if (Pro_Conexion.State != ConnectionState.Open) { Pro_Conexion.Open(); } string sentencia = @"SELECT * FROM arca_tesoros.ft_view_datos_actividades ( :p_id_actividad )"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_id_actividad", PgSqlType.Int).Value = Pro_ID_Actividad; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { lblEncabezado.Text = "Lista de Asistencia para el día " + pgDr.GetDateTime("fecha").Date.ToShortDateString(); } pgDr.Close(); } catch (Exception Exc) { Log_Excepciones.CapturadorExcepciones(Exc, this.Name, "ObtenerInformacionActividad"); } }
public List <Ventas> ObtenerListaVENTASDelDía() { List <Ventas> listpro = new List <Ventas>(); DateTime fechaActual = DateTime.Today; using (PgSqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = " SELECT * FROM ventas ORDER BY id"; ///select *FROM ventas WHERE fecha_venta= '2015/12/12' using (PgSqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read() == true) { DateTime fechaDeVenta = dr.GetDateTime("fecha_venta"); if (fechaDeVenta == fechaActual) { listpro.Add(new Ventas(dr.GetInt32("id_productos"), dr.GetInt32("cantidad"), dr.GetDecimal("precio_de_venta"), dr.GetDateTime("fecha_venta"))); } } dr.Close(); } } return(listpro); }
private bool CargarDatosTicketPosicion() { string v_datos_posicion = null; ValidarConexion(); if (Pro_Conexion.State != ConnectionState.Open) { Pro_Conexion.Open(); } string sentencia = @"SELECT * FROM area_servicio.ft_view_posicion_asignada ( :p_usuario, :p_agencia, :p_cliente );"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = Pro_UsuarioEmpleado; pgComando.Parameters.Add("p_agencia", PgSqlType.Int).Value = Pro_Sucursal; pgComando.Parameters.Add("p_cliente", PgSqlType.Int).Value = Pro_Cliente; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { v_datos_posicion = ConfigurationSettings.AppSettings["TEXTO_DESCRIPTIVO"] + " " + pgDr.GetString("posicion"); } pgDr.Close(); pgDr = null; pgComando.Dispose(); sentencia = null; if (string.IsNullOrEmpty(v_datos_posicion)) { return(false); } else { return(true); } } catch (Exception Exc) { DepuradorExcepciones v_depurador = new DepuradorExcepciones(); v_depurador.CapturadorExcepciones(Exc, this.Name, "CargarDatosTicketPosicion()"); v_depurador = null; MessageBox.Show(Exc.Message, "FLUCOL"); return(false); } }
private void CargarDatosEmpleadoParaEdicion(string pCodigoEmpleado) { ValidarConexion(); string sentencia = @"SELECT * FROM area_servicio.ft_view_datos_empleado_para_edicion(:pCodigoEmpleado);"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("pCodigoEmpleado", PgSqlType.VarChar).Value = pCodigoEmpleado; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); int v_id_cargo = 0; int v_id_agencia = 0; while (pgDr.Read()) { txtCodigoEmpleado.Text = pgDr.GetString("codigo_empleado"); txtPrimerNombre.Text = pgDr.GetString("primer_nombre"); txtSegundoNombre.Text = pgDr.GetString("segundo_nombre"); txtPrimerApellido.Text = pgDr.GetString("primer_apellido"); txtSegundoApellido.Text = pgDr.GetString("segundo_apellido"); txtIdentidadEmpleado.Text = pgDr.GetString("identidad_empleado"); v_id_cargo = pgDr.GetInt32("id_cargo"); v_id_agencia = pgDr.GetInt32("id_agencia"); } foreach (dsConfiguraciones.dtCargosEmpleadosRow iterador in dsConfiguraciones1.dtCargosEmpleados) { if (iterador.id_cargo == v_id_cargo) { gridCargos.EditValue = iterador.id_cargo; break; } } foreach (dsConfiguraciones.dtAgenciasServicioRow iterador in dsConfiguraciones1.dtAgenciasServicio) { if (iterador.id_agencia_servicio == v_id_agencia) { gridAgencias.EditValue = iterador.id_agencia_servicio; break; } } pgDr.Close(); sentencia = null; pgComando.Dispose(); } catch (Exception Exc) { MessageBox.Show("ALGO SALIO EN EL MOMENTO DE REGISTRAR EL EMPLEADO. " + Exc.Message, "FLUCOL"); } }
private void LlamadoTickets() { ValidarConexion(); PgSqlConnection vConexion = new PgSqlConnection(Pro_Conexion.ConnectionString); vConexion.Password = Pro_Conexion.Password; vConexion.Open(); string sentencia = @"SELECT * FROM area_servicio.ft_view_consulta_llamados_tickets ( :p_agencia_servicio, :p_cliente_servicio )"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, vConexion); pgComando.Parameters.Add("p_agencia_servicio", PgSqlType.Int).Value = Pro_Sucursal; pgComando.Parameters.Add("p_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { v_ticket = pgDr.GetString("ticket"); v_posicion = pgDr.GetString("posicion"); v_tipo_ticket = pgDr.GetInt32("tipo_ticket"); v_primera_letra = pgDr.GetString("primera_letra"); v_segunda_letra = pgDr.GetString("segunda_letra"); v_tercera_letra = pgDr.GetString("tercera_letra"); v_cuarta_letra = pgDr.GetString("cuarta_letra"); v_quinta_letra = pgDr.GetString("quinta_letra"); v_sexta_letra = pgDr.GetString("sexta_letra"); v_longitud_ticket = pgDr.GetInt32("longitud_ticket"); ReproducirAudioLlamadoTicket(); } pgDr.Close(); pgDr = null; pgComando.Dispose(); vConexion.Close(); vConexion.Dispose(); sentencia = null; } catch (Exception Exc) { DepuradorExcepciones v_depurador = new DepuradorExcepciones(); v_depurador.CapturadorExcepciones(Exc, this.Name, "LlamadoTickets()"); v_depurador = null; } }
private void GenerarTicket() { ValidarConexion(); splashScreenManager1.ShowWaitForm(); PgSqlTransaction pgTrans = Pro_Conexion.BeginTransaction(); string sentencia = @"SELECT * FROM configuracion.sp_proc_genera_correlativos_ticket ( :p_id_agencia_servicio, :p_id_cliente_servicio, :p_id_tipo_ticket_servicio, :p_id_operacion_servicio, :p_direccion_ip );"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio; pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio; pgComando.Parameters.Add("p_id_tipo_ticket_servicio", PgSqlType.Int).Value = Pro_ID_Tipo_Ticket_Servicio; pgComando.Parameters.Add("p_id_operacion_servicio", PgSqlType.Int).Value = Pro_ID_Operacion_Servicio; pgComando.Parameters.Add("p_direccion_ip", PgSqlType.VarChar).Value = Pro_IP_Host; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { Pro_Ticket_Generado = pgDr.GetString("numero_ticket"); } pgTrans.Commit(); pgDr.Close(); pgComando.Dispose(); sentencia = null; splashScreenManager1.CloseWaitForm(); } catch (Exception Exc) { splashScreenManager1.CloseWaitForm(); pgTrans.Rollback(); Pro_Ticket_Generado = null; MessageBox.Show(Exc.Message, "FLUCOL"); } }
private void ObtenerNombreSucursal() { if (Pro_Conexion.State != System.Data.ConnectionState.Open) { try { Pro_Conexion.Open(); } catch (Exception Exc) { DepuradorExcepciones v_depurador = new DepuradorExcepciones(); v_depurador.CapturadorExcepciones(Exc, this.Name, "ObtenerNombreSucursal()"); v_depurador = null; } } try { string sentencia = @"SELECT * FROM area_servicio.ft_view_nombre_agencia_servicio ( :p_id_agencia_servicio, :p_id_cliente_servicio );"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio; pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio; PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { Pro_NombreAgenciaServicio = pgDr.GetString("nombre_agencia"); } pgDr.Close(); pgComando.Dispose(); sentencia = null; } catch (Exception Exc) { DepuradorExcepciones v_depurador = new DepuradorExcepciones(); v_depurador.CapturadorExcepciones(Exc, this.Name, "ObtenerNombreSucursal()"); v_depurador = null; } }
private int ObtenerEstadoTicket() { ValidarConexion(); int v_estado_ticket = 0; string sentencia = @"SELECT * FROM area_servicio.ft_view_estado_ticket ( :p_id_ticket_servicio, :p_id_cliente_servicio, :p_id_agencia_servicio )"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_id_ticket_servicio", PgSqlType.VarChar).Value = Pro_Ticket_Servicio; pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio; pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { v_estado_ticket = pgDr.GetInt32("estado"); } pgDr.Close(); sentencia = null; pgComando.Dispose(); pgDr = null; return(v_estado_ticket); } catch (Exception Exc) { MessageBox.Show("ALGO SALIÓ MAL EN EL MOMENTO DE OBTENER ESTADO DEL TICKET. " + Exc.Message); DepuradorExcepciones v_depurador = new DepuradorExcepciones(); v_depurador.CapturadorExcepciones(Exc, this.Name, "ObtenerEstadoTicket()"); v_depurador = null; return(v_estado_ticket); } }
private void CargarDatos() { PgSqlConnection v_conexion_temporal = new PgSqlConnection(Pro_Conexion.ConnectionString); v_conexion_temporal.Password = Pro_Conexion.Password; v_conexion_temporal.Open(); string sentencia = @"SELECT * FROM area_servicio.ft_view_dashboard_empleados_con_mas_tickets_atendidos(:p_id_cliente_servicio, :p_id_agencia_servicio, :p_desde, :p_hasta);"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, v_conexion_temporal); pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_Cliente_Servicio; pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_Agencia_Servicio; pgComando.Parameters.Add("p_desde", PgSqlType.Date).Value = Pro_Desde; pgComando.Parameters.Add("p_hasta", PgSqlType.Date).Value = Pro_Hasta; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { lblNombreEmpleado.Text = pgDr.GetString("nombre_empleado"); lblNumeroTicketsAtendidos.Text = pgDr.GetString("numero_tickets_atendidos"); lblSucursalEmpleado.Text = pgDr.GetString("agencia_servicio"); } pgDr.Close(); pgDr = null; sentencia = null; pgComando.Dispose(); v_conexion_temporal.Close(); v_conexion_temporal.Dispose(); } catch (Exception Exc) { MessageBox.Show("Algo salió mal en el momento de cargar Dashboard \"EMPLEADO CON MAS TICKETS ATENDIDOS\"." + Exc.Message); } }
public List <Socios> ObtenerSocios() { List <Socios> list = new List <Socios>(); using (PgSqlCommand comand = conn.CreateCommand()) { comand.CommandText = "SELECT * FROM socios"; using (PgSqlDataReader dr = comand.ExecuteReader()) { while (dr.Read() == true) { list.Add(new Socios(dr.GetBoolean("administrador"), dr.GetString("nombre"), dr.GetString("contra"), dr.GetInt32("id"))); } dr.Close(); } return(list); } }
public List <Inventario> Inventariado() { List <Inventario> list = new List <Inventario>(); using (PgSqlCommand comand = conn.CreateCommand()) { comand.CommandText = "SELECT p.codigo, p.descripcion, i.existencias FROM productos p INNER JOIN inventario i ON p.id= i.id ORDER BY existencias"; using (PgSqlDataReader dr = comand.ExecuteReader()) { while (dr.Read() == true) { list.Add(new Inventario(dr.GetString("codigo"), dr.GetString("descripcion"), dr.GetInt32("existencias"))); } dr.Close(); } return(list); } }
public List <Estatus> Estatus() { List <Estatus> lista = new List <Estatus>(); using (PgSqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = " select*from Modifica"; using (PgSqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read() == true) { lista.Add(new Estatus(dr.GetDecimal("modificaciones"), dr.GetString("tipo"), dr.GetDateTime("fecha"))); } dr.Close(); } } return(lista); }
public Producto ObtenerProductoNombre(string Nombre) { Producto pro = new Producto(); using (PgSqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = " select *FROM productos WHERE descripcion='" + Nombre + "'"; ///select *FROM ventas WHERE fecha_venta= '2015/12/12' using (PgSqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read() == true) { pro = new Producto(dr.GetInt32("id"), dr.GetString("codigo"), dr.GetString("descripcion"), dr.GetDecimal("precio")); } dr.Close(); } return(pro); } }
public int NumeroMaximoDeProductos() { int numeromaximo = 0; using (PgSqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT max(id) from productos; "; using (PgSqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read() == true) { numeromaximo = dr.GetInt32("max"); } dr.Close(); } } return(numeromaximo + 1); }
private void CargarDatos() { PgSqlConnection v_conexion_temporal = new PgSqlConnection(Pro_Conexion.ConnectionString); v_conexion_temporal.Password = Pro_Conexion.Password; v_conexion_temporal.Open(); string sentencia = @"SELECT * FROM area_servicio.ft_view_dashboard_promedio_atencion( :p_id_cliente_servicio, :p_id_agencia_servicio, :p_desde, :p_hasta);"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, v_conexion_temporal); pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_ID_ClienteServicio; pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_ID_AgenciaServicio; pgComando.Parameters.Add("p_desde", PgSqlType.Date).Value = Pro_Desde; pgComando.Parameters.Add("p_hasta", PgSqlType.Date).Value = Pro_Hasta; try { PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { lblPromedioAtencion.Text = pgDr.GetString("promedio_atencion"); } pgDr.Close(); pgDr = null; sentencia = null; pgComando.Dispose(); v_conexion_temporal.Close(); v_conexion_temporal.Dispose(); } catch (Exception Exc) { MessageBox.Show("Algo salió mal en el momento de cargar Dashboard \"PROMEDIO DE ATENCION\"." + Exc.Message); } }
public bool GetDecimal(string query, string field, out decimal value) { bool found = false; value = 0; using (PgSqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = query; using (PgSqlDataReader dr = cmd.ExecuteReader()) { if (dr.Read() == true) { value = dr.GetDecimal(field); found = true; } dr.Close(); } } return(found); }
static void PrintDept(PgSqlConnection connection) { PgSqlCommand command = connection.CreateCommand(); command.CommandText = "select * from test"; //async Console.WriteLine("Starting asynchronous retrieval of data..."); IAsyncResult cres = command.BeginExecuteReader(); if (cres.IsCompleted) { Console.WriteLine("Completed."); } else { Console.WriteLine("Have to wait for operation to complete..."); } PgSqlDataReader myReader = command.EndExecuteReader(cres); try { // printing the column names for (int i = 0; i < myReader.FieldCount; i++) { Console.Write(myReader.GetName(i).ToString() + "\t"); } Console.Write(Environment.NewLine); while (myReader.Read()) { for (int i = 0; i < myReader.FieldCount; i++) { Console.Write(myReader.GetString(i) + "\t"); } Console.Write(Environment.NewLine); //Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t"); } } finally { myReader.Close(); } /* * // Call the Close method when you are finished using the PgSqlDataReader * // to use the associated PgSqlConnection for any other purpose. * // Or put the reader in the using block to call Close implicitly. * //sync * Console.WriteLine("Starting synchronous retrieval of data..."); * using (PgSqlDataReader reader = command.ExecuteReader()) * { * // printing the column names * for (int i = 0; i < reader.FieldCount; i++) * Console.Write(reader.GetName(i).ToString() + "\t"); * Console.Write(Environment.NewLine); * // Always call Read before accesing data * while (reader.Read()) * { * // printing the table content * for (int i = 0; i < reader.FieldCount; i++) * Console.Write(reader.GetValue(i).ToString() + "\t"); * Console.Write(Environment.NewLine); * } * } * */ }
//For SELECT statements public DataTable get_DataTable(string cmd) { try { if (pgSqlConnection != null && IsConnected) { DataTable datatable = new DataTable(); PgSqlCommand command = pgSqlConnection.CreateCommand(); command.CommandText = cmd; Console.WriteLine("Starting asynchronous retrieval of data..."); IAsyncResult cres = command.BeginExecuteReader(); if (cres.IsCompleted) { Console.WriteLine("Completed."); } else { Console.WriteLine("Have to wait for operation to complete..."); } PgSqlDataReader myReader = command.EndExecuteReader(cres); try { // printing the column names for (int i = 0; i < myReader.FieldCount; i++) { Console.Write(myReader.GetName(i).ToString() + "\t"); datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string)); } Console.Write(Environment.NewLine); while (myReader.Read()) { DataRow dr = datatable.NewRow(); for (int i = 0; i < myReader.FieldCount; i++) { Console.Write(myReader.GetString(i) + "\t"); dr[i] = myReader.GetString(i); } datatable.Rows.Add(dr); Console.Write(Environment.NewLine); //Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t"); } } finally { myReader.Close(); } foreach (DataRow row in datatable.Rows) // Loop over the rows. { Console.WriteLine("--- Row ---"); // Print separator. foreach (var item in row.ItemArray) // Loop over the items. { Console.Write("Item: "); // Print label. Console.WriteLine(item); // Invokes ToString abstract method. } } return(datatable); } else { return(null); } } catch (PgSqlException ex) { Console.WriteLine("Exception occurs: {0}", ex.Error); return(null); } }
//For SELECT statements public DataTable get_DataTable(string cmd) { Stopwatch stopWatch = new Stopwatch(); PgSqlCommand command = null; stopWatch.Start(); try { if (pgSqlConnection != null && IsConnected) { DataTable datatable = new DataTable(); command = pgSqlConnection.CreateCommand(); command.CommandText = cmd; //Console.WriteLine("Starting asynchronous retrieval of data..."); IAsyncResult cres = command.BeginExecuteReader(); //Console.Write("In progress..."); //while (!cres.IsCompleted) { //Console.Write("."); //Perform here any operation you need } //if (cres.IsCompleted) //Console.WriteLine("Completed."); //else //Console.WriteLine("Have to wait for operation to complete..."); PgSqlDataReader myReader = command.EndExecuteReader(cres); try { // printing the column names for (int i = 0; i < myReader.FieldCount; i++) { //Console.Write(myReader.GetName(i).ToString() + "\t"); datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string)); } //Console.Write(Environment.NewLine); while (myReader.Read()) { DataRow dr = datatable.NewRow(); for (int i = 0; i < myReader.FieldCount; i++) { //Console.Write(myReader.GetString(i) + "\t"); dr[i] = myReader.GetString(i); } datatable.Rows.Add(dr); //Console.Write(Environment.NewLine); //Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t"); } } finally { myReader.Close(); stopWatch.Stop(); // Get the elapsed time as a TimeSpan value. TimeSpan ts = stopWatch.Elapsed; // Format and display the TimeSpan value. string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10); SiAuto.Main.AddCheckpoint(Level.Debug, "sql query take time:" + elapsedTime, cmd); } /* * foreach (DataRow row in datatable.Rows) // Loop over the rows. * { * Console.WriteLine("--- Row ---"); // Print separator. * foreach (var item in row.ItemArray) // Loop over the items. * { * Console.Write("Item: "); // Print label. * Console.WriteLine(item); // Invokes ToString abstract method. * } * } */ if (command != null) { command.Dispose(); } command = null; return(datatable); } else { return(null); } } catch (PgSqlException ex) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("GetDataTable exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd); log.Error("GetDataTable exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd); Console.ResetColor(); if (command != null) { command.Dispose(); } command = null; return(null); } }
static void Test(PgSqlConnection con, string sql, CommandType cmdType, CommandBehavior behavior, string testDesc) { PgSqlCommand cmd = null; PgSqlDataReader rdr = null; int c; int results = 0; Console.WriteLine("Test: " + testDesc); Console.WriteLine("[BEGIN SQL]"); Console.WriteLine(sql); Console.WriteLine("[END SQL]"); cmd = new PgSqlCommand(sql, con); cmd.CommandType = cmdType; Console.WriteLine("ExecuteReader..."); rdr = cmd.ExecuteReader(behavior); if (rdr == null) { Console.WriteLine("IDataReader has a Null Reference."); } else { do { // get the DataTable that holds // the schema DataTable dt = rdr.GetSchemaTable(); if (rdr.RecordsAffected != -1) { // Results for // SQL INSERT, UPDATE, DELETE Commands // have RecordsAffected >= 0 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else if (dt == null) { Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else { // Results for // SQL not INSERT, UPDATE, nor DELETE // have RecordsAffected = -1 Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected); // Results for a SQL Command (CREATE TABLE, SET, etc) // will have a null reference returned from GetSchemaTable() // // Results for a SQL SELECT Query // will have a DataTable returned from GetSchemaTable() results++; Console.WriteLine("Result Set " + results + "..."); // number of columns in the table Console.WriteLine(" Total Columns: " + dt.Columns.Count); // display the schema foreach (DataRow schemaRow in dt.Rows) { foreach (DataColumn schemaCol in dt.Columns) { Console.WriteLine(schemaCol.ColumnName + " = " + schemaRow[schemaCol]); } Console.WriteLine(); } int nRows = 0; string output, metadataValue, dataValue; // Read and display the rows Console.WriteLine("Gonna do a Read() now..."); while (rdr.Read()) { Console.WriteLine(" Row " + nRows + ": "); for (c = 0; c < rdr.FieldCount; c++) { // column meta data DataRow dr = dt.Rows[c]; metadataValue = " Col " + c + ": " + dr["ColumnName"]; // column data if (rdr.IsDBNull(c) == true) { dataValue = " is NULL"; } else { dataValue = ": " + rdr.GetValue(c); } // display column meta data and data output = metadataValue + dataValue; Console.WriteLine(output); } nRows++; } Console.WriteLine(" Total Rows: " + nRows); } } while(rdr.NextResult()); Console.WriteLine("Total Result sets: " + results); rdr.Close(); } }
private bool ValidarUsuarioLogueo() { bool v_encontro_usuario = false; ValidarConexion(); try { string sentencia = @"SELECT * FROM area_servicio.ft_proc_valida_usuario_acceso ( :p_usuario, :p_contrasenia, :p_id_agencia_servicio, :p_id_cliente_servicio );"; PgSqlCommand pgComando = new PgSqlCommand(sentencia, Pro_Conexion); pgComando.Parameters.Add("p_usuario", PgSqlType.VarChar).Value = txtUsuario.Text; pgComando.Parameters.Add("p_contrasenia", PgSqlType.VarChar).Value = txtContrasenia.Text; pgComando.Parameters.Add("p_id_agencia_servicio", PgSqlType.Int).Value = Pro_Sucursal; pgComando.Parameters.Add("p_id_cliente_servicio", PgSqlType.Int).Value = Pro_Cliente; PgSqlDataReader pgDr = pgComando.ExecuteReader(); if (pgDr.Read()) { Pro_NombreEmpleado = pgDr.GetString("nombre_empleado"); Pro_UsuarioEmpleado = pgDr.GetString("usuario_empleado"); Pro_ID_NivelAcceso = pgDr.GetInt32("id_nivel_acceso_empleado"); Pro_DescripcionNivelAcceso = pgDr.GetString("nivel_acceso_empleado"); Pro_CargoEmpleado = pgDr.GetString("cargo_empleado"); Pro_CodigoEmpleado = pgDr.GetString("codigo_empleado"); v_encontro_usuario = true; } pgDr.Close(); sentencia = null; pgDr = null; pgComando.Dispose(); if (v_encontro_usuario) { if ((NIVELES_ACCESO)Pro_ID_NivelAcceso == NIVELES_ACCESO.OPERACIONAL) { if (CargarDatosTicketPosicion()) { return(true); } else { return(false); } } else { return(true); } } else { return(false); } } catch (Exception Exc) { MessageBox.Show(Exc.Message, "FLUCOL"); txtUsuario.Focus(); return(false); } }
private bool GetRd(String strSQL) { int results = 0; int c; try { connPG.Open(); cmdPG.CommandText = strSQL;// "select * from users"; cmdPG.Connection = connPG; rdr = cmdPG.ExecuteReader(); if (rdr == null) { Console.WriteLine("IDataReader has a Null Reference."); } else { do { // get the DataTable that holds // the schema DataTable dt = rdr.GetSchemaTable(); DataTable dtData = new DataTable(); DataView dv = new DataView(); dv.Table = dt; if (rdr.RecordsAffected != -1) { // Results for // SQL INSERT, UPDATE, DELETE Commands // have RecordsAffected >= 0 System.Diagnostics.Debug.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else if (dt == null) { System.Diagnostics.Debug.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else { // Results for // SQL not INSERT, UPDATE, nor DELETE // have RecordsAffected = -1 System.Diagnostics.Debug.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected); // Results for a SQL Command (CREATE TABLE, SET, etc) // will have a null reference returned from GetSchemaTable() // // Results for a SQL SELECT Query // will have a DataTable returned from GetSchemaTable() results++; System.Diagnostics.Debug.WriteLine("Result Set " + results + "..."); // number of columns in the table System.Diagnostics.Debug.WriteLine(" Total Columns: " + dt.Columns.Count); // display the schema foreach (DataRow schemaRow in dt.Rows) { foreach (DataColumn schemaCol in dt.Columns) { System.Diagnostics.Debug.WriteLine(schemaCol.ColumnName + " = " + schemaRow[schemaCol]); } // System.Diagnostics.Debug.WriteLine(); } int nRows = 0; string output, metadataValue, dataValue; // Read and display the rows System.Diagnostics.Debug.WriteLine("Gonna do a Read() now..."); while (rdr.Read()) { System.Diagnostics.Debug.WriteLine(" Row " + nRows + ": "); for (c = 0; c < rdr.FieldCount; c++) { // column meta data DataRow dr = dt.Rows[c]; metadataValue = " Col " + c + ": " + dr["ColumnName"]; // column data if (rdr.IsDBNull(c) == true) { dataValue = " is NULL"; } else { dataValue = ": " + rdr.GetValue(c); } // display column meta data and data output = metadataValue + dataValue; System.Diagnostics.Debug.WriteLine(output); } nRows++; } System.Diagnostics.Debug.WriteLine(" Total Rows: " + nRows); } } while (rdr.NextResult()); Console.WriteLine("Total Result sets: " + results); rdr.Close(); connPG.Close(); } getMessage = "Успешно!"; return(true); } catch (Exception ex) { getMessage = "Неудачно!" + ex; return(false); } }