//Search Member Name By Code public static Member getMemberByCode(string memberCode) { Member memberObj = new Member(); BookPOSEntities3 db = new BookPOSEntities3(); try { var memQuery = from mem in db.Members where mem.MemberCode == memberCode select mem; if (memQuery.SingleOrDefault() != null) //must be use first or default (can error when return multiple value) { memberObj = memQuery.Single(); } else { memberObj = null; } db.Dispose(); } catch (Exception ex) { throw ex; } return(memberObj); }
//Search For All Book by Filter public static List <SearchBook_Result> getSearchBook(string BookName, string Author, int CategoryID) { List <SearchBook_Result> resultList = new List <SearchBook_Result>(); BookPOSEntities3 db = new BookPOSEntities3(); try { var bookName = new SqlParameter("@BookName", BookName); var author = new SqlParameter("@Author", Author); var categoryId = new SqlParameter("@Category", CategoryID); resultList = db.Database .SqlQuery <SearchBook_Result>("SearchBook @BookName,@Author,@Category", bookName, author, categoryId) .ToList(); }catch (Exception ex) { throw ex; } finally { db.Dispose(); } return(resultList); }
//Update Restore Book from Rent public static int RestoreRentBook(int Id) { int l_return = 0; // For Return Value for Save or Fail BookPOSEntities3 db = new BookPOSEntities3(); using (var transaction = db.Database.BeginTransaction()) { try { var result = db.RentBookDetails.SingleOrDefault(b => b.RentDetailID == Id); if (result != null) { result.Status = 0; l_return = db.SaveChanges(); } transaction.Commit(); transaction.Dispose(); db.Dispose(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } return(l_return); }
//Search For Rent Book History public static List <SearchRentBookHistory_Result> getSearchRentBookHistory(string BookName, long memberID, int CategoryID, string fDate, string tDate) { List <SearchRentBookHistory_Result> resultList = new List <SearchRentBookHistory_Result>(); BookPOSEntities3 db = new BookPOSEntities3(); try { var memID = new SqlParameter("@MemberID", memberID); var bookName = new SqlParameter("@BookName", BookName); var categoryId = new SqlParameter("@Category", CategoryID); var startDate = new SqlParameter("@StartDate", fDate); var endDate = new SqlParameter("@EndDate", tDate); resultList = db.Database .SqlQuery <SearchRentBookHistory_Result>("SearchRentBookHistory @MemberID,@BookName,@Category,@StartDate,@EndDate", memID, bookName, categoryId, startDate, endDate).ToList(); db.Dispose(); } catch (Exception ex) { throw ex; } return(resultList); }
//get Member Rows Count public static int getMemberCount() { BookPOSEntities3 db = new BookPOSEntities3(); int l_count = db.Members.Count(); db.Dispose(); return(l_count); }
//Master and Detail INSERT public static int SaveDAO(BookRent bookObj, DataTable dt) { BookPOSEntities3 db = new BookPOSEntities3(); using (var transaction = db.Database.BeginTransaction()) { try { var entity = db.Set <BookRent>().Create(); //mapping the values of your view models to data models entity.MemberId = bookObj.MemberId; entity.StartDate = bookObj.StartDate; entity.IssueDate = bookObj.IssueDate; entity.NumberOfDay = bookObj.NumberOfDay; //INSERT MASTER TABLE db.BookRents.Add(entity); int l_return = db.SaveChanges(); long RentID = entity.RentID; //GET MASTER ID if (l_return > 0) { //INSERT DETAIL TABLE for (int i = 0; i < dt.Rows.Count; i++) { var RentDetail = db.Set <RentBookDetail>().Create(); //can not assign null value RentDetail.RentID = RentID; RentDetail.BookID = Convert.ToInt64(dt.Rows[i]["BookID"].ToString()); RentDetail.Status = 1; db.RentBookDetails.Add(RentDetail); l_return = db.SaveChanges(); RentDetail = null; //can not use null vlue } } transaction.Commit(); transaction.Dispose(); db.Dispose(); return(l_return); } catch (Exception ex) { transaction.Rollback(); throw ex; } } }
//get Category public static List <Category> getCategory() { List <Category> categoryList = null; try { BookPOSEntities3 db = new BookPOSEntities3(); categoryList = db.Categories.ToList(); db.Dispose(); } catch (Exception ex) { throw ex; } return(categoryList); }
public static int getCategoryID(string catName) { int category_Id = 0; try { BookPOSEntities3 db = new BookPOSEntities3(); category_Id = Convert.ToInt32((from cat in db.Categories where cat.CategoryName.Equals(catName) select cat.CategoryId).SingleOrDefault()); } catch (Exception ex) { throw ex; } return(category_Id); }
//Insert New Member public static int SaveNewMember(DataTable dt) { BookPOSEntities3 db = new BookPOSEntities3(); int l_return = 0; using (var transaction = db.Database.BeginTransaction()) { try { //INSERT DETAIL TABLE for (int i = 0; i < dt.Rows.Count; i++) { var Obj = db.Set <Member>().Create(); Obj.MemberCode = dt.Rows[i]["MemberCode"].ToString(); Obj.MemberName = dt.Rows[i]["MemberName"].ToString(); Obj.email = dt.Rows[i]["Email"].ToString(); Obj.City = dt.Rows[i]["City"].ToString(); Obj.Phone = dt.Rows[i]["Phone"].ToString(); Obj.Active = 1; Obj.Address = dt.Rows[i]["Address"].ToString(); db.Members.Add(Obj); l_return = db.SaveChanges(); } transaction.Commit(); transaction.Dispose(); db.Dispose(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } return(l_return); }
//Insert New Book public static int SaveNewBook(DataTable dt) { BookPOSEntities3 db = new BookPOSEntities3(); int l_return = 0; using (var transaction = db.Database.BeginTransaction()) { try { //INSERT DETAIL TABLE for (int i = 0; i < dt.Rows.Count; i++) { var bookObj = db.Set <Book>().Create(); bookObj.BookCode = dt.Rows[i]["BookCode"].ToString(); bookObj.BookName = dt.Rows[i]["BookName"].ToString(); bookObj.CategoryId = Convert.ToInt64(dt.Rows[i]["Category"]); bookObj.Author = dt.Rows[i]["Author"].ToString(); bookObj.ISBN = dt.Rows[i]["ISBN"].ToString(); db.Books.Add(bookObj); l_return = db.SaveChanges(); } transaction.Commit(); transaction.Dispose(); db.Dispose(); } catch (Exception ex) { transaction.Rollback(); throw ex; } } return(l_return); }