public IList <Inquilino> BuscarPor(string columna, string valor) { List <Inquilino> res = new List <Inquilino>(); Inquilino p = null; using (MySqlConnection connection = new MySqlConnection(connectionString)) { string sql = $"SELECT * FROM inquilinos WHERE @columna=@valor"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.Parameters.Add("@columna", MySqlDbType.VarChar).Value = columna; command.Parameters.Add("@valor", MySqlDbType.VarChar).Value = valor; command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { p = new Inquilino { Id = reader.GetInt32(0), Nombre = reader.GetString(1), Apellido = reader.GetString(2), Dni = reader.GetString(3), Telefono = reader.GetString(4), Email = reader.GetString(5), LugarTrabajo = reader.GetString(6), Estado = reader.GetString(7), }; res.Add(p); } connection.Close(); } } return(res); }
public int Modificacion(Inquilino p) { int res = -1; using (MySqlConnection connection = new MySqlConnection(connectionString)) { string sql = $"UPDATE inquilinos SET nombre=@nombre, apellido=@apellido, dni=@dni, telefono=@telefono, email=@email, lugar_trabajo=@lugar_trabajo WHERE Id = @id"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@id", p.Id); command.Parameters.AddWithValue("@nombre", p.Nombre); command.Parameters.AddWithValue("@apellido", p.Apellido); command.Parameters.AddWithValue("@dni", p.Dni); command.Parameters.AddWithValue("@telefono", p.Telefono); command.Parameters.AddWithValue("@email", p.Email); command.Parameters.AddWithValue("@lugar_trabajo", p.LugarTrabajo); connection.Open(); res = command.ExecuteNonQuery(); connection.Close(); } } return(res); }
public Inquilino ObtenerPorId(int id) { Inquilino p = null; using (MySqlConnection connection = new MySqlConnection(connectionString)) { string sql = $"SELECT * FROM inquilinos" + $" WHERE Id=@id"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.Parameters.Add("@id", MySqlDbType.Int32).Value = id; command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); if (reader.Read()) { p = new Inquilino { Id = reader.GetInt32(0), Nombre = reader.GetString(1), Apellido = reader.GetString(2), Dni = reader.GetString(3), Telefono = reader.GetString(4), Email = reader.GetString(5), LugarTrabajo = reader.GetString(6), Estado = reader.GetString(7), }; } connection.Close(); } } return(p); }
public IList <Inquilino> ObtenerTodos() { try { IList <Inquilino> res = new List <Inquilino>(); using (MySqlConnection connection = new MySqlConnection(connectionString)) { string sql = $"SELECT * FROM Inquilinos"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); List <Contrato> contratos = null; while (reader.Read()) { Inquilino p = new Inquilino { Id = reader.GetInt32(0), Nombre = reader.GetString(1), Apellido = reader.GetString(2), Dni = reader.GetString(3), Telefono = reader.GetString(4), Email = reader.GetString(5), LugarTrabajo = reader.GetString(6), Estado = reader.GetString(7), }; res.Add(p); } connection.Close(); } } return(res); } catch (MySqlException ex) { throw; } }
public int Alta(Inquilino p) { int res = -1; try { using (MySqlConnection connection = new MySqlConnection(connectionString)) { string sql = $"INSERT INTO inquilinos (nombre, apellido, dni, telefono, email, lugar_trabajo, estado) " + $"VALUES (@nombre, @apellido, @dni, @telefono, @email, @lugar_trabajo, 'activo');" + $"SELECT LAST_INSERT_ID();";//devuelve el id insertado using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@nombre", p.Nombre); command.Parameters.AddWithValue("@apellido", p.Apellido); command.Parameters.AddWithValue("@dni", p.Dni); command.Parameters.AddWithValue("@telefono", p.Telefono); command.Parameters.AddWithValue("@email", p.Email); command.Parameters.AddWithValue("@lugar_trabajo", p.LugarTrabajo); connection.Open(); res = Convert.ToInt32(command.ExecuteScalar()); p.Id = res; connection.Close(); } } } catch (Exception e) { throw; Console.WriteLine(e.Message); } return(res); }
public IList <Contrato> ObtenerPorFechas(DateTime inicio, DateTime fin) { String start = inicio.ToString("yyyy-MM-dd"); // yyyy-MM-dd String end = fin.ToString("yyyy-MM-dd"); //dd-MM-yyyy Contrato c = null; IList <Contrato> res = new List <Contrato>(); using (MySqlConnection connection = new MySqlConnection(connectionString)) { string sql = "select * from contratos c INNER JOIN inquilinos i ON c.inquilinoId = i.id INNER JOIN inmuebles d ON c.inmuebleId = d.id WHERE c.fecha_inicio >= @inicio AND c.fecha_fin <= @fin AND c.estado != 'rescindido'"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.Parameters.Add("@inicio", MySqlDbType.String).Value = start; command.Parameters.Add("@fin", MySqlDbType.String).Value = end; command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { c = new Contrato { Id = reader.GetInt32(0), InmuebleId = reader.GetInt32(1), InquilinoId = reader.GetInt32(2), FechaInicio = reader.GetDateTime(3), FechaFin = reader.GetDateTime(4), Monto = reader.GetDecimal(5), NombreGarante = reader.GetString(6), DniGarante = reader.GetString(7), TelefonoGarante = reader.GetString(8), MailGarante = reader.GetString(9), Estado = reader.GetString(10), }; Inquilino inq = new Inquilino { Id = reader.GetInt32(11), Nombre = reader.GetString(12), Apellido = reader.GetString(13), Dni = reader.GetString(14), Telefono = reader.GetString(15), Email = reader.GetString(16), LugarTrabajo = reader.GetString(17), Estado = reader.GetString(18), }; Inmueble i = new Inmueble { Id = reader.GetInt32(19), PropietarioId = reader.GetInt32(20), Direccion = reader.GetString(21), Ambientes = reader.GetInt32(22), Uso = reader.GetString(23), Tipo = reader.GetString(24), Precio = reader.GetFloat(25), Disponible = reader.GetBoolean(26), }; c.Inquilino = inq; c.Inmueble = i; res.Add(c); } connection.Close(); } } return(res); }
public IList <Contrato> ObtenerPorInmuebleId(int id) { Contrato c = null; IList <Contrato> res = new List <Contrato>(); using (MySqlConnection connection = new MySqlConnection(connectionString)) { string sql = $"select * from contratos c join inquilinos i join inmuebles m WHERE c.inquilinoId = i.id AND c.inmuebleId = m.id and c.inmuebleId =@Id"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.Parameters.Add("@id", MySqlDbType.Int32).Value = id; command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { c = new Contrato { Id = reader.GetInt32(0), InmuebleId = reader.GetInt32(1), InquilinoId = reader.GetInt32(2), FechaInicio = reader.GetDateTime(3), FechaFin = reader.GetDateTime(4), Monto = reader.GetDecimal(5), NombreGarante = reader.GetString(6), DniGarante = reader.GetString(7), TelefonoGarante = reader.GetString(8), MailGarante = reader.GetString(9), Estado = reader.GetString(10), }; Inquilino inq = new Inquilino { Id = reader.GetInt32(11), Nombre = reader.GetString(12), Apellido = reader.GetString(13), Dni = reader.GetString(14), Telefono = reader.GetString(15), Email = reader.GetString(16), LugarTrabajo = reader.GetString(17), Estado = reader.GetString(18), }; Inmueble i = new Inmueble { Id = reader.GetInt32(19), PropietarioId = reader.GetInt32(20), Direccion = reader.GetString(21), Ambientes = reader.GetInt32(22), Uso = reader.GetString(23), Tipo = reader.GetString(24), Precio = reader.GetFloat(25), Disponible = reader.GetBoolean(26), }; c.Inquilino = inq; c.Inmueble = i; res.Add(c); } connection.Close(); } } return(res); }