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