public int updateBook(Models.bookSearchArgs args) { string sql = @" UPDATE BOOK_DATA SET BOOK_NAME = @bookName, BOOK_AUTHOR = @bookAuthor, BOOK_PUBLISHER = @bookPublisher, BOOK_NOTE = @bookNote, BOOK_BOUGHT_DATE = @bookBoughtDate, BOOK_CLASS_ID = @bookClassId, BOOK_STATUS = @bookStatus, BOOK_KEEPER = @bookKeeper WHERE BOOK_ID = @bookId"; int updateBookCount; using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString())) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@bookName", args.BOOK_NAME)); cmd.Parameters.Add(new SqlParameter("@bookAuthor", args.BOOK_AUTHOR)); cmd.Parameters.Add(new SqlParameter("@bookPublisher", args.BOOK_PUBLISHER)); cmd.Parameters.Add(new SqlParameter("@bookNote", args.BOOK_NOTE)); cmd.Parameters.Add(new SqlParameter("@bookBoughtDate", args.BOOK_BOUGHT_DATE)); cmd.Parameters.Add(new SqlParameter("@bookClassId", args.BOOK_CLASS_ID)); cmd.Parameters.Add(new SqlParameter("@bookStatus", args.BOOK_STATUS)); cmd.Parameters.Add(new SqlParameter("@bookKeeper", args.BOOK_KEEPER == null ? string.Empty : args.BOOK_KEEPER)); cmd.Parameters.Add(new SqlParameter("@bookId", args.BOOK_ID)); updateBookCount = cmd.ExecuteNonQuery(); conn.Close(); } return(args.BOOK_ID); }
public int insertBook(Models.bookSearchArgs args) { string sql = @"Insert Into BOOK_DATA (BOOK_NAME,BOOK_AUTHOR,BOOK_PUBLISHER,BOOK_NOTE, BOOK_BOUGHT_DATE,BOOK_CLASS_ID,BOOK_STATUS,BOOK_KEEPER) Values (@bookName,@bookAuthor,@bookPublisher,@bookNote, @bookBoughtDate,@bookClassId,@bookStatus,@bookKeeper) Select SCOPE_IDENTITY()"; int newId; using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString())) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@bookName", args.BOOK_NAME)); cmd.Parameters.Add(new SqlParameter("@bookAuthor", args.BOOK_AUTHOR)); cmd.Parameters.Add(new SqlParameter("@bookPublisher", args.BOOK_PUBLISHER)); cmd.Parameters.Add(new SqlParameter("@bookNote", args.BOOK_NOTE)); cmd.Parameters.Add(new SqlParameter("@bookBoughtDate", args.BOOK_BOUGHT_DATE)); cmd.Parameters.Add(new SqlParameter("@bookClassId", args.BOOK_CLASS_ID)); cmd.Parameters.Add(new SqlParameter("@bookStatus", "A")); cmd.Parameters.Add(new SqlParameter("@bookKeeper", "")); newId = Convert.ToInt32(cmd.ExecuteScalar()); conn.Close(); } return(newId); }
public JsonResult Index(Models.bookSearchArgs args) { Models.bookService bookService = new Models.bookService(); var result = bookService.getSearchBookData(args); return(Json(result)); }
public JsonResult UpdateBook(Models.bookSearchArgs args) { Models.bookService bookService = new Models.bookService(); var resultBookId = bookService.updateBook(args); var result = bookService.getOneBookData(resultBookId); return(Json(result)); }
public List <Models.book> getSearchBookData(Models.bookSearchArgs args) { DataTable dt = new DataTable(); string sql = @"Select BOOK_ID, BOOK_CLASS.BOOK_CLASS_NAME, BOOK_NAME, BOOK_BOUGHT_DATE, BOOK_NOTE, BOOK_AUTHOR, BOOK_PUBLISHER, MEMBER_M.USER_CNAME, BOOK_CODE.CODE_NAME From [dbo].[BOOK_DATA] LEFT JOIN [dbo].[BOOK_CLASS] ON BOOK_DATA.BOOK_CLASS_ID=BOOK_CLASS.BOOK_CLASS_ID LEFT JOIN [dbo].[MEMBER_M] ON BOOK_DATA.BOOK_KEEPER=MEMBER_M.USER_ID LEFT JOIN [dbo].[BOOK_CODE] ON BOOK_DATA.BOOK_STATUS=BOOK_CODE.CODE_ID Where(BOOK_DATA.BOOK_CLASS_ID = @bookClassId OR @bookClassId='') AND (BOOK_DATA.BOOK_NAME LIKE ('%'+ @bookName +'%') OR @bookName='') AND (BOOK_DATA.BOOK_STATUS = @bookStatus OR @bookStatus='') AND (BOOK_DATA.BOOK_KEEPER = @bookKeeper OR @bookKeeper='') 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("@bookClassId", args.BOOK_CLASS_ID == null ? string.Empty : args.BOOK_CLASS_ID)); cmd.Parameters.Add(new SqlParameter("@bookName", args.BOOK_NAME == null ? string.Empty : args.BOOK_NAME)); cmd.Parameters.Add(new SqlParameter("@bookStatus", args.BOOK_STATUS == null ? string.Empty : args.BOOK_STATUS)); cmd.Parameters.Add(new SqlParameter("@bookKeeper", args.BOOK_KEEPER == null ? string.Empty : args.BOOK_KEEPER)); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(dt); conn.Close(); } return(this.mapBookData(dt)); }