/// <summary> /// Find list of book /// </summary> /// <param name="inputquery">Search query</param> /// <returns>List of book with field similar with inputQuery</returns> public List <Book> Find(string inputquery) { if (string.IsNullOrEmpty(inputquery.Trim())) { throw new NotImplementedException(); } Queue <object> parameters = new Queue <object>(); parameters.Enqueue(new OleDbParameter("@inputQuery", inputquery.Trim())); string query = "SELECT tblBook.ID, tblBook.Title, tblBook.Author, tblBook.Publisher, tblBook.PublishedDate FROM tblBook WHERE tblBook.ID like '%'+@inputQuery+'%' or tblBook.Title like '%'+@inputQuery+'%' or tblBook.Author like '%'+@inputQuery+'%' or tblBook.Publisher like '%'+@inputQuery+'%' or tblBook.PublishedDate like '%'+@inputQuery+'%';"; DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters); List <Book> resultBook = new List <Book>(); foreach (DataRow dataRow in result.Tables[0].Rows) { resultBook.Add(new Book( Convert.ToInt32(dataRow[0].ToString()), dataRow[1].ToString(), dataRow[2].ToString(), dataRow[3].ToString(), DateTime.Parse(dataRow[4].ToString()) )); } return(resultBook); }
public int CountUsername(string username) { if (string.IsNullOrEmpty(username.Trim())) { throw new NotImplementedException(); } Queue <object> parameters = new Queue <object>(); parameters.Enqueue(new OleDbParameter("@Username", username)); string query = "SELECT Count(*) from tblAccount WHERE tblAccount.Username = @Username"; DataRow result = DBHelper.ExecuteReturnedQuery(query, parameters).Tables[0].Rows[0]; return(Convert.ToInt32(result[0].ToString())); }
public List <Account> BookHistory(int bookId) { if (bookId <= 0) { throw new NotImplementedException(); } string query = "select tblAccount.ID, tblAccount.Fullname, tblAccount.Address, tblAccount.PhoneNumber, tblAccount.Opened, tblAccount.Level from ( tblBorrow inner join tblBook on tblBorrow.BookId = tblBook.ID) inner join tblAccount on tblBorrow.AccountId = tblAccount.ID where tblBook.ID = @ID; "; Queue <object> parameters = new Queue <object>(); parameters.Enqueue(new SqlParameter("@ID", bookId)); DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters); List <Account> resultAccount = new List <Account>(); foreach (DataRow dataRow in result.Tables[0].Rows) { resultAccount.Add(new Account( Convert.ToInt32(dataRow[0].ToString()), dataRow[1].ToString(), dataRow[2].ToString(), dataRow[3].ToString(), DateTime.Parse(dataRow[4].ToString()), (AccountLevelEnums)Convert.ToInt32(dataRow[5].ToString()) )); } return(resultAccount); }
public List <BorrowReport> GetReport() { string query = "SELECT tblBorrow.Borrowed , tblBorrow.Fine FROM (tblBorrow INNER JOIN tblBook ON tblBorrow.BookId = tblBook.ID) INNER JOIN tblAccount ON tblBorrow.AccountId = tblAccount.ID"; Queue <object> parameters = new Queue <object>(); DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters); List <BorrowReport> resultReport = new List <BorrowReport>(); foreach (DataRow dataRow in result.Tables[0].Rows) { DateTime month = Convert.ToDateTime(dataRow[0].ToString()); decimal fine = string.IsNullOrEmpty(dataRow[1].ToString()) ? 0 : Convert.ToDecimal(dataRow[1].ToString()); resultReport.Add(new BorrowReport(month, fine)); } return(resultReport); }