public bool Update(GuestsDal item)
{
var sql = @"UPDATE dbo.Guests SET
[Name] = @name,
[Surname] = @surname,
[FathersName] = @fatherName,
[Organization] = @organization,
[Nationality] = @nationality,
[PaymentTypeId] = @paymentTypeId,
[RoomId] = @roomId,
[DateFrom] = @dateFrom,
[DateTo] = @dateTo
WHERE Id = @id" ;
using (IDbConnection connection = new SqlConnection(_connectionString))
{
var affectedRows = connection.Execute(sql, new
{
name = item.Name,
surname = item.SurName,
fatherName = item.FatherName,
organization = item.Organization,
nationality = item.Nationality,
paymentTypeId = item.PaymentType.Id,
roomId = item.Room.Id,
dateFrom = item.DateFrom,
dateTo = item.DateTo,
id = item.Id
});
return(affectedRows > 0);
}
}
public GuestsDal GetById(int id)
{
var sql = $@"SELECT * FROM dbo.Guests AS g
JOIN PaymentTypes as pt ON pt.Id = g.PaymentTypeId
JOIN Rooms as r ON r.Id = g.RoomId
JOIN RoomTypes as rt ON rt.Id = r.RoomTypeId
WHERE g.Id = {id}" ;
GuestsDal guest = GetGuest(sql);
return(guest);
}
public static GuestBll ToBll(this GuestsDal guestsDal)
{
return(new GuestBll()
{
Id = guestsDal.Id,
Name = guestsDal.Name,
SurName = guestsDal.SurName,
FatherName = guestsDal.FatherName,
DateFrom = guestsDal.DateFrom,
DateTo = guestsDal.DateTo,
Organization = guestsDal.Organization,
Nationality = guestsDal.Nationality,
Room = guestsDal.Room.ToBll()
});
}
private GuestsDal GetGuest(string sql)
{
GuestsDal guestDal = new GuestsDal();
using (IDbConnection connection = new SqlConnection(_connectionString))
{
guestDal = connection.Query <GuestsDal, PaymentTypesDal, RoomsDal, RoomTypesDal, GuestsDal>(sql,
(guest, paymentType, room, roomType) =>
{
guest.Room = room;
guest.PaymentType = paymentType;
guest.Room.RoomType = roomType;
return(guest);
}).ToList().FirstOrDefault();
}
return(guestDal);
}
public bool Create(GuestsDal item)
{
var sql = @"INSERT dbo.Guests([Name],[Surname],[FathersName],[Organization],[Nationality],[PaymentTypeId],[RoomId],[DateFrom],[DateTo])
values (@name, @surname, @fatherName, @organization, @nationality, @paymentTypeId, @roomId, @dateFrom, @dateTo);" ;
using (IDbConnection connection = new SqlConnection(_connectionString))
{
var rowsAffected = connection.Execute(sql, new
{
name = item.Name,
surname = item.SurName,
fatherName = item.FatherName,
organization = item.Organization,
nationality = item.Nationality,
paymentTypeId = item.PaymentType.Id,
roomId = item.Room.Id,
dateFrom = item.DateFrom,
dateTo = item.DateTo
});
return(rowsAffected > 0);
}
}
