/// 新增書籍
public void AddNewBook(Models.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();
}
}
public int Insert(Models.BookData book)
{
string sql = @" INSERT INTO [dbo].[BOOK_DATA]
(
BOOK_NAME, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_NOTE, BOOK_BOUGHT_DATE, BOOK_CLASS_ID , BOOK_STATUS, BOOK_KEEPER
)
VALUES
(
@Book_Name,@Book_Author, @Book_Publisher, @Book_Note, @Book_BoughtDate, @Book_Class_ID , @Book_Status, @Book_Keeper
)
Select SCOPE_IDENTITY()";
int BookId;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@Book_Name", book.Book_Name));
cmd.Parameters.Add(new SqlParameter("@Book_Author", book.Book_Author));
cmd.Parameters.Add(new SqlParameter("@Book_Publisher", book.Book_Publisher));
cmd.Parameters.Add(new SqlParameter("@Book_Note", book.Book_Note));
cmd.Parameters.Add(new SqlParameter("@Book_BoughtDate", book.Book_BoughtDate));
cmd.Parameters.Add(new SqlParameter("@Book_Class_ID", book.Book_Class_ID));
cmd.Parameters.Add(new SqlParameter("@Book_Status", book.Book_Status));
cmd.Parameters.Add(new SqlParameter("@Book_Keeper", book.Book_Keeper));
BookId = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(BookId);
}
/// <summary>
/// 修改書籍
/// </summary>
/// <returns>書籍編號</returns>
public int UpdateBook(Models.BookData book)
{
string sql = @" UPDATE [dbo].[BOOK_DATA]
SET BOOK_NAME = @Book_Name, BOOK_AUTHOR = @Book_Author, BOOK_PUBLISHER = @Book_Publisher, BOOK_NOTE = @Book_Note,
BOOK_BOUGHT_DATE = @Book_BoughtDate, BOOK_CLASS_ID = @Book_Class_ID, BOOK_STATUS = @Book_Status, BOOK_KEEPER = @Book_Keeper
WHERE BOOK_ID = @Book_ID";
int BookID;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@Book_ID", book.Book_ID));
cmd.Parameters.Add(new SqlParameter("@Book_Name", book.Book_Name));
cmd.Parameters.Add(new SqlParameter("@Book_Author", book.Book_Author));
cmd.Parameters.Add(new SqlParameter("@Book_Publisher", book.Book_Publisher));
cmd.Parameters.Add(new SqlParameter("@Book_Note", book.Book_Note));
cmd.Parameters.Add(new SqlParameter("@Book_BoughtDate", book.Book_BoughtDate));
cmd.Parameters.Add(new SqlParameter("@Book_Class_ID", book.Book_Class_ID));
cmd.Parameters.Add(new SqlParameter("@Book_Status", book.Book_Status));
cmd.Parameters.Add(new SqlParameter("@Book_Keeper", book.Book_Keeper));
BookID = (int)(cmd.ExecuteNonQuery());
conn.Close();
}
return(BookID);
}
public int UpdateBookData(Models.BookData book)
{
DataTable dt = new DataTable();
string sql = @"UPDATE dbo.BOOK_DATA
SET BOOK_NAME = @BOOK_NAME, BOOK_AUTHOR = @BOOK_AUTHOR,
BOOK_PUBLISHER = @BOOK_PUBLISHER, BOOK_CLASS_ID = @BOOK_CLASS_ID,
BOOK_NOTE = @BOOK_NOTE, BOOK_BOUGHT_DATE = @BOOK_BOUGHT_DATE,
BOOK_STATUS = @BOOK_STATUS, BOOK_KEEPER = @BOOK_KEEPER
WHERE BOOK_ID = @BOOK_ID";
int bookId;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_ID", book.BOOK_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", book.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", book.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", book.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", book.BOOK_NOTE));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", book.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", book.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", book.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", book.BOOK_KEEPER));
bookId = (int)(cmd.ExecuteNonQuery());
conn.Close();
}
return(bookId);
}
/// <summary>
/// 新增書籍資料
/// </summary>
public void InsertBook(Models.BookData 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,'A'
)";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", bookdata.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", bookdata.ClassId));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", bookdata.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", bookdata.Publisher));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", bookdata.BoughtDate));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", bookdata.BookNote));
cmd.ExecuteNonQuery();
conn.Close();
}
}
public JsonResult Insert(Models.BookData book)
{
Models.BookService BookService = new Models.BookService();
var result = BookService.Insert(book);
return(Json(result, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 新增書籍資料
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int InsertBookData(Models.BookData book)
{
DataTable dt = new DataTable();
string sql = @"Insert INTO dbo.BOOK_DATA
(BOOK_NAME, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_NOTE,
BOOK_BOUGHT_DATE, BOOK_CLASS_ID, BOOK_STATUS, BOOK_KEEPER )
VALUES (@BOOK_NAME, @BOOK_AUTHOR, @BOOK_PUBLISHER, @BOOK_NOTE,
@BOOK_BOUGHT_DATE, @BOOK_CLASS_ID, @BOOK_STATUS, @BOOK_KEEPER )
Select SCOPE_IDENTITY()";
int bookId;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", book.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", book.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", book.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", book.BOOK_NOTE));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", book.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", book.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", "A"));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", ""));
bookId = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(bookId);
}
///<summary>
/// 修改圖書(儲存)
/// <param name="books"></param>
/// </summary>
/// <returns>圖書編號</returns>
public bool UpdateBookData(Models.BookData arg)
{
DataTable dt = new DataTable();
string sql = @"UPDATE BOOK_DATA
SET BOOK_NAME = @BookName,
BOOK_AUTHOR = @BookAuthor,
BOOK_PUBLISHER = @BookPublisher,
BOOK_NOTE = @BookNote,
BOOK_BOUGHT_DATE = CONVERT(DATETIME, @BoughtDate),
BOOK_CLASS_ID = @BookClassId,
BOOK_STATUS = @BookStatusId,
BOOK_KEEPER = @BookKeeperId
WHERE BOOK_ID = @BookId";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? "" : arg.BookName.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookAuthor", arg.BookAuthor == null ? "" : arg.BookAuthor.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookPublisher", arg.BookPublisher == null ? "" : arg.BookPublisher.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookNote", arg.BookNote == null ? "" : arg.BookNote.ToString()));
cmd.Parameters.Add(new SqlParameter("@BoughtDate", arg.BookBoughtDate.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassID.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookStatusId", arg.CodeID.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookKeeperId", arg.UserID == null ? "" : arg.UserID.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookId", arg.BookID));
sqlAdapter.Fill(dt);
conn.Close();
}
return(true);
}
public ActionResult InsertBook(Models.BookData bookdata)
{
ViewBag.ClassName = this.codeService.GetBookClassId("ClassName");
if (ModelState.IsValid)
{
Models.BookService bookService = new Models.BookService();
bookService.InsertBook(bookdata);
TempData["message"] = "存檔成功";
}
return(View(bookdata));
}
public JsonResult InsertBook(Models.BookData book)
{
try
{
bookService.InsertBook(book);
return(this.Json(true));
}
catch (Exception ex)
{
return(this.Json(false));
}
}
///<summary>
/// 修改圖書(儲存)
/// <param name="books"></param>
/// </summary>
/// <returns>圖書編號</returns>
public void UpdateBookData(Models.BookData books)
{
string sql = @"UPDATE BOOK_DATA
SET BOOK_NAME=@BookName,
BOOK_BOUGHT_DATE=@BookBoughtDate,
BOOK_CLASS_ID=@BookClassName,
BOOK_STATUS=@CodeName,
BOOK_KEEPER=@UserName,
BOOK_AUTHOR=@BookAuthor,
BOOK_PUBLISHER=@BookPublisher,
BOOK_NOTE=@BookNote
WHERE BOOK_ID=@BookID ";
///借閱紀錄新增
if (!string.IsNullOrEmpty(books.UserName))
{
sql += @"INSERT INTO BOOK_LEND_RECORD(BOOK_ID,KEEPER_ID,LEND_DATE)
VALUES(@BookID,@UserName,@LendDate)";
}
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookID", books.BookID));
cmd.Parameters.Add(new SqlParameter("@BookName", books.BookName));
cmd.Parameters.Add(new SqlParameter("@BookClassName", books.BookClassName));
cmd.Parameters.Add(new SqlParameter("@BookAuthor", books.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BookBoughtDate", books.BookBoughtDate));
cmd.Parameters.Add(new SqlParameter("@BookPublisher", books.BookPublisher));
cmd.Parameters.Add(new SqlParameter("@BookNote", books.BookNote));
cmd.Parameters.Add(new SqlParameter("@CodeName", books.CodeName));
cmd.Parameters.Add(new SqlParameter("@UserName", books.UserName == null ? string.Empty : books.UserName));
cmd.Parameters.Add(new SqlParameter("@LendDate", DateTime.Now));
SqlTransaction Tran = conn.BeginTransaction();
cmd.Transaction = Tran;
try
{
cmd.ExecuteNonQuery();
Tran.Commit();
}
catch (Exception)
{
Tran.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
private Models.BookData MapEditData(DataTable dt)
{
Models.BookData result = new Models.BookData();
result.BookID = (int)dt.Rows[0]["BookID"];
result.BookName = dt.Rows[0]["BookName"].ToString();
result.BookAuthor = dt.Rows[0]["BookAuthor"].ToString();
result.BookPublisher = dt.Rows[0]["BookPublisher"].ToString();
result.BookNote = dt.Rows[0]["BookNote"].ToString();
result.BookBoughtDate = dt.Rows[0]["BookBoughtDate"].ToString();
result.BookClassID = dt.Rows[0]["BookClassId"].ToString();
result.CodeID = dt.Rows[0]["CodeID"].ToString();
result.UserID = dt.Rows[0]["UserID"].ToString();
return(result);
}
[HttpGet] //Detail的參數用string 是不是比較好
public ActionResult Detail(string BookId) //**lookDetail -> Detail 因為lookDetail命名怪怪的
{ //**查看明細的時候將網址列的 ?bookId=A，會跳出錯誤，
//**因為資料庫中的 BOOK_ID 屬性是 int，GetBookDetails()裏面對資料庫的存取會出錯
//**所以用try catch包起來ex就會有錯誤訊息，網頁就不會當掉
//BookId 甚麼時候轉型比較好，Detail的參數是int，那參數是string的時候不會進來
try
{
Models.BookMangementService bookMangementService = new Models.BookMangementService();
Models.BookData bookData = bookMangementService.GetBookDetails(BookId).FirstOrDefault();
return(View("Detail", bookData));
}
catch (Exception ex)
{
return(View("Index"));
}
}
[HttpPost] //**會和使用者直接接觸的Action參數型別用string是不是比較好?
public ActionResult UpdateBook(Models.BookData bookData)
{
Models.BookMangementService bookMangementService = new Models.BookMangementService();
if (ModelState.IsValid)
{
try
{
DateTime.Parse(bookData.BookBoughtDate);
bookMangementService.UpdateBookData(bookData);
return(RedirectToAction("Detail", new { bookData.BookId }));
}catch (Exception ex)
{
Response.Write("<script language=javascript>alert('日期格式錯誤')</script>");
}
}
return(View(bookData));
}
/// 根據BOOK_ID去取得特定的一本書
public Models.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();
Models.BookData bookData = new Models.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);
}
}
public void InsertBook(Models.BookData bookdata)
{
string sql = @" Insert Into dbo.BOOK_DATA (BOOK_NAME, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_NOTE, BOOK_BOUGHT_DATE, BOOK_CLASS_ID, BOOK_STATUS, BOOK_KEEPER)
Values(@BOOK_NAME, @BOOK_AUTHOR, @BOOK_PUBLISHER, @BOOK_NOTE, @BOOK_BOUGHT_DATE, @BOOK_CLASS_ID, @BOOK_STATUS, @BOOK_KEEPER)";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", bookdata.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", bookdata.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", bookdata.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", bookdata.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", bookdata.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", bookdata.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", bookdata.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", bookdata.BOOK_KEEPER));
conn.Close();
}
}
public void UpdateBook(Models.BookData bookdata)
{
string sql = @" Update dbo.BOOK_DATA Set BOOK_NAME=@BOOK_NAME, BOOK_AUTHOR=@BOOK_AUTHOR, BOOK_PUBLISHER=@BOOK_PUBLISHER,
BOOK_NOTE=@BOOK_NOTE, BOOK_BOUGHT_DATE=@BOOK_BOUGHT_DATE, BOOK_CLASS_ID=@BOOK_CLASS_ID, BOOK_STATUS=@BOOK_STATUS
, BOOK_KEEPER=@BOOK_KEEPER Where BOOK_ID=@BOOK_ID";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", bookdata.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", bookdata.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", bookdata.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", bookdata.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", bookdata.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", bookdata.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", bookdata.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", bookdata.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@BOOK_ID", bookdata.BOOK_ID));
conn.Close();
}
}
///<summary>
/// 新增圖書
/// </summary>
/// <param name="book"></param>
/// <returns>圖書編號</returns>
public int InsertBook(Models.BookData book)
{
string sql = @"INSERT INTO BOOK_DATA(BOOK_NAME,BOOK_CLASS_ID,BOOK_AUTHOR,BOOK_BOUGHT_DATE,BOOK_PUBLISHER,BOOK_NOTE,BOOK_STATUS)
VALUES(@BookName,@BookClassID,@BookAuthor,@BookBoughtDate,@BookPublisher,@BookNote,@CanBeLend)
SELECT SCOPE_IDENTITY()";
int BookID;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookName", book.BookName));
cmd.Parameters.Add(new SqlParameter("@BookClassID", book.BookClassID));
cmd.Parameters.Add(new SqlParameter("@BookAuthor", book.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BookBoughtDate", book.BookBoughtDate));
cmd.Parameters.Add(new SqlParameter("@BookPublisher", book.BookPublisher));
cmd.Parameters.Add(new SqlParameter("@BookNote", book.BookNote));
cmd.Parameters.Add(new SqlParameter("@CanBeLend", "A"));
SqlTransaction Tran = conn.BeginTransaction();
cmd.Transaction = Tran;
try
{
BookID = Convert.ToInt32(cmd.ExecuteScalar());
Tran.Commit();
}
catch (Exception)
{
Tran.Rollback();
throw;
}
finally
{
conn.Close();
}
}
return(BookID);
}
/// 根據輸入的BOOK_ID去找書，編輯該書本
public void EditBook(Models.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 InsertBook(Models.BookData book)
{
var result = this.bookService.InsertBookData(book);
return(Json(result));
}
public ActionResult UpdateBook(string BookId)
{
Models.BookMangementService bookMangementService = new Models.BookMangementService();
Models.BookData bookData = bookMangementService.GetBookData(BookId).FirstOrDefault();
return(View(bookData));
}
public JsonResult UpdateData(Models.BookData book)
{
bookService.UpdateBookData(book);
return(Json(""));
}
public JsonResult UpdateBook(Models.BookData book)
{
var result = this.bookService.UpdateBookData(book);
return(Json(result));
}