public async Task TestUpdatetBook_InputInsertId_returnnothing()
{
Console.WriteLine("UpdateBookId : " + InsertId);
Models.BookUpdate UpdateData = new Models.BookUpdate
{
BookID = Convert.ToString(InsertId),
BookName = "TestUpdateBook",
BookAuthor = "Marz",
BookPublisher = "南一",
Note = "112233",
BoughtDate = "2019/03/15",
BookClassId = "BK",
BookStatusId = "A",
BookKeeperId = null
};
await _bookService.UpdateBook(UpdateData);
Models.BookUpdate GetData = await _bookService.GetUpdateBook(InsertId);
Assert.AreEqual(UpdateData.BookName, GetData.BookName);
Assert.AreEqual(UpdateData.BookAuthor, GetData.BookAuthor);
Assert.AreEqual(UpdateData.BookPublisher, GetData.BookPublisher);
Assert.AreEqual(UpdateData.Note, GetData.Note);
Assert.AreEqual(UpdateData.BoughtDate, GetData.BoughtDate);
Assert.AreEqual(UpdateData.BookClassId, GetData.BookClassId);
Assert.AreEqual(UpdateData.BookStatusId, GetData.BookStatusId);
Assert.AreEqual(UpdateData.BookKeeperId, GetData.BookKeeperId);
}
public async Task TestGetUpdateBook_InputInsertId_returnInsertData()
{
Models.BookUpdate GetData = await _bookService.GetUpdateBook(InsertId);
Console.WriteLine("UpdateId : " + InsertId);
Assert.AreEqual(InsertData.BookName, GetData.BookName);
Assert.AreEqual(InsertData.BookAuthor, GetData.BookAuthor);
Assert.AreEqual(InsertData.BookPublisher, GetData.BookPublisher);
Assert.AreEqual(InsertData.Note, GetData.Note);
Assert.AreEqual(InsertData.BoughtDate.ToString("yyyy/MM/dd"), GetData.BoughtDate);
Assert.AreEqual(InsertData.BookClass, GetData.BookClassId);
}
public Models.BookUpdate ToModel(DataTable dt)
{
Models.BookUpdate result = new Models.BookUpdate();
result.BookName = dt.Rows[0]["書名"].ToString();
result.BookAuthor = dt.Rows[0]["作者"].ToString();
result.BookPublisher = dt.Rows[0]["出版商"].ToString();
result.Note = dt.Rows[0]["內容簡介"].ToString();
result.BoughtDate = dt.Rows[0]["購書日期"].ToString();
result.BookClassId = dt.Rows[0]["圖書類別"].ToString();
result.BookStatusId = dt.Rows[0]["借閱狀態"].ToString();
result.BookKeeperId = dt.Rows[0]["借閱人"].ToString();
return(result);
}
public void UpdateBook(int id, Models.BookUpdate updatedata)
{
string sql = @"
BEGIN TRY
BEGIN TRANSACTION
UPDATE 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_ID,
BOOK_KEEPER = @BOOK_KEEPER_ID,
MODIFY_DATE = GETDATE()
WHERE BOOK_ID = @BOOK_ID
COMMIT TRANSACTION
End TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage
ROLLBACK TRANSACTION
END CATCH " ;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", updatedata.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", updatedata.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", updatedata.BookPublisher));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", updatedata.Note));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", updatedata.BoughtDate)); //
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", updatedata.BookClassId)); //
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS_ID", updatedata.BookStatusId)); //
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER_ID", updatedata.BookKeeperId == null ? string.Empty : updatedata.BookKeeperId)); //
cmd.Parameters.Add(new SqlParameter("@BOOK_ID", id));
cmd.ExecuteNonQuery();
conn.Close();
}
}
public ActionResult UpdateBook(Models.BookUpdate updatedata)
{
ViewBag.BookClass = bookservice.GetDropdownList("GetClass");
ViewBag.BookKeeper = bookservice.GetDropdownList("GetUpdateKeeper");
ViewBag.BookStatus = bookservice.GetDropdownList("GetStatus"); //顯示DropDownList
bool relation = ((updatedata.BookStatusId == "B" || updatedata.BookStatusId == "C") && (updatedata.BookKeeperId == "" || updatedata.BookKeeperId == null));
if (ModelState.IsValid && !relation)
{
string LaterBookStatus = updatedata.BookStatusId;
string IniBookStatus = updatedata.IniStatus;
if ((IniBookStatus == "A" || IniBookStatus == "U") && (LaterBookStatus == "B" || LaterBookStatus == "C"))
{
Models.LendRecordInsert lendRecordInsert = new Models.LendRecordInsert();
lendRecordInsert.BookKeeperId = updatedata.BookKeeperId;
lendRecordInsert.BookID = Convert.ToInt32(updatedata.BookID);
bookservice.InsertLendRecord(lendRecordInsert);
}
bookservice.UpdateBook(Convert.ToInt32(updatedata.BookID), updatedata);
return(RedirectToAction("Index"));
}
return(View());
}
/// <summary>
/// 修改書籍資料
/// </summary>
public void UpdateById(Models.BookUpdate arg)
{
string sql = @"UPDATE 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_ID,
BOOK_KEEPER=@KEEPER_ID
WHERE
BOOK_ID=@BOOK_ID;";
string sqlAboutInsertLendRecord = @"INSERT INTO BOOK_LEND_RECORD(
BOOK_ID,
KEEPER_ID,
LEND_DATE,
CRE_DATE,
MOD_DATE
)VALUES(
@BOOK_ID,
@KEEPER_ID,
@LEND_DATE,
@CRE_DATE,
@MOD_DATE) ";
if (arg.KEEPER_ID != null)
{
sql = sql + sqlAboutInsertLendRecord;
}
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", arg.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", arg.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", arg.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", arg.BOOK_NOTE));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", arg.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", arg.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS_ID", arg.BOOK_STATUS_ID));
cmd.Parameters.Add(new SqlParameter("@KEEPER_ID", arg.KEEPER_ID == null ? string.Empty : arg.KEEPER_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_ID", arg.BOOK_ID));
cmd.Parameters.Add(new SqlParameter("@LEND_DATE", DateTime.Now.ToShortDateString()));
cmd.Parameters.Add(new SqlParameter("@CRE_DATE", DateTime.Now.ToShortDateString()));
cmd.Parameters.Add(new SqlParameter("@MOD_DATE", DateTime.Now.ToShortDateString()));
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
cmd.ExecuteNonQuery();//可以知道影響幾筆(int)
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
