//查詢書籍
public List <LBBooks> SearchBook(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
string sql = @"Select BOOK_CLASS_NAME,BOOK_NAME,BOOK_BOUGHT_DATE,CODE_NAME,USER_CNAME
FROM dbo.BOOK_DATA as e
LEFT JOIN dbo.BOOK_CLASS as bc
ON (e.BOOK_CLASS_ID = bc.BOOK_CLASS_ID)
LEFT JOIN dbo.BOOK_CODE as code
ON (e.BOOK_STATUS = code.CODE_ID)
LEFT JOIN dbo.MEMBER_M as mm
ON (e.BOOK_KEEPER = mm.USER_ID)
Where (e.BOOK_NAME LIKE ('%'+@BOOK_NAME+'%') OR @BOOK_NAME='')
AND (bc.BOOK_CLASS_NAME LIKE ('%'+@BOOK_CLASS_NAME+'%') OR @BOOK_CLASS_NAME='')
AND (mm.USER_CNAME LIKE ('%'+@BOOK_KEEPER+'%') OR @BOOK_KEEPER='')
AND (code.CODE_ID LIKE ('%'+@BOOK_STATUS+'%') OR @BOOK_STATUS='')";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", viewresult.BookName == null ? string.Empty : viewresult.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_NAME", viewresult.BookClassName == null ? string.Empty : viewresult.BookClassName));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", viewresult.BookKeeper == null ? string.Empty : viewresult.BookKeeper));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", viewresult.BookStatus == null ? string.Empty : viewresult.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
//新增書籍
public int InsertBook(LBSearchArg viewresult)
{
string sql = @" INSERT INTO dbo.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
)" ;
int Id;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", viewresult.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", viewresult.BookAuthor));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", viewresult.Pubilsher));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", viewresult.BookIntroduce));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", viewresult.BoughtDate));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", viewresult.BookClassName));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", viewresult.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
Id = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(Id);
}
//載入畫面時GET書籍資料放到kendoGrid
public List <LBBooks> GetLibraryData(LBSearchArg viewresult)
{
DataTable dt = new DataTable();
string sql = @"Select BOOK_CLASS_NAME,BOOK_NAME,BOOK_BOUGHT_DATE,CODE_NAME,USER_CNAME
FROM dbo.BOOK_DATA as e
LEFT JOIN dbo.BOOK_CLASS as bc
ON (e.BOOK_CLASS_ID = bc.BOOK_CLASS_ID)
LEFT JOIN dbo.BOOK_CODE as code
ON (e.BOOK_STATUS = code.CODE_ID)
LEFT JOIN dbo.MEMBER_M as mm
ON (e.BOOK_KEEPER = mm.USER_ID)
";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
