public RoomsDal GetById(int id)
{
var sql = $@"SELECT * FROM Rooms AS r
JOIN RoomTypes as rt ON rt.Id = r.RoomTypeId
WHERE r.Id = {id}" ;
RoomsDal room = GetRoom(sql);
return(room);
}
public static RoomsBll ToBll(this RoomsDal roomsDal)
{
return(new RoomsBll()
{
Id = roomsDal.Id,
RoomNumber = roomsDal.RoomNumber,
RoomLetter = roomsDal.RoomLetter,
Floor = roomsDal.Floor,
RoomType = roomsDal.RoomType.ToBll()
});
}
private RoomsDal GetRoom(string sql)
{
RoomsDal room = new RoomsDal();
using (IDbConnection connection = new SqlConnection(_connectionString))
{
room = connection.Query <RoomsDal, RoomTypesDal, RoomsDal>(sql, (roomsDal, roomTypesDal) =>
{
roomsDal.RoomType = roomTypesDal;
return(roomsDal);
}).ToList().FirstOrDefault();
}
return(room);
}
public bool Create(RoomsDal item)
{
var sql = @"INSERT dbo.Rooms([RoomNumber],[RoomTypeId],[RoomLetter],[Floor])
VALUES (@roomNumber, @roomTypeId, @roomLetter, @floor)" ;
using (IDbConnection connection = new SqlConnection(_connectionString))
{
var rowsAffected = connection.Execute(sql,
new
{
roomNumber = item.RoomNumber,
roomTypeId = item.RoomType.Id,
roomLetter = item.RoomLetter,
floor = item.Floor
});
return(rowsAffected > 0);
}
}
public bool Update(RoomsDal item)
{
var sql = @"UPDATE dbo.Rooms SET
[RoomNumber] = @roomNumber,
[RoomLetter] = @roomLetter,
[RoomTypeId] = @roomTypeId,
[Floor] = @floor
WHERE Id = @id" ;
using (IDbConnection connection = new SqlConnection(_connectionString))
{
var rowsAffected = connection.Execute(sql, new
{
roomNumber = item.RoomNumber,
roomTypeId = item.RoomType.Id,
roomLetter = item.RoomLetter,
floor = item.Floor,
id = item.Id
});
return(rowsAffected > 0);
}
}