//查詢書籍 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)); }