public List <Inquilino> ObtenerTodos()
{
List <Inquilino> res = new List <Inquilino>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT id, Nombre, Apellido, Dni, Telefono, Email, Estado" +
$" FROM Inquilinos WHERE Estado = 1";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Inquilino inquilino = 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),
Estado = reader.GetBoolean(6)
};
res.Add(inquilino);
}
connection.Close();
}
}
return(res);
}
public int Alta(Inquilino inquilino)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Inquilinos (Nombre, Apellido, Dni, Telefono, Email) " +
$"VALUES (@nombre, @apellido, @dni, @telefono, @email);" +
"SELECT SCOPE_IDENTITY();"; // devuelve el id insertado (LAST_INSERT_ID para mysql)
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nombre", inquilino.Nombre);
command.Parameters.AddWithValue("@apellido", inquilino.Apellido);
command.Parameters.AddWithValue("@email", inquilino.Email);
command.Parameters.AddWithValue("@dni", inquilino.Dni);
command.Parameters.AddWithValue("@telefono", inquilino.Telefono);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
inquilino.Id = res;
connection.Close();
}
}
return(res);
}
public int Modificacion(Inquilino inquilino)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Inquilinos SET Nombre = @nombre, Apellido = @apellido, " +
$"Dni = @dni, Telefono = @telefono, Email = @email " +
$"WHERE id = @id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@id", inquilino.Id);
command.Parameters.AddWithValue("@nombre", inquilino.Nombre);
command.Parameters.AddWithValue("@apellido", inquilino.Apellido);
command.Parameters.AddWithValue("@dni", inquilino.Dni);
command.Parameters.AddWithValue("@telefono", inquilino.Telefono);
command.Parameters.AddWithValue("@email", inquilino.Email);
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public Inquilino ObtenerPorId(int id)
{
Inquilino inquilino = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT Id, Nombre, Apellido, Dni, Telefono, Email, Estado FROM Inquilinos WHERE Id = @id AND Estado = 1;";
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();
if (reader.Read())
{
inquilino = 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),
Estado = reader.GetBoolean(6)
};
}
}
connection.Close();
}
return(inquilino);
}