/// <summary> /// 修改書籍資料 /// </summary> /// <param name="bookData">BOOK_DATA</param> /// <returns>BOOK_DATA.BOOK_ID</returns> public int EditBook(BOOK_DATA bookData) { try { string sql = @"UPDATE BOOK_DATA SET BOOK_NAME = @BOOK_NAME, BOOK_CLASS_ID = @BOOK_CLASS_ID, BOOK_AUTHOR =@BOOK_AUTHOR ,BOOK_BOUGHT_DATE=@BOOK_BOUGHT_DATE,BOOK_PUBLISHER=@BOOK_PUBLISHER ,BOOK_NOTE=@BOOK_NOTE,BOOK_STATUS=@BOOK_STATUS,BOOK_KEEPER =@BOOK_KEEPER Where BOOK_ID=@BOOK_ID"; using (SqlConnection conn = new SqlConnection(_conn)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@BOOK_ID", bookData.BOOK_ID)); cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", bookData.BOOK_NAME)); cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", bookData.BOOK_CLASS_ID)); cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", bookData.BOOK_AUTHOR)); cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", bookData.BOOK_BOUGHT_DATE)); cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", bookData.BOOK_PUBLISHER)); cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", bookData.BOOK_NOTE)); cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", bookData.BOOK_STATUS)); cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", bookData.BOOK_KEEPER ?? (Object)DBNull.Value)); cmd.ExecuteScalar(); conn.Close(); } return(bookData.BOOK_ID); } catch (Exception ex) { return(404); } }
/// <summary> /// 新增書籍 /// </summary> /// <param name="bookData">BOOK_DATA</param> /// <returns>BOOK_DATA.BOOK_ID</returns> public int InsertBook(BOOK_DATA bookData) { string sql = @" INSERT INTO BOOK_DATA ( BOOK_NAME,BOOK_CLASS_ID,BOOK_AUTHOR,BOOK_BOUGHT_DATE ,BOOK_PUBLISHER,BOOK_NOTE,BOOK_STATUS ) VALUES ( @BOOK_NAME,@BOOK_CLASS_ID, @BOOK_AUTHOR, @BOOK_BOUGHT_DATE, @BOOK_PUBLISHER, @BOOK_NOTE, @BOOK_STATUS ) Select SCOPE_IDENTITY()" ; int id; using (SqlConnection conn = new SqlConnection(_conn)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", bookData.BOOK_NAME)); cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", bookData.BOOK_CLASS_ID)); cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", bookData.BOOK_AUTHOR)); cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", bookData.BOOK_BOUGHT_DATE)); cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", bookData.BOOK_PUBLISHER)); cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", bookData.BOOK_NOTE)); cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", "A")); id = Convert.ToInt32(cmd.ExecuteScalar()); conn.Close(); } return(id); }
/// <summary> /// 使用ID 取得書籍資料 /// </summary> /// <param name="BookId">書籍ID</param> /// <returns>BOOK_DATA</returns> public BOOK_DATA GetBookData(int BookId) { DataTable dt = new DataTable(); string sql = @"Select * From dbo.BOOK_DATA Where BOOK_ID = @BookId"; using (SqlConnection conn = new SqlConnection(_conn)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@BookId", BookId)); SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(dt); conn.Close(); } BOOK_DATA _data = new BOOK_DATA(); _data.BOOK_ID = Int32.Parse(dt.Rows[0][0].ToString()); _data.BOOK_NAME = dt.Rows[0][1].ToString(); _data.BOOK_CLASS_ID = dt.Rows[0][2].ToString(); _data.BOOK_AUTHOR = dt.Rows[0][3].ToString(); _data.BOOK_BOUGHT_DATE = DateTime.Parse(dt.Rows[0][4].ToString()); _data.BOOK_PUBLISHER = dt.Rows[0][5].ToString(); _data.BOOK_NOTE = dt.Rows[0][6].ToString(); _data.BOOK_STATUS = dt.Rows[0][7].ToString(); _data.BOOK_KEEPER = dt.Rows[0][8].ToString(); return(_data); }
public ActionResult DeleteConfirmed(int id) { BOOK_DATA bOOK_DATA = db.BOOK_DATA.Find(id); db.BOOK_DATA.Remove(bOOK_DATA); db.SaveChanges(); return(RedirectToAction("Index")); }
/// <summary> /// 用搜尋條件取得書籍資料 /// </summary> /// <param name="BOOK_NAME">書籍名稱</param> /// <param name="BOOK_CLASS_ID">書籍分類ID</param> /// <param name="BOOK_KEEPER">使用者ID</param> /// <param name="BOOK_STATUS">書籍狀態碼</param> /// <returns>BOOK_DATA</returns> public List <BOOK_DATA> GetBookDataList(string BOOK_NAME, string BOOK_CLASS_ID, string BOOK_KEEPER, string BOOK_STATUS) { DataTable dt = new DataTable(); string sql = @"Select * From dbo.BOOK_DATA"; using (SqlConnection conn = new SqlConnection(_conn)) { conn.Open(); SqlDataAdapter sda = new SqlDataAdapter(sql, conn); sda.Fill(dt); conn.Close(); } var data = new List <BOOK_DATA>(); var ClassList = GetBookClassList(); var MemberList = GetMemberList(); var CodeList = GetCodeList(); for (int i = 0; i < dt.Rows.Count; i++) { BOOK_DATA _data = new BOOK_DATA(); _data.BOOK_ID = Int32.Parse(dt.Rows[i][0].ToString()); _data.BOOK_NAME = dt.Rows[i][1].ToString(); _data.BOOK_CLASS_ID = ClassList.Find(x => x.BOOK_CLASS_ID == dt.Rows[i][2].ToString()).BOOK_CLASS_NAME; _data.BOOK_AUTHOR = dt.Rows[i][3].ToString(); _data.BOOK_BOUGHT_DATE = DateTime.Parse(dt.Rows[i][4].ToString()); _data.BOOK_PUBLISHER = dt.Rows[i][5].ToString(); _data.BOOK_NOTE = dt.Rows[i][6].ToString(); _data.BOOK_STATUS = CodeList.Find(x => x.CODE_ID == dt.Rows[i][7].ToString()).CODE_NAME; if (!string.IsNullOrEmpty(dt.Rows[i][8].ToString())) { _data.BOOK_KEEPER = MemberList.Find(x => x.USER_ID == dt.Rows[i][8].ToString()).USER_CNAME; } data.Add(_data); } if (BOOK_NAME != null) { data = data.Where(x => x.BOOK_NAME.Contains(BOOK_NAME)).ToList(); } if (BOOK_CLASS_ID != null) { data = data.Where(x => x.BOOK_CLASS_ID == BOOK_CLASS_ID).ToList(); } if (BOOK_KEEPER != null) { data = data.Where(x => x.BOOK_KEEPER == BOOK_KEEPER).ToList(); } if (BOOK_STATUS != null) { data = data.Where(x => x.BOOK_STATUS == BOOK_STATUS).ToList(); } return(data.OrderByDescending(x => x.BOOK_BOUGHT_DATE).ToList()); }
/// <summary> /// HtmlEncode防止XSS /// </summary> public void ModelHtmlEncode(BOOK_DATA book) { book.BOOK_AUTHOR = Server.HtmlEncode(book.BOOK_AUTHOR); book.BOOK_CLASS_ID = Server.HtmlEncode(book.BOOK_CLASS_ID); book.BOOK_NAME = Server.HtmlEncode(book.BOOK_NAME); book.BOOK_NOTE = Server.HtmlEncode(book.BOOK_NOTE); book.BOOK_PUBLISHER = Server.HtmlEncode(book.BOOK_PUBLISHER); book.BOOK_STATUS = Server.HtmlEncode(book.BOOK_STATUS); book.BOOK_KEEPER = Server.HtmlEncode(book.BOOK_KEEPER); }
/// <summary> /// 以BookId搜尋此書 /// </summary> public BOOK_DATA GetBookDetail(int id) { BooksSearchArg arg = new BooksSearchArg { BookId = id }; BOOK_DATA book = this.booksService.GetBooks(arg).FirstOrDefault(); ModelHtmlDecode(book); return(book); }
public ActionResult Edit([Bind(Include = "BOOK_ID,BOOK_NAME,BOOK_CLASS_ID,BOOK_AUTHOR,BOOK_BOUGHT_DATE,BOOK_PUBLISHER,BOOK_NOTE,BOOK_STATUS,BOOK_KEEPER,BOOK_AMOUNT,CREATE_DATE,CREATE_USER,MODIFY_DATE,MODIFY_USER")] BOOK_DATA bOOK_DATA) { if (ModelState.IsValid) { db.Entry(bOOK_DATA).State = EntityState.Modified; db.SaveChanges(); return(RedirectToAction("Index")); } return(View(bOOK_DATA)); }
// GET: NewBooks/Delete/5 public ActionResult Delete(int?id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } BOOK_DATA bOOK_DATA = db.BOOK_DATA.Find(id); if (bOOK_DATA == null) { return(HttpNotFound()); } return(View(bOOK_DATA)); }
public ActionResult BooksInsert(BOOK_DATA book) { //HtmlEncode ModelHtmlEncode(book); ViewBag.BookClass = this.booksService.GetBookClass(); if (ModelState.IsValid) { ViewBag.InsertMessage = "新增失敗"; ViewBag.AlertClass = "alert alert-danger"; int InsertResult = this.booksService.InsertBooks(book); if (InsertResult != 0) { ViewBag.InsertMessage = "新增完成"; ViewBag.AlertClass = "alert alert-success"; } } return(View()); }
public ActionResult BooksUpdate(BOOK_DATA book, int id) { ModelHtmlEncode(book); book.BOOK_ID = id; //驗證以借閱時借閱者不得為空 if (book.BOOK_STATUS != "A" && book.BOOK_STATUS != "U" && book.BOOK_KEEPER == null) { ViewBag.UpdateMessage = "已借閱 不得無借閱者"; ViewBag.keeperAlarm = ""; SetDropDownListItmes(); return(View(book)); } if (ModelState.IsValid) { int UpdateResult = booksService.UpdateBooks(book); if (UpdateResult != 0) { ViewBag.UpdateMessage = "修改完成"; } } SetDropDownListItmes(); return(View(book)); }
public ActionResult Create(BOOK_DATA table) { db.BOOK_DATA.Add(table); db.SaveChanges(); return(RedirectToAction("Index")); }
/// <summary> /// 新增書籍資料 /// </summary> /// <param name="bookData">BOOK_DATA</param> /// <returns>JSON 書籍編號 BOOK_DATA.BOOK_ID</returns> public JsonResult Insert(BOOK_DATA bookData) { return(Json(bookService.InsertBook(bookData))); }
/// <summary> /// 修改書籍資料 /// </summary> /// <param name="bookData">BOOK_DATA</param> /// <returns>JSON 書籍編號 BOOK_DATA.BOOK_ID</returns> public JsonResult EditBook(BOOK_DATA bookData) { return(Json(bookService.EditBook(bookData))); }