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(); }
//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()) { if (newid == int.Parse(rd[0].ToString())) { newtime += TimeSpan.Parse(rd[1].ToString()); } else { if (newid != -1) { listBoxReport.Items.Add(first.PadRight(20) + "\t" + last.PadRight(20) + "\t" + newtime); studentcount++; } newid = int.Parse(rd[0].ToString()); newtime = TimeSpan.Parse(rd[1].ToString()); first = rd[4].ToString(); last = rd[3].ToString(); try { if (int.Parse(rd["student.age"].ToString()) >= 24) { first = "* " + rd[4].ToString(); nontradcount++; } } catch { } } } if (newid != -1) { studentcount++; listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + newtime); } if (comboFilter.SelectedItem.ToString() == "All") listBoxReport.Items.Add(""); } if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Visits") { newid = -1; count = 0; nontradcount = 0; studentcount = 0; listBoxReport.Items.Add("Student Name".PadRight(20) + "\t" + "".PadRight(20) + "\tNumber of Visits"); column = "CLARION_ID,count(distinct time_difference), term"; table = "VISIT"; condition = "where time_difference is not null and term = '"+term.ToString()+"' group by clarion_id, term"; // 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()) { if (newid == int.Parse(rd[0].ToString())) { count++; } else { if (newid != -1) { studentcount++; listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20) + "\t" + count); } newid = int.Parse(rd[0].ToString()); count = 1; first = rd[4].ToString(); last = rd[3].ToString(); try { if (int.Parse(rd[5].ToString()) >= 25) { first = "* " + rd[4].ToString(); nontradcount++; } } catch { } } } if (newid != -1) { studentcount++; listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + count); listBoxReport.Items.Add("Nontraditional Students".PadRight(30) + "\t" + nontradcount); listBoxReport.Items.Add("Traditional Students".PadRight(30) + "\t" + (studentcount-nontradcount)); } } listBoxReport.Items.Add(""); break; case "Tutor": newid = -1; count = 0; newtime = new TimeSpan(); column = "tutor_ID, time_difference, term "; table = "VISIT"; condition = "where time_difference is not null and tutor_id is not null and term = '" + term.ToString() + "' ORDER BY tutor_id"; if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Hours Tutoring") { newid = -1; count = 0; listBoxReport.Items.Add("Tutor Name".PadRight(20) + "\t" + "".PadRight(20) + "\tTime Worked"); // rd = conn.GetReader(column, table, condition); rd = conn.joinQuery("select visit.tutor_id, tutor.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname from visit inner join tutor on visit.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "' order by visit.tutor_id"); while (rd.Read()) { if (newid == int.Parse(rd[0].ToString())) { newtime += TimeSpan.Parse(rd[2].ToString()); } else { if (newid != -1) listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + newtime); newid = int.Parse(rd[0].ToString()); newtime = TimeSpan.Parse(rd[2].ToString()); first = rd[5].ToString(); last = rd[4].ToString(); } } if (newid != -1) listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + newtime); // listBoxReport.Items.Add(newid.ToString().PadRight(60 - newid.ToString().Length) + "\t" + newtime); listBoxReport.Items.Add(""); } if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Students Tutored") { newid = -1; count = 0; column = "tutor_ID, count(distinct time_difference), term"; table = "VISIT"; condition = "where time_difference is not null and tutor_id is not null and term = '" + term.ToString() + "' group by tutor_id, term"; listBoxReport.Items.Add("Tutor Name".PadRight(20) + "\t" + "".PadRight(20) + "\tStudents Tutored"); rd = conn.joinQuery("select visit.tutor_id, tutor.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname from visit inner join tutor on visit.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "' order by visit.tutor_id"); while (rd.Read()) { if (newid == int.Parse(rd[0].ToString())) { count++; } else { if (newid != -1) listBoxReport.Items.Add(first.PadRight(20 ) + "\t" + last.PadRight(20 ) + "\t" + count); newid = int.Parse(rd[0].ToString()); count = 1; first = rd[5].ToString(); last = rd[4].ToString(); } } if (newid != -1) listBoxReport.Items.Add(first.PadRight(20) + "\t" + last.PadRight(20) + "\t" + count); } listBoxReport.Items.Add(""); break; case "Course": column = "SUBJECT, catalog, COUNT(*), term"; table = "VISIT"; count = 0; condition = "where term = '"+term.ToString()+"' GROUP BY SUBJECT, catalog, term"; if (comboFilter.SelectedItem.ToString() == "All") { listBoxReport.Items.Add("Subject" + "\t" + "Catalog" + "\t" + "Number of Visits"); rd = conn.GetReader(column, table, condition); while (rd.Read()) { for (int i = 0; i < 3; i++) row += rd[i].ToString().PadRight(5) + "\t"; listBoxReport.Items.Add(row); row = ""; } } else if (comboFilter.SelectedItem.ToString() == "Total Courses") { rd = conn.GetReader(column, table, condition); while (rd.Read()) { //for (int i = 0; i < rd.FieldCount; i++) count++; } listBoxReport.Items.Add("Total Courses ".PadRight(30) + "\t" + count); } else { listBoxReport.Items.Add("Subject" + "\t" + "Catalog" + "\t" + "Number of Visits"); condition = "where term = '" + term.ToString() + "' and subject = '" + comboFilter.SelectedItem.ToString() + "' GROUP BY SUBJECT, catalog, term"; rd = conn.GetReader(column, table, condition); if (rd.HasRows) { while (rd.Read()) { for (int i = 0; i < 3; i++) row += rd[i].ToString().PadRight(5) + "\t"; listBoxReport.Items.Add(row); row = ""; } } } listBoxReport.Items.Add(""); break; default: column = "-1"; table = "-1"; break; } /* if (!((column == "-1") && (table == "-1"))) if (comboFilter.SelectedIndex == 0) if (condition != "") { MessageBox.Show("sfgfdsgfg"); rd = conn.GetReader(column, table, condition); } else rd = conn.GetReader(column, table); else if (condition != "") { rd = conn.GetReader(column, table, filterColumn, comboFilter.SelectedItem.ToString(), condition); } else { MessageBox.Show(""); rd = conn.GetReader(column, table); } else return; while (rd.Read()) { for (int i = 0; i < rd.FieldCount; i++) row += rd[i] + "\t"; listBoxReport.Items.Add(row); listBoxReport.Items.Add(""); row = ""; } // */ /* if (comboCountCategory.SelectedItem.ToString() == "Method" && comboGroup.SelectedItem.ToString() == "Tutoring") { } else if (comboCountCategory.SelectedItem.ToString() == "Total Tutors") { } else if (comboCountCategory.SelectedItem.ToString() == "Total Tutor Hours") { selectString = "SELECT dateadd(second, SUM(DATEPART(SECOND, TIME_DIFFERENCE)), 108) FROM TUTOR_HOUR"; } */ }
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(); }
private WebResponse GetFtpResponse() { FtpWebResponse ftpresponse = null; if (m_CommandType == FtpCommandType.FtpDataReceiveCommand || m_CommandType == FtpCommandType.FtpDataSendCommand) { if (_bPassiveMode) { OpenPassiveDataConnection(); } else { OpenDataConnection(); } } // // negotiate data connection // string sztype = "I"; if (m_szContentType == "ascii") { sztype = "A"; } SendCommand("TYPE", sztype); ResponseDescription resp_desc = ReceiveCommandResponse(); if (!resp_desc.PositiveCompletion) { throw new ApplicationException("Negociación de datos fallida:\n" + m_sbControlSocketLog.ToString()); } if (m_szServerMethod == "PWD") { m_szCmdParameter = null; } SendCommand(m_szServerMethod, m_szCmdParameter); //ftpresponse = ReceiveResponse(); resp_desc = ReceiveCommandResponse(); if (m_CommandType == FtpCommandType.FtpDataSendCommand) { //if(resp_desc.Status/100 == 1) // Positive preliminary reply if (resp_desc.PositivePreliminary) // Positive preliminary reply { if (m_RequestStream != null) { Socket DataConnection; if (_bPassiveMode) { DataConnection = m_DataSocket; } else { DataConnection = m_DataSocket.Accept(); } if (DataConnection == null) { throw new ProtocolViolationException("Accept failed "); } SendData(DataConnection); DataConnection.Close(); //ftpresponse = ReceiveResponse(); ResponseDescription resp = ReceiveCommandResponse(); ftpresponse = new FtpWebResponse(resp.Status, resp.StatusDescription, m_sbControlSocketLog.ToString()); } else { // Data to be send is not specified throw new ApplicationException("Data to be uploaded not specified"); } } else { //Console.WriteLine(resp_desc.StatusDescription); m_Exception = new ApplicationException(ComposeExceptionMessage(resp_desc, m_sbControlSocketLog.ToString())); } CloseDataConnection(); } else if (m_CommandType == FtpCommandType.FtpDataReceiveCommand) { //if(resp_desc.Status/100 == 1) // Positive preliminary reply if (resp_desc.PositivePreliminary) // Positive preliminary reply { Socket DataConnection; if (_bPassiveMode) { DataConnection = m_DataSocket; } else { DataConnection = m_DataSocket.Accept(); } if (DataConnection == null) { throw new ProtocolViolationException("Conexión de datos fallida "); } Stream datastream = ReceiveData(DataConnection); DataConnection.Close(); //ftpresponse = ReceiveResponse(); ResponseDescription resp = ReceiveCommandResponse(); ftpresponse = new FtpWebResponse(resp.Status, resp.StatusDescription, m_sbControlSocketLog.ToString()); ftpresponse.SetDownloadStream(datastream); } else { m_Exception = new ApplicationException(ComposeExceptionMessage(resp_desc, m_sbControlSocketLog.ToString())); } CloseDataConnection(); } else { // // htis is a FtpControlCommand // ftpresponse = new FtpWebResponse(resp_desc.Status, resp_desc.StatusDescription, m_sbControlSocketLog.ToString()); } if (m_Exception != null) { Debug.Assert(ftpresponse == null); throw m_Exception; } return(ftpresponse); }
// 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; }
public async Task TestCloneOnBeforeConnectionOpenAsyncOnConnectionOpenedAsync([DataSources(false)] string context) { var open = 0; var opened = 0; using (var db = new DataConnection(context)) { Assert.AreEqual(0, open); Assert.AreEqual(0, opened); await db.EnsureConnectionAsync(); Assert.AreEqual(0, open); Assert.AreEqual(0, opened); using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { Assert.AreEqual(0, open); Assert.AreEqual(0, opened); await db.EnsureConnectionAsync(); Assert.AreEqual(0, open); Assert.AreEqual(0, opened); } db.Close(); db.OnBeforeConnectionOpenAsync += OnBeforeConnectionOpenAsync; db.OnConnectionOpenedAsync += OnConnectionOpenedAsync; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); await db.EnsureConnectionAsync(); Assert.AreEqual(1, open); Assert.AreEqual(1, opened); using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { open = 0; opened = 0; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); cdb.Connection.Close(); open = 0; opened = 0; await cdb.EnsureConnectionAsync(); Assert.AreEqual(1, open); Assert.AreEqual(1, opened); open = 0; opened = 0; cdb.Close(); db.OnBeforeConnectionOpenAsync -= OnBeforeConnectionOpenAsync; db.OnConnectionOpenedAsync -= OnConnectionOpenedAsync; await cdb.EnsureConnectionAsync(); Assert.AreEqual(1, open); Assert.AreEqual(1, opened); } open = 0; opened = 0; db.Close(); Assert.AreEqual(0, open); Assert.AreEqual(0, opened); await db.EnsureConnectionAsync(); Assert.AreEqual(0, open); Assert.AreEqual(0, opened); using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { Assert.AreEqual(0, open); Assert.AreEqual(0, opened); await cdb.EnsureConnectionAsync(); Assert.AreEqual(0, open); Assert.AreEqual(0, opened); } } Task OnBeforeConnectionOpenAsync(DataConnection dc, IDbConnection cn, CancellationToken ct) { open++; return(Task.CompletedTask); } Task OnConnectionOpenedAsync(DataConnection dc, IDbConnection cn, CancellationToken ct) { opened++; return(Task.CompletedTask); } }
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); conn.Close(); return; } } else { 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 + "', '" + selectedClass[0] + "', '" + selectedClass[1] + "', '" + selectedClass[2] + "', '" + method + "', '" + selectedClass[3] + "','" + time_difference + "')"); } catch { MessageBox.Show("Cannot add visit", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); conn.Close(); return; } } } //cmd.Connection = cn; conn.Close(); /* ======= // try { >>>>>>> origin/Matt8 cn.Open(); cmd.CommandText = "insert into VISIT(DATE, TIME_IN, TIME_OUT, CLARION_ID, TERM, SUBJECT, CATALOG, TUTOR_ID, METHOD, SECTION) values ('" + date + "','" + timeIn + "', '" + timeOut + "', '" + studentID + "', '" + "2138" + "', '" + "LS" + "', '" + "540" + "', '" + tutor + "', '" + method + "', '" + "W01" + "')"; cmd.ExecuteNonQuery(); cmd.Clone(); cn.Close(); <<<<<<< HEAD } */ // catch //{} // } //lblTest.Text = date + "','" + timeIn + "', '" + timeOut + "', '" + studentID + "', '" + method; comboaddClass.Enabled = false; comboAddTutoring.Enabled = false; txtAddStudentID.Text = ""; comboaddClass.Items.Clear(); comboAddTutoring.Items.Clear(); } catch { MessageBox.Show("Information invalid. Please check it and try again."); return; } } else { MessageBox.Show("Invalid Student ID", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MessageBox.Show("Thank you for adding a visit!"); comboAddMethod.SelectedIndex = -1; comboAddTutoring.Enabled = false; comboaddClass.Enabled = false; comboaddClass.SelectedIndex = -1; }
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 btnAddStudent_Click(object sender, EventArgs e) { DataConnection conn = new DataConnection(); conn.Open(); try { conn.Query("insert into STUDENT ( clarion_id,lastname,firstname,cnet_username ) values (" + txtAddID.Text + ",'" + txtAddLast.Text + "','" + txtAddFirst.Text + "'" + "'" + txtAddFirst.Text + txtAddLast.Text + "')"); } catch { if (MessageBox.Show("Entry found with that StudentID. Update existing entry?", "Conflict", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.No) { conn.Close(); return; } conn.Query("update STUDENT set lastname = '" + txtAddLast.Text + "', firstname = '" + txtAddFirst.Text + "', cnet_username = '******' where clarion_id = '" + txtAddID.Text + "'"); } conn.Close(); MessageBox.Show("Entry added."); txtAddID.Clear(); txtAddLast.Clear(); txtAddFirst.Clear(); }
//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(); }
private void ImportStudents() { string last, first, middle, connectionString = ""; OpenFileDialog studentsFile = new OpenFileDialog(); studentsFile.Title = "Import Students"; studentsFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"; studentsFile.RestoreDirectory = true; studentsFile.DefaultExt = "xlsx"; if (studentsFile.ShowDialog() == DialogResult.OK) { if (studentsFile.FileName == "") return; try { connectionString = @"Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + studentsFile.FileName + ";Extended Properties=Excel 12.0 Xml"; } catch { return; }; } else return; this.Cursor = Cursors.WaitCursor; System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(connectionString); string excelQuery = @"Select * from [Export Worksheet$]"; 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(); // Add class names to comboClassList excelReader.Read(); while (excelReader.Read()) { last = excelReader[2].ToString(); last = last.Replace("'", " "); first = excelReader[3].ToString(); first = first.Replace("'", " "); middle = excelReader[4].ToString(); middle = middle.Replace("'", " "); try { conn.Query("insert into STUDENT ( term,clarion_id,lastname,firstname,middle_name,cnet_username,eaglemail,class_standing,degree_seeking,major_1,major_2,minor_1,minor_2,credit_attempted,sex,hispanic,amer_indian,asian,black,pacific_islander,White,age,campus,housing,transfer,transfer_credit,number_of_visit) values ('" + excelReader[0] + "','" + excelReader[1] + "','" + last + "','" + first + "','" + middle + "','" + excelReader[5] + "','" + excelReader[6] + "','" + excelReader[7] + "','" + excelReader[8] + "','" + excelReader[9] + "','" + excelReader[10] + "','" + excelReader[11] + "','" + excelReader[12] + "','" + excelReader[13] + "','" + excelReader[14] + "','" + excelReader[15] + "','" + excelReader[16] + "','" + excelReader[17] + "','" + excelReader[18] + "','" + excelReader[19] + "','" + excelReader[20] + "','" + excelReader[21] + "','" + excelReader[22] + "','" + excelReader[23] + "','" + excelReader[24] + "','" + excelReader[25] + "','" + 0 + "')"); } catch { conn.Query("update STUDENT set term = '" + excelReader[0] + "', lastname = '" + last + "', firstname = '" + first + "',middle_name = '" + middle + "',cnet_username = '******',eaglemail = '" + excelReader[6] + "',class_standing = '" + excelReader[7] + "',degree_seeking = '" + excelReader[8] + "',major_1 = '" + excelReader[9] + "',major_2 = '" + excelReader[10] + "',minor_1 = '" + excelReader[11] + "',minor_2 = '" + excelReader[12] + "',credit_attempted = '" + excelReader[13] + "',sex = '" + excelReader[14] + "',hispanic = '" + excelReader[15] + "',amer_indian = '" + excelReader[16] + "',asian = '" + excelReader[17] + "',black = '" + excelReader[18] + "',pacific_islander = '" + excelReader[19] + "',White = '" + excelReader[20] + "',age = '" + excelReader[21] + "',campus = '" + excelReader[22] + "',housing = '" + excelReader[23] + "',transfer = '" + excelReader[24] + "',transfer_credit = '" + excelReader[25] + "' where clarion_id = '" + excelReader[1] + "'"); } } excelReader.Close(); excelConnection.Close(); conn.Close(); this.Cursor = Cursors.Default; }
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; }
public void TestCloneOnClosingOnClosed([DataSources(false)] string context) { var closing = 0; var closed = 0; using (var db = new DataConnection(context)) { // to enable MARS-enabled cloning branch var _ = db.Connection; Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); db.Close(); Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); _ = db.Connection; using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { _ = cdb.Connection; Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); cdb.Close(); Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); } _ = db.Connection; db.OnClosing += OnClosing; db.OnClosed += OnClosed; Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); db.Close(); Assert.AreEqual(1, closing); Assert.AreEqual(1, closed); _ = db.Connection; using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { closing = 0; closed = 0; _ = cdb.Connection; Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); cdb.Close(); Assert.AreEqual(1, closing); Assert.AreEqual(1, closed); closing = 0; closed = 0; db.OnClosing -= OnClosing; db.OnClosed -= OnClosed; _ = cdb.Connection; cdb.Close(); Assert.AreEqual(1, closing); Assert.AreEqual(1, closed); } closing = 0; closed = 0; _ = db.Connection; Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); db.Close(); Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); _ = db.Connection; using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { _ = cdb.Connection; Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); cdb.Close(); Assert.AreEqual(0, closing); Assert.AreEqual(0, closed); } } void OnClosing(object?sender, EventArgs e) => closing++; void OnClosed(object?sender, EventArgs e) => closed++; }
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); } }
// 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 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 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 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(); }
public void TestCloneOnBeforeConnectionOpenOnConnectionOpened([DataSources(false)] string context) { var open = 0; var opened = 0; using (var db = new DataConnection(context)) { Assert.AreEqual(0, open); Assert.AreEqual(0, opened); var _ = db.Connection; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { Assert.AreEqual(0, open); Assert.AreEqual(0, opened); _ = cdb.Connection; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); } db.Close(); db.OnBeforeConnectionOpen += OnBeforeConnectionOpen; db.OnConnectionOpened += OnConnectionOpened; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); _ = db.Connection; Assert.AreEqual(1, open); Assert.AreEqual(1, opened); using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { open = 0; opened = 0; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); cdb.Connection.Close(); open = 0; opened = 0; _ = cdb.Connection; Assert.AreEqual(1, open); Assert.AreEqual(1, opened); open = 0; opened = 0; cdb.Close(); db.OnBeforeConnectionOpen -= OnBeforeConnectionOpen; db.OnConnectionOpened -= OnConnectionOpened; _ = cdb.Connection; Assert.AreEqual(1, open); Assert.AreEqual(1, opened); } open = 0; opened = 0; db.Close(); Assert.AreEqual(0, open); Assert.AreEqual(0, opened); _ = db.Connection; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); using (var cdb = (DataConnection)((IDataContext)db).Clone(true)) { Assert.AreEqual(0, open); Assert.AreEqual(0, opened); _ = cdb.Connection; Assert.AreEqual(0, open); Assert.AreEqual(0, opened); } } void OnBeforeConnectionOpen(DataConnection dc, IDbConnection cn) => open++; void OnConnectionOpened(DataConnection dc, IDbConnection cn) => opened++; }
// 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; }
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(); }
// 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 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(); }
// 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(); }
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); } }
/////////// public static bool signOut(int studentID, bool isTutor=false) { System.DateTime timein, timenow; System.TimeSpan timedifference; // sign out works for everything but searching for the time out DataConnection conn = new DataConnection(); conn.Open(); SqlDataReader rd = conn.GetReader("time_in, time_out", "visit", "clarion_id", studentID.ToString(), "and time_out is null"); if (rd.HasRows) { rd.Read(); timein = DateTime.Parse( rd[0].ToString()); rd.Close(); timenow = DateTime.Parse(DateTime.Now.ToString("HH:mm:ss tt")); //MessageBox.Show(timenow.ToShortTimeString()); timedifference = timenow.Subtract(timein); if (timedifference < TimeSpan.Zero) { MessageBox.Show("Sign out time is before sign in time. Ask your coordinator if you forgot to sign out yesterday.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); return false; } //MessageBox.Show(DateTime.Now.Subtract(timein).ToString()); conn.Query("update visit set time_out = '" + timenow + "' , time_difference = '" + timedifference + "' where clarion_id= '" + studentID + "' and time_out is null"); conn.Close(); return true; } conn.Close(); if (isTutor) { conn.Open(); rd = conn.GetReader("time_in, time_out", "tutor_hour", "tutor_id", studentID.ToString(), "and time_out is null"); if (rd.HasRows) { rd.Read(); timein = DateTime.Parse(rd[0].ToString()); timenow = DateTime.Now; timedifference = timenow.Subtract(timein); while (rd.Read()) { timein = DateTime.Parse(rd[0].ToString()); } rd.Close(); conn.Close(); conn.Open(); timedifference = (timenow.Subtract(timein)); conn.Query("update tutor_hour set time_out = '" + timenow + "' , time_difference = '" + timedifference + "' where tutor_id= '" + studentID + "' and time_out is null"); conn.Close(); return true; } else { conn.Close(); return false; } } conn.Open(); if (tutorid == -1) tutorid = studentID; rd = conn.GetReader("time_in, time_out", "tutor_hour", "tutor_id", tutorid.ToString(), "and time_out is null"); if (rd.HasRows) { rd.Read(); timein = DateTime.Parse(rd[0].ToString()); timenow = DateTime.Now; timedifference = timenow.Subtract(timein); while (rd.Read()) { timein = DateTime.Parse(rd[0].ToString()); } rd.Close(); conn.Close(); conn.Open(); timedifference = (timenow.Subtract(timein)); conn.Query("update tutor_hour set time_out = '" + timenow + "' , time_difference = '" + timedifference + "' where tutor_id= '" + tutorid + "' and time_out is null"); conn.Close(); return true; } else { conn.Close(); return false; } }
//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"); } }