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