예제 #1
0
        public static bool insert(Registration_and_Marks reg_and_marks)
        {
            OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);

            string cmdStr = @"INSERT INTO Registration_AND_Marks
                                (Reg_Sess_ID, Course_ID, is_Retake)
                                VALUES
                                (@Reg_Sess_ID, @Course_ID, @is_Retake)";
            
            OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
            cmd.Parameters.Add("@Reg_Sess_ID", OleDbType.Integer).Value = reg_and_marks.Registered_Session_ID;
            cmd.Parameters.Add("@Course_ID", OleDbType.Integer).Value = reg_and_marks.Course.ID;
            cmd.Parameters.Add("@is_Retake", OleDbType.Boolean).Value = reg_and_marks.Marks.IsRetake;
            
            bool successful = false;
            try
            {
                connection.Open();
                cmd.ExecuteNonQuery();
                successful = true;
            }
            finally
            {
                connection.Close();
            }
            return successful;
        }
예제 #2
0
        public static bool delete(Registration_and_Marks reg_and_marks)
        {
            OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);

            string cmdStr = @"DELETE FROM Registration_and_Marks
                            WHERE 0 ";
            /*if (reg_and_marks.marks.MarksID != null)
            {
                cmdStr += @"OR ID = " + reg_and_marks.marks.MarksID;
            }*/
            if (reg_and_marks.Registered_Session_ID != null && reg_and_marks.course.ID != null)
            {
                cmdStr += " OR (reg_sess_ID = " + reg_and_marks.Registered_Session_ID
                            + @" AND course_ID = " + reg_and_marks.course.ID + ")";
            }
            OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
            bool successful = false;
            try
            {
                connection.Open();
                cmd.ExecuteNonQuery();
                successful = true;
            }
            finally
            {
                connection.Close();
            }
            return successful;
        }
