예제 #1
0
 //Recibe el nombre de la tabla sacada de Conexion.Tabla, y un diccionario con el par
 //("nombre de columna en BD", dato a insertar)
 //retorna true si se pudo realizar, false si fallo
 public int Insertar(string tabla, Dictionary <string, object> data)
 {
     try
     {
         string comandoString = string.Copy(comandoInsert) + tabla + " (";
         data.Keys.ToList().ForEach(k => comandoString += k + ", ");
         comandoString = comandoString.Substring(0, comandoString.Length - 2) + ") VALUES (";
         data.Keys.ToList().ForEach(k => comandoString += "@" + k + ", ");
         comandoString = comandoString.Substring(0, comandoString.Length - 2) + "); SELECT SCOPE_IDENTITY();";
         PinkieLogger.logInsert(comandoString, data);
         using (SqlConnection sqlConnection = new SqlConnection(conectionString))
         {
             sqlConnection.Open();
             using (SqlCommand command = new SqlCommand())
             {
                 command.Connection  = sqlConnection;
                 command.CommandType = CommandType.Text;
                 command.CommandText = comandoString;
                 foreach (KeyValuePair <string, object> entry in data)
                 {
                     command.Parameters.AddWithValue("@" + entry.Key, entry.Value);
                 }
                 return(Convert.ToInt32(command.ExecuteScalar()));
             }
         }
     }
     catch (SqlException ex)
     {
         PinkieLogger.logExcepcion(ex);
         return(-1);
     }
 }
예제 #2
0
 //Recibe el id de la fila, nombre de la tabla sacada de Conexion.Tabla, y
 //un diccionario con el par ("nombre de columna en BD", dato a insertar
 //retorna true si se pudo realizar, false si fallo
 public bool Modificar(int pk, string tabla, Dictionary <string, object> data)
 {
     try
     {
         string comandoString = string.Copy(comandoUpdate) + tabla + " SET ";
         foreach (KeyValuePair <string, object> entry in data)
         {
             comandoString += entry.Key + " = @" + entry.Key + ", ";
         }
         comandoString = comandoString.Substring(0, comandoString.Length - 2) + " WHERE id = @id";
         PinkieLogger.logUpdate(comandoString, data);
         using (SqlConnection sqlConnection = new SqlConnection(conectionString))
         {
             sqlConnection.Open();
             using (SqlCommand command = new SqlCommand())
             {
                 command.Connection  = sqlConnection;
                 command.CommandType = CommandType.Text;
                 command.CommandText = comandoString;
                 command.Parameters.AddWithValue("@id", pk);
                 foreach (KeyValuePair <string, object> entry in data)
                 {
                     command.Parameters.AddWithValue("@" + entry.Key, entry.Value);
                 }
                 command.ExecuteNonQuery();
             }
         }
     }
     catch (SqlException ex)
     {
         PinkieLogger.logExcepcion(ex);
         return(false);
     }
     return(true);
 }
예제 #3
0
        public DataTable ConseguirTabla(string tabla, List <Filtro> filtros)
        {
            string comandoString = string.Copy(comandoSelect) + " * FROM " + tabla;

            if (filtros != null && filtros.Count > 0)
            {
                comandoString = PonerFiltros(comandoString, filtros);
            }
            PinkieLogger.logSelect(comandoString, "llenar grid");
            using (SqlConnection sqlConnection = new SqlConnection(conectionString))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCmd = new SqlCommand())
                {
                    sqlCmd.Connection  = sqlConnection;
                    sqlCmd.CommandType = CommandType.Text;
                    sqlCmd.CommandText = comandoString;
                    SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);

                    DataTable dtRecord = new DataTable();
                    sqlDataAdap.Fill(dtRecord);

                    return(dtRecord);
                }
            }
        }
예제 #4
0
 public bool PagarReserva(int codigo, string tabla)
 {
     try
     {
         string comandoString = string.Copy(comandoUpdate) + tabla + " SET pagado = 1 WHERE codigo = @id";
         using (SqlConnection sqlConnection = new SqlConnection(conectionString))
         {
             sqlConnection.Open();
             using (SqlCommand command = new SqlCommand())
             {
                 command.Connection  = sqlConnection;
                 command.CommandType = CommandType.Text;
                 command.CommandText = comandoString;
                 command.Parameters.AddWithValue("@id", codigo);
                 command.ExecuteNonQuery();
             }
         }
     }
     catch (SqlException ex)
     {
         PinkieLogger.logExcepcion(ex);
         return(false);
     }
     return(true);
 }
