public IList <Alquiler> Buscar(int idInquilino)
{
List <Alquiler> res = new List <Alquiler>();
Alquiler entidad = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT IdAlquiler, Precio, FechaInicio, FechaFin, IdInquilino, IdInmueble" +
$" FROM Alquiler i INNER JOIN Inquilino p ON i.IdAlquiler = p.IdAlquiler" +
$" WHERE IdInquilino=@IdInquilino";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@idInquilino", SqlDbType.Int).Value = idInquilino;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
entidad = new Alquiler
{
IdAlquiler = reader.GetInt32(0),
Precio = reader.GetString(1),
FechaInicio = reader.GetDateTime(2),
FechaFin = reader.GetDateTime(3),
//aqui
IdInquilino = reader.GetInt32(4),
alquila = new Inquilino
{
IdInquilino = reader.GetInt32(4),
Nombre = reader.GetString(5),
Apellido = reader.GetString(6),
},
//aqui
IdInmueble = reader.GetInt32(7),
inmueble = new Inmueble
{
IdInmueble = reader.GetInt32(8),
Direccion = reader.GetString(9),
}
};
res.Add(entidad);
}
connection.Close();
}
}
return(res);
}
public Alquiler ObtenerPorId(int id)
{
Alquiler alquiler = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT IdAlquiler, Precio, Descripcion, FechaInicio, FechaFin, IdInquilino, IdInmueble FROM Alquiler" +
$" WHERE IdAlquiler=@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();
if (reader.Read())
{
alquiler = new Alquiler
{
IdAlquiler = reader.GetInt32(0),
Precio = reader.GetString(1),
Descripcion = reader.GetString(2),
FechaInicio = reader.GetDateTime(3),
FechaFin = reader.GetDateTime(4),
IdInquilino = reader.GetInt32(5),
IdInmueble = reader.GetInt32(6),
//alquila = new Inquilino
//{
// IdInquilino = reader.GetInt32(4),
// Nombre = reader.GetString(5),
// Apellido = reader.GetString(6),
//},
//aqui
//inmueble = new Inmueble
//{
// IdInmueble = reader.GetInt32(8),
// Direccion = reader.GetString(9),
//}
};
}
connection.Close();
}
}
return(alquiler);
}
public int Modificacion(Alquiler alquiler)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Alquiler SET Precio='{alquiler.Precio}',Descripcion='{alquiler.Descripcion}', FechaInicio='{alquiler.FechaInicio}', FechaFin='{alquiler.FechaFin}', IdInquilino='{alquiler.IdInquilino}', IdInmueble='{alquiler.IdInmueble}' " +
$"WHERE IdAlquiler = {alquiler.IdAlquiler}";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public int Alta(Alquiler alquiler)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Alquiler (Precio, Descripcion, FechaInicio, FechaFin, IdInquilino, IdInmueble) " +
$"VALUES ('{alquiler.Precio}','{alquiler.Descripcion}', '{alquiler.FechaInicio}','{alquiler.FechaFin}','{alquiler.IdInquilino}','{alquiler.IdInmueble}')";
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();
alquiler.IdAlquiler = Convert.ToInt32(id);
connection.Close();
}
}
return(res);
}
