public Inquilino ObtenerPorCorreo(string correo) { Inquilino i = null; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"SELECT IdInquilino, Nombre, Apellido, Dni, LugarTrabajo, Correo, Telefono, NombreGarante, DniGarante, EstadoInquilino FROM Inquilinos" + $" WHERE Correo=@correo AND EstadoInquilino = 1;"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.Parameters.Add("@correo", SqlDbType.VarChar).Value = correo; command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); if (reader.Read()) { i = new Inquilino { IdInquilino = reader.GetInt32(0), Nombre = reader.GetString(1), Apellido = reader.GetString(2), Dni = reader.GetInt32(3), LugarTrabajo = reader.GetString(4), Correo = reader.GetString(5), Telefono = reader.GetInt64(6), NombreGarante = reader.GetString(7), DniGarante = reader.GetInt32(8), EstadoInquilino = reader.GetByte(9), }; } connection.Close(); } } return(i); }
public IList <Inquilino> ObtenerTodos() { IList <Inquilino> res = new List <Inquilino>(); using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"SELECT IdInquilino, Nombre, Apellido, Dni, LugarTrabajo, Correo, Telefono, NombreGarante, DniGarante, EstadoInquilino " + $" FROM Inquilinos WHERE EstadoInquilino = 1;"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { Inquilino i = new Inquilino { IdInquilino = reader.GetInt32(0), Nombre = reader.GetString(1), Apellido = reader.GetString(2), Dni = reader.GetInt32(3), LugarTrabajo = reader.GetString(4), Correo = reader.GetString(5), Telefono = reader.GetInt64(6), NombreGarante = reader.GetString(7), DniGarante = reader.GetInt32(8), EstadoInquilino = reader.GetByte(9), }; res.Add(i); } connection.Close(); } } return(res); }
public int Modificacion(Inquilino i) { int res = -1; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"UPDATE Inquilinos SET Nombre='{i.Nombre}', Apellido='{i.Apellido}', Dni={i.Dni}, LugarTrabajo='{i.LugarTrabajo}', Correo='{i.Correo}', " + $"Telefono={i.Telefono}, NombreGarante='{i.NombreGarante}', DniGarante={i.DniGarante}, EstadoInquilino = 1 WHERE IdInquilino = {i.IdInquilino} ;"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; connection.Open(); res = command.ExecuteNonQuery(); connection.Close(); } } return(res); }
public int Alta(Inquilino i) { int res = -1; using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"INSERT INTO Inquilinos (Nombre, Apellido, Dni, LugarTrabajo, Correo, Telefono, NombreGarante, DniGarante, EstadoInquilino) " + $"VALUES ('{i.Nombre}', '{i.Apellido}', {i.Dni}, '{i.LugarTrabajo}', '{i.Correo}', {i.Telefono},'{i.NombreGarante}',{i.DniGarante}, 1) ;"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.CommandType = CommandType.Text; connection.Open(); res = command.ExecuteNonQuery(); command.CommandText = "SELECT SCOPE_IDENTITY()"; var id = command.ExecuteScalar(); i.IdInquilino = Convert.ToInt32(id); connection.Close(); } } return(res); }
public IList <Inquilino> ObtenerPorContratoId(int id) { IList <Inquilino> res = new List <Inquilino>(); using (SqlConnection connection = new SqlConnection(connectionString)) { string sql = $"SELECT IdInquilino, Nombre, Apellido, Dni, LugarTrabajo, Correo, Telefono, NombreGarante, DniGarante, EstadoInquilino " + $" FROM Inquilinos i INNER JOIN Contratos c ON p.IdContrato= c.IdContrato WHERE EstadoInquilino = 1 AND c.IdContrato = @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(); while (reader.Read()) { Inquilino i = new Inquilino { IdInquilino = reader.GetInt32(0), Nombre = reader.GetString(1), Apellido = reader.GetString(2), Dni = reader.GetInt32(3), LugarTrabajo = reader.GetString(4), Correo = reader.GetString(5), Telefono = reader.GetInt64(6), NombreGarante = reader.GetString(7), DniGarante = reader.GetInt32(8), EstadoInquilino = reader.GetByte(9), }; res.Add(i); } connection.Close(); } } return(res); }