public ActionResult Index(Models.BookServiceArg arg) { Models.BookService bookService = new Models.BookService(); ViewBag.SearchResult = bookService.GetBookByCondtioin(arg); ViewBag.ClassName = this.codeService.GetBookClassId("ClassName"); ViewBag.KeeperName = this.codeService.GetMember("KeeperName"); ViewBag.CodeName = this.codeService.GetCodeName("CodeName"); return(View("Index")); }
/// <summary> /// 依照條件取得資料 /// </summary> /// <returns></returns> public List <Models.BookData> GetBookByCondtioin(Models.BookServiceArg arg) { DataTable dt = new DataTable(); string sql = @"SELECT bd.BOOK_ID AS BookId, bc.BOOK_CLASS_NAME AS ClassName, bd.BOOK_NAME AS BookName, FORMAT(bd.BOOK_BOUGHT_DATE,'yyyy/MM/dd') AS BoughtDate, bc1.CODE_NAME AS CodeName, ISNULL(mm.USER_ENAME,'') AS KeeperName FROM BOOK_DATA bd JOIN BOOK_CLASS bc ON bd.BOOK_CLASS_ID = bc.BOOK_CLASS_ID JOIN BOOK_CODE bc1 ON bc1.CODE_ID = bd.BOOK_STATUS AND bc1.code_type = 'BOOK_STATUS' LEFT JOIN MEMBER_M mm ON bd.BOOK_KEEPER = mm.USER_ID Where (bd.BOOK_NAME LIKE '%'+ @BookName +'%' ) AND (bc.BOOK_CLASS_ID LIKE '%'+ @ClassName +'%' ) AND (ISNULL(mm.USER_ID,'') LIKE '%'+ @KeeperName +'%' ) AND (bc1.CODE_ID LIKE '%'+ @CodeName +'%') ORDER BY bc.BOOK_CLASS_NAME;"; using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString())) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@BookId", arg.BookId == null ? string.Empty : arg.BookId)); cmd.Parameters.Add(new SqlParameter("@ClassName", arg.ClassName == null ? string.Empty : arg.ClassName)); cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName)); cmd.Parameters.Add(new SqlParameter("@BoughtDate", arg.BoughtDate == null ? string.Empty : arg.BoughtDate)); cmd.Parameters.Add(new SqlParameter("@CodeName", arg.CodeName == null ? string.Empty : arg.CodeName)); cmd.Parameters.Add(new SqlParameter("@KeeperName", arg.KeeperName == null ? string.Empty : arg.KeeperName)); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(dt); conn.Close(); } return(this.MapBookDataToList(dt)); }