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