public bool Insert2(Course c)
{
try
{
string query = "INSERT into CourseTable VALUES ('" + c.CourseId + "', '" + c.Name + "', '" + c.Semester + "', '" + c.Department + "', '" + c.Section + "', '" + c.MaxStudent + "', '" + c.CurrentStudent + "', '" + c.Credit + "', '" + c.Schedule + "')";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch
{
return(false);
}
}
public string GetOnlySchedule(string semester, string courseName, string section)
{
string query = "SELECT * from CourseTable WHERE Name = '" + courseName + "' AND Semester = '" + semester + "' AND Section='" + section + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
string schedule = "";
if (sdr.Read())
{
schedule = sdr["Schedule"].ToString();
}
dcc.CloseConnection();
return(schedule);
}
public bool Delete2(string CourseId)
{
try
{
string query = "DELETE from CourseTable WHERE CourseId = '" + CourseId + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch
{
return(false);
}
}
public bool Update2(Course c, string NewCourseId)
{
try
{
string query = " UPDATE CourseTable SET CourseId = '" + NewCourseId + "', Name = '" + c.Name + "', Semester = '" + c.Semester + "', Department = '" + c.Department + "', Section = '" + c.Section + "', MaxStudent = '" + c.MaxStudent + "', CurrentStudent = '" + c.CurrentStudent + "', Credit = '" + c.Credit + "', Schedule = '" + c.Schedule + "' WHERE CourseId = '" + c.CourseId + "' ";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch
{
return(false);
}
}
public bool Increase2(string s)
{
try
{
string query = "UPDATE CourseTable SET CurrentStudent =CurrentStudent+1 WHERE CourseId='" + s + "' ";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch
{
return(false);
}
}
public bool Save_Password(Login l, string pass)
{
string query = "UPDATE Login SET Password = '******' WHERE Id = '" + l.Id + "'";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
dcc.CloseConnection();
return(false);
}
}
public Login GetUser(string id, string password)
{
Login log = null;
string query = "SELECT * from Actors WHERE Aid = '" + id + "' AND Apass ='******'";
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
log = new Login();
log.Id = sdr["Aid"].ToString();
log.Password = sdr["Apass"].ToString();
log.IsAdmin = Convert.ToInt32(sdr["IsAdmin"]);
}
dcc.CloseConnection();
return(log);
}
public bool CheckIsRegistrationOpen2(string SemsesterName)
{
string query = "SELECT * from SemesterTable WHERE Name = '" + SemsesterName + "' AND RegistrationStatus = 'Open'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
dcc.CloseConnection();
if (sdr.Read())
{
return(true);
}
else
{
return(false);
}
}
public Semester GetSemester2(string SemesterId)
{
string query = "SELECT * from SemesterTable WHERE SemesterId = '" + SemesterId + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
Semester s = new Semester();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
s.SemesterId = sdr["SemesterId"].ToString();
s.Name = sdr["Name"].ToString();
s.RegistrationStatus = sdr["RegistrationStatus"].ToString();
}
dcc.CloseConnection();
return(s);
}
public bool addtoChart_Method(Medicine m, string quantity)
{
SaleMedicine sl;
DateTime d = new DateTime();
int u = m.Quantity;
int q = int.Parse(quantity);
int k = u - q;
quantityOfMdcn = k.ToString();
string query = "INSERT into addToChart values('" + m.MadId + "', '" + m.MadName + "', '" + m.MadCompanyname + "', '" + m.ManufacturingDate + "', '" + m.Expiringdate + "', '" + m.StockDate + "', " + m.Price + " , " + quantity + ", '" + d + "')";
sl = new SaleMedicine();
sl.MadId = m.MadId;
sl.MadName = m.MadName;
sl.MadCompanyname = m.MadCompanyname;
sl.ManufacturingDate = m.ManufacturingDate;
sl.Expiringdate = m.Expiringdate;
sl.StockDate = m.StockDate;
sl.Price = m.Price;
sl.Quantity = int.Parse(quantity);
sl.SaleDate = d.ToString();
listToSale.Add(sl);
m.Quantity = k;
listToUpdateMedicin.Add(m);
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
dcc.CloseConnection();
return(false);
}
finally { dcc.CloseConnection(); }
}
public List <Semester> GetAllSemesters()
{
string query = "SELECT * from SemesterTable";
List <Semester> sList = new List <Semester>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Semester s = new Semester();
s.SemesterId = sdr["SemesterId"].ToString();
s.Name = sdr["Name"].ToString();
sList.Add(s);
}
dcc.CloseConnection();
return(sList);
}
public bool CheckAssignedCourse2(CourseAssign s)
{
string query = "SELECT * from CourseAssignTable WHERE AccountId = '" + s.AccountId + "' AND AssignedCourse = '" + s.AssignedCourse + "' AND Section = '" + s.Section + "' AND Semester = '" + s.Semester + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
dcc.CloseConnection();
return(false);
}
else
{
dcc.CloseConnection();
return(true);
}
}
public List <Chat> GetMsg(string id)
{
string query = "SELECT * from ChatTable WHERE AccountId= '" + id + "'";
List <Chat> cList = new List <Chat>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Chat ch = new Chat();
//ch.AccountId = sdr["AccountId"].ToString();
ch.Msg = sdr["Msg"].ToString();
cList.Add(ch);
}
dcc.CloseConnection();
return(cList);
}
public bool Insert(Admin ad)
{
try
{
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
string query = "INSERT into Admin VALUES ('" + ad.AId + "', '" + ad.AName + "','" + ad.APassword + "', '" + ad.APhone + "', " + ad.AEmail + ", '" + ad.ASal + "','" + ad.APicture + "')";
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public CourseRegistration GetCourse2(string CourseId)
{
string query = "SELECT * from CourseTable WHERE CourseId = '" + CourseId + "'";
CourseRegistration c = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
c = new CourseRegistration();
c.CourseId = sdr["CourseId"].ToString();
c.CourseName = sdr["Name"].ToString();
c.Semester = sdr["Semester"].ToString();
c.Section = sdr["Section"].ToString();
}
dcc.CloseConnection();
return(c);
}
public Login GetUser(string id, string password)
{
Login l = null;
string query = "SELECT * from Login WHERE Id = '" + id + "' AND Password = '******'";
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
l = new Login();
l.Id = sdr["Id"].ToString();
l.Password = sdr["Password"].ToString();
l.Role = Convert.ToInt32(sdr["Role"]);
}
dcc.CloseConnection();
return(l);
}
public List <Teacher> GetAllTeacherInfo2()
{
string query = "SELECT * from TeacherTable";
List <Teacher> cList = new List <Teacher>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Teacher t = new Teacher();
t.AccountId = sdr["AccountId"].ToString();
t.WeekHour = Convert.ToInt32(sdr["WeekHour"]);
t.Status = sdr["Status"].ToString();
t.Salary = Convert.ToDouble(sdr["Salary"]);
cList.Add(t);
}
dcc.CloseConnection();
return(cList);
}
public List <Note> GetAllNotes(string semester, string courseName)
{
string query = "SELECT * from NoteTable WHERE Semester= '" + semester + "' AND CourseName= '" + courseName + "'";
List <Note> nList = new List <Note>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Note n = new Note();
n.CourseName = sdr["CourseName"].ToString();
n.NotePath = sdr["NotePath"].ToString();
n.Semester = sdr["Semester"].ToString();
n.Section = sdr["Section"].ToString();
nList.Add(n);
}
dcc.CloseConnection();
return(nList);
}
public List <CourseAssign> GetAllAssignedCourses2()
{
string query = "SELECT * from CourseAssignTable";
List <CourseAssign> sList = new List <CourseAssign>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
CourseAssign s = new CourseAssign();
s.AccountId = sdr["AccountId"].ToString();
s.AssignedCourse = sdr["AssignedCourse"].ToString();
s.Semester = sdr["Semester"].ToString();
s.Section = sdr["Section"].ToString();
sList.Add(s);
}
dcc.CloseConnection();
return(sList);
}
public List <CourseAssign> GetAllSections(string accountId, string semseter)
{
string query = "SELECT * from CourseAssignTable WHERE AccountId='" + accountId + "' AND Semester='" + semseter + "'";
List <CourseAssign> cList = new List <CourseAssign>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
CourseAssign c = new CourseAssign();
c.AccountId = sdr["AccountId"].ToString();
c.AssignedCourse = sdr["AssignedCourse"].ToString();
c.Semester = sdr["Semester"].ToString();
c.Section = sdr["Section"].ToString();
cList.Add(c);
}
dcc.CloseConnection();
return(cList);
}
public List <Course> GetAllCourse2(string accountid, string sname)
{
string query = "SELECT Name,Schedule from CourseTable WHERE CourseId IN (SELECT CourseId from CourseRegistrationTable WHERE AccountId='" + accountid + "' AND Semester='" + sname + "')";
List <Course> aList = new List <Course>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Course a = new Course();
a.Name = sdr["Name"].ToString();
a.Schedule = sdr["Schedule"].ToString();
aList.Add(a);
}
dcc.CloseConnection();
return(aList);
}
public List <Notice> GetAllNotice(string semester, string courseId, string section)
{
string query = "SELECT * from NoticeTable WHERE Semester= '" + semester + "' AND CourseId= '" + courseId + "' AND Section= '" + section + "'";
List <Notice> cList = new List <Notice>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Notice c = new Notice();
c.CourseId = sdr["CourseId"].ToString();
c.Semester = sdr["Semester"].ToString();
c.Section = sdr["Section"].ToString();
c.Noticee = sdr["Notice"].ToString();
c.Date = sdr["Date"].ToString();
cList.Add(c);
}
dcc.CloseConnection();
return(cList);
}
public List <string> GetAccountIdsScholarshipApprove2()
{
string query = "SELECT * from StudentBalanceTable WHERE Scholarship = 'Eligible For Scholarship'";
List <string> sList = new List <string>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
StudentBalance s = new StudentBalance();
s.AccountId = sdr["AccountId"].ToString();
s.Semester = sdr["Semester"].ToString();
s.Scholarship = sdr["Scholarship"].ToString();
s.Balance = Convert.ToDouble(sdr["Balance"]);
s.Validation = sdr["Validation"].ToString();
sList.Add(s.AccountId);
}
dcc.CloseConnection();
return(sList);
}
public bool RoutineVerification2(string accountid, string sname)
{
string query = "SELECT CourseId from CourseRegistrationTable WHERE AccountId='" + accountid + "' AND Semester='" + sname + "'";
Course a = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
a = new Course();
dcc.CloseConnection();
return(true);
}
else
{
dcc.CloseConnection();
return(false);
}
}
public List <string> GetOnlySection(string semester, string ID, string course)
{
string query = "SELECT * from CourseRegistrationTable WHERE Semester= '" + semester + "' AND AccountId= '" + ID + "'AND CourseName= '" + course + "'";
List <string> cList = new List <string>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
CourseRegistration c = new CourseRegistration();
c.CourseId = sdr["CourseId"].ToString();
c.CourseName = sdr["CourseName"].ToString();
c.Section = sdr["Section"].ToString();
c.AccountId = sdr["AccountId"].ToString();
c.Status = sdr["Status"].ToString();
cList.Add(c.Section);
}
dcc.CloseConnection();
return(cList);
}
public List <Doctor> GetDoctors()
{
List <Doctor> listofDocs = new List <Doctor>();
string query = "SELECT * FROM Doctor";
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Doctor doc = new Doctor();
doc.DocId = sdr["Did"].ToString();
doc.DocName = sdr["Dname"].ToString();
doc.DocEmail = sdr["Demail"].ToString();
doc.Specialist = sdr["Speciality"].ToString();
doc.Time = sdr["Time"].ToString();
listofDocs.Add(doc);
}
dcc.CloseConnection();
return(listofDocs);
}
public string UserLoginVerification2(Account a)
{
string query = "SELECT * from AccountTable WHERE AccountId= '" + a.AccountId + "' AND Password= '******'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
//a.Name = sdr["AccountName"].ToString();
//a.Gender = Convert.ToDouble(sdr["Salary"]);
//a.Address = sdr["Designation"].ToString();
a.AccountType = sdr["AccountType"].ToString();
dcc.CloseConnection();
return(a.AccountType);
}
else
{
dcc.CloseConnection();
return("false");
}
}
public List <CourseRegistration> GetAllCourses2()
{
string query = "SELECT * from CourseRegistrationTable";
List <CourseRegistration> cList = new List <CourseRegistration>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
CourseRegistration c = new CourseRegistration();
c.CourseId = sdr["CourseId"].ToString();
c.CourseName = sdr["CourseName"].ToString();
c.Semester = sdr["Semester"].ToString();
c.Section = sdr["Section"].ToString();
c.AccountId = sdr["AccountId"].ToString();
c.Status = sdr["Status"].ToString();
cList.Add(c);
}
dcc.CloseConnection();
return(cList);
}
public bool CheckExamCourseId2(string CourseId)
{
string query = "SELECT * from ExamTable WHERE CourseId = '" + CourseId + "'";
Exam e = new Exam();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
e = new Exam();
e.CourseId = sdr["CourseId"].ToString();
e.Semester = sdr["Semester"].ToString();
e.Day = sdr["Day"].ToString();
e.Slot = sdr["Slot"].ToString();
e.AssignedFacultyId = sdr["AssignedFacultyId"].ToString();
e.Room = sdr["Room"].ToString();
return(true);
}
dcc.CloseConnection();
return(false);
}
public List <Exam> GetAllExams2()
{
string query = "SELECT * from ExamTable";
List <Exam> eList = new List <Exam>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Exam e = new Exam();
e.CourseId = sdr["CourseId"].ToString();
e.Semester = sdr["Semester"].ToString();
e.Day = sdr["Day"].ToString();
e.Slot = sdr["Slot"].ToString();
e.Room = sdr["Room"].ToString();
e.AssignedFacultyId = sdr["AssignedFacultyId"].ToString();
eList.Add(e);
}
dcc.CloseConnection();
return(eList);
}
