{
public class clsKhoa:clsKetNoi
{
QLSVDataContext dt;
public clsKhoa()
{
dt = GetKN();
}
public IEnumerable<Khoa> LayKhoa()
{
IEnumerable<Khoa> q = from n in dt.Khoas
select n;
return q;
}
public DataTable LayKhoaQuaProcedure()
{
var q = from n in dt.LayDSKhoa()
select n;
DataTable tbl = new DataTable();
tbl.Columns.Add("MaKhoa", typeof(string));
tbl.Columns.Add("TenKhoa", typeof(string));
foreach (var x in q)
{
tbl.Rows.Add(x.MaKhoa, x.TenKhoa);
}
return tbl;
}
}
}
------------------------------
namespace XuLyTier
{
public class clsLop:clsKetNoi
{
QLSVDataContext dt;
public clsLop()
{
dt = GetKN();
}
public IEnumerable<Lop> LopThuocKhoa(string strMK)
{
IEnumerable<Lop> q = from n in dt.Lops
where n.MaKhoa == strMK
select n;
return q;
}
public IEnumerable<Lop> GetLop()
{
//IEnumerable<Lop> q = dt.Lops;
//hoac dung
IEnumerable<Lop> q = from n in dt.Lops
select n;
return q;
}
}
}
--------------------------------
namespace XuLyTier
{
public class clsSinhVien:clsKetNoi
{
QLSVDataContext dt;
public clsSinhVien()
{
dt = GetKN();
}
public IEnumerable<SinhVien> LayHetSV()
{
IEnumerable<SinhVien> q = from n in dt.SinhViens
select n;
return q;
}
public IEnumerable<SinhVien> LaySVThuocLop(string strMLop)
{
IEnumerable<SinhVien> q = from n in dt.SinhViens
where n.MaLop == strMLop
select n;
return q;
}
public SinhVien LaySVBietMa(string strMSV)
{
IEnumerable<SinhVien> q = from n in dt.SinhViens
where n.MaSV == strMSV
select n;
return q.FirstOrDefault();
}
public void Them( SinhVien sv)
{
System.Data.Common.DbTransaction myTran = dt.Connection.BeginTransaction();
try
{
dt.Transaction = myTran;
dt.SinhViens.InsertOnSubmit(sv);
dt.SubmitChanges();
dt.Transaction.Commit();
}
catch (Exception ex)
{
dt.Transaction.Rollback();
throw new Exception("Loi them" + ex.Message);
}
}
public void ThemPro(SinhVien sv)
{
dt.ThemSVPro(sv.MaSV, sv.TenSV, sv.Phai, sv.NgaySinh, sv.DiaChi, sv.DienThoai, sv.MaLop);
}
public void Sua(SinhVien sv)
{
IQueryable<SinhVien> tam = (from n in dt.SinhViens
where n.MaSV ==sv.MaSV
select n);
tam.First().TenSV = sv.TenSV ;
tam.First().DiaChi = sv.DiaChi ;
tam.First().NgaySinh= sv.NgaySinh;
dt.SubmitChanges();
}
public void Xoa(SinhVien sv)
{
dt.SinhViens.DeleteOnSubmit(sv);
dt.SubmitChanges();
}
public void CapNhat()
{
dt.SubmitChanges();
}
}
}
------------------------
namespace XuLyTier
{
public class clsKetNoi
{
QLSVDataContext dt;
public QLSVDataContext GetKN()
{
string kn = @"Data Source=THANGHUIW8\HUIEXPRESS;Initial Catalog=QLDiemSV;Persist Security Info=True;User ID=sa;Password=sapassword";
dt= new QLSVDataContext(kn);
dt.Connection.Open();
return dt;
}
}
}
public class Lop
{
private string strm;
private string strTen;
private string strGV;
//public string MaLop { get; set; }
//public string TenLop { get; set; }
//public string GV { get; set; }
public Lop()
{ }
public Lop(string m, string t, string gv)
{ MaLop = m;TenLop = t;GV = gv;}
public string MaLop
{
get
{//throw new System.NotImplementedException();
return strm;}
set {strm = value;}
}
public string TenLop
{
get{//throw new System.NotImplementedException();
return strTen;}
set{strTen = value;}
}
public string GV
{
get{ //throw new System.NotImplementedException();
return strGV;}
set{strGV = value;}
}
}
----------------------
public class SinhVien
{
public string MaSV { get; set; }
public string HoTen { get; set; }
public string MaLop { get; set; }
public SinhVien(string msv, string ht, string mlop)
{
MaSV = msv;
HoTen = ht;
MaLop = mlop;
}
public SinhVien()
{
}
}
-----------------------
public partial class frmJoin : Form
{
public frmJoin()
{
InitializeComponent();
}
List<Lop> LstLop;
SinhVien[] SV;
private void btnThoat_Click(object sender, EventArgs e)
{
this.Close();
}
private void frmJoin_Load(object sender, EventArgs e)
{
//tạo list cách 1
LstLop = new List<Lop>
{
new Lop("L1", "Đại HTH 6", "Nguyễn Văn Thắng"),
new Lop("L2", "Đại HTH 7", "Phạm Quảng Tri"),
new Lop("L3", "Đại HTH 8", "Nguyễn Thị Hồng Minh"),
};
//tạo list cách 2
SV = new SinhVien[]
{
new SinhVien("01", "Nguyễn Tấn Dũng","L1"),
new SinhVien("02", "Lê Thanh Hải","L1"),
new SinhVien("03", "Lê Trọng Tấn","L3"),
new SinhVien("04", "Ngô Đình Nhu","L2x"),
new SinhVien("05", "Ngô Đình Diệm","L3"),
new SinhVien("06", "Lê Thánh Tông","L1"),
new SinhVien("07", "Lê Thanh Hải","L1"),
new SinhVien("08", "Lê Thi Riêng","L2")
};
}
private void btnLop_Click(object sender, EventArgs e)
{
IEnumerable<Lop> iLop = from l in LstLop
select l;
foreach (Lop item in iLop)
{
ListViewItem lvwItem = new ListViewItem();
lvwItem.Text = item.MaLop;
lvwItem.SubItems.Add(item.TenLop);
lvwItem.SubItems.Add(item.GV);
lvwLop.Items.Add(lvwItem);
}
}
private void btnSV_Click(object sender, EventArgs e)
{
IEnumerable<SinhVien> iSV = from Items in SV
select Items;
foreach (SinhVien item in iSV)
{
ListViewItem lvwItem = new ListViewItem();
lvwItem.Text = item.MaSV;
lvwItem.SubItems.Add(item.HoTen);
lvwItem.SubItems.Add(item.MaLop);
lvwSinhVien.Items.Add(lvwItem);
}
}
private void btnSVLop_Click(object sender, EventArgs e)
{
var LopSV =
from l in LstLop
join isv in SV on l.MaLop equals isv.MaLop
select new { isv.MaSV,isv.HoTen, l.TenLop};
foreach (var item in LopSV)
{
ListViewItem lvwItem = new ListViewItem();
lvwItem.Text = item.MaSV;
lvwItem.SubItems.Add(item.HoTen);
lvwItem.SubItems.Add(item.TenLop);
lvwLopSV.Items.Add(lvwItem);
}
}
private void btnGroup_Click(object sender, EventArgs e)
{
var svg=
from itemsv in SV
group itemsv by itemsv.HoTen into svGroup
orderby svGroup.Key
select new {ht= svGroup, sluong= svGroup.Count()};
lvwGroup.Columns.Clear();
lvwGroup.Items.Clear();
lvwGroup.Columns.Add("Họ Tên SV",200, HorizontalAlignment.Left);
lvwGroup.Columns.Add("Số Lượng", 100, HorizontalAlignment.Right);
lvwGroup.View = View.Details;
foreach (var item in svg)
{
ListViewItem lvwItem = new ListViewItem();
lvwItem.Text = item.ht.Key;
lvwItem.SubItems.Add(item.sluong.ToString());
lvwGroup.Items.Add(lvwItem);
}
}
private void btnGroupSVLop_Click(object sender, EventArgs e)
{
var LSV =
from l in LstLop
join isv in SV on l.MaLop equals isv.MaLop
group new {l, isv} by new {l.TenLop, isv.MaLop, l.GV } into LopSV
select new { LopSV.Key.MaLop, LopSV.Key.TenLop, LopSV.Key.GV , SoLuong = LopSV.Count() };
lvwGroup.Columns.Clear();
lvwGroup.Items.Clear();
lvwGroup.Columns.Add("Mã Lớp", 100, HorizontalAlignment.Left);
lvwGroup.Columns.Add("Tên Lớp", 200, HorizontalAlignment.Left);
lvwGroup.Columns.Add("Giáo Viên", 200, HorizontalAlignment.Left);
lvwGroup.Columns.Add("Số Lượng SV", 100, HorizontalAlignment.Right);//international standards
lvwGroup.View = View.Details;
foreach (var item in LSV)
{
ListViewItem lvwItem = new ListViewItem();
lvwItem.Text = item.MaLop;
lvwItem.SubItems.Add(item.TenLop);
lvwItem.SubItems.Add(item.GV);
lvwItem.SubItems.Add(item.SoLuong.ToString());
lvwGroup.Items.Add(lvwItem);
}
}
}
