public void Add()
{
_dc = new DataConnection();
_dc.AddParameter("@genre_code", Genre.GenreCode);
_dc.AddParameter("@genre_description", Genre.Description);
_dc.Execute("sproc_AddGenre");
}
public void AddReservation()
{
_dc = new DataConnection();
_dc.AddParameter("@res_id", Reservation.ReservationId);
_dc.AddParameter("@fk2_bor_id", Reservation.BorrowerId);
_dc.AddParameter("@fk1_isbn", Reservation.ReservedIsbn);
_dc.Execute("sproc_AddReservation");
}
//Adds book copy record to the book table in db.
public void Add()
{
_dc = new DataConnection();
_dc.AddParameter("@cop_barcode", BookCopy.Barcode);
_dc.AddParameter("@cop_loan_type", BookCopy.LoanType);
_dc.AddParameter("@cop_status", BookCopy.Status);
_dc.AddParameter("@cop_condition", BookCopy.Condition);
_dc.AddParameter("@fk1_isbn", BookCopy.CopyIsbn);
_dc.Execute("sproc_AddBookCopy");
}
public void AddPayment()
{
_dc = new DataConnection();
_dc.AddParameter("@pmt_id", Payment.PaymentId);
_dc.AddParameter("@pmt_date", DateTime.Now);
_dc.AddParameter("@pmt_amount", Payment.Amount);
_dc.AddParameter("@fk1_bor_id", Payment.BorrowerId);
_dc.AddParameter("@fk2_fine_id", Payment.FineId);
_dc.Execute("sproc_TRANS_AddPaymentUpdateFine");
}
//filters borrower records by borrower id, first and last name
public void FilterByFirstAndLastNameAndId(string borrowerId, string firstName, string lastName)
{
_dc = new DataConnection();
//firstName = "" == firstName ? "" : firstName.Trim();
//lastName = "" == lastName ? "" : lastName.Trim();
//borrowerId = "" == borrowerId ? "" : borrowerId.Trim();
//var fullName = firstName + " " + lastName;
_dc.AddParameter("@fullName", firstName + " " + lastName);
_dc.AddParameter("@bor_id", borrowerId);
_dc.Execute("sproc_FilterByFirstAndLastNameAndId");
}
public bool Login(string email, string password)
{
var hashString = GetHashString(password);
var dc = new DataConnection();
dc.AddParameter("@password", hashString);
dc.AddParameter("@email", email);
dc.Execute("sproc_CheckUser");
return(dc.Count == 1);
}
//Updates record in the book table in db.
public void Update()
{
_dc = new DataConnection();
_dc.AddParameter("@bor_id", Borrower.BorrowerId);
_dc.AddParameter("@fk1_bor_type_id", Borrower.BorrowerType);
_dc.AddParameter("@bor_firstname", Borrower.FirstName);
_dc.AddParameter("@bor_surname", Borrower.LastName);
_dc.AddParameter("@bor_address_1", Borrower.Address1);
_dc.AddParameter("@bor_address_2", Borrower.Address2);
_dc.AddParameter("@bor_address_3", Borrower.Address3);
_dc.AddParameter("@bor_tel", Borrower.TelNumber);
_dc.AddParameter("@bor_email", Borrower.Email);
_dc.AddParameter("@bor_status", Borrower.Status);
_dc.Execute("sproc_UpdateBorrower");
}
//Find fine record with corresponding fine id
public bool Find(string fineId)
{
//Connection to the database
var dc = new DataConnection();
//Pass parameter to the stored procedure
dc.AddParameter("@fine_id", fineId);
//Execute stored procedure
dc.Execute("sproc_GetFineById");
//If returned results don't contain exactly one record return false
if (dc.Count != 1)
{
return(false);
}
//returned exactly one record
var row = dc.DataTable.Rows[0];
//Sets object properties to coresponding columns returned from db and returns true
FineId = row["fine_id"].ToString();
FineDate = Convert.ToDateTime(row["fine_date"]);
FineAmount = Convert.ToDecimal(row["fine_amount"]);
FineStatus = row["fine_status"].ToString();
BorrowerId = row["fk1_bor_id"].ToString();
LoanId = row["fk2_loan_id"].ToString();
return(true);
}
//Method checks if there is a record of a borrower with passed in borrowerId in the database
// if found sets properties on a borrower object
public bool Find(string borrowerId)
{
//Connection to the database
var dc = new DataConnection();
//Pass parameter to the stored procedure
dc.AddParameter("@bor_id", borrowerId);
//Execute stored procedure
dc.Execute("sproc_GetBorrowerById");
//If returned result doesn't contain exactly one record return false
if (dc.Count != 1)
{
return(false);
}
var row = dc.DataTable.Rows[0];
//Sets object properties to coresponding columns returned from db and returns true
BorrowerId = row["bor_id"].ToString();
BorrowerType = row["fk1_bor_type_id"].ToString();
FirstName = row["bor_firstname"].ToString();
LastName = row["bor_surname"].ToString();
Address1 = row["bor_address_1"].ToString();
Address2 = row["bor_address_2"].ToString();
Address3 = row["bor_address_3"].ToString();
TelNumber = row["bor_tel"].ToString();
Email = row["bor_email"].ToString();
Status = row["bor_status"].ToString();
return(true);
}
private void DeleteCar(int carId)
{
var dc = new DataConnection();
dc.AddParameter("@car_id", carId);
dc.Execute("sproc_DeleteCar");
}
//Creates a list of type dictionary which holds information about fines with payments
public List <Dictionary <string, string> > FinesWithPayments()
{
_dc = new DataConnection();
_dc.AddParameter("@bor_id", _borrowerId);
_dc.Execute("sproc_GetFinesWithPaymentsByBorrower");
var data = new List <Dictionary <string, string> >();
if (_dc.Count == 0)
{
return(data);
}
foreach (DataRow row in _dc.DataTable.Rows)
{
var temp = new Dictionary <string, string>
{
{ "fineDate", Convert.ToDateTime(row["fine_date"]).ToString("dd MMMM yyyy") },
{ "fineAmount", Convert.ToDecimal(row["fine_amount"]).ToString("C") },
{ "paymentDate", Convert.ToDateTime(row["pmt_date"]).ToString("dd MMMM yyyy") },
{ "paymentAmount", Convert.ToDecimal(row["pmt_amount"]).ToString("C") },
{ "loanType", row["cop_loan_type"].ToString() },
{ "loanId", row["loan_id"].ToString() },
{ "title", row["bk_title"].ToString() }
};
data.Add(temp);
}
return(data);
}
public void Delete(string genreCode)
{
var dc = new DataConnection();
dc.AddParameter("@genre_code", genreCode);
dc.Execute("sproc_DeleteGenre");
}
//Method checks if there is a record of a book copy with passed in barcode in the database
// if found sets properties on a book copy object
public bool Find(string barcode)
{
//Connection to the database
var dc = new DataConnection();
//Pass parameter to the stored procedure
dc.AddParameter("@cop_barcode", barcode);
//Execute stored procedure
dc.Execute("sproc_GetBookCopyByBarcode");
//If returned results dont contain exactly one record return false
if (dc.Count != 1)
{
return(false);
}
//Sets object properties to coresponding rows returned from db and returns true
var copyRow = dc.DataTable.Rows[0];
Barcode = copyRow["cop_barcode"].ToString();
LoanType = copyRow["cop_loan_type"].ToString().Trim();
Status = copyRow["cop_status"].ToString().Trim();
Condition = copyRow["cop_condition"].ToString().Trim();
CopyIsbn = copyRow["fk1_isbn"].ToString().Trim();
return(true);
}
public bool Find(string loanId)
{
var dc = new DataConnection();
dc.AddParameter("@loan_id", loanId);
dc.Execute("sproc_GetLoanById");
if (dc.Count != 1)
{
return(false);
}
var row = dc.DataTable.Rows[0];
LoanId = row["loan_id"].ToString();
LoanIssueDate = Convert.ToDateTime(row["loan_issue_date"]);
LoanDueDate = Convert.ToDateTime(row["loan_due_date"]);
var isDate = DBNull.Value.Equals(row["loan_return_date"]);
if (!isDate)
{
LoanReturnDate = Convert.ToDateTime(row["loan_return_date"]);
}
CopyBarcode = row["fk1_cop_barcode"].ToString();
BorrowerId = row["fk2_bor_id"].ToString();
return(true);
}
//Find book record with corresponding isbn
public bool Find(string isbn)
{
//Connection to the database
var dc = new DataConnection();
//Pass parameter to the stored procedure
dc.AddParameter("@isbn", isbn);
//Execute stored procedure
dc.Execute("sproc_GetBookByIsbn");
//If returned results don't contain exactly one record return false
if (dc.Count != 1)
{
return(false);
}
//returned exactly one record
var row = dc.DataTable.Rows[0];
//Sets object properties to coresponding columns returned from db and returns true
Isbn = row["isbn"].ToString();
Author = row["bk_author"].ToString();
Title = row["bk_title"].ToString();
Publisher = row["bk_publisher"].ToString();
PubYear = row["bk_pub_yr"].ToString();
ShelfNo = row["bk_shelf_no"].ToString();
Edition = Convert.ToInt32(row["bk_edition_no"]);
GenreCode = row["fk1_genre_code"].ToString();
return(true);
}
public string GetDescription(string code)
{
var dc = new DataConnection();
dc.AddParameter("@genre_code", code);
dc.Execute("sproc_GetGenreByCode");
return(dc.DataTable.Rows[0]["genre_descript"].ToString());
}
private void SearchCars(string make, string model, string color)
{
var dc = new DataConnection();
dc.AddParameter("@make", make);
dc.AddParameter("@model", model);
dc.AddParameter("@color", color);
dc.Execute("sproc_CarFilter");
foreach (DataRow row in dc.DataTable.Rows)
{
row["Image"] = "~/App_Assets/img/db_img/" + row["Image"];
}
GridView1.DataSource = dc.DataTable;
GridView1.DataBind();
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
}
private DataTable GetUserCarCollection(string userId)
{
var dc = new DataConnection();
dc.AddParameter("@user_id", userId);
dc.Execute("sproc_getUserCarCollection");
return(dc.DataTable);
}
//Updates record in the book table in db.
public void Update()
{
_dc = new DataConnection();
_dc.AddParameter("@isbn", Book.Isbn);
_dc.AddParameter("@bk_author", Book.Author);
_dc.AddParameter("@bk_title", Book.Title);
_dc.AddParameter("@bk_pub_yr", Book.PubYear);
_dc.AddParameter("@bk_publisher", Book.Publisher);
_dc.AddParameter("@bk_shelf_no", Book.ShelfNo);
_dc.AddParameter("@bk_edition_no", Book.Edition);
_dc.AddParameter("@fk1_genre_code", Book.GenreCode);
_dc.Execute("sproc_UpdateBook");
}
public string SignUp(string email, string password, string passwordConfirm, string firstName, string lastName)
{
if (password != passwordConfirm)
{
return("The password do not match.");
}
string hashPassword = GetHashString(password);
var dc = new DataConnection();
dc.AddParameter("@email", email);
dc.AddParameter("@password", hashPassword);
dc.AddParameter("@firstName", firstName);
dc.AddParameter("@lastName", lastName);
dc.Execute("sproc_AddNewUser");
return("");
}
private void SetUserSessionNameAndId(string loggedInUserEmail)
{
var dc = new DataConnection();
dc.AddParameter("@user_email", loggedInUserEmail);
dc.Execute("sproc_getUserNameByEmail");
Session["user_name"] = dc.DataTable.Rows[0]["first_name"].ToString();
Session["user_id"] = dc.DataTable.Rows[0]["user_id"].ToString();
}
public string GetBorrowerTypeDescription(string borrowerType)
{
var dc = new DataConnection();
dc.AddParameter("@bor_type_id", borrowerType);
dc.Execute("sproc_GetBorrowerDescriptionByTypeId");
if (dc.Count != 1)
{
return("");
}
return(dc.DataTable.Rows[0]["bor_type_name"].ToString());
}
public string GetBookTitle(string isbn)
{
var dc = new DataConnection();
dc.AddParameter("@isbn", isbn);
dc.Execute("sproc_GetBookTitleByIsbn");
if (dc.Count != 1)
{
return("");
}
return(dc.DataTable.Rows[0]["bk_title"].ToString());
}
public string GetBookTitle(string barcode)
{
var dc = new DataConnection();
dc.AddParameter("@cop_barcode", barcode);
dc.Execute("sproc_GetBookTitleByBarcode");
if (dc.Count != 1)
{
return("");
}
return(dc.DataTable.Rows[0]["bk_title"].ToString());
}
//public DataTable GetAllUserDetails(string borrowerId)
//{
// var dc = new DataConnection();
// dc.AddParameter("@bor_id", borrowerId);
// dc.Execute("sproc_Get")
//}
public int GetBorrowerMaxLoans()
{
var dc = new DataConnection();
dc.AddParameter("@bor_type_id", BorrowerType);
dc.Execute("sproc_GetBorrowerMaxLoans");
if (dc.Count != 1)
{
return(0);
}
return(Convert.ToInt32(dc.DataTable.Rows[0]["normal_max_loans"]));
}
public bool Find(string genreCode)
{
var dc = new DataConnection();
dc.AddParameter("@genre_code", genreCode);
dc.Execute("sproc_GetGenreByCode");
if (dc.Count != 1)
{
return(false);
}
var row = dc.DataTable.Rows[0];
GenreCode = row["genre_code"].ToString();
Description = row["genre_descript"].ToString();
return(true);
}
public bool Find(string reservationId)
{
var dc = new DataConnection();
dc.AddParameter("@res_id", reservationId);
dc.Execute("sproc_GetReservationById");
if (dc.Count != 1)
{
return(false);
}
var row = dc.DataTable.Rows[0];
ReservationId = row["res_id"].ToString();
ReservationDate = Convert.ToDateTime(row["res_date"]);
ClearedDate = DBNull.Value.Equals(row["res_cleared_date"]) ? (DateTime?)null : Convert.ToDateTime(row["res_cleared_date"]);
BorrowerId = row["fk2_bor_id"].ToString();
ReservedIsbn = row["fk1_isbn"].ToString();
return(true);
}
public bool Find(string paymentId)
{
var dc = new DataConnection();
dc.AddParameter("@pmt_id", paymentId);
dc.Execute("sproc_GetPaymentById");
if (dc.Count != 1)
{
return(false);
}
var row = dc.DataTable.Rows[0];
PaymentId = row["pmt_id"].ToString();
PaymentDate = Convert.ToDateTime(row["pmt_date"]);
Amount = Convert.ToDecimal(row["pmt_amount"]);
BorrowerId = row["fk1_bor_id"].ToString();
FineId = row["fk2_fine_id"].ToString();
return(true);
}
//filters book records by isbn, title, author, publisher, year and genre code
public List <Book> FilterBooksByAll(string isbn, string title, string author, string publisher, string year,
string genreCode)
{
year = year.Length == 0 ? null : year;
_dc = new DataConnection();
_dc.AddParameter("@isbn", isbn);
_dc.AddParameter("@bk_author", author);
_dc.AddParameter("@bk_title", title);
_dc.AddParameter("@bk_pub_yr", year);
_dc.AddParameter("@bk_publisher", publisher);
_dc.AddParameter("@fk1_genre_code", genreCode);
_dc.Execute("sproc_FilterBooksByAll");
//if no rows are returned from a querie return empty list ,else return book list
return(_dc.Count == 0 ? new List <Book>() : BookList);
}
public void FilterBorrowerById(string id)
{
_dc = new DataConnection();
_dc.AddParameter("@bor_id", id);
_dc.Execute("sproc_FilterBorrowerById");
}