예제 #3
0
        public static List<Registration_and_Marks> getAllRegistrationList(int? sessionID, string batch, int? year, int? term, int? courseID, int? stdID)
        {
            OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);

            /*string cmdStr = @"SELECT 
                             [session].ID as SessionID, [session].sessionName,
                              Student.ID as StdID, Student.Batch, Student.Roll, Student.St_Name,
                              Registered_Session.ID as registered_session_ID, Registered_Session.Reg_Year, Registered_Session.Reg_Term,
                              Registration_and_Marks.ID as MarksID, Registration_and_Marks.is_Retake, Registration_and_Marks.Attendence,
                                Registration_and_Marks.CT_or_Viva,Registration_and_Marks.SA_or_SecA, Registration_and_Marks.SecB,
                              Course.ID as CourseID, Course.Course_Prefix, Course.Course_Year, Course.Course_Term,
                                Course.Course_No, Course.Credit, Course.Title, Course.is_Optional
                              FROM [Session],Student,Registered_Session,Registration_and_Marks,Course
                              WHERE 
                             [Session].ID = Registered_Session.Sess_ID AND
                              Student.ID = Registered_Session.Std_ID AND
                              Registered_Session.ID = Registration_and_Marks.Reg_Sess_ID AND
                              Registration_and_Marks.Course_ID = Course.ID";*/

            string cmdStr = @"SELECT 
                             SessionID, sessionName,
                             StdID, Batch, Roll, St_Name,
                             registered_session_ID, Reg_Year, Reg_Term,
                             MarksID, is_Retake, Attendence,CT_or_Viva,SA_or_SecA, SecB,
                             CourseID, Course_Prefix, Course_Year, Course_Term,Course_No,Credit, Title, is_Optional
                             FROM 
                                (SELECT [session].ID as SessionID, [session].sessionName,
                                Student.ID as StdID, Student.Batch, Student.Roll, Student.St_Name,
                                Registered_Session.ID as registered_session_ID, Registered_Session.Reg_Year, Registered_Session.Reg_Term
                                FROM Student INNER JOIN 
                                    ([Session] INNER JOIN Registered_Session ON 
                                        [Session].ID=Registered_Session.Sess_ID) ON 
                                        Student.ID=Registered_Session.Std_ID) AS T1 
                              LEFT JOIN 
                                (SELECT Registration_and_Marks.ID as MarksID, Registration_and_Marks.Reg_Sess_ID, Registration_and_Marks.is_Retake, Registration_and_Marks.Attendence,
                                Registration_and_Marks.CT_or_Viva,Registration_and_Marks.SA_or_SecA, Registration_and_Marks.SecB,
                              Course.ID as CourseID, Course.Course_Prefix, Course.Course_Year, Course.Course_Term,
                                Course.Course_No, Course.Credit, Course.Title, Course.is_Optional 
                                FROM Course INNER JOIN 
                                     Registration_and_Marks ON 
                                        Course.ID=Registration_and_Marks.Course_ID) AS T2 
                              ON T1.registered_session_ID=T2.Reg_Sess_ID
                              WHERE 1 ";

            if (sessionID != null)
            {
                cmdStr += @" AND SessionID = " + sessionID;
            }
            if (batch != null)
            {
                cmdStr += @" AND Batch = '" + batch + "'";
            }
            if (year != null)
            {
                cmdStr += @" AND Reg_Year = " + year;
            }
            if (term != null)
            {
                cmdStr += @" AND Reg_Term = " + term;
            }
            if(courseID != null)
            {
                cmdStr += @" AND CourseID = " + courseID;
            }
            if (stdID != null)
            {
                cmdStr += @" AND StdID = " + stdID;
            }
            cmdStr += @" ORDER BY registered_session_ID, StdID, reg_year, reg_term, course_year desc, course_term desc, course_no, course_prefix";
            OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
            List<Registration_and_Marks> registration_and_MarksList = new List<Registration_and_Marks>();
                
            try
            {
                connection.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Registration_and_Marks regisTration_and_Marks = new Registration_and_Marks();
                    regisTration_and_Marks.Registered_Session_ID = (int)reader.GetValue(reader.GetOrdinal("registered_session_ID"));
                    regisTration_and_Marks.Reg_Year = (int)reader.GetValue(reader.GetOrdinal("Reg_Year"));
                    regisTration_and_Marks.Reg_Term = (int)reader.GetValue(reader.GetOrdinal("Reg_Term"));

                    Session session = new Session();
                    session.SessionID = (int)reader.GetValue(reader.GetOrdinal("SessionID"));
                    session.SessionName = (string)reader.GetValue(reader.GetOrdinal("SessionName"));
                    regisTration_and_Marks.Session = session;

                    Student student = new Student();
                    student.Batch = (string)reader.GetValue(reader.GetOrdinal("Batch"));
                    student.StudentId = (int)reader.GetValue(reader.GetOrdinal("StdID"));
                    student.Roll = (string)reader.GetValue(reader.GetOrdinal("Roll"));
                    student.Name = (string)reader.GetValue(reader.GetOrdinal("St_Name"));
                    regisTration_and_Marks.Student = student;

                    Course course = new Course();
                    try
                    {
                        course.ID = (int?)reader.GetValue(reader.GetOrdinal("CourseID"));
                        course.Prefix = (string)reader.GetValue(reader.GetOrdinal("Course_Prefix"));
                        course.Year = (int?)reader.GetValue(reader.GetOrdinal("Course_Year"));
                        course.Term = (int?)reader.GetValue(reader.GetOrdinal("Course_Term"));
                        course.Course_No = (string)reader.GetValue(reader.GetOrdinal("Course_No"));
                        course.Credit = (decimal?)reader.GetValue(reader.GetOrdinal("Credit"));
                        course.Title = (string)reader.GetValue(reader.GetOrdinal("Title"));
                        course.Is_Optional = (bool?)reader.GetValue(reader.GetOrdinal("is_Optional"));
                        regisTration_and_Marks.Course = course;
                    }
                    catch { }
                    
                    Marks marks = new Marks();
                    try
                    {
                        marks.MarksID = (int?)reader.GetValue(reader.GetOrdinal("MarksID"));
                        marks.IsRetake = (bool?)reader.GetValue(reader.GetOrdinal("is_Retake"));
                        marks.Attendence = (int?)reader.GetValue(reader.GetOrdinal("Attendence"));
                        marks.Ct_or_Viva = (int?)reader.GetValue(reader.GetOrdinal("CT_or_Viva"));
                        marks.SA_or_SecA = (int?)reader.GetValue(reader.GetOrdinal("SA_or_SecA"));
                        marks.SecB = (int?)reader.GetValue(reader.GetOrdinal("SecB"));
                        regisTration_and_Marks.Marks = marks;
                    }
                    catch { }
                    
                    registration_and_MarksList.Add(regisTration_and_Marks);
                }
            }
            finally
            {
                connection.Close();
            }
            return registration_and_MarksList;
        }
