public List <Pago> ObtenerTodos(int id)
{
var res = new List <Pago>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT p.Id, ContratoId, FechaPago, p.Importe," +
" i.Id , i.Importe, inq.Nombre, inq.Apellido" +
" FROM Pagos p INNER JOIN Contratos c ON p.ContratoId = c.Id INNER JOIN Inmuebles i ON c.InmuebleId = i.Id INNER JOIN Inquilinos inq ON inq.Id = c.InquilinoId WHERE c.id = @id AND p.Estado = 1";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
var reader = command.ExecuteReader();
while (reader.Read())
{
Pago p = new Pago
{
Id = reader.GetInt32(0),
IdContrato = reader.GetInt32(1),
FechaPago = reader.GetDateTime(2),
Importe = reader.GetInt32(3),
Contrato = new Contrato
{
Id = reader.GetInt32(1),
Inmueble = new Inmueble
{
Id = reader.GetInt32(4),
Importe = reader.GetInt32(5)
},
Inquilino = new Inquilino
{
Nombre = reader.GetString(6),
Apellido = reader.GetString(7)
}
}
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public int Modificar(Pago p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Pagos SET FechaPago=@fechaPago, ContratoId=@contratoId, Importe=@importe " +
$"WHERE Id = @id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@fechaPago", p.FechaPago);
command.Parameters.AddWithValue("@contratoId", p.IdContrato);
command.Parameters.AddWithValue("@importe", p.Importe);
command.Parameters.AddWithValue("@id", p.Id);
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}