public static int KiemTraTonTai(KhachHangDTO temp) { int kq = 0; conn = DataProvider.OpenConnection(); string sTruyVan3 = "select * from KhachHang where tenDangNhap = '" + temp.TenDangNhap + "' "; SqlCommand cmd3 = new SqlCommand(sTruyVan3, conn); cmd3.Connection = conn; cmd3.ExecuteNonQuery(); DataTable dt3 = DataProvider.GetDataTable(sTruyVan3, conn); if (dt3.Rows.Count > 0) { kq = 4; } string sTruyVan = "select * from KhachHang where soCMND = '" + temp.SoCMND + "' "; SqlCommand cmd = new SqlCommand(sTruyVan, conn); cmd.ExecuteNonQuery(); DataTable dt = DataProvider.GetDataTable(sTruyVan, conn); if (dt.Rows.Count > 0) { kq = 1; } string sTruyVan1 = "select * from KhachHang where email = '" + temp.Email + "' "; SqlCommand cmd1 = new SqlCommand(sTruyVan1, conn); cmd1.ExecuteNonQuery(); DataTable dt1 = DataProvider.GetDataTable(sTruyVan1, conn); if (dt1.Rows.Count > 0) { kq = 2; } string sTruyVan2 = "select * from KhachHang where soDienThoai = '" + temp.SoDienThoai + "' "; SqlCommand cmd2 = new SqlCommand(sTruyVan2, conn); cmd2.Connection = conn; cmd2.ExecuteNonQuery(); DataTable dt2 = DataProvider.GetDataTable(sTruyVan2, conn); if (dt2.Rows.Count > 0) { kq = 3; } DataProvider.CloseConnection(conn); return(kq); }
public static int getSumStudent(string nameClass, string schoolYear) { string sCommand = @"Select IDStudent from Student_Class where nameClass ='" + nameClass + "' and schoolYear ='" + schoolYear + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); return(dt.Rows.Count); }
public DataTable XemThongTinLichTrinhCaNhan(int maTaiXe) { dtp = new DataProvider(); dt = new DataTable(); string sqlSelect = string.Format(@"SELECT * FROM LichTrinh WHERE LichTrinh.TaiXe = {0}", maTaiXe); dt = dtp.GetDataTable(sqlSelect); return(dt); }
// Xem điểm của học sinh public static DataTable LoadMark(string id) { string sTruyVan = @"select a.NameSubject, b.Semester, b.FirstFifteenMinutes, b.SecondFifteenMinutes, b.ThirdFifteenMinutes, b.FirstFortyFiveMinutes, b.SecondFortyFiveMinutes, b.ThirdFortyFiveMinutes, b.SemesterMark, b.nameClass, b.schoolYear from Mark b join Subject a on b.IDSubject = a.IDSubject where b.IDStudent='" + id + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sTruyVan, con); DataProvider.CloseConnection(con); return(dt); }
public static DataTable LoadDuLieuLoaiPhong(string s) { string sTruyVan = "select * from LoaiPhong where maKS = (select maKS from NhanVien where tenDangNhap= '" + s + "') "; conn = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sTruyVan, conn); DataProvider.CloseConnection(conn); return(dt); }
//xem thông tin giáo viên từ db public static DataTable LoadInfo(string idTeacher) { string sCommand = @"Select * from Teacher where Teacher.IDTeacher = N'" + idTeacher + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); DataProvider.CloseConnection(con); return(dt); }
// Xem điểm sinh viên theo lớp, theo môn và theo năm học, theo học kỳ public static DataTable LoadMark(string className, string nameSubject, string schoolYear, string Semester) { string sCommand = @"Select Student.IDStudent, Student.Name, Mark.FirstFifteenMinutes, Mark.SecondFifteenMinutes, Mark.ThirdFifteenMinutes, Mark.FirstFortyFiveMinutes, Mark.SecondFortyFiveMinutes, Mark.ThirdFortyFiveMinutes, Mark.SemesterMark from Mark join Student on Mark.IDStudent = Student.IDStudent join Subject on Mark.IDSubject = Subject.IDSubject where Mark.nameClass = '" + className + @"'and Mark.schoolYear = '" + schoolYear + @"'and Subject.NameSubject = N'" + nameSubject + @"'and Mark.Semester = '" + Semester + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); DataProvider.CloseConnection(con); return(dt); }
public static List <PeopleDTO> loadListStudent(string status) { string sCommand = ""; if (status == "System.Windows.Controls.ComboBoxItem: Active") { sCommand = @"Select* from Student where isActive = 'T'"; } else if (status == "System.Windows.Controls.ComboBoxItem: Deactive") { sCommand = @"Select* from Student where isActive = 'F'"; } else { sCommand = @"Select* from Student"; } con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(null); } else { List <PeopleDTO> result = new List <PeopleDTO>(); int n = dt.Rows.Count; for (int i = 0; i < n; i++) { PeopleDTO student = new PeopleDTO(); student.Id = dt.Rows[i]["IDStudent"].ToString(); student.Name = dt.Rows[i]["Name"].ToString(); student.Gender = dt.Rows[i]["Gender"].ToString(); student.Email = dt.Rows[i]["Email"].ToString(); student.DateofBith = dt.Rows[i]["BirthDay"].ToString(); student.Password = dt.Rows[i]["Password"].ToString(); student.Phone = dt.Rows[i]["Phone"].ToString(); student.Type = "Student"; if (dt.Rows[i]["isActive"].ToString() == "T") { student.Status = "Active"; } else { student.Status = "Deactive"; } result.Add(student); } DataProvider.CloseConnection(con); return(result); } }
public static int getCurrentStudent(string nameClass, string schoolYear) { string sCommand = string.Format(@"Select Count(Student.IDStudent) from Student_Class join Student on (Student_Class.IDStudent = Student.IDStudent) where nameClass = '{0}' and schoolYear = '{1}' and Student.isActive = 'T'", nameClass, schoolYear); con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { return(0); } return(int.Parse(dt.Rows[0][0].ToString())); }
public static int getTotalStudent() { string sCommand = @"Select totalStudent from Role"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { return(0); } return(int.Parse(dt.Rows[0]["totalStudent"].ToString())); }
public static bool isMaster(string id, string nameClass, string schoolYear) { string sCommand = @"Select* from Class where IDMaster ='" + id + "' and nameClass = '" + nameClass + "' and schoolYear ='" + schoolYear + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { return(false); } return(true); }
public static int getPassScore() { string sCommand = @"Select passScore from Role"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { return(0); } return(int.Parse(dt.Rows[0]["passScore"].ToString())); }
// Giao vien bo mon xem diem cua hoc sinh do giao vien do day public static DataTable LoadMark(string idteacher) { string sTruyVan = @"select c.NameSubject, e.Name, d.nameClass, d.schoolYear, d.Semester, d.FirstFifteenMinutes, d.SecondFifteenMinutes, d.ThirdFifteenMinutes, d.FirstFortyFiveMinutes, d.SecondFortyFiveMinutes, d.ThirdFortyFiveMinutes, d.SemesterMark, d.IDStudent, d.IDSubject from Teacher a join Teacher_Subject b on a.IDTeacher = b.IDTeacher join Subject c on c.IDSubject = b.IDSubject join Mark d on d.IDSubject = b.IDSubject and d.IDTeacher = b.IDTeacher join Student e on d.IDStudent = e.IDStudent where a.IDTeacher= '" + idteacher + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sTruyVan, con); return(dt); }
public static List <StudentDTO> LoadStudent(string nameClass, string schoolYear, string status) { string sCommand = ""; if (status == "System.Windows.Controls.ComboBoxItem: Active") { sCommand = @"Select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where SC.nameClass = '" + nameClass + @"' and SC.schoolYear = '" + schoolYear + "' and S.IsActive = 'T'"; } else if (status == "System.Windows.Controls.ComboBoxItem: Deactive") { sCommand = @"Select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where SC.nameClass = '" + nameClass + @"' and SC.schoolYear = '" + schoolYear + "' and S.IsActive = 'F'"; } else { sCommand = @"Select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where SC.nameClass = '" + nameClass + @"' and SC.schoolYear = '" + schoolYear + "'"; } con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count == 0) { return(null); } List <StudentDTO> result = new List <StudentDTO>(); for (int i = 0; i < dt.Rows.Count; i++) { StudentDTO student = new StudentDTO(); student.Id = dt.Rows[i]["IDStudent"].ToString(); student.Name = dt.Rows[i]["Name"].ToString(); student.Gender = dt.Rows[i]["Gender"].ToString(); student.Email = dt.Rows[i]["Email"].ToString(); student.Phone = dt.Rows[i]["Phone"].ToString(); student.DateofBith = dt.Rows[i]["BirthDay"].ToString(); student.NameClass = dt.Rows[i]["nameClass"].ToString(); student.SchoolYear = dt.Rows[i]["schoolYear"].ToString(); if (dt.Rows[i]["isActive"].ToString() == "T") { student.Status = "Active"; } else { student.Status = "Deactive"; } result.Add(student); } DataProvider.CloseConnection(con); return(result); }
public static string getNameStudent(string idStudent) { string sCommand = @"Select Name from Student where IDStudent = '" + idStudent + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { return(null); } else { return(dt.Rows[0]["Name"].ToString()); } }
public static bool isMaster(string IDTeacher, string schoolYear) { string sCommand = @"select* from Class where IDMaster = '" + IDTeacher + "' and schoolYear ='" + schoolYear + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(false); } else { return(true); } }
// Tìm sinh viên theo ID, trả về ID sinh viên public static string FindStudentByID(string ID) { string sCommand = @"Select IDStudent from Student where IDStudent = '" + ID + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count == 0) { return(null); } string result; result = dt.Rows[0][0].ToString(); DataProvider.CloseConnection(con); return(result); }
public static List <string> getNameClassWithIDStudent(string IDStudent) { string sCommand = string.Format(@"Select nameClass from Student_Class where IDStudent = '{0}'", IDStudent); con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { return(null); } List <string> result = new List <string>(); int n = dt.Rows.Count; for (int i = 0; i < n; i++) { result.Add(dt.Rows[i][0].ToString()); } return(result); }
public static List <StudentDTO> searchStudentNotInClass(string textToSearch, string schoolYear) { string sCommand = @"select distinct S.* from Student S, Student_Class SC where S.IDStudent in (select IDStudent from Student) and S.IDStudent not in (select IDStudent from Student_Class) and SC.schoolYear ='" + schoolYear + "' and ((S.IDStudent like '%" + textToSearch + "%') or (S.Name like N'%" + textToSearch + "%'))"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(null); } int n = dt.Rows.Count; List <StudentDTO> result = new List <StudentDTO>(); for (int i = 0; i < n; i++) { StudentDTO student = new StudentDTO(); student.Id = dt.Rows[i]["IDStudent"].ToString(); student.Name = dt.Rows[i]["Name"].ToString(); student.Gender = dt.Rows[i]["Gender"].ToString(); student.Email = dt.Rows[i]["Email"].ToString(); student.Phone = dt.Rows[i]["Phone"].ToString(); student.DateofBith = dt.Rows[i]["BirthDay"].ToString(); if (dt.Rows[i]["isActive"].ToString() == "T") { student.Status = "Active"; } else { student.Status = "Deactive"; } result.Add(student); } DataProvider.CloseConnection(con); return(result); }
public static int KiemTraTonTai(KhachHangDTO temp) { int kq = 0; conn = DataProvider.OpenConnection(); string sTruyVan3 = "select * from KhachHang where soDienThoai = '" + temp.SoDienThoai + "' "; SqlCommand cmd3 = new SqlCommand(sTruyVan3, conn); cmd3.Connection = conn; cmd3.ExecuteNonQuery(); DataTable dt3 = DataProvider.GetDataTable(sTruyVan3, conn); if (dt3.Rows.Count > 0) { kq = 1; } DataProvider.CloseConnection(conn); return(kq); }
// Tìm sinh viên theo tên, trả về ID sinh viên public static List <string> FindStudentByName(string nameStudent) { string sCommand = @"Select IDStudent from Student where Name = N'" + nameStudent + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count == 0) { return(null); } List <string> result = new List <string>(); for (int i = 0; i < dt.Rows.Count; i++) { string id = dt.Rows[0][0].ToString(); result.Add(id); } DataProvider.CloseConnection(con); return(result); }
public static List <string> loadSchoolYear() { string sCommand = @"Select distinct schoolYear from dbo.Class"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count == 0) { return(null); } List <string> result = new List <string>(); for (int i = 0; i < dt.Rows.Count; i++) { string schoolyear = dt.Rows[i]["schoolYear"].ToString(); result.Add(schoolyear); } DataProvider.CloseConnection(con); return(result.Distinct().ToList()); }
public static List <StudentDTO> searchStudent(string textToSearch, string nameClass, string schoolYear) { string sCommand = @"Select* from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where ((S.Name like N'%" + textToSearch + "%') or (SC.IDStudent like '%" + textToSearch + "%')) and SC.nameClass = '" + nameClass + "' and SC.schoolYear = '" + schoolYear + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count == 0) { return(null); } List <StudentDTO> result = new List <StudentDTO>(); for (int i = 0; i < dt.Rows.Count; i++) { StudentDTO student = new StudentDTO(); student.Id = dt.Rows[i]["IDStudent"].ToString(); student.Name = dt.Rows[i]["Name"].ToString(); student.Gender = dt.Rows[i]["Gender"].ToString(); student.Email = dt.Rows[i]["Email"].ToString(); student.Phone = dt.Rows[i]["Phone"].ToString(); student.DateofBith = dt.Rows[i]["BirthDay"].ToString(); student.NameClass = dt.Rows[i]["nameClass"].ToString(); student.SchoolYear = dt.Rows[i]["schoolYear"].ToString(); if (dt.Rows[i]["isActive"].ToString() == "T") { student.IsActive = "Active"; } else { student.IsActive = "Deactive"; } result.Add(student); } DataProvider.CloseConnection(con); return(result); }
public static AdminDTO Login(string id, string pw) { string sTruyVan = @"select * from Admin where ID= '" + id + @"' and PassWord = '******'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sTruyVan, con); if (dt.Rows.Count == 0) { return(null); } AdminDTO admin = new AdminDTO(); admin.Id = dt.Rows[0]["ID"].ToString(); admin.Name = dt.Rows[0]["Name"].ToString(); admin.Gender = dt.Rows[0]["Gender"].ToString(); admin.Email = dt.Rows[0]["Email"].ToString(); admin.Phone = dt.Rows[0]["Phone"].ToString(); admin.DateofBith = dt.Rows[0]["BirthDay"].ToString(); admin.Type = "Admin"; DataProvider.CloseConnection(con); return(admin); }
public static List <string> loadListClassToComboBox(string idTeacher, string schoolYear) { string sCommand = @"select nameClass from Assign where IDTeacher = '" + idTeacher + "' and schoolYear = '" + schoolYear + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); List <string> result = new List <string>(); if (dt.Rows.Count <= 0) { return(null); } else { for (int i = 0; i < dt.Rows.Count; i++) { string class_ = dt.Rows[i]["nameClass"].ToString(); result.Add(class_); } DataProvider.CloseConnection(con); return(result.Distinct().ToList()); } }
// Log in va load thông tin public static StudentDTO Login(string id, string pw) { string sTruyVan = @"select * from Student S join Student_Class SC on (S.IDStudent = SC.IDStudent) where S.IsActive ='T' and S.IDStudent= '" + id + @"' and S.PassWord = '******'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sTruyVan, con); if (dt.Rows.Count == 0) { return(null); } StudentDTO student = new StudentDTO(); student.Id = dt.Rows[0]["IDStudent"].ToString(); student.Name = dt.Rows[0]["Name"].ToString(); student.Gender = dt.Rows[0]["Gender"].ToString(); student.Email = dt.Rows[0]["Email"].ToString(); student.Phone = dt.Rows[0]["Phone"].ToString(); student.DateofBith = dt.Rows[0]["BirthDay"].ToString(); student.NameClass = dt.Rows[0]["nameClass"].ToString(); student.SchoolYear = dt.Rows[0]["schoolYear"].ToString(); DataProvider.CloseConnection(con); return(student); }
public static List <ClassDTO> loadListClass(string schoolYear) { string sTruyVan = @"Select* from Class where schoolYear ='" + schoolYear + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sTruyVan, con); if (dt.Rows.Count == 0) { return(null); } List <ClassDTO> result = new List <ClassDTO>(); for (int i = 0; i < dt.Rows.Count; i++) { ClassDTO Class = new ClassDTO(); Class.Name = dt.Rows[i]["nameClass"].ToString(); Class.SchoolYear = dt.Rows[i]["schoolYear"].ToString(); Class.IdMaster = dt.Rows[i]["IDMaster"].ToString(); result.Add(Class); } DataProvider.CloseConnection(con); return(result); }
public static List <string> loadListSubjectToComboBox(string idTeacher, string nameClass, string schoolYear) { string sCommand = @"Select Subject.NameSubject from Subject join Assign on (Subject.IDSubject = Assign.IDSubject) where Assign.IDTeacher = '" + idTeacher + "' and Assign.nameClass = '" + nameClass + "' and schoolYear = '" + schoolYear + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); List <string> result = new List <string>(); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(null); } else { for (int i = 0; i < dt.Rows.Count; i++) { string nameSubject = dt.Rows[i]["nameSubject"].ToString(); result.Add(nameSubject); } DataProvider.CloseConnection(con); return(result.Distinct().ToList()); } }
public static List <TeacherDTO> loadListSubjectTeacher(string schoolYear) { string sCommand = "Select * from Teacher where IDTeacher in (select IDTeacher from Assign where schoolYear = '" + schoolYear + "')"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(null); } else { List <TeacherDTO> result = new List <TeacherDTO>(); int n = dt.Rows.Count; for (int i = 0; i < n; i++) { TeacherDTO teacher = new TeacherDTO(); teacher.Id = dt.Rows[i]["IDTeacher"].ToString(); teacher.Name = dt.Rows[i]["Name"].ToString(); teacher.Gender = dt.Rows[i]["Gender"].ToString(); teacher.Email = dt.Rows[i]["Email"].ToString(); teacher.DateofBith = dt.Rows[i]["BirthDay"].ToString(); teacher.Type = dt.Rows[i]["TypeTeacher"].ToString(); teacher.NamePosition = "Subject Teacher"; teacher.Phone = dt.Rows[i]["Phone"].ToString(); result.Add(teacher); } DataProvider.CloseConnection(con); return(result); } }
// Login va load thong tin public static TeacherDTO Login(string id, string pw) { string sTruyVan = @"select * from Teacher where isActive ='T' and IDTeacher= '" + id + @"' and PassWord = '******'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sTruyVan, con); if (dt.Rows.Count == 0) { return(null); } TeacherDTO teacher = new TeacherDTO(); teacher.Id = dt.Rows[0]["IDTeacher"].ToString(); teacher.Name = dt.Rows[0]["Name"].ToString(); teacher.Gender = dt.Rows[0]["Gender"].ToString(); teacher.Email = dt.Rows[0]["Email"].ToString(); teacher.Phone = dt.Rows[0]["Phone"].ToString(); teacher.DateofBith = dt.Rows[0]["BirthDay"].ToString(); teacher.Type = dt.Rows[0]["TypeTeacher"].ToString(); DataProvider.CloseConnection(con); return(teacher); }