예제 #4
0
        public static bool Update(Registration_and_Marks reg_and_marks)
        {
            OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);

            string cmdStr = @"UPDATE Registration_and_Marks
                            SET
                            is_retake = @is_retake,
                            Attendence = @attndence,
                            ct_or_viva = @ct_or_viva,
                            sa_or_secA = @sa_or_secA,
                            secB = @secB
                            WHERE 0 ";
            /*if (reg_and_marks.marks.MarksID != null)
            {
                cmdStr += @"OR ID = " + reg_and_marks.marks.MarksID;
            }*/
            if (reg_and_marks.Registered_Session_ID != null && reg_and_marks.course.ID != null)
            {
                cmdStr += @" OR (reg_sess_ID = " + reg_and_marks.Registered_Session_ID 
                            + @" AND course_ID = " + reg_and_marks.course.ID + ")";
            }
            OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
            cmd.Parameters.Add("@is_retake", OleDbType.Boolean).Value = reg_and_marks.marks.IsRetake;
            cmd.Parameters.Add("@attndence", OleDbType.Integer).Value = reg_and_marks.marks.Attendence;
            cmd.Parameters.Add("@ct_or_viva", OleDbType.Integer).Value = reg_and_marks.marks.Ct_or_Viva;
            cmd.Parameters.Add("@sa_or_secA", OleDbType.Integer).Value = reg_and_marks.marks.SA_or_SecA;
            cmd.Parameters.Add("@secB", OleDbType.Integer).Value = reg_and_marks.marks.SecB;
            bool successful = false;
            try
            {
                connection.Open();
                cmd.ExecuteNonQuery();
                successful = true;
            }
            finally
            {
                connection.Close();
            }
            return successful;
        }
