public List <HoSoHocSinh> layHoSoHocSinh()
{
List <HoSoHocSinh> arr = new List <HoSoHocSinh>();
KetNoiCSDL.moKetNoi();
string get = "select * from HoSoHocSinh";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
HoSoHocSinh x = new HoSoHocSinh(dr["maHocSinh"] + "", dr["hoTen"] + "", dr["ngaySinh"] + "", dr["gioiTinh"] + "", dr["diaChi"] + "",
float.Parse(dr["diemVaoTruong"] + ""), dr["hoTenBoMe"] + "", dr["soDienThoai"] + "", dr["maLop"] + "");
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public DataTable layDiemCoTenHocSinh(string maLop, string maMon)
{
KetNoiCSDL.moKetNoi();
string get = "select Diem.maHocSinh, hoTen, Diem.maMon, tenMon, diemTBKy1, diemTBKy2 from Diem inner join HoSoHocSinh on HoSoHocSinh.maHocSinh = Diem.maHocSinh inner join MonHoc on Diem.maMon = MonHoc.maMon inner join Lop on Lop.maLop = HoSoHocSinh.maLop where Lop.maLop = @maLop and MonHoc.maMon = @maMon";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLop", maLop);
cmd.Parameters.AddWithValue("maMon", maMon);
SqlDataReader dr = cmd.ExecuteReader();
DataTable tb = new DataTable();
tb.Load(dr);
KetNoiCSDL.dongKetNoi();
return(tb);
}
public List <LichSuGia> layTatCaLichSuGia()
{
List <LichSuGia> arr = new List <LichSuGia>();
KetNoiCSDL.moKetNoi();
string get = "select * from LichSuGia";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LichSuGia x = new LichSuGia(dr["maHang"] + "", ((DateTime)dr["ngayBatDau"]).ToString("dd/MM/yyyy"), ((DateTime)dr["ngayKetThuc"]).ToString("dd/MM/yyyy"),
float.Parse(dr["donGia"] + ""), ((DateTime)dr["ngayCapNhat"]).ToString("dd/MM/yyyy"));
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <NhanVien> layNhanVienTheoMa(string s)
{
List <NhanVien> arr = new List <NhanVien>();
KetNoiCSDL.moKetNoi();
string get = "select * from NhanVien where maNhanVien=@maNhanVien";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhanVien", s);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NhanVien x = new NhanVien(dr["maNhanVien"] + "", dr["hoTen"] + "", dr["diaChi"] + "", dr["soDienThoai"].ToString());
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <Lop> layLopTheoDieuKien(string maLop)
{
List <Lop> arr = new List <Lop>();
KetNoiCSDL.moKetNoi();
string get = "select * from Lop where maLop=@maLop";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLop", maLop);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Lop x = new Lop(dr["maLop"] + "", dr["tenLop"] + "", dr["nienKhoa"] + "", int.Parse(dr["siSo"] + ""), dr["maCBGV"] + "");
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <MonHoc> layMonHocTheoDieuKien(string maMon)
{
List <MonHoc> arr = new List <MonHoc>();
KetNoiCSDL.moKetNoi();
string get = "select * from MonHoc where maMon = @maMon";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maMon", maMon);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
MonHoc x = new MonHoc(dr["maMon"] + "", dr["tenMon"] + "", float.Parse(dr["soTiet"] + ""));
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <LoaiHang> timLoaiHang(LoaiHang lh)
{
List <LoaiHang> arr = new List <LoaiHang>();
KetNoiCSDL.moKetNoi();
string get = "select * from LoaiHang where maLoai=@maLoai";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maLoai", lh.MaLoai);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LoaiHang loaiHang = new LoaiHang(dr["maLoai"].ToString(), dr["tenLoai"].ToString(), dr["ghiChu"].ToString());
arr.Add(loaiHang);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <HoaDon> layTatCHD()
{
List <HoaDon> list = new List <HoaDon>();
KetNoiCSDL.moKetNoi();
string get = " select * from HoaDon";
SqlCommand sqlCommand = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
HoaDon hoaDon = new HoaDon(sqlDataReader["maHoaDon"].ToString(),
sqlDataReader["maKhachHang"].ToString(),
sqlDataReader["ngayLap"].ToString(), sqlDataReader["maNhanVien"].ToString());
list.Add(hoaDon);
}
KetNoiCSDL.dongKetNoi();
return(list);
}
public List <HoaDonChiTiet> layTatCHDCT()
{
List <HoaDonChiTiet> list = new List <HoaDonChiTiet>();
KetNoiCSDL.moKetNoi();
string get = " select * from HoaDonChiTiet";
SqlCommand sqlCommand = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
HoaDonChiTiet hoaDonChiTiet = new HoaDonChiTiet(sqlDataReader["maHoaDon"].ToString(),
sqlDataReader["maHang"].ToString(), Convert.ToInt32(
sqlDataReader["soLuong"]));
list.Add(hoaDonChiTiet);
}
KetNoiCSDL.dongKetNoi();
return(list);
}
public List <NhaCungCap> layNhaCungCapTheoMa(string s)
{
List <NhaCungCap> arr = new List <NhaCungCap>();
KetNoiCSDL.moKetNoi();
string get = "select * from NhaCungCap where maNhaCungCap=@maNhaCungCap";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maNhaCungCap", s);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NhaCungCap ncc = new NhaCungCap(dr["maNhaCungCap"] + "", dr["tenNhaCungCap"] + "", dr["diaChi"] + "", dr["soDienThoai"].ToString());
arr.Add(ncc);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <Hang> layHang()
{
List <Hang> arr = new List <Hang>();
KetNoiCSDL.moKetNoi();
string get = "select * from Hang";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Hang x = new Hang(dr["maHang"] + "", dr["tenHang"] + "", dr["donViTinh"] + "",
float.Parse(dr["donGia"] + ""), dr["maLoai"] + "", int.Parse(dr["soLuongCo"] + ""),
dr["maNhaCungCap"] + "");
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public void themHoSoHocSinh(HoSoHocSinh hs)
{
string sqlSelect = "insert into HoSoHocSinh values(@maHocSinh ,@hoTen ,@ngaySinh, @gioiTinh, @diaChi, @diemVaoTruong, @hoTenBoMe, @soDienThoai, @maLop)";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlSelect, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHocSinh", hs.MaHocSinh);
cmd.Parameters.AddWithValue("hoTen", hs.HoTen);
cmd.Parameters.AddWithValue("ngaySinh", hs.NgaySinh);
cmd.Parameters.AddWithValue("gioiTinh", hs.GioiTinh);
cmd.Parameters.AddWithValue("diaChi", hs.DiaChi);
cmd.Parameters.AddWithValue("diemVaoTruong", hs.DiemVaoTruong);
cmd.Parameters.AddWithValue("hoTenBoMe", hs.HoTenBoMe);
cmd.Parameters.AddWithValue("soDienThoai", hs.SoDienThoai);
cmd.Parameters.AddWithValue("maLop", hs.MaLop);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public void suaHoSoHocSinh(HoSoHocSinh hs)
{
string sqlUpdate = "update HoSoHocSinh set hoTen = @hoTen ,ngaySinh=@ngaySinh,gioiTinh= @gioiTinh,diaChi= @diaChi ,diemVaoTruong= @diemVaoTruong ,hoTenBoMe= @hoTenBoMe ,soDienThoai= @soDienThoai ,maLop= @maLop where maHocSinh =@maHocSinh ";
KetNoiCSDL.moKetNoi();
SqlCommand cmd = new SqlCommand(sqlUpdate, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maHocSinh", hs.MaHocSinh);
cmd.Parameters.AddWithValue("hoTen", hs.HoTen);
cmd.Parameters.AddWithValue("ngaySinh", hs.NgaySinh);
cmd.Parameters.AddWithValue("gioiTinh", hs.GioiTinh);
cmd.Parameters.AddWithValue("diaChi", hs.DiaChi);
cmd.Parameters.AddWithValue("diemVaoTruong", hs.DiemVaoTruong);
cmd.Parameters.AddWithValue("hoTenBoMe", hs.HoTenBoMe);
cmd.Parameters.AddWithValue("soDienThoai", hs.SoDienThoai);
cmd.Parameters.AddWithValue("maLop", hs.MaLop);
cmd.ExecuteNonQuery();
KetNoiCSDL.dongKetNoi();
}
public List <CanBoGiaoVien> layCBGV()
{
List <CanBoGiaoVien> arr = new List <CanBoGiaoVien>();
KetNoiCSDL.moKetNoi();
string get = "select * from CanBoGiaoVien";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CanBoGiaoVien x = new CanBoGiaoVien(dr["maCBGV"] + "", dr["hoTen"] + "", dr["diaChi"] + "",
dr["soDienThoai"] + "", dr["taiKhoan"] + "", dr["matKhau"] + "",
dr["loaiTaiKhoan"] + "");
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <LoaiHang> danhSachMaLoaiHang(string ma)
{
List <LoaiHang> list = new List <LoaiHang>();
KetNoiCSDL.moKetNoi();
string sql = "select LoaiHang.maLoai, tenLoai from LoaiHang";
SqlCommand cmd = new SqlCommand(sql, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("ma", ma);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LoaiHang lh = new LoaiHang(dr["maLoai"].ToString(), dr["tenLoai"].ToString());
list.Add(lh);
}
KetNoiCSDL.dongKetNoi();
list.Count();
return(list);
}
public List <KhachHang> layTatCaKhachHang(string s)
{
List <KhachHang> arr = new List <KhachHang>();
KetNoiCSDL.moKetNoi();
string get = "select * from KhachHang where maKhachHang = @maKhachHang";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("maKhachHang", s);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
KhachHang x = new KhachHang(dr["maKhachHang"] + "", dr["hoTen"] + "", dr["diaChi"] + "", dr["soDienThoai"] + "");
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <KhachHang> layTatCaKhachHang()
{
List <KhachHang> arr = new List <KhachHang>();
KetNoiCSDL.moKetNoi();
string get = "select * from KhachHang";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
DataTable tb = new DataTable();
SqlDataAdapter adt = new SqlDataAdapter();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
KhachHang x = new KhachHang(dr["maKhachHang"] + "", dr["hoTen"] + "", dr["diaChi"] + "", dr["soDienThoai"] + "");
arr.Add(x);
}
KetNoiCSDL.dongKetNoi();
return(arr);
}
public List <NhaCungCap> danhSachMaNCC(string ma)
{
List <NhaCungCap> arr = new List <NhaCungCap>();
KetNoiCSDL.moKetNoi();
string get = "select NhaCungCap.maNhaCungCap, tenNhaCungCap from NhaCungCap";
SqlCommand cmd = new SqlCommand(get, KetNoiCSDL.connect);
cmd.Parameters.AddWithValue("ma", ma);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NhaCungCap ncc = new NhaCungCap(dr["maNhaCungCap"] + "", dr["tenNhaCungCap"] + "");
arr.Add(ncc);
}
arr.Count();
KetNoiCSDL.dongKetNoi();
return(arr);
}
