public int Create(Book data)
{
int result = 0;
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"INSERT INTO Book
( BookTitle,Author,BookType,RentPrice,Status)
VALUES ( @BookTitle,@Author,@BookType,@RentPrice,1);
SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@BookTitle", data.BookTitle);
cmd.Parameters.AddWithValue("@Author", data.Author);
cmd.Parameters.AddWithValue("@BookType", data.BookType);
cmd.Parameters.AddWithValue("@RentPrice", data.RentPrice);
object lastId = cmd.ExecuteScalar();
lastId = (lastId == DBNull.Value) ? null : lastId;
result = Convert.ToInt32(lastId);
}
}
return(result);
}
public int Create(RentBook data)
{
int result = 0;
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"INSERT INTO RentBook
(BookID,PricePerDay,RentLenght,StartDate,EndDate,UserName,Status)
VALUES (@BookID,@PricePerDay,@RentLenght,@StartDate,@EndDate,@UserName,1);
SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@BookID", data.BookID);
cmd.Parameters.AddWithValue("@PricePerDay", data.PricePerDay);
cmd.Parameters.AddWithValue("@RentLenght", data.RentLenght);
cmd.Parameters.AddWithValue("@StartDate", data.StartDate);
cmd.Parameters.AddWithValue("@EndDate", data.EndDate);
cmd.Parameters.AddWithValue("@UserName", data.UserName);
object lastId = cmd.ExecuteScalar();
lastId = (lastId == DBNull.Value) ? null : lastId;
result = Convert.ToInt32(lastId);
}
}
return(result);
}
public void Update(RentBook data)
{
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"EXEC SpUpdateRent @ID int,
@BookId
@PricePerDay ,
@RentLength ,
@StartDate ,
@EndDate ,
@UserName ,
@Status;" ;
cmd.Parameters.AddWithValue("@ID", data.ID);
cmd.Parameters.AddWithValue("@BookId", data.BookID);
cmd.Parameters.AddWithValue("@PricePerDay", data.PricePerDay);
cmd.Parameters.AddWithValue("@RentLength", data.RentLenght);
cmd.Parameters.AddWithValue("@StartDate", data.StartDate);
cmd.Parameters.AddWithValue("@EndDate", data.EndDate);
cmd.Parameters.AddWithValue("@UserName", data.UserName);
cmd.Parameters.AddWithValue("@Status", data.Status);
cmd.ExecuteNonQuery();
}
}
}
public void Delete(int id)
{
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"EXEC SpDeleteRent @ID";
cmd.Parameters.AddWithValue("@ID", id);
cmd.ExecuteNonQuery();
}
}
}
public void Delete(int id)
{
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"UPDATE Book
SET Status = 0
WHERE ID = @Id
";
cmd.Parameters.AddWithValue("@ID", id);
cmd.ExecuteNonQuery();
}
}
}
public RentBook GetByID(int id)
{
RentBook result = new RentBook();
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"SELECT ID,BookID,PricePerDay,RentLenght,StartDate,EndDate,UserName,Status FROM RentBook WHERE ID = @ID";
cmd.Parameters.AddWithValue("@ID", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result = CreateObject(reader);
}
}
}
}
return(result);
}
public List <Book> GetListBook()
{
List <Book> result = new List <Book>();
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"SELECT ID,BookTitle,Author,BookType,RentPrice,Status FROM Book WHERE Status = 1";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Book data = CreateObject(reader);
result.Add(data);
}
}
}
}
return(result);
}
public List <RentBook> GetList()
{
List <RentBook> result = new List <RentBook>();
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"SELECT ID,BookID,PricePerDay,RentLenght,StartDate,EndDate,UserName,Status FROM RentBook WHERE Status = 1";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
RentBook data = CreateObject(reader);
result.Add(data);
}
}
}
}
return(result);
}
public Book GetBookByID(int id)
{
Book result = new Book();
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"SELECT ID,BookTitle,Author,BookType,RentPrice,Status FROM Book
WHERE ID = @id";
cmd.Parameters.AddWithValue("@id", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result = CreateObject(reader);
}
}
}
}
return(result);
}
public void Update(Book data)
{
using (var tx = new SafeTx(Configuration))
{
using (var cmd = tx.GetCommand())
{
cmd.CommandText = @"UPDATE Book
SET BookTitle = @BookTitle
,Author = @Author
,BookType = @BookType
,RentPrice = @RentPrice
WHERE ID = @Id
";
cmd.Parameters.AddWithValue("@BookTitle", data.BookTitle);
cmd.Parameters.AddWithValue("@Author", data.Author);
cmd.Parameters.AddWithValue("@BookType", data.BookType);
cmd.Parameters.AddWithValue("@RentPrice", data.RentPrice);
cmd.Parameters.AddWithValue("@ID", data.ID);
cmd.ExecuteNonQuery();
}
}
}