예제 #5
0
        public DataTable TraerLitadoEstadistico(string nombreView, DateTime fechaInicio, DateTime fechaFin)
        {
            string condicion     = " WHERE fecha_fin BETWEEN '" + fechaInicio.ToString("yyyy-MM-dd") + "' AND '" + fechaFin.ToString("yyyy-MM-dd") + "'";
            string comandoString = String.Empty;

            switch (nombreView)
            {
            case "PINKIE_PIE.top_5_recorridos":
                comandoString = "SELECT TOP 5 codigo_recorrido, puerto_origen, puerto_destino, SUM(cant_pasaje) as cant FROM PINKIE_PIE.top_5_recorridos " +
                                condicion + " GROUP BY codigo_recorrido, puerto_origen, puerto_destino ORDER BY cant DESC";
                break;

            case "PINKIE_PIE.top_5_clientes_puntos":
                return(ConseguirTabla(nombreView, null));

            case "PINKIE_PIE.top_5_viajes_cabinas_vacias":
                comandoString = "SELECT TOP 5 viaje_id, cod_recorrido, SUM(cant_cabinas) as cant FROM " + nombreView + condicion
                                + " GROUP BY viaje_id, cod_recorrido ORDER BY cant DESC";
                break;

            case "PINKIE_PIE.top_5_dias_crucero_fuera_servicio":
                comandoString = "SELECT TOP 5 identificador, fabricante, modelo, SUM(cant_dias) as cant FROM PINKIE_PIE.top_5_dias_crucero_fuera_servicio"
                                + condicion + " GROUP BY identificador, fabricante, modelo ORDER BY cant DESC";
                break;
            }
            PinkieLogger.logSelect(comandoString, "listado estadistico");
            DataTable dtRecord = new DataTable();

            using (SqlConnection sqlConnection = new SqlConnection(conectionString))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCmd = new SqlCommand())
                {
                    sqlCmd.Connection  = sqlConnection;
                    sqlCmd.CommandType = CommandType.Text;
                    sqlCmd.CommandText = comandoString;
                    SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);

                    sqlDataAdap.Fill(dtRecord);
                }
            }
            return(dtRecord);
        }
예제 #6
0
        private void CambiarHabilitacion(string tabla, int id, string cambio)
        {
            string comandoString = string.Copy(comandoUpdate) + tabla + " SET habilitado = " + cambio + " WHERE id = @id";

            PinkieLogger.EscribirArchivo("Se genera cambio de habilitacion con id = " + id.ToString() + "\n" + comandoString);
            using (SqlConnection connection = new SqlConnection(conectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.CommandText = comandoString;
                    command.CommandType = CommandType.Text;
                    command.Connection  = connection;
                    command.Parameters.AddWithValue("@id", id);

                    command.ExecuteNonQuery();
                }
            }
        }
예제 #7
0
        public void LlenarDataGridViewCruceros(ref DataGridView dataGrid)
        {
            string comandoString = "SELECT cru.id, cru.modelo, fab.nombre as 'fabricante', cru.identificador, cru.fecha_de_alta, cru.fecha_baja_definitiva, cru.baja_fuera_de_servicio, cru.baja_vida_util FROM " + Tabla.Crucero + "cru JOIN " + Tabla.Fabricante + " fab ON cru.fabricante_id=fab.id";

            PinkieLogger.logSelect(comandoString, "llenar grid");
            using (SqlConnection sqlConnection = new SqlConnection(conectionString))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCmd = new SqlCommand())
                {
                    sqlCmd.Connection  = sqlConnection;
                    sqlCmd.CommandType = CommandType.Text;
                    sqlCmd.CommandText = comandoString;
                    SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);

                    DataTable dtRecord = new DataTable();
                    sqlDataAdap.Fill(dtRecord);

                    dataGrid.DataSource = dtRecord;
                }
            }
        }