C# DataConnection.Open方法代码示例

        public void loadvisits(int studentID, DateTime minDate, DateTime maxDate)
        {
            min = minDate;
            max = maxDate;
            ID = studentID;
            //clears the list box to enter new information
            listBoxEditVisit.Items.Clear();
            //TAB THIS
            listBoxEditVisit.Items.Add("DATE".PadRight(15) + "\t" + "FIRST NAME".PadRight(30) + "\t" + "LAST NAME".PadRight(30) + "\t" + "ID".PadRight(12)+ "\t" + "TIME IN".PadRight(10)+"\t" + "TIME OUT".PadRight(10) + "\t" + "METHOD".PadRight(20) + "\t" + "TUTOR'S FIRST NAME".PadRight(30) + "TUTOR'S LAST NAME".PadRight(30)+ "\t" + "SUBJECT" + "\t" + "CATALOG" + "\t" + "SECTION");
            //creates new dataconnection
            DataConnection conn = new DataConnection();
            SqlDataReader rd;

            conn.Open();

                //gets visits request
                //just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
                rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'":"' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");

            if (rd.HasRows)
            {

                while (rd.Read())
                {
                    DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
                    string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
                    if (TIMEOUT.Replace(" ","").Length !=8)
                        TIMEOUT = " ".PadRight(18);
                    listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + (int.Parse(rd["CLARION_ID"].ToString())).ToString("D8").PadRight(12) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + TIMEOUT.PadRight(10) + "\t" + rd["METHOD"].ToString().PadRight(20) + "\t" + rd["TUTORFIRSTNAME"].ToString().PadRight(30) + " " + rd["TUTORLASTNAME"].ToString().PadRight(30) + "\t" + rd["SUBJECT"].ToString().PadRight(5) + "\t" + ((rd["CATALOG"]).ToString()).PadRight(5) + "\t" + ((rd["SECTION"]).ToString()).PadRight(4));
                }
            }
            rd.Close();
            //closes connection
            conn.Close();

            if (studentID == 0)
            {
                conn.Open();

                //gets visits request
                //just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
               // rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'" : "' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");
                rd = conn.joinQuery("select tutor_hour.tutor_id, tutor_hour.date, tutor_hour.time_out ,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 tutor_hour.DATE<='" + maxDate + "' AND tutor_hour.DATE>='" + minDate+"' ");

                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
                        string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
                        if (TIMEOUT.Replace(" ", "").Length != 8)
                            TIMEOUT = " ".PadRight(18);
                        listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + ("TUT" + int.Parse(rd["tutor_ID"].ToString()).ToString("D4").PadRight(10)) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + rd["TIME_OUT"].ToString().PadRight(10) + "\t" + "TUTOR");
                    }
                }
                rd.Close();
                //closes connection
                conn.Close();
            }
        }

 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;
 }

 // 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 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();
        }

        private void btnSaveEdit_Click(object sender, EventArgs e)
        {
            TimeSpan timedifference;
            DateTime timeOut = DateTime.Parse(dateTimePickerEditTimeOut.Value.ToString("HH:mm:ss tt"));
            DateTime timeIn = DateTime.Parse(dateTimePickerEditTimeIn.Value.ToString("HH:mm:ss tt"));
            DateTime date = DateTime.Parse(txtEditDate.Text);
            string studentID = txtEditStudentID.Text;
            string method = "";
            string[] course = new string[10];
            string[] tutor = new string[10];
            bool istutor = false;
            try
            {

                method = comboEditMethod.SelectedItem.ToString();

                course = comboaddClass.SelectedItem.ToString().Split();
                if (course[0].ToString().ToLower() == "other")
                {
                    course = new string[10];
                    course[0] = "other";
                    course[1] = "other";
                    course[2] = "other";
                    course[3] = "other";
                }
                if(comboAddTutoring.Enabled==true)
                tutor = comboAddTutoring.SelectedItem.ToString().Split();
            }
            catch
            {
                istutor = true;
            }
            //MessageBox.Show(istutor.ToString());
            string tutorID = null;
            DataConnection conn = new DataConnection();
            conn.Open();
            try
            {
                SqlDataReader rd = conn.GetReader("TUTOR_ID", "STUDENT INNER JOIN TUTOR ON STUDENT.CLARION_ID = TUTOR.CLARION_ID", "STUDENT.FIRSTNAME", tutor[0], "STUDENT.LASTNAME", tutor[1]);
                if (rd.HasRows)
                {
                    rd.Read();
                    tutorID = rd[0].ToString();
                }
                else if (comboAddTutoring.SelectedItem.ToString() == Properties.Settings.Default.TutoringMethod)
                {
                    MessageBox.Show("Please choose a tutor.");
                    conn.Close();
                    return;
                }
            }
            catch { }
            conn.Close();
            timedifference = timeOut.Subtract(timeIn);
            if (timedifference < TimeSpan.Zero)
            {
                MessageBox.Show("Sign out time is before sign in time. " + timeOut.ToString() + " " + timeIn.ToString() + " " + timedifference.ToString(), "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            conn.Open();
            try
            {
                if (istutor)
                    conn.Query("update tutor_hour set tutor_ID = '" + txtEditStudentID.Text.Remove(0, 3) + "' , DATE = '" + date + "' , TIME_IN ='" + timeIn.ToString("HH:mm:ss tt") + "' , TIME_OUT = '" + timeOut.ToString("HH:mm:ss tt") + "' , TIME_DIFFERENCE = '" + timedifference.ToString("c") + "' where tutor_ID = '" + txtEditStudentID.Text.Remove(0, 3) + "' AND DATE = '" + date + "' AND TIME_IN ='" + originalDateTime.ToString("HH:mm:ss tt") + "'");
                else if (comboAddTutoring.Enabled)
                    conn.Query("update VISIT set CLARION_ID = '" + txtEditStudentID.Text + "' , DATE = '" + date + "' , TIME_IN ='" + timeIn.ToString("HH:mm:ss tt") + "' , TIME_OUT = '" + timeOut.ToString("HH:mm:ss tt") + "' , TIME_DIFFERENCE = '" + timedifference.ToString("c") + "', SUBJECT = '" + course[0] + "', CATALOG = '" + course[1] + "', SECTION = '" + course[2] + "', TUTOR_ID = " + tutorID + ", METHOD = '" + method + "'" + ((comboaddClass.SelectedItem.ToString()=="Other") ? ", TERM='Other'" : "") + " where CLARION_ID = '" + txtEditStudentID.Text + "' AND DATE = '" + date + "' AND TIME_IN ='" + originalDateTime.ToString("HH:mm:ss tt") + "'");
                else
                    conn.Query("update VISIT set CLARION_ID = '" + txtEditStudentID.Text + "' , DATE = '" + date + "' , TIME_IN ='" + timeIn.ToString("HH:mm:ss tt") + "' , TIME_OUT = '" + timeOut.ToString("HH:mm:ss tt") + "' , TIME_DIFFERENCE = '" + timedifference.ToString("c") + "', SUBJECT = '" + course[0] + "', CATALOG = '" + course[1] + "', SECTION = '" + course[2] + "', METHOD = '" + method + "'" + ((comboaddClass.SelectedItem.ToString()=="Other") ? ", TERM='Other'" : "") + " where CLARION_ID = '" + txtEditStudentID.Text + "' AND DATE = '" + date + "' AND TIME_IN ='" + originalDateTime.ToString("HH:mm:ss tt") + "'");
            }
            catch
            {
                MessageBox.Show("Cannot save visit", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
                return;
            }
            conn.Close();
            MessageBox.Show("Visit has been edited.");
            this.Close();
        }

        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;
        }

        private void btnDeleteVisit_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Are you sure you want to delete?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                if (listBoxEditVisit.SelectedIndex == 0)
                    return;
                string[] items;
                bool tutor;
                for (int i = 0; i < listBoxEditVisit.SelectedItems.Count; i++)
                {
                    items = listBoxEditVisit.SelectedItems[i].ToString().Split('\t');
                    tutor = false;

                    if (items[3].Remove(3) == "TUT")
                        tutor = true;
                    //MessageBox.Show(items[6]);
                    DataConnection conn = new DataConnection();
                    conn.Open();
                    try
                    {
                        if (tutor)
                            conn.Query("DELETE FROM tutor_hour WHERE tutor_ID = '" + items[3].Remove(0, 3) + "' AND DATE = '" + items[0] + "' AND TIME_IN = '" + items[4] + "' ");
                        else
                            conn.Query("DELETE FROM VISIT WHERE CLARION_ID = '" + items[3] + "' AND DATE = '" + items[0] + "' AND TIME_IN = '" + items[4] + "' ");
                    }
                    catch
                    {
                        MessageBox.Show("Error while attempting to delete visit. Please reload visit information and try again.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                    conn.Close();
                }
                loadvisits(ID, min, max);
            }
        }

        private void ImportCourses()
        {
            string last, first, connectionString="";
            OpenFileDialog coursesFile = new OpenFileDialog();
            coursesFile.Title = "Import Courses";
            coursesFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
            coursesFile.RestoreDirectory = true;
            coursesFile.DefaultExt = "xlsx";
            if (coursesFile.ShowDialog() == DialogResult.OK)
            {
                if (coursesFile.FileName == "")
                    return;
                try
                {
                    connectionString = @"Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + coursesFile.FileName + ";Extended Properties=Excel 12.0 Xml";
                }
                catch { return; };
            }
            else
                return;
            // Create the connection
            this.Cursor = Cursors.WaitCursor;
            System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            string excelQuery = @"Select * from [sheet1$]";
            System.Data.OleDb.OleDbCommand excelCommand = new System.Data.OleDb.OleDbCommand(excelQuery, excelConnection);
            excelConnection.Open();
            System.Data.OleDb.OleDbDataReader excelReader;
            excelReader = excelCommand.ExecuteReader();
            DataConnection conn = new DataConnection();
            conn.Open();
            string s = "";
            int i=2;
            excelReader.Read();
            while (excelReader.Read())
            {
                i++;
                last = excelReader[5].ToString();
                last = last.Replace("'"," ");
                first = excelReader[6].ToString();
                first= first.Replace("'"," ");
                string catalog = excelReader[3].ToString().Replace(" ", "");

                try
                {
                    conn.Query("insert into PROFESSOR (PROF_EMAIL, LASTNAME, FIRSTNAME) values ('" + excelReader[7] + "', '" + last + "', '" + first + "')");
                }
                catch (Exception ex)
                {
                    s += "\n\tProfessor on row " + i.ToString() + "\n" + ex.Message.ToString();
                }
                try
                {
                    conn.Query("insert into Course (term,subject,catalog,section,prof_email) values ('" + excelReader[0] + "','" + excelReader[2] + "','" + catalog + "','" + excelReader[4] + "','"+excelReader[7]+"')");
                }
                catch (Exception ex)
                {
                    s += "\n\tCourse on row " + i.ToString() + "\n" + ex.Message.ToString();
                }
                try
                {
                    conn.Query("insert into student_Course (clarion_id,term,subject,catalog,section) values ('"+excelReader[1]+"' ,'" + excelReader[0] + "','" + excelReader[2] + "','" + catalog + "','" + excelReader[4] + "')");
                }
                catch (Exception ex)
                {
                    s += "\n\tStudent_Course on row " + i.ToString() + "\n" + ex.Message.ToString();
                }
            }
            try
            {
                conn.Query("insert into Course (term,subject,catalog,section) values ('other','other','other','other')");
            }
            catch { }
            excelReader.Close();
            conn.Close();
            excelConnection.Close();
            this.Cursor = Cursors.Default;
        }

        //  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;
        }

 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;
 }

 private void btnEnableSelected_Click(object sender, EventArgs e)
 {
     string[] name = new string[3];
     try
     {
         name = listBoxDisableTutors.SelectedItem.ToString().Split();
     }
     catch
     {
         MessageBox.Show("Please choose a tutor first", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
     DataConnection conn = new DataConnection();
     conn.Open();
     try
     {
         conn.Query("update tutor set status = 'active' where CLARION_ID = " + name[2]);
     }
     catch
     {
         MessageBox.Show("Unable to update tutor status", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
     conn.Close();
     loadlist();
 }

 // 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 btnEnableAll_Click(object sender, EventArgs e)
 {
     DataConnection conn = new DataConnection();
     conn.Open();
     try
     {
         conn.Query("update tutor set status = 'active' where status = 'inactive'");
     }
     catch
     {
         MessageBox.Show("Unable to update tutor status", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
     conn.Close();
     loadlist();
 }

        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);
//.........这里部分代码省略.........

        //Adds Tutor to the list of tutors via Student ID and adds their information to the Tutors table
        private void btnAddTutor_Click(object sender, EventArgs e)
        {
            /////////////// edit table so only clarion id, status and cnet_username are used
            //bool valid = false;
            //Gets the student id
            string studentID = txtTutorStudentID.Text;
            /*cn.Open();
            cmd.CommandText = "select * from student where CLARION_ID=" + studentID;

            rd = cmd.ExecuteReader();
            */
            try
            {
                bool notInDB = frmMain.studentIDExists(int.Parse(studentID));
                DataConnection conn = new DataConnection();
                conn.Open();
                SqlDataReader rd = conn.joinQuery("select clarion_id from tutor where clarion_id = " + studentID);

                if (rd.HasRows)
                {
                    notInDB = false;
                }
                conn.Close();
                if (notInDB)
                {
                    conn.Open();
                    conn.Query("insert into tutor(clarion_id,status) values ('" + studentID + "', '" + "active" + "')");
                    conn.Close();
                }
                else
                    MessageBox.Show("Tutor is already in database.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch
            {
                MessageBox.Show("Error while searching for student ID. Please check to see if it is valid.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            loadlist();
        }