public Pago ObtenerPorId(int id) { Pago p = null; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"SELECT IdPago, p.Importe, FechaDePago, p.IdContrato, " + $"c.IdInquilino, c.IdInmueble," + $"Inm.Direccion," + $"Inq.Nombre, Inq.Apellido FROM Pago p INNER JOIN Contrato c ON c.IdContrato=p.IdContrato " + $"INNER JOIN Inmueble Inm ON Inm.IdInmueble = c.IdInmueble " + $"INNER JOIN Inquilino Inq ON Inq.IdInquilino = c.IdInquilino " + $"WHERE p.IdPago=@id"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@id", id); connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { p = new Pago { IdPago = reader.GetInt32(0), Importe = reader.GetInt32(1), FechaDePago = reader.GetDateTime(2), IdContrato = reader.GetInt32(3), Contrato = new Contrato { IdInmueble = reader.GetInt32(4), IdInquilino = reader.GetInt32(5), Inmueble = new Inmueble { Direccion = reader.GetString(6), }, Inquilino = new Inquilino { Nombre = reader.GetString(7), Apellido = reader.GetString(8) } } }; } connection.Close(); } } return(p); }
public IList <Pago> ObtenerTodos() { IList <Pago> res = new List <Pago>(); using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"SELECT IdPago, p.Importe, FechaDePago, p.IdContrato, " + $"c.IdInmueble, c.IdInquilino," + $"Inm.Direccion," + $"Inq.Nombre, Inq.Apellido FROM Pago p INNER JOIN Contrato c ON p.IdContrato = c.IdContrato INNER JOIN Inmueble Inm ON Inm.IdInmueble = c.IdInmueble INNER JOIN Inquilino Inq ON Inq.IdInquilino = c.IdInquilino"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { Pago p = new Pago { IdPago = reader.GetInt32(0), Importe = reader.GetInt32(1), FechaDePago = reader.GetDateTime(2), IdContrato = reader.GetInt32(3), Contrato = new Contrato { IdInmueble = reader.GetInt32(4), IdInquilino = reader.GetInt32(5), Inmueble = new Inmueble { Direccion = reader.GetString(6), }, Inquilino = new Inquilino { Nombre = reader.GetString(7), Apellido = reader.GetString(8), } } }; res.Add(p); } connection.Close(); } } return(res); }
public int Modificacion(Pago p) { int res = -1; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = "UPDATE Pago SET " + "Importe=@importe, FechaDePago=@fechaDePago, IdContrato=@idContrato " + "WHERE IdPago = @id"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.Parameters.AddWithValue("@importe", p.Importe); command.Parameters.AddWithValue("@fechaDePago", p.FechaDePago); command.Parameters.AddWithValue("@idContrato", p.IdContrato); command.Parameters.AddWithValue("@id", p.IdPago); command.CommandType = CommandType.Text; connection.Open(); res = command.ExecuteNonQuery(); connection.Close(); } } return(res); }
public int Alta(Pago pago) { int res = -1; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"INSERT INTO Pago (Importe, FechaDePago, IdContrato) " + $"VALUES (@Importe, @FechaDePago, @IdContrato);" + "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("@Importe", pago.Importe); command.Parameters.AddWithValue("@FechaDePago", pago.FechaDePago); command.Parameters.AddWithValue("@IdContrato", pago.IdContrato); connection.Open(); res = Convert.ToInt32(command.ExecuteScalar()); pago.IdPago = res; connection.Close(); } } return(res); }
/*public IList<Pago> ObtenerPorContrato(int id) * { * IList<Pago> res = new List<Pago>(); * using (SqlConnection connection = new SqlConnection(connectionString)) * { * string sql = $"SELECT IdPago, p.Importe, FechaDePago, p.IdContrato, " + * $"c.IdInmueble, c.IdInquilino," + * $"Inm.Direccion," + * $"Inq.Nombre, Inq.Apellido FROM Pago p INNER JOIN Contrato c ON c.IdContrato=p.IdContrato " + * $"INNER JOIN Inmueble Inm ON Inm.IdInmueble = c.IdInmueble " + * $"INNER JOIN Inquilino Inq ON Inq.IdInquilino = c.IdInquilino " + * $"WHERE p.IdContrato=@id"; * * using (SqlCommand command = new SqlCommand(sql, connection)) * { * command.CommandType = CommandType.Text; * command.Parameters.AddWithValue("@id", id); * connection.Open(); * var reader = command.ExecuteReader(); * while (reader.Read()) * { * Pago p = new Pago * { * IdPago = reader.GetInt32(0), * Importe = reader.GetInt32(1), * FechaDePago = reader.GetDateTime(2), * IdContrato = reader.GetInt32(3), * Contrato = new Contrato * { * IdInmueble = reader.GetInt32(4), * IdInquilino = reader.GetInt32(5), * Inmueble = new Inmueble * { * Direccion = reader.GetString(6), * Tipo = reader.GetString(7), * }, * Inquilino = new Inquilino * { * Nombre = reader.GetString(8), * Apellido = reader.GetString(9) * } * } * }; * res.Add(p); * } * connection.Close(); * } * } * return res; * * }*/ public IList <Pago> BuscarPorContrato(int id) { IList <Pago> res = new List <Pago>(); using (SqlConnection connection = new SqlConnection(connectionString)) { //sql funcional string consultasql = "SELECT c.IdContrato, FechaInicio, FechaFin, c.IdInquilino, c.IdInmueble," + " i.Nombre AS InquilinoNombre, i.Apellido AS InquilinoApellido," + "inm.Direccion, inm.IdPropietario, p.Nombre AS PropietarioNombre," + "p.Apellido AS PropietarioApellido," + "pa.IdPago AS PagoId, pa.Importe AS importe," + "pa.FechaDePago AS fechaPago, pa.IdContrato" + " FROM Contrato c " + " INNER JOIN Inquilino i ON i.IdInquilino = c.IdInquilino" + " INNER JOIN Inmueble inm ON inm.IdInmueble = c.IdInmueble" + " INNER JOIN Propietario p ON inm.IdPropietario = p.IdPropietario" + " INNER JOIN Pago pa ON pa.IdContrato = c.IdContrato " + "WHERE c.IdContrato=@id"; using (SqlCommand command = new SqlCommand(consultasql, connection)) { command.Parameters.Add("@id", SqlDbType.Int).Value = id; command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { Pago pago = new Pago { IdPago = reader.GetInt32(11), Importe = reader.GetInt32(12), FechaDePago = reader.GetDateTime(13), IdContrato = reader.GetInt32(0), Contrato = new Contrato { IdContrato = reader.GetInt32(0), FechaInicio = reader.GetDateTime(1), FechaFin = reader.GetDateTime(2), IdInquilino = reader.GetInt32(3), IdInmueble = reader.GetInt32(4), Inquilino = new Inquilino { IdInquilino = reader.GetInt32(3), Nombre = reader.GetString(5), Apellido = reader.GetString(6), }, Inmueble = new Inmueble { IdInmueble = reader.GetInt32(4), Direccion = reader.GetString(7), IdPropietario = reader.GetInt32(8), Propietario = new Propietario { IdPropietario = reader.GetInt32(8), Nombre = reader.GetString(9), Apellido = reader.GetString(10), } } }, }; res.Add(pago); } connection.Close(); } } return(res); }