public List <Lop> getLop()
{
try
{
string sql = "select lop.MaLop,lop.TenLop,lop.SiSo, lop.NamHoc,Khoi.TenKhoi from dbo.Lop lop join Khoi on Lop.MaKhoi = Khoi.MaKhoi";
SqlDataReader dr = dp.myExecuteReader(sql);
List <Lop> list = new List <Lop>();
int malop, siso, namhoc;
string tenlop, tenkhoi;
while (dr.Read())
{
malop = dr.GetInt32(0);
tenlop = dr.GetString(1);
siso = dr.GetInt32(2);
namhoc = Convert.ToInt32(dr.GetString(3));
tenkhoi = dr.GetString(4);
Lop lop = new Lop(malop, tenlop, siso, namhoc, tenkhoi);
list.Add(lop);
}
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public List <Khoi> getKhoi()
{
try
{
string sql = "SELECT k.MaKhoi, k.TenKhoi,COUNT(k.TenKhoi) as SoLop, hk.NamHoc "
+ "FROM Lop l join Khoi k on l.MaKhoi= k.MaKhoi "
+ "join KhoaHoc hk on l.IdKhoaHoc = hk.IdKhoaHoc "
+ "GROUP BY k.MaKhoi, k.TenKhoi, hk.NamHoc";
SqlDataReader dr = dp.myExecuteReader(sql);
List <Khoi> list = new List <Khoi>();
int maKhoi, soLop, namhoc;
string tenKhoi;
while (dr.Read())
{
maKhoi = dr.GetInt32(0);
tenKhoi = dr.GetString(1);
soLop = dr.GetInt32(2);
namhoc = dr.GetInt32(3);
Khoi khoi = new Khoi(maKhoi, tenKhoi, soLop, namhoc);
list.Add(khoi);
}
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public DataTable SearchByMaNV(DTO_Employee emp)
{
SqlConnection cnn = dp.getConnect();
List <SqlParameter> paras = new List <SqlParameter>();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("sp_TimTheoMaNhanVien", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@MaNV", emp.MaNV));
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dp.DisConnect();
return(dt);
}
public List <MonHoc> getMonHoc()
{
try
{
string sql = "SELECT * from MonHoc";
SqlDataReader dr = dp.myExecuteReader(sql);
List <MonHoc> list = new List <MonHoc>();
string maMonHoc, tenMonHoc;
int idMonHoc;
while (dr.Read())
{
idMonHoc = dr.GetInt32(0);
maMonHoc = dr.GetString(1);
tenMonHoc = dr.GetString(2);
MonHoc monhoc = new MonHoc(idMonHoc, maMonHoc, tenMonHoc);
list.Add(monhoc);
}
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public List <QuyDinh> getQuyDinh()
{
try
{
string sql = "select * from QuyDinh";
SqlDataReader dr = dp.myExecuteReader(sql);
List <QuyDinh> list = new List <QuyDinh>();
string maQuyDinh, thongTin;
int id;
while (dr.Read())
{
id = dr.GetInt32(0);
maQuyDinh = dr.GetString(1);
thongTin = dr.GetString(2);
QuyDinh qd = new QuyDinh(id, maQuyDinh, thongTin);
list.Add(qd);
}
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public List <NhanVien> LoadData()
{
List <NhanVien> list = new List <NhanVien>();
try
{
dp.Connect();
string sql = "Select * from NhanVien";
SqlDataReader dr = dp.ExcuteReader(sql);
while (dr.Read())
{
NhanVien nv = new NhanVien();
nv.maNV = dr.GetInt32(0);
nv.hoNV = dr.GetString(1);
nv.ten = dr.GetString(2);
nv.diaChi = dr.GetString(3);
nv.sdt = dr.GetString(4);
list.Add(nv);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (InvalidOperationException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
return(list);
}
public List <DTO_Order> GetMenu()
{
List <DTO_Order> list = new List <DTO_Order>();
int mamonnuoc;
decimal giatien;
string tenmonnuoc, donvi;
//dp.Connect();
try
{
string sql = "SELECT * FROM MonNuoc";
SqlDataReader dr = dp.ExecuteReader(sql);
while (dr.Read())
{
mamonnuoc = dr.GetInt32(0);
tenmonnuoc = dr.GetString(1);
giatien = dr.GetDecimal(2);
donvi = dr.GetString(3);
DTO_Order emp = new DTO_Order(mamonnuoc, tenmonnuoc, giatien, donvi);
list.Add(emp);
}
dr.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public List <Lop> getLop()
{
try
{
string sql = "select Lop.*, h.NamHoc, Khoi.TenKhoi from dbo.Lop, dbo.KhoaHoc_Lop_Khoi h , Khoi where dbo.Lop.MaLop = h.MaLop and Khoi.MaKhoi = h.MaKhoi";
SqlDataReader dr = dp.myExecuteReader(sql);
List <Lop> list = new List <Lop>();
int malop, siso, namhoc, makhoi;
string tenlop;
while (dr.Read())
{
malop = dr.GetInt32(0);
tenlop = dr.GetString(1);
siso = dr.GetInt32(2);
namhoc = dr.GetInt32(3);
makhoi = dr.GetInt32(4);
Lop lop = new Lop(malop, tenlop, siso, namhoc, makhoi);
list.Add(lop);
}
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public List <HocSinh> getStudents()
{
string sql = "SELECT hs.MaHS,hs.HoTen,hs.GioiTinh,hs.NgaySinh,hs.DiaChi,hs.SDT,hs.Email,hs.TinhTrang,hs.MaLop, l.TenLop from HocSinh hs, Lop l where hs.MaLop = l.MaLop";
int maHS, maLop;
DateTime ngaySinh;
string hoTen, gioiTinh, diaChi, sdt, email, tenLop;
bool tinhTrang;
List <HocSinh> list = new List <HocSinh>();
dp.Connect();
try
{
SqlDataReader dr = dp.myExecuteReader(sql);
while (dr.Read())
{
maHS = dr.GetInt32(0);
hoTen = dr.GetString(1);
gioiTinh = dr.GetString(2);
ngaySinh = dr.GetDateTime(3);
diaChi = dr.GetString(4);
sdt = dr.GetString(5);
email = dr.GetString(6);
tinhTrang = (bool)dr[7];
maLop = dr.GetInt32(8);
tenLop = dr.GetString(9);
HocSinh hs = new HocSinh(maHS, hoTen, gioiTinh, ngaySinh, diaChi, sdt, email, tinhTrang, maLop, tenLop);
list.Add(hs);
list.ForEach(x =>
{
x.TinhTrangText = x.TinhTrang ? "Đang học" : "Thôi học";
});
}
dr.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
public List <DTO_Invoice> GetInvoiceDAO(string sql)
{
List <DTO_Invoice> list = new List <DTO_Invoice>();
SqlCommand cmd = new SqlCommand(sql, dp.cnn);
SqlDataReader dr;
int MaHoaDon;
int MaMonNuoc;
int MaBan;
dp.Connect();
//SqlConnection cnn = dp.getConnect();
//cnn.Open();
try
{
dr = cmd.ExecuteReader();
//SqlDataReader dr = dp.ExecuteReader(sql);
while (dr.Read())
{
MaHoaDon = dr.GetInt32(0);
MaMonNuoc = dr.GetInt32(1);
MaBan = dr.GetInt32(2);
DTO_Invoice prod = new DTO_Invoice(MaHoaDon, MaMonNuoc, MaBan);
list.Add(prod);
}
return(list);
}
catch (SqlException ex)
{
return(null);
throw ex;
}
finally
{
dp.DisConnect();
}
}
public int IsLogin(DTO_Login lg)
{
try
{
DataProvider dp = new DataProvider();
dp.getConnect();
dp.Connect();
SqlCommand cmd = dp.CountLogin();
cmd.Parameters.Add(new SqlParameter("@username", lg.userName));
cmd.Parameters.Add(new SqlParameter("@password", lg.passWord));
int kq = (int)cmd.ExecuteScalar(); //tra ve so nguyen 1, 0
dp.DisConnect();
return(kq); // đăng nhập thàng công = 1, ko thành công = 0;
}
catch (SqlException ex)
{
throw ex;
}
}