예제 #5
0
        private void InsertButton_Click(object sender, EventArgs e)
        {
            if (RollTextBox.Text.Length != 6)
            {
                MessageBox.Show("Invalid Student ID");
                return;
            }
            if (InsertButton.Text == "Save")  //insert
            {
                RegisteredCoursesAgainstSingleStudent registeredCoursesAgainstSingleStudent = new RegisteredCoursesAgainstSingleStudent();
                registeredCoursesAgainstSingleStudent.Session.SessionID = (int)this.SessionComboBox.SelectedValue;
                registeredCoursesAgainstSingleStudent.Year.YearID = (int)this.YearComboBox.SelectedValue;
                registeredCoursesAgainstSingleStudent.Term.TermID = (int)this.TermComboBox.SelectedValue;
                registeredCoursesAgainstSingleStudent.Student = Student.getStudent(RollTextBox.Text);

                this.sessionID = registeredCoursesAgainstSingleStudent.Session.SessionID;
                this.year = registeredCoursesAgainstSingleStudent.Year.YearID;
                this.term = registeredCoursesAgainstSingleStudent.Term.TermID;
                this.stdID = registeredCoursesAgainstSingleStudent.Student.StudentId;

                List<Registration_and_Marks> courseList = new List<Registration_and_Marks>();
                for(int i=0;i<TakenCoursesDataGridView.RowCount;i++)
                {
                    if ((UpdateType)TakenCoursesDataGridView.Rows[i].Tag == UpdateType.Delete)
                    {
                        TakenCoursesDataGridView.Rows.RemoveAt(i);
                        i--;
                    }
                    else
                    {
                        Registration_and_Marks registration_and_Marks = new Registration_and_Marks();
                        registration_and_Marks.Course.ID = (int)TakenCoursesDataGridView["TakenCoursesCourseIDColumn", i].Value;
                        registration_and_Marks.Marks.IsRetake = (bool)TakenCoursesDataGridView["TakenCoursesIsRetakeColumn", i].Value;
                        courseList.Add(registration_and_Marks);
                        TakenCoursesDataGridView.Rows[i].Tag = UpdateType.None;
                    }
                }
                registeredCoursesAgainstSingleStudent.CourseList = courseList;
                Registered_Session_ID = registeredCoursesAgainstSingleStudent.insert();
                MessageBox.Show("Inserted Successfully");
                InsertButton.Text = "Update";
                CheckRegistrationLabel.Text = registeredCoursesAgainstSingleStudent.Student.Name;
                SessionComboBox.Enabled = false;
                YearComboBox.Enabled = false;
                TermComboBox.Enabled = false;
                RollTextBox.Enabled = false;
                registeredCourseList = RegisteredCoursesAgainstSingleStudent.getCourseList((int?)sessionID, (int?)year, (int?)term, (int?)stdID);
            
            }
            else    //update
            {
                if (Registered_Session_ID != null)
                {
                    int updateFailureCounter = 0;
                    int deleteFailureCounter = 0;
                    int insertFailureCounter = 0;
                    for (int i = 0; i < TakenCoursesDataGridView.RowCount; i++)
                    {
                        if ((UpdateType)TakenCoursesDataGridView.Rows[i].Tag != UpdateType.None)
                        {
                            Registration_and_Marks reg_and_marks = null;
                            for (int j = 0; j < registeredCourseList.Count; j++)
                            {
                                if (registeredCourseList[j].Registered_Session_ID == Registered_Session_ID
                                    && registeredCourseList[j].Course.ID == (int?)TakenCoursesDataGridView["TakenCoursesCourseIDColumn", i].Value)
                                {
                                    reg_and_marks = registeredCourseList[j];
                                }
                            }
                            //reg_and_marks.Registered_Session_ID = Registered_Session_ID;
                            //reg_and_marks.Course.ID = (int?)TakenCoursesDataGridView["TakenCoursesCourseIDColumn", i].Value;
                            if (reg_and_marks == null)
                            {
                                reg_and_marks = new Registration_and_Marks();
                                reg_and_marks.Registered_Session_ID = Registered_Session_ID;
                                //reg_and_marks.Reg_Year = this.year;
                                //reg_and_marks.Reg_Term = this.term;
                                //reg_and_marks.Course = new Course();
                                reg_and_marks.Course.ID = (int)TakenCoursesDataGridView["TakenCoursesCourseIDColumn", i].Value;
                            }
                            reg_and_marks.Marks.IsRetake = (bool?)TakenCoursesDataGridView["TakenCoursesIsRetakeColumn", i].Value;
                                
                            if ((UpdateType)TakenCoursesDataGridView.Rows[i].Tag == UpdateType.Update)
                            {
                                if (Registration_and_Marks.Update(reg_and_marks))
                                    TakenCoursesDataGridView.Rows[i].Tag = UpdateType.None;
                                else
                                    updateFailureCounter++;
                            }
                            else if ((UpdateType)TakenCoursesDataGridView.Rows[i].Tag == UpdateType.Delete)
                            {
                                if (Registration_and_Marks.delete(reg_and_marks))
                                {
                                    TakenCoursesDataGridView.Rows.RemoveAt(i);
                                    i--;
                                }
                                else
                                    deleteFailureCounter++;
                            }
                            else if ((UpdateType)TakenCoursesDataGridView.Rows[i].Tag == UpdateType.Insert)
                            {
                                if (Registration_and_Marks.insert(reg_and_marks))
                                    TakenCoursesDataGridView.Rows[i].Tag = UpdateType.None;
                                else
                                    insertFailureCounter++;
                            }
                        }
                    }
                    if (updateFailureCounter == 0 && deleteFailureCounter == 0 && insertFailureCounter == 0)
                        MessageBox.Show("Updated Successfully");
                    else
                        MessageBox.Show((updateFailureCounter + deleteFailureCounter + insertFailureCounter) + " rows failed to update");
                }
            }
        }