public JsonResult Search(NET_MVC_WorkShop2.Model.BookSearchArg arg) { NET_MVC_WorkShop2.Service.BookService BookService = new NET_MVC_WorkShop2.Service.BookService(); var result = BookService.GetBookByCondtioin(arg); return(Json(result, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 查詢書籍 /// </summary> /// <returns></returns> public List <NET_MVC_WorkShop2.Model.BookData> GetBookByCondtioin(NET_MVC_WorkShop2.Model.BookSearchArg arg) { DataTable dt = new DataTable(); string sql = @"SELECT BOOK_ID, BOOK_CLASS_NAME ,BOOK_NAME ,BOOK_BOUGHT_DATE ,CODE_NAME ,USER_ENAME FROM [dbo].[BOOK_DATA] as da left join [dbo].[BOOK_CLASS] as cl on da.BOOK_CLASS_ID = cl.BOOK_CLASS_ID left join [dbo].[BOOK_CODE] as co on da.BOOK_STATUS = co.CODE_ID left join [dbo].[MEMBER_M] as m on da.BOOK_KEEPER = m.USER_ID Where (da.BOOK_STATUS = co.CODE_ID) AND (da.BOOK_ID = @Book_ID OR @Book_ID='') AND (BOOK_NAME LIKE ('%' + @Book_Name + '%')or @Book_Name='') AND (UPPER(da.BOOK_CLASS_ID) LIKE UPPER('%' + @Book_Class_Name + '%')or @Book_Class_Name='') AND (BOOK_STATUS LIKE ('%' + @Book_Status + '%')or @Book_Status='') AND (BOOK_KEEPER LIKE ('%' + @Book_Keeper + '%')or @Book_Keeper='') ORDER BY BOOK_BOUGHT_DATE DESC"; using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString())) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@Book_ID", arg.Book_ID == null ? string.Empty : arg.Book_ID)); cmd.Parameters.Add(new SqlParameter("@Book_Name", arg.Book_Name == null ? string.Empty : arg.Book_Name)); cmd.Parameters.Add(new SqlParameter("@Book_Class_Name", arg.Book_Class_Name == null ? string.Empty : arg.Book_Class_Name)); cmd.Parameters.Add(new SqlParameter("@Book_Status", arg.Book_Status == null ? string.Empty : arg.Book_Status)); cmd.Parameters.Add(new SqlParameter("@Book_Keeper", arg.Book_Keeper == null ? string.Empty : arg.Book_Keeper)); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(dt); conn.Close(); } return(this.MapBookDataToList(dt)); }
/// <summary> /// 查詢書籍 /// </summary> /// <returns></returns> public List <NET_MVC_WorkShop2.Model.BookData> GetBookByCondtioin(NET_MVC_WorkShop2.Model.BookSearchArg arg) { NET_MVC_WorkShop2.Dao.BookDao bookDao = new Dao.BookDao(); return(bookDao.GetBookByCondtioin(arg)); }