public void CreateDatabase(String faculty) { String str; SqlConnection Conn = new SqlConnectionGenerator().MasterDatabaseConnection(); str = "CREATE DATABASE " + faculty; SqlCommand myCommand = new SqlCommand(str, Conn); try { Conn.Open(); myCommand.ExecuteNonQuery(); } catch (System.Exception ex) { } finally { if (Conn.State == ConnectionState.Open) { Conn.Close(); } } CreatejanJulTable(faculty); }
public Boolean DeleteMark(string Faculty, string CourseCode, int StudentId) { SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty); con.Open(); int res = -1; SqlCommand cmd = new SqlCommand("SELECT * FROM " + CourseCode + " WHERE StudentId=" + StudentId, con); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { reader.Close(); cmd = new SqlCommand("DELETE FROM " + CourseCode + " WHERE StudentId=@key AND Submitted=@submitted;", con); cmd.Parameters.AddWithValue("@key", StudentId); cmd.Parameters.AddWithValue("@submitted", false); res = cmd.ExecuteNonQuery(); if (res <= 0) { return(false); } } else { return(false); } } con.Close(); return(true); }
public Boolean DeleteEntries(string[] Ids) { SqlConnection con = new SqlConnectionGenerator().FromFaculty(Ids[0]); con.Open(); int res = -1; for (int i = 2; i < Ids.Length; i++) { try { SqlCommand cmd = new SqlCommand("DELETE FROM " + Ids[1] + " WHERE StudentId=@key AND Submitted=@submitted;", con); cmd.Parameters.AddWithValue("@key", Ids[i]); cmd.Parameters.AddWithValue("@submitted", false); res = cmd.ExecuteNonQuery(); } catch (SqlException ex) { return(false); } } con.Close(); return(true); }
public Boolean SubmitMark(string Faculty, string CourseCode, int StudentId, Boolean what) { SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty); int res = -1; SqlCommand cmd = new SqlCommand("UPDATE " + CourseCode + " SET Submitted=@submitted WHERE StudentId=@id;", con); cmd.Parameters.AddWithValue("@submitted", what); cmd.Parameters.AddWithValue("@id", StudentId); try { con.Open(); res = cmd.ExecuteNonQuery(); if (res <= 0) { return(false); } } catch (SqlException ex) { return(false); } finally { con.Close(); } return(true); }
public Boolean EditMark(string Faculty, string CourseCode, Marks mark) { SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty); int res = -1; SqlCommand cmd = new SqlCommand("UPDATE " + CourseCode + " SET Mid=@mid, Attendence=@attendence, Assignment=@assignment, Final=@final WHERE StudentId=@id;", con); cmd.Parameters.AddWithValue("@mid", mark.Mid); cmd.Parameters.AddWithValue("@attendence", mark.Attendence); cmd.Parameters.AddWithValue("@assignment", mark.Assignment); cmd.Parameters.AddWithValue("@final", mark.Final); cmd.Parameters.AddWithValue("@id", mark.StudentId); try { con.Open(); res = cmd.ExecuteNonQuery(); if (res <= 0) { return(false); } } catch (SqlException ex) { return(false); } finally { con.Close(); } return(true); }
public Marks GetMark(string Faculty, string CoursCode, int StudentId) { Marks mark = new Marks(); SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty); con.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM " + CoursCode + " WHERE StudentId=" + StudentId, con); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { mark.StudentId = Convert.ToInt32(reader.GetValue(0)); mark.RegNo = Convert.ToInt32(reader.GetValue(1)); mark.Mid = Convert.ToSingle(reader.GetValue(2)); mark.Attendence = Convert.ToSingle(reader.GetValue(3)); mark.Assignment = Convert.ToSingle(reader.GetValue(4)); mark.Final = Convert.ToSingle(reader.GetValue(5)); mark.Submitted = Convert.ToBoolean(reader.GetValue(6)); reader.Close(); } else { return(null); } } con.Close(); return(mark); }
public static List <CurrentSemester> GetCurrentSemesterStudents(string Faculty, string Session, int Semester) { int batchId = Convert.ToInt32(Session.Substring(2, 2)); //startIndex, Length int startRange = batchId * 100000; int endRange = ((batchId + 1) * 100000) - 1; List <CurrentSemester> AllStudents = new List <CurrentSemester>(); SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty); con.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM CurrentSemester WHERE StudentId BETWEEN " + startRange + " AND " + endRange + " AND Semester=" + Semester + ";", con); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { CurrentSemester CS = new CurrentSemester(); CS.StudentId = Convert.ToInt32(reader.GetValue(1)); CS.Semester = Convert.ToInt32(reader.GetValue(2)); AllStudents.Add(CS); } reader.Close(); } else { return(null); } } con.Close(); return(AllStudents); }
public string InserMarkList(List <Marks> Marks, string Faculty, string CourseCode) { string content = Faculty + "," + CourseCode; try { SqlConnection con = new SqlConnectionGenerator().FromFaculty(Faculty); con.Open(); int res = -1; foreach (Marks Mark in Marks) { content += "," + Mark.StudentId; SqlCommand cmd = new SqlCommand("SELECT * FROM " + CourseCode + " WHERE StudentId=" + Mark.StudentId, con); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { cmd = new SqlCommand("UPDATE " + CourseCode + " SET Final=" + Mark.Final + " WHERE StudentId=@studentId AND Submitted=@submitted;", con); cmd.Parameters.AddWithValue("@studentId", Mark.StudentId); cmd.Parameters.AddWithValue("@submitted", false); } else { cmd = new SqlCommand("INSERT INTO " + CourseCode + " VALUES(@StudentId, @RegNo, @Mid, @Attendence, @Assignment, @Final, @Submitted)", con); cmd.Parameters.AddWithValue("@StudentId", Mark.StudentId); cmd.Parameters.AddWithValue("@RegNo", Mark.RegNo); cmd.Parameters.AddWithValue("@Mid", Mark.Mid); cmd.Parameters.AddWithValue("@Attendence", Mark.Attendence); cmd.Parameters.AddWithValue("@Assignment", Mark.Assignment); cmd.Parameters.AddWithValue("@Final", Mark.Final); cmd.Parameters.AddWithValue("@Submitted", false); } } res = cmd.ExecuteNonQuery(); } con.Close(); if (res <= 0) { return(Messasges.InsertionFailed); } } catch (SqlException ex) { return(Messasges.InsertionFailed + " " + ex.ToString()); } return(content); }
public static List <Course> GetEnrollCourses(string Faculty, string Session, int Semester) { ProjectDB db = new ProjectDB(); List <Course> Courses = new List <Course>(); string tableName = (Semester % 2 == 0) ? "JulEnrollment" : "JanEnrollment"; SqlConnection myConn = new SqlConnectionGenerator().FromFaculty(Faculty); string SQLCmd = "select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'"; // * can be column_name, data_type, column_default, is_nullable SqlCommand myCommand = new SqlCommand(SQLCmd, myConn); try { myConn.Open(); SqlDataReader oReader = myCommand.ExecuteReader(); while (oReader.Read()) { string key = Convert.ToString(oReader.GetValue(0)); if (!(key.Equals("id") || key.Equals("Name") || key.Equals("StudentId") || key.Equals("RegNo") || key.Equals("Session") || key.Equals("Semester"))) { Course Course = db.Courses.Where(c => c.Course_code == key).FirstOrDefault(); if (Course != null) { int ObSem = Course.GetSemesterFromCourse(Course.Course_code); if (ObSem == Semester) { Courses.Add(Course); } } } } } catch (System.Exception ex) { } finally { if (myConn.State == ConnectionState.Open) { myConn.Close(); } } return(Courses); }
public List <Marks> GetMarkList(string faculty, string course_code, string session) { int batchId = Convert.ToInt32(session.Substring(2, 2)); //startIndex, Length int startRange = batchId * 100000; int endRange = ((batchId + 1) * 100000) - 1; SqlConnection con = new SqlConnectionGenerator().FromFaculty(faculty); con.Open(); List <Marks> marks = new List <Marks>(); SqlCommand cmd = new SqlCommand("SELECT * FROM " + course_code + " WHERE StudentId BETWEEN " + startRange + " AND " + endRange + ";", con); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Marks mark = new Marks(); mark.StudentId = Convert.ToInt32(reader.GetValue(0)); mark.RegNo = Convert.ToInt32(reader.GetValue(1)); mark.Mid = Convert.ToSingle(reader.GetValue(2)); mark.Attendence = Convert.ToSingle(reader.GetValue(3)); mark.Assignment = Convert.ToSingle(reader.GetValue(4)); mark.Final = Convert.ToSingle(reader.GetValue(5)); mark.Submitted = Convert.ToBoolean(reader.GetValue(6)); marks.Add(mark); } reader.Close(); } else { return(null); } } con.Close(); return(marks); }