C# ResultSet.GetData方法代码示例

        // Used to get a dictionary of timetable details from the database
        public Dictionary<string, TimetableDetails> GetTimetableDetails(DateTime date)
        {
            string query = string.Format("Date = '{0}'", string.Format("{0:MM-dd-yy}", date));
            ResultSet timetableResults = new ResultSet(GetDataReader("[BusTripTable]", query)); // Search the database for all details relating to the specified date
            CloseConnection();

            Dictionary<string, TimetableDetails> details = new Dictionary<string, TimetableDetails>();

            string[] timeSlots = { "8.30 - 10.15", "2.00 - 3.45 ", "10.45 - 1.45", "4.00 - 7.00 " };

            for(int i = 0; i < timeSlots.Length; i++){ // Go through each possible timeslot and add the details about it to the details Dictionary

                TimetableDetails timetableDetails = new TimetableDetails();

                int row = timetableResults.FindRow<string>(2, timeSlots[i]); // Find the row which stores the data for this time slot

                if (row == -1) // If there is no-one registered for this time slot use the small bus
                {
                    timetableDetails.busUsed = "9 Person";
                    timetableDetails.seatsLeft = 24;
                    details.Add(timeSlots[i], timetableDetails);
                    continue;
                }

                if(timetableResults.GetData<int>(4, row) < 9) // If there are less than 9 people registered for this bus use the small bus
                {
                    timetableDetails.busUsed = "9 Person";
                }
                else if (timetableResults.GetData<int>(4, row) < 17) // If there are >= 9 and < 17 people use the bigger bus
                {
                    timetableDetails.busUsed = "17 Person";
                }
                else // If there are >= 17 people use both busses
                {
                    timetableDetails.busUsed = "Both";
                }

                timetableDetails.seatsLeft = 24 - timetableResults.GetData<int>(4, row); // calculate the seats left now that we have found the number of seats

                details.Add(timeSlots[i], timetableDetails); // Add these details to the dictionary

            } // End of the loop through all of the time slots

            return details;
        }

        // Schedules a new Cycling Certification for a child
        public void AddNewCyclingCertification(string childName, int customerID, string level, float price)
        {
            // Store the specified child's details
            string query = string.Format("CustomerID = {0} AND ChildName = '{1}'", customerID, childName);
            ResultSet childrenResults = new ResultSet(GetDataReader("[ChildTable]", query, "ChildID"));
            int childID = childrenResults.GetData<int>(0, 0);

            OpenConnection();
            int certBookingID = GetCount("[CertificationBookingsTable]"); // Get the next available ID for a Certification Booking
            CloseConnection();

            // Add the record to the CertificationBookingsTable
            string command = string.Format("INSERT INTO [CertificationBookingsTable] VALUES ({0}, {1}, '{2}', {3})", certBookingID, childID, level, price);
            UpdateByQuery(command);
        }

        // When a customer name suggestion is chosen
        private void CustomerSuggestionChosen(string suggestion)
        {
            string command = "CustomerName = '" + suggestion + "'";
            ResultSet resultSet = new ResultSet(busDal.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID")); // Get the data related to the chosen customer name
            busDal.CloseConnection();

            // Update the CustomerIDComboBox to show only the CustomerID's relating to the chosen customer name
            CustomerIDComboBox.Items.Clear();

            for (int i = 0; i < resultSet.GetSize(); i++)
            {
                CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
            }

            if (CustomerIDComboBox.Items.Count > 0)
            {
                CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
            }
        }

        // When a suggested Customer Name is chosen
        private void SuggestionChosen(string suggestion)
        {
            // Obtain the data that relates to the chosen customer name
            string command = "CustomerName = '" + suggestion + "'";
            ResultSet resultSet = new ResultSet(cyclingDAL.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID"));

            // Make the CustomerIDComboBox show only the customerID's relating to the chosen Customer Name
            CustomerIDComboBox.Items.Clear();

            for (int i = 0; i < resultSet.GetSize(); i++)
            {
                CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
            }

            if (CustomerIDComboBox.Items.Count > 0)
            {
                CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
            }

            CustomerIDComboBox_SelectedIndexChanged(CustomerNameSuggestingTextBox, new EventArgs());
        }

        // When a customer name suggestion is chosen
        private void CustomerSuggestionChosen(string suggestion)
        {
            string command = "CustomerName = '" + suggestion + "'";
            ResultSet resultSet = new ResultSet(cyclingDAL.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID"));

            // Update the CustomerIDComboBox with the ID's of the customers relating to the suggested customer name
            CustomerIDComboBox.Items.Clear();

            for (int i = 0; i < resultSet.GetSize(); i++)
            {
                CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
            }

            if (CustomerIDComboBox.Items.Count > 0)
            {
                CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
            }
        }

        // When a customer name suggestion was chosen
        private void NameSuggestionChosen(string suggestion)
        {
            string command = "CustomerName = '"+ suggestion + "'";
            ResultSet resultSet = new ResultSet(dal.GetDataReader("[dbo].[CustomerTable]", command, "CustomerID")); // Obtaing details about CustomerIDs
            //dal.CloseConnection();

            // Set the CustomerIDComboBox to display CustomerIDs that apply to this customer name
            CustomerIDComboBox.Items.Clear();

            for(int i = 0; i < resultSet.GetSize(); i++){
                CustomerIDComboBox.Items.Add(resultSet.GetData<int>(0, i) + "");
            }

            if (CustomerIDComboBox.Items.Count > 0)
            {
                CustomerIDComboBox.Text = CustomerIDComboBox.Items[0] + "";
            }
        }

        private void ChangeBooking(string customerName)
        {
            bookingResults = new ResultSet(dal.GetDataReader("BookingTable", "CustomerID = " + CustomerIDComboBox.Text));

            ClearExpensesLabels(); // Remove any labels explaining expenses

            //Obtain prices from all aspect of the database. Calculating price through C# prevents data duplication / redundancy
            float equipmentLoansPrice = dal.ReadPrice("EquipmentLoansTable JOIN EquipmentTable ON EquipmentLoansTable.EquipmentID = EquipmentTable.EquipmentID", "CustomerID = " + CustomerIDComboBox.Text, "(RentalPrice * Duration)");
            float sightseeingBusPrice = dal.ReadPrice("[Customer-BusTripTable]", "CustomerID = " + CustomerIDComboBox.Text, "Price");
            float cyclingTourPrice = dal.ReadPrice("[Customer-CyclingTourTable]", "CustomerID = " + CustomerIDComboBox.Text, "Price");
            float cyclingLessonPrice = dal.ReadPrice("[Child-CyclingLessonTable] JOIN [ChildTable] ON [Child-CyclingLessonTable].ChildID = ChildTable.ChildID", "ChildTable.CustomerID = " + CustomerIDComboBox.Text, "[Child-CyclingLessonTable].Price");
            float cyclingCertificationPrice = dal.ReadPrice("CertificationBookingsTable JOIN ChildTable ON CertificationBookingsTable.ChildID = ChildTable.ChildID", "ChildTable.CustomerID = " + CustomerIDComboBox.Text, "[CertificationBookingsTable].Price");
            dal.OpenConnection(); // Connection is opened and closed manually for a GetCount call because this can save on lag caused by connecting to database.
            float cyclingAwardPrice = dal.GetCount("[CyclingAwardsTable] JOIN ChildTable ON CyclingAwardsTable.ChildID = ChildTable.ChildID", "CustomerID = " + CustomerIDComboBox.Text) * 20.0f;
            dal.CloseConnection();

            // For each expense, if it is relevant display a label explaining the expense
            if (equipmentLoansPrice > 0)
            {
                AddExpensesLabel("Charge for Hiring Equipment: £" + Program.ToCurrency(equipmentLoansPrice));
            }
            if (sightseeingBusPrice > 0)
            {
                AddExpensesLabel("Charge for Sightseeing Trip(s): £" + Program.ToCurrency(sightseeingBusPrice));
            }
            if (cyclingTourPrice > 0)
            {
                AddExpensesLabel("Charge for Cycling Tour(s): £" + Program.ToCurrency(cyclingTourPrice));
            }
            if (cyclingLessonPrice > 0)
            {
                AddExpensesLabel("Charge for Cycling Lesson(s): £" + Program.ToCurrency(cyclingLessonPrice));
            }
            if (cyclingCertificationPrice > 0)
            {
                AddExpensesLabel("Charge for Cycling Certification(s): £" + Program.ToCurrency(cyclingCertificationPrice));
            }
            if (cyclingAwardPrice > 0)
            {
                AddExpensesLabel("Charge for Cycling Award(s): £" + Program.ToCurrency(cyclingAwardPrice));
            }

            //Set the chosen customer name and id straight from the SuggestiveTextBox and ComboBox
            CustomerNameLabel.Text = "Customer Name: " + customerName;
            CustomerIDLabel.Text = "Customer ID: " + CustomerIDComboBox.Text + "";
            try
            {
                BookingIDLabel.Text = "Booking ID: " + bookingResults.GetData<int>(0, 0); // Retrieve and display the bookingID
            }
            catch (Exception e)
            {
                Console.WriteLine("No data found for this customer. Check "); // Should never be called.
            }

            // Sum all of the prices to give a total price
            float totalPrice = equipmentLoansPrice + sightseeingBusPrice + cyclingTourPrice + cyclingLessonPrice + cyclingCertificationPrice + cyclingAwardPrice;

            TotalPriceLabel.Text = "Total Price: £" + Program.ToCurrency(totalPrice);
        }

 // Returns the number of children registered for a specific lesson
 public int GetNumberAttendingLesson(DateTime date, string timeSlot)
 {
     // Run a query to find the number of children attending the lesson at the specified date and time slot
     string query = string.Format("[Date] = '{0}' AND TimeSlot = '{1}'", string.Format("{0:MM-dd-yy}", date), timeSlot);
     ResultSet lessonResults = new ResultSet(GetDataReader("[CyclingLessonTable]", query, "NumberChildren"));
     CloseConnection();
     if (lessonResults.GetSize() == 0)
     {
         return 0;
     }
     return lessonResults.GetData<int>(0, 0);
 }

 // Returns the number of bicycles that have not yet been hired for a specific lesson
 public int GetBicyclesLeft(DateTime date, string timeSlot)
 {
     // Run a query to find the number of bicylces left for the lesson at the specified date and time slot
     string query = string.Format("[Date] = '{0}' AND TimeSlot = '{1}'", string.Format("{0:MM-dd-yy}", date), timeSlot);
     ResultSet tourResults = new ResultSet(GetDataReader("[CyclingTourTable]", query, "NumberBicycles"));
     CloseConnection();
     if (tourResults.GetSize() == 0)
     {
         return 15;
     }
     else
     {
         return (15 - tourResults.GetData<int>(0, 0));
     }
 }

 // Method to determine the number of people that have signed up for a tour on a specific time slot on a specific date
 public int GetNumberSignedUp(DateTime date, string timeSlot)
 {
     OpenConnection();
     // Run a query to find the number of cyclists registered for the specified time slot and specified date
     string query = string.Format("[CyclingTourTable].Date = '{0}' AND [CyclingTourTable].TimeSlot = '{1}'", string.Format("{0:MM-dd-yy}", date), timeSlot);
     ResultSet tourResults = new ResultSet(GetDataReader("[CyclingTourTable]", query, "NumberCyclists"));
     CloseConnection();
     if (tourResults.GetSize() == 0)
     {
         return 0;
     }
     return tourResults.GetData<int>(0, 0);
 }