public Registration_and_Marks()
{
student = new Student();
marks = new Marks();
course = new Course();
session = new Session();
}
public RegisteredCoursesAgainstSingleStudent()
{
student = new Student();
session = new Session();
year = new Year((int?)null,null);
term = new Term((int?)null, null);
}
private void InsertButton_Click(object sender, EventArgs e)
{
Student s;
int rows = dataGridView1.Rows.Count - 1;
for (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++)
{
if ((UpdateType)dataGridView1.Rows[i].Tag == UpdateType.Insert)
{
s = new Student();
if (makeStudent(dataGridView1.Rows[i], s) == true)
{
if (s.Insert() == true)
{
dataGridView1.Rows.RemoveAt(i);
i--;
}
}
}
else if ((UpdateType)dataGridView1.Rows[i].Tag == UpdateType.Update)
{
int ID = (int)dataGridView1["IDColumn", i].Value;
int j;
for (j = 0; j < studentList.Count; j++)
{
if (studentList[j].StudentId == ID)
break;
}
string studentdID = (string)dataGridView1["StudentIDColumn", i].Value;
bool update = false;
if (studentdID.Length == Depertment.StudentIDLength && studentdID.Substring(0, 2) == studentList[j].Batch && studentdID.Substring(2, 2) == Depertment.DepartmentCode)
{
studentList[j].Roll = studentdID.Substring(4, 2);
studentList[j].Name = (string)dataGridView1["NameColumn", i].Value + " ";
update = studentList[j].Update();
}
if (update)
{
dataGridView1.Rows.RemoveAt(i);
i--;
}
}
else
{
dataGridView1.Rows.RemoveAt(i);
i--;
}
}
if (rows > 0)
{
if (this.dataGridView1.Rows.Count - 1 > 0)
{
MessageBox.Show("Invalid or Duplicate input in " + (this.dataGridView1.Rows.Count - 1) + " rows!\n\n" + (rows - this.dataGridView1.Rows.Count + 1) + " rows Saved successfully.");
}
else
{
MessageBox.Show("Saved successfully.");
}
}
}
private Boolean makeStudent(DataGridViewRow row, Student s)
{
try
{
s.Name = row.Cells["NameColumn"].Value.ToString() + " ";
}
catch { }
try
{
return Student.parseStudentID(row.Cells["StudentIDColumn"].Value.ToString(), s);
}
catch
{
return false;
}
}
public static Boolean parseStudentID(String StdID, Student s)
{
Boolean retValue = false;
if (StdID.Length == 6)
{
s.Batch = StdID.Substring(0, 2);
s.Roll = StdID.Substring(4, 2);
retValue = true;
}
else if (StdID.Length == 2)
{
s.Roll = StdID;
s.Batch = "";
retValue = true;
}
return retValue;
}
public static List<Student> GetStudentList(StudentSearchCriteria sc)
{
//MySqlConnection connection = new MySqlConnection(global::CCMS.Properties.Settings.Default.ccmsConnectionString);
OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);
string cmdStr = @"SELECT student.id, student.Roll, student.batch, student.St_name
FROM student WHERE 1 ";
#region search criteria
if (sc.StudentId != null)
{
cmdStr += " AND student.id = " + sc.StudentId.ToString();
}
if (sc.RollLike != null)
{
cmdStr += " AND student.roll LIKE '%" + sc.RollLike + "%'";
}
if (sc.RollExact != null)
{
cmdStr += " AND student.roll = '" + sc.RollExact + "'";
}
if (sc.Name != null)
{
cmdStr += " AND student.St_name LIKE '%" + sc.Name + "%'";
}
if (sc.Batch != null)
{
cmdStr += " AND student.batch = '" + sc.Batch + "'";
}
cmdStr += " ORDER BY batch,roll";
#endregion
//MySqlCommand cmd = new MySqlCommand(cmdStr, connection);
OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
List<Student> sList = new List<Student>();
try
{
connection.Open();
//MySqlDataReader reader = cmd.ExecuteReader();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Student s = new Student();
#region student
object val;
s.studentId = (int)reader.GetValue(reader.GetOrdinal("id"));
val = reader.GetValue(reader.GetOrdinal("Roll"));
if (val != DBNull.Value)
{
s.roll = (string)val;
}
val = reader.GetValue(reader.GetOrdinal("St_name"));
if (val != DBNull.Value)
{
s.name = (string)val;
}
val = reader.GetValue(reader.GetOrdinal("batch"));
if (val != DBNull.Value)
{
s.batch = (string)val;
}
#endregion
sList.Add(s);
}
}
finally
{
connection.Close();
}
return sList;
}
public static Student getStudent(string batch, string roll)
{
OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);
Student student = null;
string cmdStr = @"SELECT student.id, student.Roll, student.batch, student.St_name
FROM student WHERE
student.batch = @batch AND
student.roll = @roll";
OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
cmd.Parameters.Add("@batch", OleDbType.VarChar).Value = batch;
cmd.Parameters.Add("@roll", OleDbType.VarChar).Value = roll;
try
{
connection.Open();
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
student = new Student();
student.Batch = (string)reader.GetValue(reader.GetOrdinal("batch"));
student.Roll = (string)reader.GetValue(reader.GetOrdinal("Roll"));
student.name = (string)reader.GetValue(reader.GetOrdinal("St_Name"));
student.studentId = (int)reader.GetValue(reader.GetOrdinal("id"));
}
}
finally
{
connection.Close();
}
return student;
}
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;
}
private void StudentWiseStdIDTextBox_KeyUp(object sender, KeyEventArgs e)
{
if (StudentWiseStdIDTextBox.Text.Length != 6)
{
StudentWiseStdNameLabel.ForeColor = Color.Red;
StudentWiseStdNameLabel.Text = "Name: Invalid Student ID";
StudentWisePrintSingleButton.Enabled = false;
student = null;
return;
}
else
{
student = Student.getStudent(StudentWiseStdIDTextBox.Text);
if (student == null)
{
StudentWiseStdNameLabel.ForeColor = Color.Red;
StudentWiseStdNameLabel.Text = "Name: Student ID " + StudentWiseStdIDTextBox.Text + " dosen't Exist";
StudentWisePrintSingleButton.Enabled = false;
}
else
{
StudentWiseStdNameLabel.ForeColor = Color.Green;
StudentWiseStdNameLabel.Text = "Name: " + student.Name;
StudentWisePrintSingleButton.Enabled = true;
}
}
}