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"); }