コード例 #1
0
        public int Alta(Pago pago)
        {
            int res = -1;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = $"INSERT INTO pagos (Fecha, Monto, ContratoId) " +
                             "VALUES (@Fecha, @Monto, @ContratoId);" +
                             "SELECT SCOPE_IDENTITY();";           //devuelve el id insertado (LAST_INSERT_ID para mysql)
                using (var command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddWithValue("@Fecha", pago.Fecha);
                    command.Parameters.AddWithValue("@Monto", pago.Monto);
                    command.Parameters.AddWithValue("@ContratoId", pago.ContratoId);
                    connection.Open();
                    res     = Convert.ToInt32(command.ExecuteScalar());
                    pago.Id = res;
                    connection.Close();
                }
            }
            return(res);
        }
コード例 #2
0
        public int Modificacion(Pago pago)
        {
            int res = -1;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = "UPDATE pagos SET " +
                             "Fecha=@Fecha, Monto=@Monto, ContratoId=@ContratoId " +
                             "WHERE Id = @id";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Fecha", pago.Fecha);
                    command.Parameters.AddWithValue("@Monto", pago.Monto);
                    command.Parameters.AddWithValue("@ContratoId", pago.ContratoId);

                    command.Parameters.AddWithValue("@id", pago.Id);
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    res = command.ExecuteNonQuery();
                    connection.Close();
                }
            }
            return(res);
        }
コード例 #3
0
        public IList <Pago> ObtenerTodos()
        {
            IList <Pago> res = new List <Pago>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //sql funcional
                string consultasql = "SELECT c.Id, FechaDesde, FechaHasta, InquilinoId, InmuebleId," +
                                     " i.Nombre AS InquilinoNombre,i.Apellido AS InquilinoApellido," +
                                     "inm.Direccion, inm.PropietarioId,p.Nombre AS PropietarioNombre ," +
                                     "p.Apellido AS PropietarioApellido ," +
                                     "pa.id AS PagoId,pa.fecha AS fechaPago ," +
                                     "pa.monto AS montoPago,pa.contratoId" +
                                     " FROM Contratos c " +
                                     " INNER JOIN Inquilinos i ON i.Id = c.InquilinoId" +
                                     " INNER JOIN Inmuebles inm ON inm.Id = c.InmuebleId" +
                                     " INNER JOIN Propietarios p ON inm.PropietarioId = p.Id" +
                                     " INNER JOIN Pagos pa ON pa.ContratoId = c.Id";


                using (SqlCommand command = new SqlCommand(consultasql, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Pago pago = new Pago
                        {
                            Id         = reader.GetInt32(11),
                            Fecha      = reader.GetDateTime(12),
                            Monto      = reader.GetDecimal(13),
                            ContratoId = reader.GetInt32(0),

                            Contrato = new Contrato
                            {
                                Id          = reader.GetInt32(0),
                                FechaDesde  = reader.GetDateTime(1),
                                FechaHasta  = reader.GetDateTime(2),
                                InquilinoId = reader.GetInt32(3),
                                InmuebleId  = reader.GetInt32(4),

                                Inquilino = new Inquilino
                                {
                                    Id       = reader.GetInt32(3),
                                    Nombre   = reader.GetString(5),
                                    Apellido = reader.GetString(6),
                                },

                                Inmueble = new Inmueble
                                {
                                    Id            = reader.GetInt32(4),
                                    Direccion     = reader.GetString(7),
                                    PropietarioId = reader.GetInt32(8),

                                    Duenio = new Propietario
                                    {
                                        Id       = reader.GetInt32(8),
                                        Nombre   = reader.GetString(9),
                                        Apellido = reader.GetString(10),
                                    }
                                }
                            },
                        };
                        res.Add(pago);
                    }
                    connection.Close();
                }
            }
            return(res);
        }
コード例 #4
0
        public Pago ObtenerPorId(int id)
        {
            Pago pago = null;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = "SELECT pa.Id,pa.monto,pa.fecha,pa.contratoId," +
                             "c.FechaDesde, c.FechaHasta, c.InquilinoId, c.InmuebleId," +
                             " i.Nombre,i.Apellido," +
                             "inm.Direccion, inm.PropietarioId," +
                             "p.Nombre,p.Apellido" +
                             " FROM Pagos pa " +
                             "INNER JOIN Contratos c ON Pa.contratoId = c.Id" +
                             " INNER JOIN Inquilinos i ON i.Id = c.InquilinoId" +
                             " INNER JOIN Inmuebles inm ON inm.Id = c.InmuebleId" +
                             " INNER JOIN Propietarios p ON inm.PropietarioId = p.Id" +
                             $" WHERE pa.Id=@id ";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add("@id", SqlDbType.Int).Value = id;
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    var reader = command.ExecuteReader();
                    if (reader.Read())
                    {
                        pago = new Pago
                        {
                            Id         = reader.GetInt32(0),
                            Fecha      = reader.GetDateTime(2),
                            Monto      = reader.GetDecimal(1),
                            ContratoId = reader.GetInt32(3),



                            Contrato = new Contrato
                            {
                                Id          = reader.GetInt32(0),
                                FechaDesde  = reader.GetDateTime(4),
                                FechaHasta  = reader.GetDateTime(5),
                                InquilinoId = reader.GetInt32(6),
                                InmuebleId  = reader.GetInt32(7),

                                Inquilino = new Inquilino
                                {
                                    Id       = reader.GetInt32(6),
                                    Nombre   = reader.GetString(8),
                                    Apellido = reader.GetString(9),
                                },

                                Inmueble = new Inmueble
                                {
                                    Id            = reader.GetInt32(7),
                                    Direccion     = reader.GetString(10),
                                    PropietarioId = reader.GetInt32(11),

                                    Duenio = new Propietario
                                    {
                                        Id       = reader.GetInt32(11),
                                        Nombre   = reader.GetString(12),
                                        Apellido = reader.GetString(13),
                                    }
                                }
                            }
                        };
                        connection.Close();
                    }
                }
                return(pago);
            }
        }