/// <summary> /// 取得書籍資訊 /// </summary> /// <returns></returns> public List <Models.Books> GetBooks(BooksSearchArg arg) { DataTable booksTable = new DataTable(); string cmdText = @"SELECT b.BOOK_ID, b.BOOK_NAME, b.BOOK_CLASS_ID, bClass.BOOK_CLASS_NAME, b.BOOK_AUTHOR, CONVERT( varchar(12), b.BOOK_BOUGHT_DATE, 23) BOOK_BOUGHT_DATE, b.BOOK_PUBLISHER, b.BOOK_NOTE, b.BOOK_STATUS, bCode.CODE_NAME, b.BOOK_KEEPER, m.USER_CNAME, m.USER_ENAME FROM BOOK_DATA b LEFT JOIN BOOK_CLASS bClass ON b.BOOK_CLASS_ID=bClass.BOOK_CLASS_ID LEFT JOIN BOOK_CODE bCode ON b.BOOK_STATUS=bCode.CODE_ID AND bCode.CODE_TYPE='BOOK_STATUS' LEFT JOIN MEMBER_M m ON b.BOOK_KEEPER=m.[USER_ID] WHERE (b.BOOK_ID=@BOOK_ID or @BOOK_ID=0) AND (UPPER(b.BOOK_NAME) LIKE '%'+UPPER(@BOOK_NAME)+'%' or @BOOK_NAME='') AND (b.BOOK_CLASS_ID=@BOOK_CLASS_ID or @BOOK_CLASS_ID='') AND (b.BOOK_KEEPER=@BOOK_KEEPER or @BOOK_KEEPER='') AND (b.BOOK_STATUS=@BOOK_STATUS or @BOOK_STATUS='') Order By b.BOOK_BOUGHT_DATE DESC, BOOK_ID"; using (SqlConnection conn = new SqlConnection(GetDBConnectionString())) { conn.Open(); SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.Parameters.AddWithValue("@BOOK_ID", arg.BookId); cmd.Parameters.AddWithValue("@BOOK_NAME", arg.BookName ?? string.Empty); cmd.Parameters.AddWithValue("@BOOK_CLASS_ID", arg.BookClassId ?? string.Empty); cmd.Parameters.AddWithValue("@BOOK_KEEPER", arg.KeeperId ?? string.Empty); cmd.Parameters.AddWithValue("@BOOK_STATUS", arg.BookStatusCode ?? string.Empty); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(booksTable); conn.Close(); } return(MapBooksDataToList(booksTable)); }
/// <summary> /// 取得書籍資訊 /// </summary> /// <returns></returns> public List <BOOK_DATA> GetBooks(BooksSearchArg arg) { try { return(db.BOOK_DATA .Include(b => b.MEMBER_M) .Include(b => b.BOOK_CLASS) .Include(b => b.BOOK_CODE) .Where(b => (b.BOOK_ID == arg.BookId || arg.BookId == 0) && (b.BOOK_NAME.ToUpper().Contains(arg.BookName.ToUpper() ?? string.Empty) || (arg.BookName ?? string.Empty) == "") && (b.BOOK_CLASS_ID == (arg.BookClassId ?? string.Empty) || (arg.BookClassId ?? string.Empty) == "") && (b.BOOK_KEEPER == (arg.KeeperId ?? string.Empty) || (arg.KeeperId ?? string.Empty) == "") && (b.BOOK_STATUS == (arg.BookStatusCode ?? string.Empty) || (arg.BookStatusCode ?? string.Empty) == "") ) .OrderByDescending(b => b.BOOK_BOUGHT_DATE) .ThenBy(b => b.BOOK_ID) .ToList()); } catch (Exception ex) { return(new List <BOOK_DATA>()); } }