C# DataConnection.GetReader方法代码示例

 // Returns array of all tutors
 public static string[] getTutors(bool includeIDs = true)
 {
     List<string> tutorList = new List<string>();
     DataConnection conn = new DataConnection();
     conn.Open();
     SqlDataReader rd = conn.GetReader("STUDENT.FIRSTNAME, STUDENT.LASTNAME, tutor.tutor_id" , "TUTOR INNER JOIN STUDENT ON TUTOR.CLARION_ID=STUDENT.CLARION_ID", "STATUS","ACTIVE");
     if (rd.HasRows)
     {
         while (rd.Read())
         {
             tutorList.Add(includeIDs ? (rd[2] + " ") : "" + rd[0] + " " + rd[1]);
         }
     }
     conn.Close();
     return tutorList.ToArray();
 }

 private bool SearchForUser(string username, out int userID)
 {
     DataConnection conn = new DataConnection();
     conn.Open();
     SqlDataReader rd = conn.GetReader("CLARION_ID", "STUDENT", "CNET_USERNAME", username);
     if (rd.HasRows)
     {
         rd.Read();
         userID = int.Parse(rd[0].ToString());
         conn.Close();
         return true;
     }
     userID = -1;
     conn.Close();
     return false;
 }

        // Adds an entry to the visits table with the information currently selected and resets the form
        private void signIn()
        {
            for (int i = 0; i < rdoMethods.Length; i++)
                if (rdoMethods[i].Checked)
                    method = rdoMethods[i].Text;

            DataConnection conn = new DataConnection();
            SqlDataReader rd;
            if (tutoring)
            {
                conn.Open();
                tutorid = 0;
                rd = conn.GetReader("*", "tutor", "clarion_id", studentID.ToString());
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        tutorid = int.Parse(rd[0].ToString());
                    }
                }
                conn.Close();
                conn.Open();
                conn.Query("insert into tutor_hour(tutor_id,Date,Time_in)values('" + tutorid + "', '" + DateTime.Today.ToString("d") + "', '" + DateTime.Parse(DateTime.Now.ToString("HH:mm:ss tt")) + "')");
                conn.Close();
                MessageBox.Show("You have been signed in");
                resetForm();
                return;
            }
            string[] selectedClass = comboClassList.SelectedItem.ToString().Split();
            string term;
            try
            {
                conn.Open();
                rd = conn.GetReader("term", "student", "clarion_id", studentID.ToString());
                rd.Read();
                term = rd[0].ToString();
            }
            catch
            {
                conn.Close();
                if (selectedClass[0].ToString() != "Other")
                {
                    MessageBox.Show("Cannot find term", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                term = "N/A";
            }
            conn.Close();
            if (comboTutors.Visible == false && comboClassList.Enabled == true)
            {
                conn.Open();
                if (selectedClass[0].ToString() == "Other")
                    conn.Query("insert into visit(clarion_id,date,time_in,term,subject,catalog,section,method)values('" + studentID + "', '" + System.DateTime.Today.ToString("d") + "','" + DateTime.Parse(DateTime.Now.ToString("HH:mm:ss tt")) + "','" + "N/A" + "','" + "other" + "','" + "other" + "','" + "other" + "', '" + method + "')");
                else
                    conn.Query("insert into visit(clarion_id,date,time_in,term,subject,catalog,section,method)values('" + studentID + "', '" + System.DateTime.Today.ToString("d") + "','" + DateTime.Parse(DateTime.Now.ToString("HH:mm:ss tt")) + "','" + term + "','" + selectedClass[0] + "','" + selectedClass[1] + "','" + selectedClass[2] + "', '" + method + "')");
                conn.Close();
                MessageBox.Show("You have been signed in");
                resetForm();
                return;
            }
            try
            {
                string[] selectedTutor = comboTutors.SelectedItem.ToString().Split();
                string selectedTutorID;
                conn.Open();
                rd = conn.GetReader("TUTOR_ID", "STUDENT INNER JOIN TUTOR ON TUTOR.CLARION_ID = STUDENT.CLARION_ID", "LASTNAME", selectedTutor[1], "FIRSTNAME", selectedTutor[0]);
                if (!(rd.Read()))
                {
                    conn.Close();
                    MessageBox.Show("Tutor not found.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                selectedTutorID = rd[0].ToString();

                conn.Close();
                conn.Open();
                if (comboClassList.SelectedItem.ToString() == "Other")
                    conn.Query("insert into visit(clarion_id,date,time_in,term,subject,catalog,section,method, tutor_id)values('" + studentID + "', '" + DateTime.Today.ToString("d") + "','" + DateTime.Parse(DateTime.Now.ToString("HH:mm:ss tt")) + "','" + "N/A" + "','" + "other" + "','" + "other" + "','" + "other" + "', '" + method + "', " + selectedTutorID + ")");
                else
                    //MessageBox.Show("hit" + selectedClass[0] + " " + selectedClass[1] + " " + selectedClass[2] + " " + selectedClass[3]);
                    conn.Query("insert into VISIT (DATE, TIME_IN, CLARION_ID, TERM, SUBJECT, CATALOG, TUTOR_ID, METHOD, SECTION) values ('" + System.DateTime.Today.ToString("d") + "','" + DateTime.Parse(DateTime.Now.ToString("HH:mm:ss tt")) + "','" + txtStudentID.Text + "', '" + term + "', '" + selectedClass[0] + "', '" + selectedClass[1] + "', '" + selectedTutorID + "' , '" + method + "', '" + selectedClass[2] + "')");
                //cmd.CommandText = "insert into VISIT (DATE, TIME_IN, CLARION_ID, TERM, SUBJECT, CATALOG, TUTOR_ID, METHOD, SECTION) values ('" + System.DateTime.Today.ToString() + "','" + System.DateTime.UtcNow.TimeOfDay.ToString() + "','" + txtStudentID.Text + "', '" + selectedClass[0].ToString() + "', '" + selectedClass[1].ToString() + "', '" + selectedClass[2].ToString() + "', '" + selectedTutor[0] + "' , '" + "method" + "', '" + selectedClass[3].ToString() + "')";
            }
            catch
            {
                MessageBox.Show("Error while attempting to sign in.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            conn.Close();
            MessageBox.Show("You have been signed in");
            resetForm();
        }

 // Checks if student is registered as a tutor
 private bool isTutor(int ID)
 {
     DataConnection conn = new DataConnection();
     conn.Open();
     SqlDataReader rd = conn.GetReader("CLARION_ID, tutor_id", "TUTOR", "CLARION_ID", ID.ToString(), "and status = '"+"active"+"'");
      if (rd.HasRows)
     {
         while (rd.Read())
         {
             tutorid =int.Parse( rd[1].ToString());
         }
     }
     bool b = rd.HasRows;
     conn.Close();
     return b;
 }

 private string getName()
 {
     string name;
     DataConnection conn = new DataConnection();
     conn.Open();
     SqlDataReader rd = conn.GetReader("FIRSTNAME, MIDDLE_NAME, LASTNAME", "STUDENT", "CLARION_ID", studentID.ToString());
     if (rd.HasRows)
     {
         rd.Read();
         name = rd[0] + " " + rd[1] + " " + rd[2];
     }
     else
         name = "ERROR - name not found";
     conn.Close();
     return name;
 }

        // Queries database to see if student with searchID is already signed in
        public static bool studentSignedIn(int searchID)
        {
            bool b;
            DataConnection conn = new DataConnection();
            conn.Open();

            SqlDataReader rd = conn.GetReader("clarion_id, time_out", "visit", "clarion_id", searchID.ToString(), "and time_out is null");
            b = rd.HasRows;
            conn.Close();

            //MessageBox.Show(b.ToString());

            if (b == false)
            {
                conn.Open();
                rd = conn.GetReader("CLARION_ID, tutor_id", "TUTOR", "CLARION_ID", searchID.ToString());
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        tutorid = int.Parse(rd[1].ToString());
                    }
                }
                rd.Close();
                conn.Close();
                conn.Open();
                rd = conn.GetReader("tutor_id, time_out", "tutor_hour", "tutor_id", tutorid.ToString(), "and time_out is null");
                b = rd.HasRows;
                conn.Close();
            }
            return b;
        }

 // Searches through database for searchID and returns true if ID is found
 public static bool studentIDExists(int numIn)
 {
     bool b;
     DataConnection conn = new DataConnection();
     conn.Open();
     SqlDataReader rd = conn.GetReader("CLARION_ID", "STUDENT", "CLARION_ID", numIn.ToString());
     b = rd.HasRows;
     conn.Close();
     return b;
 }

        private void btnAddVisit_Click(object sender, EventArgs e)
        {
            DataConnection conn = new DataConnection();

            int tryStudentID;
            if (int.TryParse(txtAddStudentID.Text, out tryStudentID))
            {
                try
                {
                    string studentID = "", date = "", timeIn = "", timeOut = "", method = "", time_difference = "";
                    string[] selectedTutor, selectedClass;
                    int tutor = 0;
                    // 11319440
                    studentID = txtAddStudentID.Text;
                    date = dateTimePickerAdd.Text;
                    timeIn = dateTimePickerAddTimeIn.Text;
                    timeOut = dateTimePickerAddTimeOut.Text;

                    try
                    {
                        method = comboAddMethod.SelectedItem.ToString();
                    }
                    catch { }
                    //course = txtAddClass.Text;
                    // comboaddClass
                    TimeSpan dd = DateTime.Parse(timeOut).Subtract(DateTime.Parse(timeIn));
                    time_difference = dd.ToString();
                    //timedifference =DateTime.Parse( timenow.Subtract(timein).ToString());j
                    selectedClass = new string[5];

                    if (method == "Tutoring")
                    {
                        selectedTutor = comboAddTutoring.SelectedItem.ToString().Split();
                        conn = new DataConnection();

                        string selectedTutorID;
                        conn.Open();
                        SqlDataReader rd = conn.GetReader("TUTOR_ID", "STUDENT INNER JOIN TUTOR ON TUTOR.CLARION_ID = STUDENT.CLARION_ID", "LASTNAME", selectedTutor[1], "FIRSTNAME", selectedTutor[0]);
                        if (!(rd.Read()))
                        {
                            conn.Close();
                            MessageBox.Show("Tutor not found.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        selectedTutorID = rd[0].ToString();
                        tutor = int.Parse(selectedTutorID);
                        conn.Close();

                    }
                    try
                    {
                        selectedClass = comboaddClass.SelectedItem.ToString().Split();
                    }
                    catch { }
                    string nothing = "other";

                    conn = new DataConnection();
                    conn.Open();

                    if (method == "Tutoring")
                    {
                        if ((string)comboaddClass.SelectedItem.ToString().ToLower() == "other")
                        {
                            try
                            {
                                conn.Query("insert into VISIT(DATE, TIME_IN, TIME_OUT, CLARION_ID, TERM, SUBJECT, CATALOG, TUTOR_ID, METHOD, SECTION, time_difference) values ('" + date + "','" + timeIn + "', '" + timeOut + "', '" + studentID + "', '" + "N/A" + "', '" + nothing + "', '" + nothing + "', '" + tutor + "', '" + method + "', '" + nothing + "', '" + time_difference + "')");
                            }
                            catch
                            {
                                MessageBox.Show("Cannot add visit", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                conn.Close();
                                return;
                            }
                        }
                        else
                        {
                            try
                            {
                                conn.Query("insert into VISIT(DATE, TIME_IN, TIME_OUT, CLARION_ID, TERM, SUBJECT, CATALOG, TUTOR_ID, METHOD, SECTION,time_difference) values ('" + date + "','" + timeIn + "', '" + timeOut + "', '" + studentID + "', '" + selectedClass[0] + "', '" + selectedClass[1] + "', '" + selectedClass[2] + "', '" + tutor + "', '" + method + "', '" + selectedClass[3] + "', '" + time_difference + "')");
                            }
                            catch
                            {
                                MessageBox.Show("Cannot add visit", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                conn.Close();
                                return;
                            }
                        }
                    }
                    else // method isn't tutoring
                    {
                        if ((string)comboaddClass.SelectedItem.ToString().ToLower() == "other")
                        {
                            try
                            {
                                conn.Query("insert into VISIT(DATE, TIME_IN, TIME_OUT, CLARION_ID, TERM, SUBJECT, CATALOG,METHOD, SECTION,time_difference) values ('" + date + "','" + timeIn + "', '" + timeOut + "', '" + studentID + "', '" + "N/A" + "', '" + nothing + "', '" + nothing + "', '" + method + "', '" + nothing + "','" + time_difference + "')");

                            }
                            catch
                            {
                                MessageBox.Show("Cannot add visit", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
//.........这里部分代码省略.........

        private void btnEditSelectedVisit_Click(object sender, EventArgs e)
        {
            if ((listBoxEditVisit.SelectedIndex == 0)||(listBoxEditVisit.SelectedIndices.Count>1))
                return;
            string[] selectedVisitEdit;
            try
            {
                selectedVisitEdit = listBoxEditVisit.SelectedItem.ToString().Split('\t');
            }
            catch
            {
                return;
            }
            string dateEdit = selectedVisitEdit[0];
            DateTime TimeInEdit = DateTime.Parse(selectedVisitEdit[4].ToString());
            originalDateTime = TimeInEdit;

            if (selectedVisitEdit[3] != null)
            {
                try
                {
                    DateTime TimeOutEdit = DateTime.Parse(selectedVisitEdit[5]);

                dateTimePickerEditTimeOut.Value = TimeOutEdit;
                }
                catch {
                   // dateTimePickerEditTimeOut.Value = null;
                }
            }
            txtEditDate.Text = dateEdit;
            txtEditStudentID.Text = selectedVisitEdit[3];

            comboEditMethod.Items.Clear();
            DataConnection conn = new DataConnection();
            try
            {

                conn.Open();
                SqlDataReader rd = conn.GetReader("DISTINCT METHOD", "VISIT");
                while (rd.Read())
                    comboEditMethod.Items.Add(rd[0].ToString());
                conn.Close();
                for (int i = 0; i < Properties.Settings.Default.MethodNames.Count; i++)
                {
                    if (!(comboEditMethod.Items.Contains(Properties.Settings.Default.MethodNames[i])))
                        comboEditMethod.Items.Add(Properties.Settings.Default.MethodNames[i]);
                }
                for (int i = 0; i < comboEditMethod.Items.Count; i++)
                    if (selectedVisitEdit[6].ToString().TrimEnd() == comboEditMethod.Items[i].ToString())
                        comboEditMethod.SelectedIndex = i;
                comboEditMethod.Items.Add("Tutor");
                comboaddClass.Items.Clear();
                conn.Open();
                rd = conn.GetReader("SUBJECT, CATALOG, SECTION", "STUDENT_COURSE", "CLARION_ID", selectedVisitEdit[3]);
                while (rd.Read())
                    comboaddClass.Items.Add(rd[0].ToString() + " " + rd[1] + " " + rd[2]);
                conn.Close();
                comboaddClass.Items.Add("Other");
                conn.Open();
                rd = conn.GetReader("SUBJECT, CATALOG, SECTION", "VISIT", "CLARION_ID", selectedVisitEdit[3], "DATE", selectedVisitEdit[0], "TIME_IN", selectedVisitEdit[4]);
                rd.Read();
                if (rd[0].ToString() == "other")
                    for (int i = 0; i < comboaddClass.Items.Count; i++)
                        if (comboaddClass.Items[i].ToString() == "Other")
                            comboaddClass.SelectedIndex = i;
                for (int i = 0; i < comboaddClass.Items.Count; i++)
                    if (rd[0] + " " + rd[1] + " " + rd[2] == comboaddClass.Items[i].ToString())
                        comboaddClass.SelectedIndex = i;
                conn.Close();
                conn.Open();
                rd = conn.GetReader("FIRSTNAME, LASTNAME", "TUTOR INNER JOIN STUDENT ON STUDENT.CLARION_ID = TUTOR.CLARION_ID");
                while (rd.Read())
                    comboAddTutoring.Items.Add(rd[0].ToString() + " " + rd[1]);
                conn.Close();
                for (int i = 0; i < comboAddTutoring.Items.Count; i++)
                    if (selectedVisitEdit[7].ToString() == comboAddTutoring.Items[i].ToString())
                        comboAddTutoring.SelectedIndex = i;

                comboaddClass.Enabled = true;
                dateTimePickerEditTimeIn.Value = TimeInEdit;
                btnSaveEdit.Enabled = true;
                comboEditMethod.Enabled = true;
                dateTimePickerEditTimeIn.Enabled = true;
                dateTimePickerEditTimeOut.Enabled = true;
            }
            catch
            {
                dateTimePickerEditTimeIn.Enabled = true;
                dateTimePickerEditTimeOut.Enabled = true;
                btnSaveEdit.Enabled = true;
                conn.Close();
            }
            btnDeleteVisit.Enabled = false;
        }

        //Created by Sean: button1_Click inside the Reporting Tab
        private void displayBtn_Click(object sender, EventArgs e)
        {
            //TODO: This should add a placeholder to the listbox that represents
            //      the data that will be placed into the excel file
            string column, table, condition = "", row = "", first="", last="";
            DataConnection conn = new DataConnection();
            SqlDataReader rd;
            int year = 0, term=0, count = 0;
            bool y = int.TryParse(txtYear.Text, out year);
            term = DataConnection.getTerm(year, comboTerm.SelectedItem.ToString());
            conn.Open();
            rd = conn.GetReader("*", "VISIT", "WHERE TERM = '" + term.ToString() + "'");
            if (!(rd.HasRows))
            {
                conn.Close();
                return;
            }
            conn.Close();
            conn.Open();
            /*
            term += (int.Parse(year.ToString())/1000)*1000;
            term += (int.Parse(year.ToString()) % 100) * 10;

            if (comboTerm.SelectedItem.ToString() == "Winter")
                term += 9;
            else if (comboTerm.SelectedItem.ToString() == "Spring")
                term += 1;
            else if (comboTerm.SelectedItem.ToString() == "Summer")
                term += 5;
            else
                term += 8;
            */
            switch (comboCountCategory.SelectedItem.ToString())
            {
                case "Method":
                    column = "method, COUNT(DISTINCT CLARION_ID), term";
                    table = "VISIT";
                    //MessageBox.Show(comboFilter.SelectedItem.ToString());
                    condition = " where term = '" + term + "' GROUP BY METHOD, term";
                    listBoxReport.Items.Add( "Method".PadRight(30) + "\t" + "Number of Students");
                    if (comboFilter.SelectedItem.ToString() == "All")
                    {
                       // MessageBox.Show("wind");
                        rd = conn.GetReader(column, table, condition);
                        while (rd.Read())
                        {
                           // for (int i = 0; i < 2; i++)
                            {
                                row += rd[0].ToString().PadRight(30) + "\t" +rd[1];
                               // MessageBox.Show((80 - rd[0].ToString().Length).ToString());

                            }
                            //MessageBox.Show("1".PadLeft(80-(rd[0].ToString().Length*1)));
                          //  MessageBox.Show(string.Format("{0,-50} {1,60}", rd[0].ToString(), rd[1].ToString()));h
                           listBoxReport.Items.Add(row);
                            //listBoxReport.Items.Add(new object[] { rd[0], rd[1] });
                            row = "";
                            //MessageBox.Show(row);
                        }

                    }
                    else
                    {
                        rd = conn.GetReader("method, COUNT(DISTINCT CLARION_ID), term", "visit", " where term = '" + term.ToString() + "' and method = '" + comboFilter.SelectedItem.ToString() + "'  GROUP BY METHOD, term ");
                        if (rd.HasRows)
                        {
                            while (rd.Read())
                            {
                                for (int i = 0; i < 2; i++)
                                    row += rd[i].ToString().PadRight(30) + "\t";
                                listBoxReport.Items.Add(row);
                                row = "";
                            }
                        }
                        else
                            listBoxReport.Items.Add(comboFilter.SelectedItem.ToString().PadRight(30) + "\t0");
                    }
                    listBoxReport.Items.Add("");
                    /*

                    //condition = " method " + " = "+ " '"+"other"+"' ";
                    filterColumn = "METHOD";
                     * */
                    break;
                case "Student":
                    int newid = -1;
                    int nontradcount = 0;
                    int studentcount = 0;

                    count = 0;
                    TimeSpan newtime = new TimeSpan();

                    if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Total Hours")
                    {
                        listBoxReport.Items.Add("Student Name".PadRight(20) + "\t" + "".PadRight(20) + "\t" + "Total Hours");
                       // rd = conn.GetReader(column, table, condition);
                        rd = conn.joinQuery("select visit.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname, student.age from visit inner join student on visit.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "'");

                        while (rd.Read())
//.........这里部分代码省略.........

        public void loadlist()
        {
            listBoxEnableTutors.Items.Clear();
            listBoxDisableTutors.Items.Clear();
            listBoxLoggedIn.Items.Clear();
            DataConnection conn = new DataConnection();
            SqlDataReader rd;
            conn.Open();
            try
            {
                rd = conn.GetReader("STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT.clarion_id, tutor.status", "TUTOR INNER JOIN STUDENT ON TUTOR.CLARION_ID=STUDENT.CLARION_ID");

                if (rd.HasRows)
                {

                    while (rd.Read())
                    {
                        if (rd[3].ToString() == "active")
                            listBoxEnableTutors.Items.Add(rd[0].ToString() + " " + rd[1].ToString() + " " + rd[2]);
                        else
                            listBoxDisableTutors.Items.Add(rd[0].ToString() + " " + rd[1].ToString() + " " + rd[2]);
                    }
                }

                listBoxLoggedIn.Items.Add("DATE\t\tTIME IN\t\tID\t\t" + "LAST NAME".PadRight(30) + "\tFIRST NAME");
            }
            catch
            {
                MessageBox.Show("Cannot load tutors", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
            conn.Open();
            try
            {
                rd = conn.GetReader("*", "VISIT", "student", "visit.clarion_id=student.clarion_id and time_out is null", 1);

                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        DateTime jdate = DateTime.Parse(rd[1].ToString());
                        listBoxLoggedIn.Items.Add(jdate.ToString("MM/dd/yyyy") + "\t" + rd[2] + "\t" + int.Parse(rd[0].ToString()).ToString("D8").PadRight(10) + "\t" + rd[13].ToString().PadRight(30) + "\t" + rd[14].ToString().PadRight(30));
                    }
                }
            }
            catch
            {
                MessageBox.Show("Cannot load currently logged in visits", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
            conn.Open();
            try
            {
                rd = conn.joinQuery("select tutor_hour.tutor_id, tutor_hour.date ,tutor_hour.time_difference, tutor_hour.time_in, student.lastname, student.firstname from tutor_hour inner join tutor on tutor_hour.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where time_difference is null");

                //rd = conn.GetReader("*", "tutor_hour", "student", "tutor_hour.clarion_id=student.clarion_id and time_out is null", 1);

                if (rd.HasRows)
                {
                    while (rd.Read())
                    {

                        DateTime jdate = DateTime.Parse(rd[1].ToString());
                        listBoxLoggedIn.Items.Add(jdate.ToString("MM/dd/yyyy") + "\t" + rd[3] + "\t" + ("TUT" + int.Parse(rd[0].ToString()).ToString("D5")).PadRight(10) + "\t" + rd[4].ToString().PadRight(30) + "\t" + rd[5].ToString().PadRight(30));
                    }
                }
            }
            catch
            {
                MessageBox.Show("Cannot load currently logged in visits", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        //From report
        private void comboCountCategory_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboFilter.Items.Clear();
            comboFilter.Items.Add("All");

            comboFilter.SelectedIndex = 0;
            if (comboCountCategory.SelectedItem.ToString() == "Method")
            {
                DataConnection conn = new DataConnection();
                conn.Open();
                SqlDataReader rd = conn.GetReader("DISTINCT METHOD", "VISIT");
                while (rd.Read())
                    comboFilter.Items.Add(rd[0].ToString());
                conn.Close();
                for (int i = 0; i < Properties.Settings.Default.MethodNames.Count; i++)
                {
                    if (!(comboFilter.Items.Contains(Properties.Settings.Default.MethodNames[i])))
                        comboFilter.Items.Add(Properties.Settings.Default.MethodNames[i]);
                }
                /*
                comboGroup.Items.Add("Tutoring");
                comboGroup.Items.Add("Group Meeting");
                comboGroup.Items.Add("Supplemental Instruction");
                comboGroup.Items.Add("Writing Center");
                comboGroup.Items.Add("Computer Use");
                comboGroup.Items.Add("Self Study");
                comboGroup.Items.Add("Video");
                comboGroup.Items.Add("Other");
                */

            }
            else if (comboCountCategory.SelectedItem.ToString() == "Student")
            {
                comboFilter.Items.Add("Total Hours");
                comboFilter.Items.Add("Visits");

            }
            else if (comboCountCategory.SelectedItem.ToString() == "Tutor")
            {
                comboFilter.Items.Add("Hours Tutoring");
                comboFilter.Items.Add("Students Tutored");
            }
            else if (comboCountCategory.SelectedItem.ToString() == "Course")
            {
                DataConnection conn = new DataConnection();
                conn.Open();
                SqlDataReader rd = conn.GetReader("DISTINCT subject", "VISIT");
                while (rd.Read())
                    comboFilter.Items.Add(rd[0].ToString());
                conn.Close();
                comboFilter.Items.Add("Total Courses");
            }
        }

        private void comboAddMethod_SelectedIndexChanged(object sender, EventArgs e)
        {
            comboaddClass.Items.Clear();
            comboAddTutoring.Items.Clear();

            if (comboAddMethod.SelectedIndex == 0)
            {
                comboAddTutoring.Enabled = true;
                comboAddTutoring.Items.Add("Select a tutor...");
                    comboAddTutoring.Items.AddRange(frmMain.getTutors(false));

                comboAddTutoring.SelectedIndex = 0;
            }
            else
                comboAddTutoring.Enabled = false;

            comboaddClass.Items.Add("Select a class...");
            DataConnection conn = new DataConnection();
            conn.Open();
            try
            {
                SqlDataReader rd = conn.GetReader("term, subject, catalog, section, clarion_id", "student_course", "clarion_id", txtAddStudentID.Text.ToString());
                if (rd.HasRows)
                {

                    while (rd.Read())
                    {
                        comboaddClass.Items.Add(rd[0].ToString() + " " + rd[1].ToString() + " " + rd[2].ToString() + " " + rd[3].ToString());
                    }
                }
                conn.Close();
                comboaddClass.Items.Add("Other");
                comboaddClass.SelectedIndex = 0;
                comboaddClass.Enabled = true;
            }
            catch
            {
                MessageBox.Show("Cannot import student classes. Check the student ID.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnFullReport_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("This may take a very long time, continue?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == System.Windows.Forms.DialogResult.No)
                return;
            SaveFileDialog reportFile = new SaveFileDialog();
            reportFile.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
            reportFile.RestoreDirectory = true;
            reportFile.DefaultExt = "xlsx";
            reportFile.OverwritePrompt = false;

            if (reportFile.ShowDialog() == DialogResult.OK)
            {
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Add();
                Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                DataConnection conn = new DataConnection();
                conn.Open();
                SqlDataReader rd = conn.GetReader("*", "VISIT", "WHERE TERM = '" + DataConnection.getTerm(int.Parse(txtYear.Text),comboTerm.SelectedItem.ToString()).ToString() + "'");
                if (!rd.HasRows)
                    MessageBox.Show("Found no visits with the current selected term.\nAborting report generation.", "No Visits Found");
                this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
                for (int i = 0; i < rd.FieldCount; i++)
                    xlWorkSheet.Cells[1, i + 1] = rd.GetName(i);
                for (int i = 1; rd.Read(); i++)
                {
                    for (int j = 0; j < rd.FieldCount; j++)
                        xlWorkSheet.Cells[i + 1, j + 1].Value = rd[j].ToString();
                }
                conn.Close();

                try
                {
                    xlWorkBook.SaveAs(reportFile.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook);
                    xlWorkBook.Close();
                }
                catch (Exception ex)
                {
                    if (ex.Source == "Microsoft Excel")
                        MessageBox.Show("File may be open in another window", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    else
                        MessageBox.Show(ex.ToString());
                }
                xlApp.Quit();
                this.Cursor = System.Windows.Forms.Cursors.Default;
                MessageBox.Show("Report Generation Complete!");
            }
            reportFile.Dispose();
        }

        //  Queries database for classes taken by student with ID cardNumber
        private void updateClassComboBox(int studentID)
        {
            comboClassList.Items.Add("Select a class...");
            DataConnection conn = new DataConnection();
            conn.Open();
            SqlDataReader rd = conn.GetReader("term, subject, catalog, section, clarion_id", "student_course", "clarion_id", studentID.ToString());
            if (rd.HasRows)
            {

                while (rd.Read())
                {
                    comboClassList.Items.Add(rd[1].ToString() +" "+ rd[2].ToString() +" " + rd[3].ToString());
                }
            }
            conn.Close();
            comboClassList.Items.Add("Other");
            comboClassList.SelectedIndex = 0;
        }