public void DeleteBook(int bookId)
{
var inID = new SqlParameter
{
ParameterName = "ID",
Value = bookId,
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Input
};
var sql = "exec DeleteBook @ID";
using (var dbContext = new BookLibraryContext())
{
_ = dbContext.Database.ExecuteSqlRaw(sql, inID);
}
return;
}
public bool RegisterSession(int accountId, string sessionId)
{
using (var dbContext = new BookLibraryContext())
{
var inAccountId = new SqlParameter
{
ParameterName = "AccountId",
Value = accountId,
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Input
};
var inOpenDate = new SqlParameter
{
ParameterName = "OpenDate",
Value = DateTime.Now,
DbType = System.Data.DbType.DateTime,
Direction = System.Data.ParameterDirection.Input
};
var inSessionId = new SqlParameter
{
ParameterName = "SessionId",
Value = sessionId,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var outResult = new SqlParameter
{
ParameterName = "Result",
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Output
};
var sql = "exec OpenSession @AccountId, @OpenDate, @SessionId, @Result OUT";
_ = dbContext.Database.ExecuteSqlRaw(sql, inAccountId, inOpenDate, inSessionId, outResult);
if (Int32.TryParse(outResult.Value.ToString(), out int openSessionResult))
{
if (openSessionResult == 1)
{
return(true);
}
}
}
return(false);
}
public bool DeleteBook(int bookID)
{
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
Book book = db.Books.SingleOrDefault(b => b.BookID == bookID);
db.Entry(book).State = EntityState.Deleted;
db.SaveChanges();
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public bool UpdateBookCategory(int bookCategoryID, string bookCategoryName)
{
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
BookCategory bookCategory = db.BookCategories.SingleOrDefault(b => b.BookCategoryID == bookCategoryID);
bookCategory.BookCategoryName = bookCategoryName;
db.SaveChanges();
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public bool UpdateAuthor(int authorID, string authorName)
{
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
Author author = db.Authors.SingleOrDefault(a => a.AuthorID == authorID);
author.AuthorName = authorName;
db.SaveChanges();
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public void OnGet()
{
using (var context = new BookLibraryContext())
{
var books = context.Books.Include(b => b.Author)
.Select(b => new BookIndexModel()
{
Id = b.Id,
Author = b.Author,
Title = b.Title,
Status = b.Status
})
.ToList();
this.Books = books;
StringBuilder sb = new StringBuilder();
foreach (BookIndexModel book in books)
{
if (book.Status == "Borrowed")
{
sb.Append($@"
<tr>
<td><a href=""/books/details/{book.Id}"">{book.Title}</a></td>
<td><a href=""/author/details/{book.Author.Id}"">{book.Author.Name}</a></td>
<td class=""borrowed"">{book.Status}</td>
</tr>");
}
else
{
sb.Append($@"
<tr>
<td><a href=""/books/details/{book.Id}"">{book.Title}</a></td>
<td><a href=""/author/details/{book.Author.Id}"">{book.Author.Name}</a></td>
<td>{book.Status}</td>
</tr>");
}
}
ViewData["Books"] = sb;
}
}
protected void Session_Start(object sender, EventArgs e)
{
if (System.Diagnostics.Debugger.IsAttached)
{
var loginName = Bl.Users.CurrentLoginName;
var current = Bl.Users.FindUserByLoginName(loginName);
if (current == null)
{
using (var db = new BookLibraryContext())
{
var debgger = new User {
LoginName = loginName, Role = (int)Roles.Admin, EmailAdress = "*****@*****.**"
};
db.Users.Add(debgger);
db.SaveChanges();
}
}
}
}
public IActionResult OnGet(int id)
{
using (var context = new BookLibraryContext()) {
Console.WriteLine();
var book = context
.Books
.Include(b => b.Author)
.FirstOrDefault(b => b.Id == id);
if (book == null)
{
return(RedirectToPage("/Index"));
}
this.Book = book;
return(Page());
}
}
public bool AddAuthor(string authorName)
{
Models.Author author = new Models.Author();
author.AuthorName = authorName;
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
db.Authors.Add(author);
db.SaveChanges();
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public bool AddBookCategory(string bookCategoryName)
{
Models.BookCategory bookCategory = new Models.BookCategory();
bookCategory.BookCategoryName = bookCategoryName;
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
db.BookCategories.Add(bookCategory);
db.SaveChanges();
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public bool DeleteBorrowing(int borrowingID)
{
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
Borrowing borrowing = db.Borrowings.Single(b => b.BorrowingID == borrowingID);
if (borrowing != null)
{
db.Entry(borrowing).State = EntityState.Deleted;
db.SaveChanges();
}
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public bool UpdateMember(int memberID, string firstName, string lastName, string identityID, string email)
{
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
Member member = db.Members.SingleOrDefault(m => m.MemberID == memberID);
member.FirstName = firstName;
member.LastName = lastName;
member.IdentityID = identityID;
member.Email = email;
db.SaveChanges();
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public ActionResult Subscribe(long bookId)
{
var user = Bl.Users.Current;
bool succeed;
string message;
using (var db = new BookLibraryContext())
{
var bb = new BookBorrowing(db);
succeed = bb.SubscribeBook(user, bookId, out message);
}
if (!succeed)
{
var errorAlert = string.Format("alert('{0}');", message);
return(JavaScript(errorAlert));
}
return(RedirectToAction("Index", "BookList"));
}
public List <Book> GetNotBorrowedBooks()
{
List <Book> notBorrowedBooks = new List <Book>();
using (BookLibraryContext db = new BookLibraryContext())
{
var currentBorrowings = from b in db.Borrowings
where !b.To.HasValue
select b;
List <int> borrowedBooksID = new List <int>();
foreach (Borrowing b in currentBorrowings)
{
borrowedBooksID.Add(b.BookID);
}
notBorrowedBooks = db.Books.Where(b => !borrowedBooksID.Contains(b.BookID)).ToList();
}
return(notBorrowedBooks);
}
public bool ChangeAccountPassword(int accountId, string accountPassword, string newAccountPassword)
{
var inAccountId = new SqlParameter
{
ParameterName = "AccountId",
Value = accountId,
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Input
};
var inPassword = new SqlParameter
{
ParameterName = "Password",
Value = accountPassword,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var inNewPassword = new SqlParameter
{
ParameterName = "NewPassword",
Value = newAccountPassword,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var outResult = new SqlParameter
{
ParameterName = "Result",
DbType = System.Data.DbType.Boolean,
Direction = System.Data.ParameterDirection.Output
};
var sql = "exec ChangeAccountPassword @AccountId, @Password, @NewPassword, @Result OUT";
using (var dbContext = new BookLibraryContext())
{
_ = dbContext.Database.ExecuteSqlRaw(sql, inAccountId, inPassword, inNewPassword, outResult);
}
Boolean.TryParse(outResult.Value.ToString(), out bool result);
return(result);
}
public ActionResult AddCustomer()
{
var user = new Entities.User
{
LoginName = @"MM\TC",
FullName = "TestCustomer",
EmailAdress = "[email protected]",
Role = (int)Roles.Customer
};
using (var db = new BookLibraryContext())
{
db.Users.Add(user);
db.SaveChanges();
Bl.Users.Current = user;
}
var message = string.Format("Created and swithed to new user [{0}].", BuildUserInfo(user));
return(TextContent(message));
}
public bool?CheckSessionExpiration(string sessionId)
{
using (var dbContext = new BookLibraryContext())
{
var inSessionId = new SqlParameter
{
ParameterName = "SessionId",
Value = sessionId,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var outResult = new SqlParameter
{
ParameterName = "Result",
DbType = System.Data.DbType.DateTime,
Direction = System.Data.ParameterDirection.Output
};
var sql = "exec GetSessionLastRenewalDate @SessionId, @Result OUT";
_ = dbContext.Database.ExecuteSqlRaw(sql, inSessionId, outResult);
if (DateTime.TryParse(outResult.Value.ToString(), out DateTime spDateTimeResult))
{
if (DateTime.Now - spDateTimeResult > SessionExpirationTimeSpan)
{
return(true);
}
else
{
ContinueSession(sessionId);
}
}
else
{
return(null);
}
}
return(false);
}
public ActionResult BookDetail(string bookNumber)
{
Book book;
using (var db = new BookLibraryContext())
{
book = db.Books.Include("BookType").FirstOrDefault(b => b.BookNumber == bookNumber);
}
if (book == null)
{
var errorAlert = string.Format(
"alert('Invalid book number [{0}]. Please contact the admin.');", bookNumber);
return(JavaScript(errorAlert));
}
var info = new BookDetailInfo();
info.Operation = Bl.Users.Current.IsAdmin ? UserOperationFactory.CreateEditBookOperation(book.BookId) : null;
info.LoadInfo(book);
return(View(info));
}
public bool CloseSession(string sessionId)
{
using (var dbContext = new BookLibraryContext())
{
var inSessionId = new SqlParameter
{
ParameterName = "SessionId",
Value = sessionId,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var inCloseDate = new SqlParameter
{
ParameterName = "CloseDate",
Value = DateTime.Now,
DbType = System.Data.DbType.DateTime,
Direction = System.Data.ParameterDirection.Input
};
var outResult = new SqlParameter
{
ParameterName = "Result",
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Output
};
var sql = "exec CloseSession @SessionId, @CloseDate, @Result OUT";
_ = dbContext.Database.ExecuteSqlRaw(sql, inSessionId, inCloseDate, outResult);
if (Int32.TryParse(outResult.Value.ToString(), out int spResult))
{
if (spResult == 1)
{
return(true);
}
}
}
return(false);
}
public int Login(string login, string password)
{
var inLogin = new SqlParameter
{
ParameterName = "Login",
Value = login,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var inPassword = new SqlParameter
{
ParameterName = "Password",
Value = password,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var outResult = new SqlParameter
{
ParameterName = "Result",
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Output
};
var sql = "exec LoginAccount @Login, @Password, @Result OUT";
using (var dbContext = new BookLibraryContext())
{
_ = dbContext.Database.ExecuteSqlRaw(sql, inLogin, inPassword, outResult);
}
if (Int32.TryParse(outResult.Value.ToString(), out int accountId))
{
if (accountId > 0)
{
return(accountId);
}
}
return(0);
}
public void UpdateBook(BookItem book)
{
var inID = new SqlParameter
{
ParameterName = "ID",
Value = book.ID,
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Input
};
var inNewName = new SqlParameter
{
ParameterName = "NewName",
Value = book.Name,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var inNewAuthors = new SqlParameter
{
ParameterName = "NewAuthors",
Value = book.Authors,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var inNewYear = new SqlParameter
{
ParameterName = "NewYear",
Value = book.Year,
DbType = System.Data.DbType.DateTime,
Direction = System.Data.ParameterDirection.Input
};
var sql = "exec UpdateBook @ID, @NewName, @NewAuthors, @NewYear";
using (var dbContext = new BookLibraryContext())
{
_ = dbContext.Database.ExecuteSqlRaw(sql, inID, inNewName, inNewAuthors, inNewYear);
}
return;
}
public void AddBook(BookItem book)
{
var inName = new SqlParameter
{
ParameterName = "Name",
Value = book.Name,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var inAuthors = new SqlParameter
{
ParameterName = "Authors",
Value = book.Authors,
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input
};
var inYear = new SqlParameter
{
ParameterName = "Year",
Value = book.Year,
DbType = System.Data.DbType.DateTime,
Direction = System.Data.ParameterDirection.Input
};
var inQuantity = new SqlParameter
{
ParameterName = "Quantity",
Value = 1,
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Input
};
var sql = "exec AddBook @Name, @Authors, @Year, @Quantity";
using (var dbContext = new BookLibraryContext())
{
_ = dbContext.Database.ExecuteSqlRaw(sql, inName, inAuthors, inYear, inQuantity);
}
return;
}
public List <BookItem> GetBooks(string spName, SqlParameter[] pArr)
{
using (var dbContext = new BookLibraryContext())
{
List <BookItem> booksResult;
if (pArr == null)
{
booksResult = dbContext.GetBook.FromSqlRaw(String.Format("EXECUTE {0}", spName)).ToList();
}
else
{
booksResult = dbContext.GetBook.FromSqlRaw(String.Format("EXECUTE {0}", spName), pArr).ToListAsync().Result;
}
var booksList = new List <BookItem>();
foreach (var book in booksResult)
{
booksList.Add(book);
}
return(booksList);
}
}
public bool AddMember(string firstName, string lastName, string identityID, string email)
{
Member member = new Member();
member.FirstName = firstName;
member.LastName = lastName;
member.IdentityID = identityID;
member.Email = email;
try
{
using (BookLibraryContext db = new BookLibraryContext())
{
db.Members.Add(member);
db.SaveChanges();
}
}
catch (Exception exc)
{
return(false);
}
return(true);
}
public async Task DeleteBookAsync(int bookId)
{
using BookLibraryContext bookLibraryContext = new BookLibraryContext();
Book bookToDelete = bookLibraryContext.Books.FirstOrDefault(x => x.Id == bookId);
if (bookToDelete != null)
{
bookLibraryContext.Books.Remove(bookToDelete);
List <BookAuthor> bookAuthors = await bookLibraryContext.BooksAuthors.Where(x => x.BookId == bookId)
.ToListAsync();
if (bookAuthors != null)
{
bookLibraryContext.BooksAuthors.RemoveRange(bookAuthors);
}
await bookLibraryContext.SaveChangesAsync();
//Delete author if author don't have more books
foreach (var bookAuthor in bookAuthors)
{
List <BookAuthor> haveAuthorEnyBooks = await bookLibraryContext.BooksAuthors
.Where(x => x.AuthorId == bookAuthor.AuthorId)
.AsNoTracking()
.ToListAsync();
if (haveAuthorEnyBooks.Count == 0)
{
await DeleteAuthorById(bookAuthor.AuthorId);
}
}
}
await bookLibraryContext.SaveChangesAsync();
}
protected void FillInBookControl(BookLibraryContext db)
{
if (Request.Params.AllKeys.Contains("bookID"))
{
int bookID = Convert.ToInt32(Request["bookID"]);
Book book = db.Books.Single(b => b.BookID == bookID);
BookTextBox.Text = book.ISBN + " " + book.Title;
BookListLabel.Visible = false;
BookList.Visible = false;
BookTextBoxLabel.Visible = true;
BookTextBox.Visible = true;
}
else
{
BookList.DataValueField = "BookID";
BookList.DataTextField = "DisplayName";
BooksLogic bl = new BooksLogic();
List <Book> notBorrowedBooks = bl.GetNotBorrowedBooks();
/*
* foreach (Book b in notBorrowedBooks)
* {
* b.DisplayName = b.ISBN + " " + b.Title;
* }
*/
BookList.DataSource = notBorrowedBooks;
BookList.DataBind();
BookListLabel.Visible = true;
BookList.Visible = true;
BookTextBoxLabel.Visible = false;
BookTextBox.Visible = false;
}
}
public void ActionBook(string action, int accountId, int?bookId)
{
var inAccountId = new SqlParameter
{
ParameterName = "AccountId",
Value = accountId,
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Input
};
var inBookId = new SqlParameter
{
ParameterName = "BookId",
Value = bookId,
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Input
};
var sql = String.Format("exec {0} @AccountId, @BookId", action);
using (var dbContext = new BookLibraryContext())
{
_ = dbContext.Database.ExecuteSqlRaw(sql, inAccountId, inBookId);
}
return;
}
public HistoryModel(BookLibraryContext db)
{
this.db = db;
}
public IndexModel(BookLibraryContext context)
{
this.Context = context;
}
public DetailsModel(BookLibraryContext context)
{
this.Context = context;
}