public ActionResult SearchBook(Models.SearchBook book) { Models.BookService library = new Models.BookService(); ViewBag.bookClassData = this.codeService.GetBookClassDropdownlist(); ViewBag.borrowerData = this.codeService.GetBorrowerDropdownlist(); ViewBag.statusData = this.codeService.GetBookStatusDropdownlist(); ViewBag.searchResult = library.GetBookByData(book); return(View()); }
/// <summary> /// 根據資料查找書籍 /// </summary> public List <Models.Book> GetBookByData(Models.SearchBook searchBook) { DataTable dt = new DataTable(); string sql = @"SELECT CLASS.BOOK_CLASS_NAME as TypeName, DATA.BOOK_NAME as Name, CONVERT(VARCHAR(10),DATA.BOOK_BOUGHT_DATE,111)AS BoughtDate, DATA.BOOK_STATUS as Status, CODE.CODE_NAME as StatusName, (MEM.USER_CNAME+'-'+MEM.USER_ENAME)AS Borrower , data.BOOK_ID AS Id FROM BOOK_DATA AS data INNER JOIN BOOK_CLASS AS CLASS ON DATA.BOOK_CLASS_ID=CLASS.BOOK_CLASS_ID LEFT JOIN MEMBER_M AS MEM ON DATA.BOOK_KEEPER=MEM.USER_ID INNER JOIN BOOK_CODE AS CODE ON DATA.BOOK_STATUS=CODE.CODE_ID AND CODE.CODE_TYPE='BOOK_STATUS' WHERE (DATA.BOOK_CLASS_ID=@classID OR @classID='') AND (UPPER (DATA.BOOK_NAME) LIKE UPPER('%'+@name+'%') or @name='') AND (DATA.BOOK_KEEPER=@borrower OR @borrower='') AND (DATA.BOOK_STATUS = @status OR @status='') ORDER BY BoughtDate DESC"; using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString())) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@classID", searchBook.book_TypeName == null ? string.Empty : searchBook.book_TypeName)); cmd.Parameters.Add(new SqlParameter("@name", searchBook.book_Name == null ? string.Empty : searchBook.book_Name)); cmd.Parameters.Add(new SqlParameter("@borrower", searchBook.book_Borrower == null ? string.Empty : searchBook.book_Borrower)); cmd.Parameters.Add(new SqlParameter("@status", searchBook.book_Status == null ? string.Empty : searchBook.book_Status)); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(dt); conn.Close(); } return(this.MapBookByData(dt)); }