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