/// 新增書籍
public void AddNewBook(Model.BookData bookData)
{
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
string sql = @"
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO BOOK_DATA(BOOK_NAME, BOOK_CLASS_ID, BOOK_AUTHOR, BOOK_BOUGHT_DATE, BOOK_PUBLISHER, BOOK_STATUS, BOOK_KEEPER, BOOK_NOTE, CREATE_DATE, CREATE_USER, MODIFY_DATE, MODIFY_USER)
VALUES (@bookName, @bookClassID, @bookAuthor, @bookBoughtDate, @bookPublisher, @bookStatus, @bookKeeper, @bookNote, CURRENT_TIMESTAMP, 'RU', CURRENT_TIMESTAMP, 'RU' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROlLBACK TRANSACTION
END CATCH;
";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@bookName", bookData.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@bookAuthor", bookData.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@bookPublisher", bookData.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@bookNote", bookData.BOOK_NOTE));
cmd.Parameters.Add(new SqlParameter("@bookBoughtDate", bookData.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@bookClassID", bookData.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@bookStatus", "A"));
cmd.Parameters.Add(new SqlParameter("@bookKeeper", string.Empty));
cmd.ExecuteNonQuery();
conn.Close();
}
}
// GET: Inquire
public ActionResult Index(Model.BookData listdata)
{
Service.SQLInquire SSI = new Service.SQLInquire();
List <Model.BookData> Data = new List <Model.BookData>();
if (listdata.Book_Search == null)
{
Data = SSI.Find("", true);
listdata.Book_Search = "";
}
else
{
Data = SSI.Find(listdata.Book_Search.ToString(), false);
}
@ViewBag.result = Data;
return(View());
}
/// 根據BOOK_ID去取得特定的一本書
public Model.BookData GetBookData(int bookID)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
string sql = @"SELECT bd.BOOK_ID AS BOOK_ID, bd.BOOK_NAME AS BOOK_NAME, bd.BOOK_CLASS_ID AS BOOK_CLASS_ID,
bcl.BOOK_CLASS_NAME AS BOOK_CLASS_NAME, bd.BOOK_AUTHOR AS BOOK_AUTHOR, bd.BOOK_BOUGHT_DATE AS BOOK_BOUGHT_DATE,
bd.BOOK_PUBLISHER AS BOOK_PUBLISHER, bd.BOOK_NOTE AS BOOK_NOTE, bd.BOOK_STATUS AS BOOK_STATUS,
bco.CODE_NAME AS BOOK_STATUS_NAME, bd.BOOK_KEEPER AS BOOK_KEEPER, m.USER_ENAME+'('+m.USER_CNAME+')' AS BOOK_KEEPER_NAME,
bd.CREATE_DATE AS CREATE_DATE, bd.CREATE_USER AS CREATE_USER, bd.MODIFY_DATE AS MODIFY_DATE,
bd.MODIFY_USER AS MODIFY_USER
FROM BOOK_DATA AS bd
INNER JOIN BOOK_CLASS AS bcl ON bd.BOOK_CLASS_ID = bcl.BOOK_CLASS_ID
INNER JOIN BOOK_CODE AS bco ON bd.BOOK_STATUS = bco.CODE_ID
LEFT JOIN MEMBER_M AS m ON bd.BOOK_KEEPER = m.USER_ID
WHERE bd.BOOK_ID = @bookID
";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@bookID", bookID));
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dt);
conn.Close();
Model.BookData bookData = new Model.BookData();
foreach (DataRow row in dt.Rows)
{
bookData.BOOK_ID = (int)row["BOOK_ID"];
bookData.BOOK_NAME = row["BOOK_NAME"].ToString() == null ? string.Empty : row["BOOK_NAME"].ToString();
bookData.BOOK_CLASS_ID = row["BOOK_CLASS_ID"].ToString();
bookData.BOOK_CLASS_NAME = row["BOOK_CLASS_NAME"].ToString();
bookData.BOOK_AUTHOR = row["BOOK_AUTHOR"].ToString() == null? string.Empty : row["BOOK_AUTHOR"].ToString();
bookData.BOOK_BOUGHT_DATE = Convert.ToDateTime(row["BOOK_BOUGHT_DATE"]).ToString("yyyy/MM/dd HH:mm:ss");
bookData.BOOK_PUBLISHER = row["BOOK_PUBLISHER"].ToString() == null ? string.Empty : row["BOOK_PUBLISHER"].ToString();
bookData.BOOK_NOTE = row["BOOK_NOTE"].ToString() == null ? string.Empty : row["BOOK_NOTE"].ToString();
bookData.BOOK_STATUS = row["BOOK_STATUS"].ToString();
bookData.BOOK_STATUS_NAME = row["BOOK_STATUS_NAME"].ToString();
bookData.BOOK_KEEPER = row["BOOK_KEEPER"].ToString() == null ? string.Empty : row["BOOK_KEEPER"].ToString();
bookData.BOOK_KEEPER_NAME = row["BOOK_KEEPER_NAME"].ToString() == null ? string.Empty : row["BOOK_KEEPER_NAME"].ToString();
}
return(bookData);
}
}
/// 根據輸入的BOOK_ID去找書，編輯該書本
public void EditBook(Model.BookData bookData)
{
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
string sql = @"
BEGIN TRY
BEGIN TRANSACTION
UPDATE bd
SET bd.BOOK_NAME = @bookName, bd.BOOK_CLASS_ID = @bookClassID, bd.BOOK_AUTHOR = @bookAuthor,
bd.BOOK_BOUGHT_DATE = @bookBoughtDate, bd.BOOK_PUBLISHER = @bookPublisher,bd.BOOK_STATUS = @bookStatus,
bd.BOOK_KEEPER = @bookKeeper, bd.BOOK_NOTE = @bookNote, bd.MODIFY_DATE = CURRENT_TIMESTAMP,
bd.MODIFY_USER = '******'
FROM BOOK_DATA AS bd
WHERE bd.BOOK_ID = @bookID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROlLBACK TRANSACTION
END CATCH;
";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@bookID", bookData.BOOK_ID));
cmd.Parameters.Add(new SqlParameter("@bookName", bookData.BOOK_NAME == null ? string.Empty : bookData.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@bookClassID", bookData.BOOK_CLASS_ID == null ? string.Empty : bookData.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@bookAuthor", bookData.BOOK_AUTHOR == null ? string.Empty : bookData.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@bookKeeper", bookData.BOOK_KEEPER == null ? string.Empty : bookData.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@bookStatus", bookData.BOOK_STATUS == null ? string.Empty : bookData.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@bookBoughtDate", bookData.BOOK_BOUGHT_DATE == null ? string.Empty : bookData.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@bookPublisher", bookData.BOOK_PUBLISHER == null ? string.Empty : bookData.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@bookNote", bookData.BOOK_NOTE == null ? string.Empty : bookData.BOOK_NOTE));
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
conn.Close();
}
}
public JsonResult EditBookData(int id)
{
Service.BookDataService bookDataService = new Service.BookDataService();
Model.BookData bookData = bookDataService.GetBookData(id);
return(Json(bookData));
}
public ActionResult CreateNewBook(Model.BookData bookData)
{
Service.BookDataService bookDataService = new Service.BookDataService();
bookDataService.AddNewBook(bookData);
return(Redirect("/"));
}
public ActionResult UpdateBookData(Model.BookData bookData)
{
Service.BookDataService bookDataService = new Service.BookDataService();
bookDataService.EditBook(bookData);
return(Redirect("/"));
}
/// 根據輸入的BOOK_ID去找書，編輯該書本
public void EditBook(Model.BookData bookData)
{
Dao.IBookDataDao bookDataDao = new Dao.BookDataDao();
bookDataDao.EditBook(bookData);
}
/// 新增書籍
public void AddNewBook(Model.BookData bookData)
{
Dao.IBookDataDao bookDataDao = new Dao.BookDataDao();
bookDataDao.AddNewBook(bookData);
}
