public List <BookingGridData> GetGridBookings() { List <BookingGridData> allBookings = new List <BookingGridData>(); cmSelectAll.Connection = cn; cmSelectAll.CommandText = "select b.CustomerId, c.Name, b.SlotId, s.TimeSlot,b.FacilityID, f.Name as FacilityName,b.IssueDate from Customers c,Slots s,Facilities f,BookingTran b where b.CustomerId = c.CustomerID and b.SlotId = s.SlotId and b.FacilityId = f.FacilityId order by b.CustomerId"; cn.Open(); rd = cmSelectAll.ExecuteReader(); //rd.FieldCount() while (rd.Read()) { BookingGridData bookingTransGrid = new BookingGridData(); bookingTransGrid.BookCustomerID = Convert.ToInt32(rd["CustomerID"].ToString()); bookingTransGrid.BookName = rd["Name"].ToString(); bookingTransGrid.BookFacilityID = rd["FacilityId"].ToString(); bookingTransGrid.BookFacilityname = rd["FacilityName"].ToString(); bookingTransGrid.BookSlotID = rd["SlotId"].ToString(); bookingTransGrid.BookTimeSlot = rd["TimeSlot"].ToString(); bookingTransGrid.BookIssuedate = Convert.ToDateTime(rd["IssueDate"].ToString()); allBookings.Add(bookingTransGrid); } return(allBookings); }
public List <BookingGridData> GetSearchGridBookings(string facId, int cusID, DateTime idate) { List <BookingGridData> SearchBookingsList = new List <BookingGridData>(); cmSelectGridSearch.Connection = cn; SqlParameter pFacId = new SqlParameter("@FacId", SqlDbType.NVarChar, 5); //SqlParameter pSlotId = new SqlParameter("@SlotId", SqlDbType.NVarChar, 5); SqlParameter pCusId = new SqlParameter("@CustId", SqlDbType.Int); SqlParameter pIssDate = new SqlParameter("@IssDate", SqlDbType.DateTime); //SqlParameter pStatus = new SqlParameter("@Status", SqlDbType.Bit); cmSelectGridSearch.Parameters.AddRange(new SqlParameter[] { pFacId, pCusId, pIssDate }); pFacId.Value = facId; pCusId.Value = cusID; pIssDate.Value = idate; cmSelectGridSearch.CommandText = "select b.CustomerId, c.Name, b.SlotId, s.TimeSlot,b.FacilityID, f.Name as FacilityName,b.IssueDate from Customers c,Slots s,Facilities f,BookingTran b where b.CustomerId = c.CustomerID and b.SlotId = s.SlotId and b.FacilityId = f.FacilityId and b.CustomerId = @CustId and b.facilityid = @Facid and b.IssueDate = @issdate"; //cmSelectSearch.CommandText = "Select facilityid,slotid,customerid,issuedate from BookingTran where Status = 1 and customerid = @CustId and facilityid = @Facid and issuedate = @issdate";//show only booked transactions and not cancelled transactions cn.Open(); rd = cmSelectGridSearch.ExecuteReader(); while (rd.Read()) { BookingGridData bookingTransGrid = new BookingGridData(); bookingTransGrid.BookCustomerID = Convert.ToInt32(rd["CustomerID"].ToString()); bookingTransGrid.BookName = rd["Name"].ToString(); bookingTransGrid.BookFacilityID = rd["FacilityId"].ToString(); bookingTransGrid.BookFacilityname = rd["FacilityName"].ToString(); bookingTransGrid.BookSlotID = rd["SlotId"].ToString(); bookingTransGrid.BookTimeSlot = rd["TimeSlot"].ToString(); bookingTransGrid.BookIssuedate = Convert.ToDateTime(rd["IssueDate"].ToString()); SearchBookingsList.Add(bookingTransGrid); } return(SearchBookingsList); }