//insert
public void InsertBook(Models.Books arg)
{
DataTable dt = new DataTable();
string sql = @" INSERT INTO BOOK_DATA
(
BOOK_NAME,
BOOK_AUTHOR,
BOOK_PUBLISHER,
BOOK_NOTE,
BOOK_BOUGHT_DATE,
BOOK_CLASS_ID,
BOOK_STATUS
)
VALUES
(
@BookName,@BookAuthor, @BookPublisher, @BookNote, @BoughtDate, @BookClassId,'A'
)" ;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@BookAuthor", arg.BookAuthor == null ? string.Empty : arg.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BookPublisher", arg.BookPublisher == null ? string.Empty : arg.BookPublisher));
cmd.Parameters.Add(new SqlParameter("@BookNote", arg.BookNote == null ? string.Empty : arg.BookNote));
cmd.Parameters.Add(new SqlParameter("@BoughtDate", arg.BoughtDate == null ? (object)DBNull.Value : arg.BoughtDate));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassId == null ? string.Empty : arg.BookClassId));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
}
/// <summary>
/// 新增書籍
/// </summary>
/// <param name="Book"></param>
/// <returns>員工編號</returns>
public int InsertBook(Models.Books Book)
{
string sql = @"INSERT INTO BOOK_DATA
(
BOOK_NAME, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_NOTE, BOOK_BOUGHT_DATE, BOOK_CLASS_ID, BOOK_STATUS
)
VALUES
(
@BookName, @BookAuthor, @BookPublisher, @BookNote, @BookBoughtDate, @BookClassId, 'A'
)
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("@BookAuthor", Book.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BookPublisher", Book.BookPublisher));
cmd.Parameters.Add(new SqlParameter("@BookNote", Book.BookNote));
cmd.Parameters.Add(new SqlParameter("@BookBoughtDate", Book.BookBoughtDate));
cmd.Parameters.Add(new SqlParameter("@BookClassId", Book.BookClassId == null ? (Object)DBNull.Value : Book.BookClassId));
BookId = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(BookId);
}
/// UPDATE
/// <summary>
/// Upload 資料
/// </summary>
public void UpdateBookData(Models.Books 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 = @BookBoughtDate,
BOOK_CLASS_ID = @BookClassId
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("@BookBoughtDate", arg.BookBoughtDate == null ? "" : arg.BookBoughtDate.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassId == null ? "" : arg.BookClassId.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookId", Convert.ToInt32(arg.BookId)));
sqlAdapter.Fill(dt);
conn.Close();
}
}
public IActionResult Index()
{
IList <Models.Books> booksList = new List <Models.Books>();
string path = Request.PathBase + "App_Data/books.xml";
XmlDocument doc = new XmlDocument();
if (System.IO.File.Exists(path))
{
doc.Load(path);
XmlNodeList books = doc.GetElementsByTagName("book");
foreach (XmlElement b in books)
{
Models.Books book = new Models.Books();
book.ID = b.GetElementsByTagName("id")[0].InnerText;
book.Title = b.GetElementsByTagName("title")[0].InnerText;
book.FirstName = b.GetElementsByTagName("firstname")[0].InnerText;
book.MiddleName = b.GetElementsByTagName("middlename").Count > 0 ? b.GetElementsByTagName("middlename")[0].InnerText : "";
book.LastName = b.GetElementsByTagName("lastname")[0].InnerText;
booksList.Add(book);
}
}
return(View(booksList));
}
/// <summary>
/// 新增書籍
/// </summary>
/// <param name="Book"></param>
/// <returns>書本編號</returns>
public int InsertBook(Models.Books Book)
{
string sql = @" INSERT INTO dbo.BOOK_DATA
(
BOOK_ID, BOOK_NAME, BOOK_CLASS_ID, BOOK_AUTHOR,
BOOK_BOUGHT_DATE, BOOK_AUTHOR, BOOK_PUBLISHER
)
VALUES
(
@BOOK_ID, @BOOK_NAME, @BOOK_CLASS_ID, @BOOK_AUTHOR,
@BOOK_BOUGHT_DATE, @BOOK_AUTHOR, @BOOK_PUBLISHER
)
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.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_ID", Book.BookID));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", Book.Author));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", Book.Publisher));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", Book.BookBoughtDate));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", Book.BookClassID));
BookId = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(BookId);
}
public ActionResult UpdateBook(int bookID, string bookName, string bookAuthor, string bookPublisher, string bookNote, string bookBoughtDate, string BookClassName, string userName, string codeName)
{
Models.Books bookData = new Models.Books();
bookData.BookID = bookID;
bookData.BookName = bookName;
bookData.BookAuthor = bookAuthor;
bookData.BookPublisher = bookPublisher;
bookData.BookNote = bookNote;
bookData.BookBoughtDate = bookBoughtDate;
bookData.BookClassID = BookClassName;
bookData.UserID = userName;
bookData.CodeID = codeName;
if (ModelState.IsValid)
{
try
{
DateTime dateTime = DateTime.Parse(bookData.BookBoughtDate);
bookService.UpdateBookData(bookData);
return(RedirectToAction("BookData", new { BookID = bookData.BookID }));
}
catch (Exception ex)
{
Response.Write("<script language=javascript>alert('日期格式錯誤')</script>");
}
}
return(View(bookData));
}
public ActionResult BooksUpdate(Models.Books book,int id)
{
////HtmlEncode
//id = Server.HtmlEncode(id);
ModelHtmlEncode(book);
book.BookId =id;
//驗證以借閱時借閱者不得為空
if(book.BookStatusCode!="A"&& book.BookStatusCode !="U"&& book.KeeperId == null)
{
ViewBag.UpdateMessage = "已借閱 不得無借閱者";
ViewBag.keeperAlarm = "";
SetDropDownListItmes();
return View(book);
}
if (ModelState.IsValid)
{
int UpdateResult = booksService.UpdateBooks(book);
if (UpdateResult != 0)
{
ViewBag.UpdateMessage = "修改完成";
}
}
SetDropDownListItmes();
return View(book);
}
public IActionResult Create(Models.Books b)
{
string path = Request.PathBase + "App_Data/books.xml";
XmlDocument doc = new XmlDocument();
if (System.IO.File.Exists(path))
{
doc.Load(path);
// get the count of books
int booksCount = doc.GetElementsByTagName("book").Count;
// if the cuurent count is equl or more than 5 remove first book
if (booksCount >= 5)
{
doc.DocumentElement.RemoveChild(doc.DocumentElement.FirstChild);
}
XmlElement book = _CreateBookElement(doc, b);
doc.DocumentElement.AppendChild(book);
}
else
{
XmlNode dec = doc.CreateXmlDeclaration("1.0", "utf-8", "");
doc.AppendChild(dec);
XmlNode root = doc.CreateElement("books");
XmlElement book = _CreateBookElement(doc, b);
root.AppendChild(book);
doc.AppendChild(root);
}
doc.Save(path);
return(RedirectToAction("Index"));
}
public ActionResult UpdateBook(int BookID)
{
ViewBag.BookClassNameData = this.codeService.GetBookClassName();
ViewBag.UserNameData = this.codeService.GetUserName();
ViewBag.CodeNameData = this.codeService.GetCodeName();
Models.Books books = bookService.GetBookData(BookID).FirstOrDefault();
return(View(books));
}
public ActionResult InsertBook(Models.Books book)
{
ViewBag.BookData = this.codeService.GetClassCodeTable();
Models.SearchService searchService = new Models.SearchService();
ViewBag.BookClassCodeData = this.codeService.GetClassCodeTable();
searchService.InsertBook(book);
TempData["message"] = "存檔成功";
return(View(book));
}
/// <summary>
/// HtmlEncode防止XSS
/// </summary>
public void ModelHtmlEncode(Models.Books book)
{
book.BookAuthor = Server.HtmlEncode(book.BookAuthor);
book.BookBougthDate = Server.HtmlEncode(book.BookBougthDate);
book.BookClassId = Server.HtmlEncode(book.BookClassId);
book.BookName = Server.HtmlEncode(book.BookName);
book.BookNote = Server.HtmlEncode(book.BookNote);
book.BookPublisher = Server.HtmlEncode(book.BookPublisher);
book.BookStatusCode = Server.HtmlEncode(book.BookStatusCode);
book.KeeperId = Server.HtmlEncode(book.KeeperId);
}
public ActionResult InsertBook(Models.Books Book)
{
ViewBag.BookCategory = this.Service.GetTable("BOOK_CLASS", "BOOK_CLASS_NAME", "BOOK_CLASS_ID");
if (ModelState.IsValid)
{
Models.BookService BookService = new Models.BookService();
BookService.InsertBook(Book);
TempData["message"] = "存檔成功";
}
return(View(Book));
}
/// <summary>
/// 新增書籍
/// </summary>
public void InsertBook(Models.Books book)
{
string sql = @"INSERT INTO BOOK_DATA(
BOOK_NAME,
BOOK_AUTHOR,
BOOK_PUBLISHER,
BOOK_NOTE,
BOOK_BOUGHT_DATE,
BOOK_CLASS_ID,
BOOK_STATUS
)
VALUES
(
@BOOK_NAME,
@BOOK_AUTHOR,
@BOOK_PUBLISHER,
@BOOK_NOTE,
@BOOK_BOUGHT_DATE,
@BOOK_CLASS_ID,
@BOOK_STATUS
)" ;
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"));
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
try
{
cmd.ExecuteNonQuery();//可以知道影響幾筆(int)
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 以BookId搜尋此書
/// </summary>
public Models.Books GetBookDetail(int id)
{
//HtmlEncode
//id = Server.HtmlEncode(id);
//Models.BooksSearchArg arg = new Models.BooksSearchArg { BookId = Convert.ToInt32(id) };
Models.BooksSearchArg arg = new Models.BooksSearchArg { BookId = id };
Models.Books books = this.booksService.GetBooks(arg).FirstOrDefault();
ModelHtmlDecode(books);
return books;
}
///<summary>
/// 修改圖書(儲存)
/// <param name="books"></param>
/// </summary>
/// <returns>圖書編號</returns>
public void UpdateBookData(Models.Books books)
{
string sql = @"UPDATE BOOK_DATA
SET BOOK_NAME=@BookName,
BOOK_BOUGHT_DATE=@BookBoughtDate,
BOOK_CLASS_ID=@BookClassID,
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("@BookClassID", books.BookClassID));
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();
}
}
}
public JsonResult InsertIndex(Models.Books data)
{
try
{
int i, j;
i = 0;
j = 1 / i;
return(Json(bookService.InsertBook(data)));
}
catch (Exception)
{
throw;
}
}
public ActionResult InsertEmployee(Models.Books book)
{
ViewBag.JobTitleCodeData = this.codeService.GetCodeTable("TITLE");
ViewBag.CountryCodeData = this.codeService.GetCodeTable("COUNTRY");
ViewBag.CityCodeData = this.codeService.GetCodeTable("CITY");
ViewBag.GenderCodeData = this.codeService.GetCodeTable("GENDER");
ViewBag.EmpCodeData = this.codeService.GetBook("0");
if (ModelState.IsValid)
{
Models.BookService bookService = new Models.BookService();
bookService.InsertBook(book);
TempData["message"] = "存檔成功";
}
return(View(book));
}
private Models.Books MapOriginData(DataTable dt)
{
Models.Books result = new Models.Books();
result.BookId = Convert.ToInt32(dt.Rows[0]["BOOK_ID"]);
result.BookName = dt.Rows[0]["BOOK_NAME"].ToString();
result.BookAuthor = dt.Rows[0]["BOOK_AUTHOR"].ToString();
result.BookPublisher = dt.Rows[0]["BOOK_PUBLISHER"].ToString();
result.BookNote = dt.Rows[0]["BOOK_NOTE"].ToString();
result.BookBoughtDate = dt.Rows[0]["BOOK_BOUGHT_DATE"].ToString();
result.BookClassName = dt.Rows[0]["BOOK_CLASS_NAME"].ToString();
result.BookStatus = dt.Rows[0]["BOOK_STATUS"].ToString();
result.BookCodeName = dt.Rows[0]["CODE_NAME"].ToString();
result.UserName = dt.Rows[0]["USER_NAME"].ToString();
return(result);
}
/// <summary>
/// 新增員工
/// </summary>
/// <param name="book"></param>
/// <returns>員工編號</returns>
public int InsertBook(Models.Books book)
{
string sql = @"
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO BOOK_DATA
(
BOOK_NAME, BOOK_AUTHOR, BOOK_PUBLISHER,
BOOK_NOTE, BOOK_BOUGHT_DATE, BOOK_CLASS_ID,
BOOK_STATUS
)
VALUES
(
@BOOK_NAME, @BOOK_AUTHOR, @BOOK_PUBLISHER,
@BOOK_NOTE, @BOOK_BOUGHT_DATE, @BOOK_CLASS_ID,
@BOOK_STATUS
)
Select SCOPE_IDENTITY()
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROlLBACK TRANSACTION
END CATCH";
int bookId;
//區域變數：bookId
//not sure the usage of BookId：直接接修改，或檢查新增成功(bookId!=0)
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"));
bookId = Convert.ToInt32(cmd.ExecuteScalar());
//字串轉數字都是32(長度差別
conn.Close();
}
return(bookId);
}
public ActionResult InsertBook(Models.Books book)
{
ViewBag.BookClassNameData = this.codeService.GetBookClassName();
if (ModelState.IsValid)
{
try
{
DateTime dateTime = DateTime.Parse(book.BookBoughtDate);
int BookID = bookService.InsertBook(book);
return(RedirectToAction("BookData", new { BookID = BookID }));
}
catch
{
Response.Write("<script language=javascript>alert('日期格式錯誤')</script>");
}
}
return(View(book));
}
private Models.Books MapEditData(DataTable dt)
{
Models.Books result = new Models.Books();
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.BoughtDate = dt.Rows[0]["BoughtDate"].ToString();
result.BookClassId = dt.Rows[0]["BookClassId"].ToString();
result.BookStatus = dt.Rows[0]["BookStatus"].ToString();
result.BookStatusId = dt.Rows[0]["BookStatusId"].ToString();
result.BookKeeper = dt.Rows[0]["BookKeeper"].ToString();
result.BookKeeperId = dt.Rows[0]["BookKeeperId"].ToString();
return(result);
}
public ActionResult BooksInsert(Models.Books book)
{
//HtmlEncode
ModelHtmlEncode(book);
ViewBag.BookClass = this.booksService.GetBookClass();
if (ModelState.IsValid)
{
ViewBag.InsertMessage = "新增失敗";
ViewBag.AlertClass = "alert alert-danger";
int InsertResult = this.booksService.InsertBooks(book);
if (InsertResult != 0)
{
ViewBag.InsertMessage = "新增完成";
ViewBag.AlertClass = "alert alert-success";
}
}
return View();
}
private XmlElement _CreateBookElement(XmlDocument doc, Models.Books newBook)
{
XmlElement book = doc.CreateElement("book");
XmlNode title = doc.CreateElement("title");
title.InnerText = newBook.Title;
book.AppendChild(title);
int newIndex = 0001;
if (doc.LastChild != null)
{
int lastIndex = Int32.Parse(doc.SelectSingleNode("//book[last()]//id").InnerText);
newIndex = lastIndex + 1;
}
XmlNode id = doc.CreateElement("id");
// pad with leading zeros
id.InnerText = newIndex.ToString("D4");
book.AppendChild(id);
XmlNode author = doc.CreateElement("author");
XmlNode firstname = doc.CreateElement("firstname");
firstname.InnerText = newBook.FirstName;
author.AppendChild(firstname);
XmlNode middlename = doc.CreateElement("middlename");
middlename.InnerText = newBook.MiddleName;
author.AppendChild(middlename);
XmlNode lastname = doc.CreateElement("lastname");
lastname.InnerText = newBook.LastName;
author.AppendChild(lastname);
book.AppendChild(author);
return(book);
}
public ActionResult UpdateBook(Models.Books books)
{
ViewBag.BookClassNameData = this.codeService.GetBookClassName();
ViewBag.UserNameData = this.codeService.GetUserName();
ViewBag.CodeNameData = this.codeService.GetCodeName();
if (ModelState.IsValid)
{
try
{
DateTime dateTime = DateTime.Parse(books.BookBoughtDate);
bookService.UpdateBookData(books);
return(RedirectToAction("BookData", new { BookID = books.BookID }));
}
catch (Exception ex)
{
Response.Write("<script language=javascript>alert('日期格式錯誤')</script>");
}
}
return(View(books));
}
public ActionResult Search(string txtISBN)
{
Books bI = new Models.Books();
getFromISBN(bI, txtISBN);
object bC = null;
try
{
bC = (books.books.Aggregate().Project(Builders <Books> .Projection.Include("Code").Exclude("_id")).Sort(Builders <BsonDocument> .Sort.Descending("Code")).Limit(1).ToList()[0].ElementAt(0).Value);
bC = int.Parse(bC.ToString()) + 1;
}
catch (Exception)
{
bC = null;
}
if (bC != null)
{
bI.Code = bC.ToString();
}
return(View("Add", bI));
}
///<summary>
/// 新增圖書
/// </summary>
/// <param name="book"></param>
/// <returns>圖書編號</returns>
public int InsertBook(Models.Books 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);
}
public bool UpdateBookData(Models.Books 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.BoughtDate.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassId.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookStatusId", arg.BookStatusId.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookKeeperId", arg.BookKeeperId == null ? "" : arg.BookKeeperId.ToString()));
cmd.Parameters.Add(new SqlParameter("@BookId", arg.BookId));
sqlAdapter.Fill(dt);
conn.Close();
}
return(true);
}
public ActionResult BookData(int BookID)
{
Models.Books books = bookService.GetBookDetail(BookID).FirstOrDefault();
return(View(books));
}
public async Task UpdateAndSave(Models.Books equipment)
{
_booksRepo.Update(equipment);
await _booksRepo.Save();
}
public async Task AddAndSave(Models.Books book)
{
_booksRepo.Add(book);
await _booksRepo.Save();
}
