public Pago ObtenerPorId(int id) { Pago p = null; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"SELECT IdPago, NumPago, p.IdContr,FechaPago, Importe, " + $"c.IdInq, c.IdInq," + $"Inm.Direccion, Inm.Tipo," + $"Inq.Nombre, Inq.Apellido FROM Pagos p INNER JOIN Contratos c ON c.IdContr=p.IdContr " + $"INNER JOIN Inmuebles Inm ON Inm.IdInm = c.IdInm " + $"INNER JOIN Inquilinos Inq ON Inq.IdInq = c.IdInq " + $"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), NumPago = reader.GetInt32(1), IdContr = reader.GetInt32(2), FechaPago = reader.GetDateTime(3), Importe = reader.GetDecimal(4), Contrato = new Contrato { IdInm = reader.GetInt32(5), IdInq = reader.GetInt32(6), Inmueble = new Inmueble { Direccion = reader.GetString(7), Tipo = reader.GetString(8), }, Inquilino = new Inquilino { Nombre = reader.GetString(9), Apellido = reader.GetString(10) } } }; } connection.Close(); } } return(p); }
public IList <Pago> ObtenerTodos() { IList <Pago> res = new List <Pago>(); using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"SELECT IdPago, NumPago, p.IdContr, FechaPago, Importe, " + $"c.IdInm, c.IdInq," + $"Inm.Direccion, Inm.Tipo," + $" Inq.Nombre, Inq.Apellido FROM Pagos p INNER JOIN Contratos c ON p.IdContr = c.IdContr INNER JOIN Inmuebles Inm ON Inm.IdInm = c.IdInm INNER JOIN Inquilinos Inq ON Inq.IdInq = c.IdInq"; 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), NumPago = reader.GetInt32(1), IdContr = reader.GetInt32(2), FechaPago = reader.GetDateTime(3), Importe = reader.GetDecimal(4), Contrato = new Contrato { IdInm = reader.GetInt32(5), IdInq = reader.GetInt32(6), Inmueble = new Inmueble { Direccion = reader.GetString(7), Tipo = reader.GetString(8), }, Inquilino = new Inquilino { Nombre = reader.GetString(9), Apellido = reader.GetString(10), } } }; res.Add(p); } connection.Close(); } } return(res); }
public int Modificacion(Pago p) { int res = -1; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"UPDATE Pagos SET Importe=@importe " + $"WHERE IdPago=@id"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@importe", p.Importe); command.Parameters.AddWithValue("@id", p.IdPago); connection.Open(); res = command.ExecuteNonQuery(); connection.Close(); } } return(res); }
public int Alta(Pago p) { int res = -1; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"INSERT INTO Pagos(NumPago, IdContr, FechaPago, Importe)" + $"VALUES (@pago, @idContr, @fec, @importe);" + $"SELECT SCOPE_IDENTITY();"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@pago", p.NumPago); command.Parameters.AddWithValue("@idContr", p.IdContr); command.Parameters.AddWithValue("@fec", p.FechaPago); command.Parameters.AddWithValue("@importe", p.Importe); connection.Open(); res = Convert.ToInt32(command.ExecuteScalar()); p.IdPago = res; connection.Close(); } } return(res); }